Fernando,

There is another way to upload binary data from VFP to a SQL Server image field. You have to send the binary data as HEX. The code below should give you an idea. Unfortunately, I don't know of any other way to retrieve binary data from SQL Server other than the hack with the General/Memo field described in Vlad's article.

*-- convert PDF to HEX
lcHex = StrToHex(FILETOSTR("c:\temp\SomeFile.pdf"))
*-- add to SQL Server
SQLEXEC(lnSql, "INSERT INTO YourTable (ImageField) VALUES(" + lcHex + ")")

Here's the code for StrToHex (you will need to download Albert Ballinger's FoxCrypto.fll) :

*) Program...........: StrToHex
*  Author............: Daniel Gramunt
*  Project...........: common
*  Created...........: 28.01.2002 - 18:01:04 (Visual FoxPro 07.00.0000.9465)
*  Copyright.........: (c) Nokia, 2002
*) Description.......: Converts a character string into hexadecimal form.
*)                   : Returns a character string that contains each byte
*)                   : (character) of the passed string in its two-character, 
*)                   : hexadecimal form.
*)                   : 
*)                   : The returned string is exactly twice as long as < tcString >
*)                   : plus two characters ("0x" prefix to indicate HEX format).
*)                   :
*)                   : Dependencies: This program uses Albert Ballinger's great
*)                   :               utility FoxCrypto.fll
*)                   :               http://fox.wikis.com/wc.dll?Wiki~FoxCrypto~VFP
*)                   : 
*  Calling Samples...: ?StrToHex("ABC123") && 0x414243313233
*  Parameter List....: tcString - Character string that is recognized as a
*                    :            byte sequence.
*  Major change list.:
*--------------------------------------------------------------------------------------------------
LPARAMETERS tcString

IF NOT "FOXCRYPTO.FLL" $ UPPER(SET("LIBRARY"))
   SET LIBRARY TO FoxCrypto.fll ADDITIVE
ENDIF

RETURN "0x" + HexEncoder( tcString, .f. )
*-- EOF StrToHex ----------------------------------------------------------------------------------

HTH
Daniel


>Hi Daniel,
>
>Let me, please, report my tests, so far.
>
>I talked to the SQL Server people on my client and they said there is no type blob, as Bob Lee (message #919331) suggested, but we found yet another data type, ntext ("Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters"), to be used in our tests.
>
>If you (or anyone reading this message) have some comments about, I would apreciate.
>
>Thanks,
>
>Fernando
>
>Summary of tests, trying to insert the content of a .PDF file to a SQL Server table field:
>

>
1. Field Type Image: Connectivity error: [Microsoft][ODBC SQL Server Driver][SQL Server]Operand type clash: text is incompatible with image
   >
   >
2. Field Type Binary: Connectivity error: [Microsoft][ODBC SQL Server Driver][SQL Server]Operand type clash: text is incompatible with binary
   >
   >
3. Field Type nText: It presents no error message, but when reading back the content of that field into a VFP variable and copying it to disk via StrToFile() and trying to open the file created (with a .PDF extention) with Adobe Reader, it presents an error message: "There was an error opening this document. The file is damaged and could not be repaired.".
   >

>The code below was used to perform the tests. TestField type was created according to the tests performed.
>

> cSQLDataSource = "MyDataSource"
> cSQLUserId     = "MyUserId"
> cSQLPassword   = "MyPassword"
>
> nSQLHandle = SqlConnect (cSQLDataSource, cSQLUserId, cSQLPassword)
>
> cPdfFile   = FileToStr ("The Myth of Self-Describing XML.pdf")
>
> cSQLString = "Insert Into Teste (nTextField) " + ;
>              "                              " + ;
>              "           Values (?cPdfFile) "
>
> SqlExec (nSQLHandle, cSQLString)
>

>In order to bypass that problem I adapted Vlad's routine to (FAQ #7995), as you pointed me:
>
>"Text field on SQL Serevr is not good to store binary data because it is not reliable and often SQL Server corrupts binary data in text field. varbinary type could be too short.
>
>There are certain difficulties to use image field on SQL Server to store and read binary data in VFP. Image field on SQL Server mapped to general field in VFP by default, causing some difficulties when using SQL Pass Through functions. The workaround idea is to try to use memo field to update data on SQL server."
>

> cSQLDataSource = "MyDataSource"
> cSQLUserId     = "MyUserId"
> cSQLPassword   = "MyPassword"
>
> nSQLHandle = SqlConnect (cSQLDataSource, cSQLUserId, cSQLPassword)
>
> cPdfFile = FileToStr ("C:\Temp\The horror of XML.doc")
>
>lcFileName = "TWAIN20.ZIP" && this file got corrupted when use text field on SQL server
>* create temporary buffer table
>
>if file("C:\Temp\Lixo.dbf")
>  erase ("C:\Temp\Lixo.*")
>endif
>
>create table "C:\Temp\Lixo.dbf" FREE (mData M)
>* take binary data from file into memo
>use C:\Temp\Lixo.dbf alias ttt
>select ttt
>append blank
>append memo mData from ("C:\Temp\The Myth of Self-Describing XML.pdf") overwrite
>use
>* change memo field type to general to match to image type on SQL Server
>ll = fopen("C:\Temp\Lixo.dbf",12)
>fseek(ll,43)
>fwrite(ll,'G')
>fclose(ll)
>
>* write data to SQL Server
>use C:\Temp\Lixo.dbf alias ttt
>
> cSQLString = "Insert Into Teste (TestField ) " + ;
>              "                               " + ;
>              "           Values (?ttt.mData) "
>
> ExecuteSql (nSQLHandle, cSQLString, "", .F., Program (), LineNo ())
>use
>

>Really tricky! After all this solution worked but I don't like having to rely on this kind of workaround.
>
>It's very hard to me to accept that I can't do such a thing (VFP inserting that stuff in SQL Server) in a straightforward way. What amazes me is that I can easyly accomplish what I want if the table involved is a VFP DBF:
>

> PdfFilesDbf = "C:\Temp\PdfFilesDbf"
>
> If ! File (PdfFilesDbf)
>    Delete File (PdfFilesDbf + ".*")
> endif
>
> Create Table (PdfFilesDbf) Free (PdfReport M)
> Use
>
> cPdfFile = FileToStr ("C:\Temp\The Myth of Self-Describing XML.pdf")
>
> Use (PdfFilesDbf) In 0 Alias PdfFilesDbf
>
> Select PdfFilesDbf
>
> Insert Into PdfFilesDbf (PdfReport) Value (cPdfFile)
>
> Use
>

>Then, latter, if I want to retrieve that information and store it as a .pdf file what is required is just:
>

> PdfFilesDbf = "C:\Temp\PdfFilesDbf"
>
> Use (PdfFilesDbf) In 0 Alias PdfFilesDbf
>
> Select PdfFilesDbf
> Go     Top
>
> cMyMemoReport = PdfReport
>
> StrToFile (cMyMemoReport, "C:\Temp\MyMemoReport.Pdf")
>
> Use
>