Using AdoStream to extract binary from a field object

[Bob Mechler]

I've previously place PDF in a VarLongChar and thought I was home free. However after storing a 1000 or so PDF's and retrieiving them using the ODBC method, I find that some of them are retrieved and indicate invalid characters or blank pages. Enough of them so that I must try and do it with VARLONGBINARY.

I've read several places since then that you can't depend on PDF's stored in a Long Char Field because of high bits being set and embedded null's.

Is this the right way to read a column in SQL that is VARBINARY(max) and save the results to a file:

Code Select Expand


      lpField = AdoFields_GetItem(lpFields,"PDFDOCBIN")
      lpStream = AdoCreateObject("ADODB.Stream")
      AdoStream_SetType(lpStream,%adTypeBinary)
      AdoStream_Open(lpStream)
      AdoStream_Write(lpStream,vRes)
      AdoStream_SaveToFile(lpField,"C:\PDFS\SSA1.PDF")
      AdoStream_Close(lpStream)
      AdoRelease lpField
      AdoRelease lpStream


Bob Mechler

[Bob Mechler]

For reference this is the code I used to insert the record referred to above.

Code Select Expand


' *************************************************************
' The Jet OLE DB version 4.0 provider supports the
' SELECT @@Identity query that allows you to retrieve the value
' of the auto-increment field generated on your connection.
' SED_PBCC ' Use the PBCC compiler
' *************************************************************

#COMPILE EXE
#DIM ALL
#INCLUDE "Win32Api.inc"
#INCLUDE "TB_ADO28.INC"

'$strDbPath = "biblio.mdb"  ' <-- change As needed

FUNCTION PBMAIN

   LOCAL lpConnection AS DWORD
   LOCAL lpCommand AS DWORD
   LOCAL lpParameters AS DWORD
   LOCAL lpParameter AS DWORD
   LOCAL ConStr AS STRING
   LOCAL SqlStr AS STRING
   LOCAL lpRecordset AS DWORD
   LOCAL vRes AS VARIANT

   ' Creates an ADO connection object
   lpConnection = AdoCreateObject("ADODB.Connection")
   IF ISFALSE lpConnection THEN EXIT FUNCTION

   ' Connection String - Remember to change the path of the Data Source
   'ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $strDbPath
   ConStr ="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;PWD=mEch6596;Initial Catalog=TNETonlinedemo;Data Source=BOBM-HWA\SQLEXPRESS1"

   ' Opens the connection
   AdoConnection_Open(lpConnection, ConStr)
   IF AdoError THEN GOTO Terminate

   ' Creates an ADO command object
   lpCommand = AdoCreateObject("ADODB.Command")
   IF ISFALSE lpCommand THEN EXIT FUNCTION

   ' Set command properties
   AdoCommand_PutRefActiveConnection lpCommand, lpConnection
   TRY   
     AdoCommand_SetCommandType lpCommand, %adCmdText
     AdoCommand_SetCommandText lpCommand,"DELETE FROM STMTPDFS WHERE ACCOUNT = '1101500'"     
     AdoCommand_Execute lpCommand, %adExecuteNoRecords
   CATCH
     MSGBOX("Account does not exist")
   END TRY
   AdoCommand_SetCommandType lpCommand, %adCmdText
   AdoCommand_SetCommandText lpCommand,"INSERT INTO STMTPDFS (ACCOUNT,DATESTR,QUALIFIER,PDFDOC,PDFDOCBIN) VALUES (?,?,?,?,?)"

   ' Create command parameters
   LOCAL strAccount AS STRING
   LOCAL strDateStr AS STRING
   LOCAL strQualifier AS STRING
   LOCAL strPDFDOC AS STRING
   strAccount = "1101500"
   strDateStr = "2009-04-23"
   strQualifier = "UNAUDITED"
   OPEN "C:\PDFS\SSA.PDF" FOR BINARY AS 1
     GET$ 1,LOF(1),strPDFDOC
   CLOSE
   lpParameters = AdoCommand_GetParameters(lpCommand)
   lpParameter = AdoCommand_CreateParameter(lpCommand, "ACCOUNT", %adVarWChar, %adParamInput, 255, strAccount)
   AdoParameters_Append lpParameters, lpParameter
   AdoRelease lpParameter
   lpParameter = AdoCommand_CreateParameter(lpCommand, "DATESTR", %adVarWChar, %adParamInput, 255, strDateStr)
   AdoParameters_Append lpParameters, lpParameter
   AdoRelease lpParameter
   lpParameter = AdoCommand_CreateParameter(lpCommand, "QUALIFIER", %adVarWChar, %adParamInput, 255, strQualifier)
   AdoParameters_Append lpParameters, lpParameter
   AdoRelease lpParameter
   lpParameter = AdoCommand_CreateParameter(lpCommand, "PDFDOC", %adLongVarWChar, %adParamInput, 536870910, strPDFDOC)
   AdoParameters_Append lpParameters, lpParameter
   AdoRelease lpParameter
   lpParameter = AdoCommand_CreateParameter(lpCommand, "PDFDOCBIN", %adLongVarBinary, %adParamInput, 536870910, strPDFDOC)
   AdoParameters_Append lpParameters, lpParameter
   AdoRelease lpParameter
   AdoRelease lpParameters

   ' Run the command (perform the Insert)
   AdoCommand_Execute lpCommand, %adExecuteNoRecords

   ' Get the new AutoNumber value
   'LOCAL vRecordsAffected AS VARIANT
   'lpRecordset = AdoConnection_Execute(lpConnection, "SELECT @@Identity", vRecordsAffected, %adCmdText)
   'AdoRecordset_GetCollect(lpRecordset, 0, vRes)
   'PRINT VARIANT#(vRes)

Terminate:

   ' Close and release the recordset
   IF ISTRUE lpRecordset THEN
      AdoRecordset_Close(lpRecordset)
      AdoRelease(lpRecordset)
   END IF

   IF ISTRUE lpCommand THEN AdoRelease lpCommand

   ' Close and release the connection
   IF ISTRUE lpConnection THEN
      AdoConnection_Close(lpConnection)
      AdoRelease(lpConnection)
   END IF

   MSGBOX "Done"

END FUNCTION


[José Roca]

Is this the right way to read a column in SQL that is VARBINARY(max) and save the results to a file:

Code Select Expand


      lpField = AdoFields_GetItem(lpFields,"PDFDOCBIN")
      lpStream = AdoCreateObject("ADODB.Stream")
      AdoStream_SetType(lpStream,%adTypeBinary)
      AdoStream_Open(lpStream)
      AdoStream_Write(lpStream,vRes)
      AdoStream_SaveToFile(lpField,"C:\PDFS\SSA1.PDF")
      AdoStream_Close(lpStream)
      AdoRelease lpField
      AdoRelease lpStream


No. it should be:

Code Select Expand


      lpField = AdoFields_GetItem(lpFields,"PDFDOCBIN")
      AdoField_GetValue(lpField, vRes)
      lpStream = AdoCreateObject("ADODB.Stream")
      AdoStream_SetType(lpStream,%adTypeBinary)
      AdoStream_Open(lpStream)
      AdoStream_Write(lpStream,vRes)
      AdoStream_SaveToFile(lpStream,"C:\PDFS\SSA1.PDF")
      AdoStream_Close(lpStream)
      AdoRelease lpField
      AdoRelease lpStream


[Bob Mechler]

Thanks for your time. It worked great.

Bob Mechler

[Bob Mechler]

Spoke too soon.

The files that are created by the above code are exactly twice the original size. I tried adding

Code Select Expand


AdoStream_SetCharSet(lpStream,"Windows-1252")

right after AdoStream_SetType  thinking it was a Unicode thing with the Stream Object but it didn't change anything.

Using a Hex editor comparing the original PDF to the one retrieved from the Binary field it seems like the retrieved PDF contents are every other position with nulls in between most of the time.

Not sure at all what I'm dealing with here. Any help would be appreciated.

Bob Mechler

[José Roca]

 
With

Code Select Expand


OPEN "C:\PDFS\SSA.PDF" FOR BINARY AS 1
  GET$ 1,LOF(1),strPDFDOC
CLOSE
...
...
...
lpParameter = AdoCommand_CreateParameter(lpCommand, "PDFDOCBIN", %adLongVarBinary, %adParamInput, 536870910, strPDFDOC)


You are storing the data in unicode. Try using GET to read the file in a byte array and then assign it to a variant (e.g. vPDFDOC = ByteArray()), that you will pass in the call to AdoCommand_CreateParameter instead of strPDFDOC.

[Bob Mechler]

You're right.

Instead of doing what you suggested, which will work, I think I should use the Stream object to store it as well as retrieve it.

I wrote the whole deal in VB and realized I should have used the Stream object to store the pdf as well and the stream object to retrieve it. I've got it working properly in VB so translating back into PB should go more smoothly.

VB code:

Code Select Expand


Private Sub getbinary_Click()
 Dim ad As ADODB.Connection
 Set ad = New ADODB.Connection
 Dim vpdf As Variant
 Dim strSQL As String
 Dim tempfile As String
 tempfile = "C:\PDFS\SSA1.PDF"
 ad.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;PWD=mEch6596;Initial Catalog=TESTDB;Data Source=BOBM-HWA\SQLEXPRESS1"
 ad.Open
 Dim rs As ADODB.Recordset
 Dim stm As ADODB.Stream
 Set rs = New ADODB.Recordset
 rs.Open "Select PDFDOC FROM STMTPDFS WHERE ACCOUNT = '1101500' AND DATESTR = '2008-04-30' AND QUALIFIER = 'UNAUDITED'", ad
 'rs.MoveFirst
 If Not IsNull(rs.Fields("PDFDOC").Value) Then
   Set stm = New ADODB.Stream
   With stm
     .Type = adTypeBinary
     .Open
     .Write rs.Fields("PDFDOC").Value
     .SaveToFile tempfile, adSaveCreateOverWrite
   End With
   Set stm = Nothing
 End If
 rs.Close
 ad.Close
End Sub

Private Sub savebin_Click()
 Dim stm As ADODB.Stream
 Dim ad As ADODB.Connection
 Set ad = New ADODB.Connection
 Dim vpdf As Variant
 Dim strSQL As String
 Dim VPDFDOC As String
 
 Dim tempfile As String
 tempfile = "C:\PDFS\SSA.PDF"
 ad.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;PWD=mEch6596;Initial Catalog=TESTDB;Data Source=BOBM-HWA\SQLEXPRESS1"
 'ad.ConnectionString = txtConStr
 ad.Open
 Dim rs As ADODB.Recordset
 Set rs = New ADODB.Recordset
 rs.Open "INSERT INTO STMTPDFS (ACCOUNT,DATESTR,QUALIFIER) VALUES('1101500','2008-04-30','UNAUDITED')", ad
 rs.Open "SELECT PDFDOC FROM STMTPDFS WHERE ACCOUNT = '1101500' AND DATESTR = '2008-04-30'", ad, adOpenStatic, adLockOptimistic
 Set stm = New ADODB.Stream
 stm.Type = adTypeBinary
 stm.Open
 stm.LoadFromFile (tempfile)
 rs.Fields("PDFDOC").Value = stm.Read
 rs.Update
 stm.Close
 rs.Close
Set stm = Nothing


End Sub



[Bob Mechler]

I ended up trying your suggestion first. Your answers sometimes go over my head at first. The following code using your example for parameter filling puts a pdf in the table and I can retrieve as a valid PDF with the Visual basic Getbin function from the prevous example and I think will work in my other code now.

Code Select Expand


' *************************************************************
' The Jet OLE DB version 4.0 provider supports the
' SELECT @@Identity query that allows you to retrieve the value
' of the auto-increment field generated on your connection.
' SED_PBCC ' Use the PBCC compiler
' *************************************************************

#COMPILE EXE
#DIM ALL
#INCLUDE "Win32Api.inc"
#INCLUDE "TB_ADO28.INC"

'$strDbPath = "biblio.mdb"  ' <-- change As needed

FUNCTION PBMAIN

   LOCAL lpConnection AS DWORD
   LOCAL lpCommand AS DWORD
   LOCAL lpParameters AS DWORD
   LOCAL lpParameter AS DWORD
   LOCAL ConStr AS STRING
   LOCAL SqlStr AS STRING
   LOCAL lpRecordset AS DWORD
   LOCAL vRes AS VARIANT
   DIM DATESTR AS STRING
   DIM PDFNAME AS STRING
   DIM strSQLDelete AS STRING
   DATESTR$ = INPUTBOX$("Enter yyyy-mm-dd")
   PDFNAME$ = INPUTBOX$("Enter mm")

   ' Creates an ADO connection object
   lpConnection = AdoCreateObject("ADODB.Connection")
   IF ISFALSE lpConnection THEN EXIT FUNCTION

   ' Connection String - Remember to change the path of the Data Source
   ConStr ="Provider=SQLOLEDB.1;Password=mEch6596;Persist Security Info=False;User ID=sa;Initial Catalog=TESTDB;Data Source=BOBM-HWA\SQLEXPRESS1"
   'ConStr ="Provider=SQLOLEDB.1;Password=Sud474;Persist Security Info=False;User ID=sa;Initial Catalog=TNETonlinedemo;Data Source=TANGO\SQLEXPRESS"

   ' Opens the connection
   AdoConnection_Open(lpConnection, ConStr)
   IF AdoError THEN GOTO Terminate

   ' Creates an ADO command object
   lpCommand = AdoCreateObject("ADODB.Command")
   IF ISFALSE lpCommand THEN EXIT FUNCTION

   ' Set command properties
   AdoCommand_PutRefActiveConnection lpCommand, lpConnection
   TRY   
     AdoCommand_SetCommandType lpCommand, %adCmdText
     strSQLDelete$ = "DELETE FROM STMTPDFS WHERE ACCOUNT = '1101500' AND DATESTR = '" + DATESTR$ + "'"
     AdoCommand_SetCommandText lpCommand,strSQLDelete$     
     AdoCommand_Execute lpCommand, %adExecuteNoRecords
   CATCH
     MSGBOX("Account does not exist")
   END TRY
   AdoCommand_SetCommandType lpCommand, %adCmdText
   AdoCommand_SetCommandText lpCommand,"INSERT INTO STMTPDFS (ACCOUNT,DATESTR,QUALIFIER,PDFDOC) VALUES (?,?,?,?)"
   ' Create command parameters
   LOCAL strAccount AS STRING
   LOCAL strDateStr AS STRING
   LOCAL strQualifier AS STRING
   LOCAL strPDFDOC AS STRING
   LOCAL sPDFDOC() AS BYTE, count as long,y as long,P AS STRING
   LOCAL vPDFDOC AS VARIANT
   strAccount = "1101500"
   strDateStr = DATESTR$
   strQualifier = "UNAUDITED"
   P$ = "C:\PDFS\1101500-2008-" + PDFNAME$ + "-30.PDF" 
   OPEN P$ FOR BINARY AS 1
   DIM sPDFDOC(1 to LOF(1))
     GET# 1,1,sPDFDOC() RECORDS LOF(1) to y&
   CLOSE #1                   
   lpParameters = AdoCommand_GetParameters(lpCommand)
   lpParameter = AdoCommand_CreateParameter(lpCommand, "ACCOUNT", %adVarWChar, %adParamInput, 255, strAccount)
   AdoParameters_Append lpParameters, lpParameter
   AdoRelease lpParameter
   lpParameter = AdoCommand_CreateParameter(lpCommand, "DATESTR", %adVarWChar, %adParamInput, 255, strDateStr)
   AdoParameters_Append lpParameters, lpParameter
   AdoRelease lpParameter
   lpParameter = AdoCommand_CreateParameter(lpCommand, "QUALIFIER", %adVarWChar, %adParamInput, 255, strQualifier)
   AdoParameters_Append lpParameters, lpParameter
   AdoRelease lpParameter 
   vPDFDOC = sPDFDOC()
   lpParameter = AdoCommand_CreateParameter(lpCommand, "PDFDOC", %adLongVarBinary, %adParamInput, 536870910, vPDFDOC)
   AdoParameters_Append lpParameters, lpParameter
   AdoRelease lpParameter
   AdoRelease lpParameters

   ' Run the command (perform the Insert)
   AdoCommand_Execute lpCommand, %adExecuteNoRecords


Terminate:

   ' Close and release the recordset
   IF ISTRUE lpRecordset THEN
      AdoRecordset_Close(lpRecordset)
      AdoRelease(lpRecordset)
   END IF

   IF ISTRUE lpCommand THEN AdoRelease lpCommand

   ' Close and release the connection
   IF ISTRUE lpConnection THEN
      AdoConnection_Close(lpConnection)
      AdoRelease(lpConnection)
   END IF

   MSGBOX "Done"

END FUNCTION


[José Roca]

I ended up trying your suggestion first. Your answers sometimes go over my head at first.

It takes time to master COM programming. The explanation is that the last parameter of the CreateParameter method is a BYVAL VARIANT, and when you assign a string to a variant, PB converts it automatically to unicode. But if we read the file with GET and store its contents in a byte array, when we assign that array to the variant it is not converted to unicode.