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

[CBellucci]

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?

 

[Chris Miller]

In https://doughennig.com/Papers/Pub/Hennig_ODBC.pdf you learn about what you describe:

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:

cursorsetprop('MapBinary', .T., 0)
lcSQL = "SELECT CAST(Picture AS IMAGE) AS Picture FROM DriverTest"
sqlexec(lnHandle, lcSQL, 'DriverTest')

 

[CBellucci]

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!

 

[Chris Miller]

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:

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

But it seems it's not necessary in this case.