AppendChunk, GetChunk, Storing images( binary files ) to SQL server with Win32ole for Jose Gimenez (

[Rick Lipkin]

Jose
I have a project where I have to store documents ( .doc, .pdf, .txt ) to a
MS SQL Server 2005. Using ADO ( win32ole ) I am trying to import any
document into a SQL table ( for this example ) 3 fields
1) Filename char25
2) DataLen Num 18,0
3) Charter VarBinary(max) // image field
To test my data consider these two tests .. test 1 is to test fopen and
fread .. test 2 uses the same fread but writes the binary file to the SQL
table using the appendchunk() method and the getchunk() retrieval
Copy and paste this text into a .txt file called Rick.txt :
This is a sample text file for importing documents into sql server.
Consider this code and the expected results:
//--- test1
//cPATH was passed as static from cGetFile() // C:DBTMPRICK.TXT
cFILE := ALLTRIM( cPATH ) // C:DBTMPRICK.TXT
nLEN := LEN( cFILE )
nSTART := RAT( "", cFILE )
IF nSTART > 0
ELSE
SAYING := "INVALID File name or Location .. Aborting"
Alerto( SAYING )
RETURN(.F.)
ENDIF
// get file name //
cFILENAME := SUBSTR( cPATH, nSTART+1, nLEN-nSTART ) // RICK.TXT
// open file //
nHANDLE := FOpen( cFILE )
IF FERROR() <> 0
SAYING := "Error reading file "+cFILE+CHR(10)
SAYING += " "+STR(FERROR())+CHR(10)
Alert( SAYING )
RETURN(.F.)
ENDIF
// get number of bytes in file
nBYTES := FSEEK( nHANDLE, 0,2 )
// pad the buffer nBytes+1
cBUFFER := SPACE(nBYTES+1)
FSeek( nHANDLE, 0, 0 )
nBytesRead := FRead( nHANDLE, @cBuffer, nBytes )
FClose( nHANDLE )
SAYING := "nBytesRead = "+str(nBYTESREAD)+CHR(10)
SAYING += "nBytes = "+str(nBYTES)+CHR(10)
Alert( saying )
if nBytesRead != nBytes
SAYING := "nBytesRead = "+str(nBYTESREAD)+CHR(10)
SAYING += "nBytes = "+str(nBYTES)+CHR(10)
SAYING += "Error Reading Data"+chr(10)
Alert( saying )
RETURN ( .F. )
endif
FERASE( "C:DBTMPRICK1.TXT" )
nHANDLE := FCREATE( "C:DBTMPRICK1.TXT", 0 )
FWRITE( nHANDLE, cBUFFER ) // write out the file
FCLOSE( nHANDLE )
RETURN(NIL)
Test 1 results ( as you would expect )
Rick.txt --> This is a sample text file for importing documents into sql
server
Rick1.txt -> This is a sample text file for importing documents into sql
server
// test2 ..
// writing and retrieving from SQL server
//cPATH was passed as static from cGetFile() // C:DBTMPRICK.TXT
cFILE := ALLTRIM( cPATH ) // C:DBTMPRICK.TXT
nLEN := LEN( cFILE )
nSTART := RAT( "", cFILE )
IF nSTART > 0
ELSE
SAYING := "INVALID File name or Location .. Aborting"
Alert( SAYING )
RETURN(.F.)
ENDIF
// get file name //
cFILENAME := SUBSTR( cPATH, nSTART+1, nLEN-nSTART ) // CDBTMPRICK.TXT
// open file //
nHANDLE := FOpen( cFILE )
IF FERROR() <> 0
SAYING := "Error reading file "+cFILE+CHR(10)
SAYING += " "+STR(FERROR())+CHR(10)
Alert( SAYING )
RETURN(.F.)
ENDIF
// get number of bytes in file
nBYTES := FSEEK( nHANDLE, 0,2 )
// pad the buffer nBytes+1
cBUFFER := SPACE(nBYTES+1)
FSeek( nHANDLE, 0, 0 )
nBytesRead := FRead( nHANDLE, @cBuffer, nBytes )
FClose( nHANDLE )
SAYING := "nBytesRead = "+str(nBYTESREAD)+CHR(10)
SAYING += "nBytes = "+str(nBYTES)+CHR(10)
Alert( saying )
if nBytesRead != nBytes
SAYING := "nBytesRead = "+str(nBYTESREAD)+CHR(10)
SAYING += "nBytes = "+str(nBYTES)+CHR(10)
SAYING += "Error Reading Data"+chr(10)
Alert( saying )
RETURN ( .F. )
endif
// oRsCH is an open recordset on Sql table
oRsCh:AddNew()
oRsCh:Fields("datalen"):Value := nBYTES
oRsCh:Fields("filename"):Value := cFILENAME
oRsCh:Fields("charter"):AppendChunk(cBUFFER) // binary file from fread()
oRsCh:Update()
SysReFresh()
cREAD := oRsCh:Fields("charter"):GetChunk( oRsCh:Fields("datalen"):Value) //
get binary file from charter VarBinary(max) field
saying := "Len of cRead written "+str(len(cread))+chr(10)
SAYING += "Number of nBytes in database "+STR(NBYTES)+CHR(10)
Alert( saying )
FERASE( "C:DBTMPRICK2.TXT" )
nHANDLE := FCREATE( "C:DBTMPRICK2.TXT", 0 )
FWRITE( nHANDLE, cREAD ) // write out the file
FCLOSE( nHANDLE )
RETURN(NIL)
Test 2 results ( NOTE spacing and truncation on Rick2.txt )
Rick.txt --> This is a sample text file for importing documents into sql
server
Rick2.txt ->
T h i s i s a s a m p l e t e x t f i l e f o r i m p o
Difficult to see the first bytes of Rick2.txt .. however I think you can see
how much different the results are when you write to a VarBinary(max) Sql
server field and retrieve and write the same value file back.
It looks to me like there is either a syntax error on my part with
AppendChunk() and GetChunk() or there is a problem in Win32ole specifically
writing to SQL server VarBinary(max) field ..
Jose .. I realize this is more of an xHarbour question but you have helped
me in the past with a fix to Win32ole .. Any ideas here ??
Rick Lipkin
SC Dept of Health, USA