Insert VFP LowLevelFile to SQL Image

[stanlyn]

Hi,

How can I insert an image.jpg file into a Sql 2008r2 image field? Using VFP9sp2 as the local client where the actual disk based image file is, I need to read the image file and save it into a variable for inclusion in a insert script that I create using textmerge. This script will be executed against a remote Sql server using the sqlexec command. It needs to be done purely in sql on the server side as there is only sql server running remotely. Is there a way to pass the variable that contains the image file through a converter (??2hex, ??2binary, ??2str, or some other converter so that it can be passed into the textmerge script, which actually supports text or similar? I'm trying to avoid using some other transport mechanism for delivery of the image file to the sql server, and the processing it on the server side, by including it directly in the sql insert statement. I'm also using a SQL Server native client connection string. Below is what I'm doing...

TEXT to m.lcSqlStr textmerge noshow
Insert Into [User]
(
[county_id],
[USER_ID],
[USER_NAME],
[photo],
[is_deleted]
)
VALUES
(
'<<pcCountyId>>',
'<<ui>>',
'<<un>>',
'<<data_from_image_file>>',
<<lnIsDeleted>>
)
EndText

lnSqlResults = SQLExec(ConnectionHandle, m.lcSqlStr)

Thanks, Stanley

 

[stanlyn]

I'm getting around the prev error by first issuing
lwImage = Cast(lmImageData as Blob)

I do not know if this is producing a valid image on the sql side.

On the VFP side issuing
Replace UPLOAD.Memo03 With lmImageData creates and stores a perfect image in the vfp table. Memo3 is vfp type M(4) which appears to be memo text...

If M(4) is memo text, what does the type() function return for memo binary? If you don't know, I can create a test for it, as I did not see it listed in the types and vartypes help sections.

So, if I cast this as a blob, would I consider this the actual files bytes written to the table as a native image file?

And would there need any conversions needed when binding to an image control?

 

[Olaf Doschke]

I'm getting around the prev error by first issuing
lwImage = Cast(lmImageData as Blob)

That's what my sample code included, see first answer:
lwImage = Cast(filetostr(getpic()) as Blob)

Memo(binary) is the same as M nocptran. The type() function has no different letter for that. A Blob field on VFP side is, well "blob" or W, that's why I named the variable lwImage.

Even in a codepage 1252 dbf memo fields don't necessarily translate binary data you put in there. Maybe because they are stored in the seperate fpt file, I don't know, but don't trust them to store the binary data unchganged, use M nocptran or W fields on the foxpro side.

Bye, Olaf.

 

[Olaf Doschke]

Actually it's quite simple: dbfs have a codepage, and that typically is the same as the current codepage, so memo fields normally act the same way as memo(binary) or nocptran (no codepage translation) fields, as there is no code page difference to overcome.

Still in regard to the outside world, eg spt, it makes a huge difference, if you transfer a variable interpreted as large text or a variable casted as blob.

Bye, Olaf.

 

[stanlyn]

Thanks Olaf for all you've done. I have it working now. I also confirmed that the actual image file size is equal to the casted variable as blob for sql, which is also equal to vfp's low level read to a text memo field... All 3 sizes are identical...

Thanks again, Stanley