Inserting Images using pointers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Inserting Images using pointers

Author

Topic

cuetan
Starting Member

8 Posts

Posted - 2002-05-11 : 12:34:20

I was using this statement to try using the pointer to inserting an image:

SET QUOTED_IDENTIFIER OFF
GO
USE Wrox_Golf_Results
GO
INSERT INTO Players
(Society_Group, Player_First_Name, Player_Last_Name, Photo_Image, Date_Of_Birth, Has_Left_The_Club, Points_Scored, Games_Played)
VALUES (1, "Annette", "Kelly", xFFFFFFF, "05/18/1978", "0", "2", "1")

Then I use this as a pointer:

DECLARE @Pointer_Value varbinary(16)
SELECT @Pointer_Value = TEXTPTR(Photo_Image)
FROM Players
WHERE Player_Id = 1
WRITETEXT Players.Photo_Image @Pointer_Value
'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Wrox_Golf_Results\Images\ak.bmp'

I get this error message:

Server: Msg 7133, Level 16, State 2, Line 5
NULL textptr (text, ntext, or image pointer) passed to WriteText function.

Can anyone please tell me what am I doing wrong here?

Please, omnce again thanks in advance.

Chetan

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-11 : 16:36:49

Don't insert a value or pointer into the image column. Leave the Photo_Image column out of the column list, INSERT the row, then SELECT the pointer like you are now. Trying to insert an explicit pointer value will only confuse SQL Server. As in any language that uses pointers, they are never meant to be handled or modified directly.

cuetan
Starting Member

8 Posts

Posted - 2002-05-11 : 18:07:20

robvolk: I still cant do what you recommended. I may be doing something wrong here. Would it be possible for you to explain the steps or just the code to skip. I am sorry I am new at this and am just learning the ropes. Would it be possible for you to write exactly what I need to do? I would really appreciate your patience and time.

Thanks in advance.

Chetan

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-11 : 21:08:16

What you really should do is NOT insert the image directly into the database. If you've lurked around SQL Team a bit you'll have found out that we don't recommend doing that, it's not a good idea, and you don't gain anything. Keep it as a disk file and store the path to that file in your table, it's easier and more flexible. Here's some issues that can come up if you DO store the image in the database:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10477

If you absolutely insist on storing the image in the database, there's a link in the SQL Team FAQ that discusses how to do it.

cuetan
Starting Member

8 Posts

Posted - 2002-05-11 : 22:13:10

Well I am not trying to store the image on the database. I have used a path to map the file and hence using pointers. Isn't that what you are getting at? Is there something in my code that says that the image is stored in the database directly. I appreciate your help.

Is there a better way to write a pointer? Cause I have exhausted my resources in order for a efficient way to write a pointer for an image that would be on file level instead of the database itself.

Thanks Once again

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-11 : 22:22:48

See, that's what happens when I MISREAD YOUR QUESTION!

INSERT INTO Players
(Society_Group, Player_First_Name, Player_Last_Name, Date_Of_Birth, Has_Left_The_Club,
Points_Scored, Games_Played)
VALUES (1, 'Annette', 'Kelly', '05/18/1978', '0', '2', '1')

DECLARE @Pointer_Value varbinary(16)
SELECT @Pointer_Value = TEXTPTR(Photo_Image)
FROM Players
WHERE Player_Id = 1
WRITETEXT Players.Photo_Image @Pointer_Value
'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Wrox_Golf_Results\Images\ak.bmp'

That should work. Unless you are using SQL 6.5, you don't need to text column to store long file names. You can use a varchar column, up to 8000 characters, and if I remember correctly a full path (even a UNC) either can't, or will very rarely, exceed 255 characters, so you could also use varchar in SQL 6.5 to store the file name.

Just for laughs, see if this works:

INSERT INTO Players
(Society_Group, Player_First_Name, Player_Last_Name, Photo_Image, Date_Of_Birth, Has_Left_The_Club, Points_Scored, Games_Played)
VALUES (1, 'Annette', 'Kelly', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Wrox_Golf_Results\Images\ak.bmp', '05/18/1978', '0', '2', '1')

If Photo_Image is a text or ntext column, that should work (will not work correctly if Photo_Image is an image column type) Also, don't use double quotes to delimit strings in SQL Server, use single quotes always.

If none of these work, you'll have to post the DDL of your table(s) for us to go any further with it.

cuetan
Starting Member

8 Posts

Posted - 2002-05-12 : 02:37:41

Well I would use your method but the fact is that I have the data type as image. Now I am trying to change it to text or ntext but I keep on getting an error message saying, "Conversion from 'image' to 'text' is not supported on the connected database. How would I change this?

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-12 : 10:02:53

Unless you already have data in that column that you must preserve, just use ALTER TABLE...DROP COLUMN and then ALTER TABLE...ADD COLUMN to put it back in as a text column.

Why not use varchar? If you're storing file and path information only, it can hold it, and it is FAR easier and more flexible to use than text data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources