Rick Strahl's Weblog  

Wind, waves, code and everything in between...
.NET • C# • Markdown • WPF • All Things Web
Contact   •   Articles   •   Products   •   Support   •   Advertise
Sponsored by:
Markdown Monster - The Markdown Editor for Windows

Reading Sql Server Image/Blob data in Visual FoxPro 9


:P
On this page:

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... <sigh>

 


The Voices of Reason


 

RVboy
August 06, 2006

# re: Reading Sql Server Image/Blob data in Visual FoxPro 9

This is one reason why some of us like Remote Views. ;-) In a RV you can simply declare the blob field as a memo or binary memo, after which you can treat it like any other memo.

Rick Strahl
August 06, 2006

# re: Reading Sql Server Image/Blob data in Visual FoxPro 9

Well, you can now do the same without using Remote Views <s> if you set the MapBinary flag at startup.

Did Remove Views work like this prior to 9.0 as well?

Rick Strahl
August 06, 2006

# re: Reading Sql Server Image/Blob data in Visual FoxPro 9

Ooops this is interesting - the markup of the binary characters apparently got mangled in the post although it looked fine going into the editor. Anyway - in case you're wondering the binary string input is a string with embedded binary characters inside of it.

RVboy
August 10, 2006

# re: Reading Sql Server Image/Blob data in Visual FoxPro 9

Rick, RVs worked this way since 1995. ;-)

Rick Strahl
August 10, 2006

# re: Reading Sql Server Image/Blob data in Visual FoxPro 9

Interesting. I wonder what RVs did to make that happen - RV is just a wrapper around SQL Passthrough basically, so the commands should be available to regular code too...

I guess it doesn't really matter now. I'm certainly not going back to pre-9.0 code <g>...


RVboy
August 10, 2006

# re: Reading Sql Server Image/Blob data in Visual FoxPro 9

Sure- why use a RV when you can write arcane code to achieve the same effect? ;-)

Rick Strahl
August 10, 2006

# re: Reading Sql Server Image/Blob data in Visual FoxPro 9

If you're gonna be sarcastic JOHN why don't you just use your real name...



Sarcastic John, the RV Boy
August 10, 2006

# re: Reading Sql Server Image/Blob data in Visual FoxPro 9

Sheesh, busted again...

del.icio.us/esparta/vfp
September 28, 2006

# esparta's bookmarks tagged with &quot;vfp&quot; on del.icio.us

# DotNetSlackers: Reading Sql Server Image/Blob data in Visual FoxPro 9


Naomi
March 08, 2012

# re: Reading Sql Server Image/Blob data in Visual FoxPro 9

Hi Rick,

I found this page doing a search. We're using GDViewer and we were using remote views approach. I want to switch to MapBinary. My question is - how can I feed this ActiveX with the blob data directly?

Rick Strahl
March 16, 2012

# re: Reading Sql Server Image/Blob data in Visual FoxPro 9

@Naomi - you should be able to use VFP's binhex format or if that fails CreateBinary() which creates a COM array from the data. Which format it supports depends on what the control can deal with, but most COM clients will accept binary data using what CreateBinary() creates.

West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2024