Need PB translation of this VB handling of ADO stream object

[Bob Mechler]

Jose,

In the examples you provided with TB_ADO28.INC I found one for LongVarChar but not LongVarBinary which I think requires chunks or the ADO Stream Object. Here is some code I got from Experts Exchange that needs some translation. I'm not strong in this area  (and many other areas).

I currently use your examples to save and retrieve a PDF in a VARCHAR(max) field in SQL 2005 Express. We want to add the last 18 months of PDF statements (12K TO 80 k in size each) to the website software we sell but don't want to store these pdf's on the Web Server due to auditor complaints.

I need to use SQL Express so the DB can be inside the domain and be read from the DMZ zone via 1433,1434 and IPSEC to supply a Website with a PDF on a per account and criteria basis.

The following code for AddImage is what I want to use in Powerbasic in the domain to fill the SQL 2005 DB. On the Web side we use ASP.NET 1.14322 and our Web programmer says he can retrieve and display it easily. He programs in Team Developer (Centura) for another product we sell and doesn't know PB.

Inside the domain I want to be able to manage the PDF's since they might need updating and/or deleting. The Trust company wants to post Unaudited and then Final PDF statements which would require deleting and inserting PDF Statements for customers on a monthly basis.

The result would be a managed PDF Statement Archive, useful for the Web site and for the Trust company should someone call and want to review their statement with a person at the Trust company 6 months ago without running a statement for that time frame.

Here's the code I would like to see translated to PB or point me to an existing example that would give me the hints I need. Your/ or someone else's help here would be much appreciated.

Thanks,

Bob Mechler

Code Select Expand


Sub AddImage(rs As ADODB.Recordset, ByVal FileName As String)
Dim stm As ADODB.Stream

Set stm = New ADODB.Stream
With stm
  .Type = adTypeBinary
  .Open
  .LoadFromFile FileName
 
  'Insert the binary object into the table.
  rs.AddNew
  rs.Fields("Image").Value = .Read
  rs.Update
  .Close
End With
Set stm = Nothing

End Sub

To display the image just reverse the operation:

Sub ShowImage(rs As ADODB.Recordset)
Const InitDir = "C:\temp\" 'Some temporary folder
Dim stm As ADODB.Stream
Dim TempFile As String

If Not IsNull(rs.Fields("Image").Value) Then
  Set stm = New ADODB.Stream
  With stm
     .Type = adTypeBinary
     .Open
     .Write rs.Fields("Image").Value
     TempFile = InitDir & "Temp.jpg"
     .SaveToFile TempFile, adSaveCreateOverWrite
     .Close
  End With
  Set stm = Nothing
  Set Picture1.Picture = LoadPicture(TempFile)
Else
  Set Picture1.Picture = LoadPicture
End If

End Sub


[José Roca]

 
Here is a translation step by step:

Code Select Expand


'Sub AddImage(rs As ADODB.Recordset, ByVal FileName As String)
SUB AddImage(BYVAL rs AS DWORD, BYVAL strFileName AS STRING)

'Dim stm As ADODB.Stream
LOCAL stm AS DWORD

'Set stm = New ADODB.Stream
stm = AdoCreateObject("ADODB.Stream")


'With stm
'  .Type = adTypeBinary
   AdoStream_SetType(stm, %adTypeBinary)
'  .Open
   AdoStream_Open(stm)
'  .LoadFromFile strFileName
  AdoStream_LoadFromFile(stm, strFileName)
  'Insert the binary object into the table.
'  rs.AddNew
   AdoRecordset_AddNew(rs)
'  rs.Fields("Image").Value = .Read
   LOCAL pFields AS DWORD
   pFields = AdoRecordset_GetFields(rs)
   LOCAL pField AS DWORD
   pField = AdoFields_GetItem(pFields, "Image")
   LOCAL vValue AS VARIANT
   AdoStream_Read(stm, -1, vValue)
   AdoField_SetValue(pField, vValue)
'  rs.Update
   AdoRecordset_Update(rs)
'  .Close
   AdoStream_Close(stm)
'End With

'Set stm = Nothing
AdoRelease(stm)

'End Sub
END SUB


[Bob Mechler]

Thanks. From your answer I'm trying to get the reading and saving to a file part.

Would this then be the translation of going the other way and reading the 'image' and saving it as a file

Code Select Expand


SUB ShowImage(BYVAL rs AS DWORD)
Const InitDir = "C:\temp\" 'Some temporary folder
'Dim stm As ADODB.Stream
Dim stm as Dword
Dim TempFile As String

'If Not IsNull(rs.Fields("Image").Value) Then
 'Set stm = New ADODB.Stream
 stm = AdoCreateObject("ADODB.Stream")
'  With stm
'     .Type = adTypeBinary
      AdoStream_SetType(stm, %adTypeBinary)
'     .Open
      AdoStream_Open(stm)
    '.Write rs.Fields("Image").Value
    LOCAL pFields AS DWORD
    pFields = AdoRecordset_GetFields(rs)
    LOCAL pField AS DWORD
    pField = AdoFields_GetItem(pFields, "Image")
    LOCAL vValue AS VARIANT
    AdoStream_Write(stm, -1, vValue)
    AdoField_SetValue(pField, vValue) 'Do I need this step?
    TempFile = InitDir & "Temp.jpg"
    '.SaveToFile TempFile, adSaveCreateOverWrite
    AdoStream_SaveToFile(pField,TempFile)    
    '.Close
    AdoStream_Close(stm)
'  End With
 Set stm = Nothing
 Set Picture1.Picture = LoadPicture(TempFile)
'Else
'  Set Picture1.Picture = LoadPicture
'End If

End Sub



Bob Mechler

[José Roca]

     AdoStream_Write(stm, -1, vValue)

Must be:

Code Select Expand


     AdoStream_Write(stm, vValue)


     AdoField_SetValue(pField, vValue) 'Do I need this step?

No. This was used in the previous code to save the stream in the database. Now you are doing the opposite.

[Bob Mechler]

Thank you, José.

Bob Mechler