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

 

[Olaf Doschke]

Hi Stanley,

First: Image fields are not to be used, they will deprecate, use a blob field or filestream.

The difficulty of how to use such fields from vfp remains, but stay away from image.

Obviously you don't use textmerge functionality to put in binary data, use parameterization (and this also should work for Image for now, even using Blob on the VFP side):

lwImage = Cast(filetostr(getpic()) as Blob)

* just to show the pic works:
_screen.addobject("image1","image")
_screen.image1.pictureval = lwImage
_screen.image1.visible = .t.

TEXT to m.lcSqlStr Noshow
Insert Into [User]
(
[county_id],
[USER_ID],
[USER_NAME],
[photo],
[is_deleted]
)
     VALUES
(
?pcCountyId,
?ui,
?un,
?lwBlob,
?lnIsDeleted
) 
EndText

lnSqlResults = SQLExec(ConnectionHandle, m.lcSqlStr)

?variable in an sql string sends this variable seperate from the sql command to sqlserver, which allows to "embed" (in this sense) binary data. Besides that you can have a static sql command this way. I assume ui, un, pcCountrID lnIsDeleted are vars or fields, you can pass them the same way, and it's recommended, not just to have a static command, but also because of sql injection, eg in the simplest case through single quotes in some of your variables, which would break the correctness of the sql!

Bye, Olaf.

 

[stanlyn]

>> First: Image fields are not to be used, they will deprecate, use a blob field or filestream.
Are you talking about SQL field types here? If so, I was thing of varimage(max) which is the new image field type that is replacing the image type. I have read some stuff stating that this new 2008 sql type is problematic with vfp9... Is this true?

If you were speaking of VFP field types, then it probably won't matter here as I'm not writing it into a VFP table here, only grabbing it from a physical harddisk image.jpg file.


>> The difficulty of how to use such fields from vfp remains, but stay away from image.

Once again, VFP or SQL image type?


>> ?lwBlob,
And this will pass the binary value to the server where it will get merged into the insert statement?? If so, then wonderful...

Thanks,
Stanley

 

[Olaf Doschke]

I was only talking about SQL Server Image Field type, there is no Foxpro Image Field type you could stay away from.

Foxpro has General for ages, which is also to avoid and since version 8 or 9 we have foxpro Blob fields, which SQL Server also has.

In SQL Server the fields dedicated to binary large objects are blob (well, that means binary large object) or filestream, the latter does store the files seperate on the server side. Blob is fine, as both sql server and foxpro offer it.

Bye, Olaf.

 

[Olaf Doschke]

The only thing to add is, that the blob field type is called varbinary(max) on the sql server side.
And yes, it works via ?variable.

Bye, Olaf.

 

[JRB-Bldr]

While Olaf's method is the best to use for what you have stated you want to do, you should question if that is the best way to handle the file information.

A much better way to handle large file types (like image files, etc.) is to store them in a Windows directory somewhere.

Then store the Path + Filename in the data table and not the file contents.

With that information stored, an application can still access the file itself in whatever manner it needs.

Good Luck,
JRB-Bldr

 

[stanlyn]

Hi jrbbldr,

>> A much better way to handle large file types (like image files, etc.) is to store them in a Windows directory somewhere.
>>Then store the Path + Filename in the data table and not the file contents.

I've been doing it your way (storing only the filename path) in my vfp tables for the last 12 years. Now that SQL is being used to replace VFP, the arguments to keep only the pathing info is deminished. Here are some of my arguments for changing to a "include the image in the table" layout...

1. In VFP, the 2gb limit for the .fpt seriously limited how many images could be saved.

2. Live backups are not available in VFP.

3. Security of the image files, as it is too easy for a competitor with the agreeance of the customer to lift the drive and circumvent ntfs perms structures and have all your images. And if you made it real easy for them by naming them in a way that makes sense to humans, then you can really be left in the cold... Having them as part of the database greatly adds to the security model. I could do things like adding an additional header to the image that would break the image unless removed prior to using it to populate an image object...

4. Static Backups would be extremely faster in SQL as the system I/O in VFP is really taxed by the millions of file open/read/close operations when backing up individual image files as we currently do in VFP. Currently, to backup 2.6 million 60kb images on an I7 raid 5 system, it takes more that 24 hours to do. It also requires human intervention at times, so the process has to be "hand held", due to system prompts if done via windows explorer... It takes between 1-2 hours to do it via Acronis where sector reading is occuring and the massive i/o of individual files are avoided. I understand (and not tested yet) that SQL static backup speed would be similar to Acronis's speed.

5. I'm changing from a local model to a remote model for web access, and somehow I believe it to be less trouble if the image is contained within the database.

6. SQL is supposedly stable enough...


If you see any thing here that is questionable, or I should look at further, then please advise. I have not made the final decisions yet, but have been weighing the pros and cons as best as I can when comparing to the experiences of the last 12 years of doing this.

Can you thing of other pros and cons? Please advise...

Thanks, Stanley

 

[JRB-Bldr]

Stanley -

You suggested in #1 above saving the image file data in the Memo (fpt) field?
ABSOLUTELY NOT !
Save the Path + Filename in a Character field and then when needed have your application go get the file from the appropriate Windows directory and use it.

Despite no longer having the 2GB data table limitation within SQL Server data tables, you still will most likely experience table BLOAT with the entire contents of the image file within the table itself.

And that BLOAT can diminish SQL Server response times in returning data, SQL Server backup times, etc.

And, since the contents of an image files themselves are not likely to be very dynamic the backup issue seems moot.

That functionality should not be handled within VFP anyway, but with a more flexible 'real' backup utility running concurrently such as Acronis, etc. where 'Live' backups of the files themselves can be configured and scheduled.
One of my clients is doing 'Live file backups' of each of their 6 servers every 15 minutes - even to an off-site location.

Admittedly security could be a concern, but it can be addressed by the Network Administrator limiting user rights to the image file storage directory.

So, while the issues you list are valid, I'd still recommend staying with your previous model, but addressing those various issues in another (in my opinion - better) manner.

Maybe other gurus will have their own viewpoints to share.

Good Luck,
JRB-Bldr

 

[stanlyn]

Hi Olaf, when I tried using the suggestions in these 3 ways, I get 3 different errors... Any ideas?


ERROR run1...
In the textmerge script I have
'<<ALLTRIM(UPLOAD.Chr99Str05)>>',
'<<?lmImageData>>',
<<lnIsDeleted>>

which produces this when evaluated...
[IMAGE1] = '', when evaluated...

***************************************************

ERROR run2...
In the textmerge script I have
'<<ALLTRIM(UPLOAD.Chr99Str05)>>',
<<?lmImageData>>,
<<lnIsDeleted>>

which produces this when evaluated...
[SEC_ID] = '',
[IMAGE1] = ,
[is_deleted] = 0

***************************************************

ERROR run3...
In the textmerge script I have

'<<ALLTRIM(UPLOAD.Chr99Str05)>>',
?lmImageData,
<<lnIsDeleted>>

which produces this when evaluated...

[SEC_ID] = '',
[IMAGE1] = ?lmImageData,
[is_deleted] = 0

 

[Olaf Doschke]

Third is correct, the result is ?variable, there is no textmerge nor print of the image data, this reamins in the variable,

SQLEXEC() then sends BOTH the sql command string AND the data within the variables.

Bye, Olaf.

 

[stanlyn]

The third item as well as the other 2 items produces a < 0 result from the sqlexec command which is some sort of an error...

lnSqlResults = SQLExec(ConnectionHandle, m.lcSqlStr)
If lnSqlResults < 0

So, wonder what is making this fail? If I remove the line
[IMAGE1] = ?lmImageData,
it does not fail...

Also note that my variable "lmImageData" contains the data from the image by using the file low level routines in which I am also saving into a VFP blob field. That part is working OK now and for the past 12 years.

Would the image format be of any concern, of would this routine just stuff the binary variable contents into the sql field without regard to the image file format (.jpg, .bmp, .eps, .xyz as in non-standard image formats)?

How does one trace/troubleshoot this, as it is failing on the sql side?

 

[Olaf Doschke]

Use AERROR after SQLExec returns <0 to get more error information.

Is the variable still in scope when you execute the sql? Eg setting a local variable, then calling a method calling sqlexec() would error on that, of course.

Bye, Olaf.

 

[stanlyn]

Yes the variable is in scope and contains image data.

I'm getting an error message: "Connectivity error. [Microsoft ODBC SQL Server Driver][SQL Server]Operand type clash: text is incompatible with varbinary(max)"

I'm going to switch the sql field type to the depreciated type of "image" and see if that works.

 

[stanlyn]

OK, same error except instead of incompatibility with varbinary(max) its image...

Any other ideas?

 

[Olaf Doschke]

What version of sql server are you using and why did you switch from image to text and back to image, when you should switch to blob aka varbinary(max)?

Bye, Olaf.

 

[stanlyn]

2008r2

>> why did you switch from image to text and back to image

I did not... I initially tried type "varbinary(max)" and after the error claims incompatibility, I switched to type "image" which also failed with incompatibility. I have never tried text or variation of text.

Stanley

 

[Olaf Doschke]

Passing ?lwBlob worked for me with sql2008r2 using the "SQL Server" Driver version 2000.85.1132.00 from 14.04.2008.

You may try to update your ODBC driver or switch to another one, eg "SQL Server Native Client 10.0" instead of "SQL Server".

Bye, Olaf.

 

[Olaf Doschke]

I think you use an older driver and that driver transports the foxpro blob variable as image or text, which is causing the trouble server side.

Bye, Olaf.

 

[stanlyn]

I'm using the exact odbc driver as you...

 

[Olaf Doschke]

Is your database running in some compatibility mode, perhaps?

Bye, Olaf.