VFP i BLOB-y

Rick Strahl's WebLog
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]
- String"
? 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...