Bob Lucas posted a nice Visual FoxPro 9.0 tip on the Message Board earlier today in respect to how you can get Image values in and out of a SQL Server database and I thought I’d repost this here with a little more information. VFP 9 makes it a lot easier to work with Image/Blob data types than previous versions because it has native support for a binary datatype.

One of the issues in previous versions of VFP has always been talking to SQL Server image\binary data. FoxPro wants to convert any binary data into a General field when it returns it and sending data in has to be done with binary field types. In the past I had some nasty workarounds for this which involved creating a temporary cursor and modifying the general field physically on disk to turn it into a regular binary memo to read the data. I also did this to write the data although writing is actually easier.

Let’s start with VFP 9 because it’s easiest. The sample below uses a wwSQL class which is just a thin wrapper around SQL Passthrough (or a CursorAdapter if the ADO version is used). But you can replace it easily enough with SQLStringConnect and SQLEXEC calls.

CLEAR

DO WCONNECT

oSQL = CREATEOBJECT("wwSQL")

? oSQL.Connect("driver={Sql Server};database=WebStore2;server=.")

*** Create a string with some binary data

lcValue = "TEST ÿ[1]

? lcValue

*** Convert into a binary value – this works for VFP 8 as well

pcBin = CREATEBINARY(lcValue)

*** Or in 9.0 only:

*lcBin = CAST(lcValue as Blob)

? pcBin

CURSORSETPROP("MapBinary",.F.,0) && MapBinary not required

? oSql.ExecuteNonQuery("update wws_customers set [image] = ?lcBin where pk = 1")

? "Records: " + TRANSFORM(oSQL.nAffectedRecords)

? "Error: "  + oSql.cErrorMsg

CURSORSETPROP("MapBinary",.T.,0)

? oSql.execute("select * from wws_customers")

browse

? Image  

? CAST(Image as M)

RETURN

This works directly passing the data through the system as  blob, which is great since this was a lot more of a pain in previous versions of VFP.

Actually the Update/Insert code above works just fine in VFP 8 (and probably earlier) if you remove the CursorSetProp call. CreateBinary creates a binary string and VFP detects the binary string and properly updates the Image field in the database even in 8.0.

However, reading binary data in 8.0 is another story. The only way I managed to do this, was to use this nasty code:

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

So you can imagine it’s nice to be able to pull image data directly as part of a SQL Statement rather than going through all of this rigamarole. I hadn’t tried this but I think the CursorAdapter with an ADO data source also may have been able to pull the data out in VFP 8.

VFP9's support for a binary data type is the main reason this is much easier in 9.0 - a lot of features have been adjusted to take advantage of the new binary type including better support for some ActiveX controls that feed binary data back from their methods.

Now if they only could have done the same thing and have provided us with a Unicode type... <sigh>