Sql table image to VFP

[kuma.lk]

Dear All,

i have ms sql table contain user_num and user_picture( like in image)

how can extract .jpg image to vfp form from sql table

Thank you

 

[Olaf Doschke]

You could set an image controls PictureVal property to a blob field you retrieve from that MSSQL column, or you can STRTOFILE() that blob value to a file on hdd and then use that filename for Picture or use it in any way other controls (ActiveX) use pictures, eg via LOADPICTURE(). But the simplest case surely is the PictureVal property.

What you may need to retrieve blobs is a) VFP9 and b) CURSORSETPROP("MapBinary",.T.,0), so binary ODBC types are converted to VFP binary types as Blob is. doing this once (per datasession) for workarea number 0 means activating such binary mapping for every remote query result from remote views, SQL Passsthrough (SQLExec) or CursorAdapters.

Bye, Olaf.

 

[kuma.lk]

Thanks for ur reply.

i am using VFP 6

can you give me sample codes for it

Thank you

 

[Olaf Doschke]

Well, in VFP9 you only have the second option to save as file first.

It was long since I used VFP6, so I don't remember what you can and can't do in many aspects. You can try to use the Code Rick Strahl here gives as the function "ReadBinaryField" for VFP8, that may work for 6, too:

https://weblog.west-wind.com/posts/2006/Aug/06/Reading-Sql-Server-ImageBlob-data-in-Visual-FoxPro-9

First store this as

FUNCTION ReadBinaryField
LPARAMETERS lcField,lcTable,lcWhereClause
LOCAL lcTFileName, lcFileName, lcAlias, lcResult,llField, lnResult
 
IF EMPTY(lcWhereClause)
   lnResult = this.Execute("select " + lcField + " from " + lcTable)
ELSE  
   lnResult = this.Execute("select " + lcField + " from " + lcTable + " where " + lcWhereClause)
ENDIF  

IF lnResult < 1
   RETURN ""
ENDIF  
  
lcTFileName =  SYS(2023) + "\"  + SYS(2015) + ".dbf"
lcFileName = DBF()
lcAlias = ALIAS()
 
*** Copy out the data - cursor deletes automatically
COPY TO (lcTFileName)
 
*** Close the cursor
SELECT (lcAlias)
USE

* Change the flag in the general field
llFile = fopen(lcTFilename,12)
fseek(llFile,43)
fwrite(llFile,'M')
fclose(llFile)
 
*** Reopen the copied file
USE (lcTFileName) ALIAS __TImage EXCL
 
*** Retrieve the binary field value
lcResult = EVAL("__Timage."  + lcField)
 
*** Close and Erase the temp file
USE
ERASE (FORCEEXT(lcTfileName,"*")
 
IF !EMPTY(lcAlias)
  SELECT (lcAlias)
ENDIF
 
RETURN lcResult

SET PROCEDURE in your main.prg and finally in a form do:

lcImage = ReadBinaryField("user_picture","yoursqltablename","where id=1") && adapt table name and where clause to retrieve a single record/image only.
STRTOFILE(lcImage,addbs(SYS(2023))+"test.jpg")
thisform.image1.Picture = addbs(SYS(2023))+"test.jpg"

I wonder if Rick is complicating the case, as previous VFP versions didn't have blob but did have binary memo already, but I think the cursorsetprop about mapping binary fields is not available in VFP8 or lower.

Bye, Olaf.

 

[kuma.lk]

thanks Olaf, i will try this

 

[atlopes]

If you use a "SQL Server Native Client 11.0" driver or later to connect to the database, VFP6 will recognize directly a Varbinary(MAX) on the MSSQL server as a Memo(Binary) in the DBF. Next, [tt]STRTOFILE(<field>, <filename>)[/tt] will produce the corresponding binary file.