Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gmmastros on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Retrieving Images (varbinary(max)) from MS SQL Server 1

Status
Not open for further replies.

CBellucci

Programmer
Apr 20, 2007
38
US
This is a two-part question. And I know it has been answered, but I can't replicate successful results with the answers I'm finding, so I am throwing out all I know to beg for help.

I am connecting to a MS SQL Server table containing an ID value and field named Image that is a varbinary(max) that contains an image. Using VFP9 SP2 and ODBC Driver 18 for SQL Server. (First question: Is that the correct Driver or is there something else I should be using?)

I have no problems connecting to the SQL Server, issuing a SELECT like this:
SQL_Select = "SELECT IDNum, CAST(Image as varbinary(max)) As DocImage From ITable Where IDNum = 3"
in the SQLExec which works, returning DocImage in the cursor as Type = Q, but there is nothing in the fields.

I have tried this:
CursorSetProp('MapBinary', .T., 0)
SQL_Select = "SELECT IDNum, CAST(Image as varbinary(max)) As DocImage From ITable Where IDNum = 3"
in the SQLExec with the same results.

What piece of the puzzle am I missing? Is it the Driver? Or am I missing something in the code?
 
In https://doughennig.com/Papers/Pub/Hennig_ODBC.pdf you learn about what you describe:
1726513763600.png
Doug still recommends using the newest ODBC Driver X for SQL Server series with workarounds.

For images in VARBINARY(MAX) columns, which is the preferred data type for images
because IMAGE is deprecated, it’s more complicated: using MapBinary makes them come
into VFP as VarBinary(0) with the newer drivers, so you have to use both MapBinary and
cast the column to IMAGE:

Code:
cursorsetprop('MapBinary', .T., 0)
lcSQL = "SELECT CAST(Picture AS IMAGE) AS Picture FROM DriverTest"
sqlexec(lnHandle, lcSQL, 'DriverTest')
 
Yep, I read all that. And it makes me wonder: Is it possible that the field being named Image (Image is the field name, not the type) is causing a problem since it mimics a Data Type?

ETA: Thank you, Chris! I successfully retrieved the data using:
CursorSetProp('MapBinary', .T., 0)
SELECT IDNum, CAST(Image as Image) As DocImage From ITable Where IDNum = 3

Checked the number of characters between the MS SQL Server and my VFP... they matched!

Thanks!
 
Last edited:
Using field names that are reserved words, you can make sure SQL Server gets what you intend to say by using square brackets as name delimiters.

So:
Code:
SELECT IDNum, CAST([Image] as Image) As DocImage From ITable Where IDNum = 3

But it seems it's not necessary in this case.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top