Thread: Retrieving Binary Data in VB6

Hi everybody.

I've been trying different ways to store and retrieve binary data in my
PostgreSQL databases, some running in Red Hat 9 (which will be upgraded
within this month), some in CentOS 4.3 and some in Windows XP Pro (Spanish)
SP2; in all of them, PostgreSQL version is 8.1.4 (with SSL support enabled),
but in all cases, clients are running Win XP Pro (Spanish) SP2 with psqlODBC
8.01.01.01 (I know it's not the latest one, but I've been having some
problems with recent versions).

After googling and digging in PostgreSQL Archives, I found this link
http://archives.postgresql.org/pgsql-odbc/2003-03/msg00063.php
and took that code, modified it a little, cleaned up some comments and I
have this one:

Public Function SQLGetLargeObject(strTableName As String, strThisRow As
String, strField As String, strFile As String, strSQL As String) As Integer
  Dim rsFetch As ADODB.Recordset
  Dim intFileHandle As Integer
  Dim lngFileLength As Long
  Dim byteMemChunk() As Byte
  SQLGetLargeObject = 1
  Set adoConnection = New ADODB.Connection
  adoConnection.Open vbConexion
  Set rsFetch = New ADODB.Recordset
  With rsFetch
    .Open strSQL, adoConnection, adOpenKeyset, adLockOptimistic, adCmdText
    If .RecordCount > 0 Then
      .MoveFirst
      .Find strThisRow
    Else
      Exit Function
    End If
    If .EOF = True Then
      rsFetch.Close
      Set rsFetch = Nothing
      Exit Function
    End If
  End With
  lngFileLength = -1
  lngFileLength = rsFetch.Fields(strField).ActualSize
  If lngFileLength = 0 Then
    SQLGetLargeObject = 2
    Exit Function
  End If
  intFileHandle = FreeFile
  Open strFile For Binary Access Write As intFileHandle
  ReDim byteMemChunk(1 To lngFileLength)

  ' Error occurs with this command
  byteMemChunk() = rsFetch.Fields(strField).GetChunk(lngFileLength)

  Put intFileHandle, , byteMemChunk()
  Close
  rsFetch.Close
  Set rsFetch = Nothing
  SQLGetLargeObject = 0
End Function

I get the Runtime error 3219: "The operation requested by the application is
not allowed in this context" in the instruction commented in the above code.

The function is called in this way (as I'm just testing it):

intGetPicture = SQLGetLargeObject("sistema.liveupdate", "id = " & strFileID,
"archivo_rar", "C:\Prueba.zip", "SELECT * FROM sistema.liveupdate WHERE id =
" & strFileID)

The table from which I'm trying to retrieve my data is:

CREATE TABLE sistema.liveupdate
(
  id bigserial NOT NULL,
  archivo_rar lo NOT NULL,
  nombre varchar(32) NOT NULL,
  hash_md5 char(32) NOT NULL,
  creacion timestamp NOT NULL DEFAULT now(),
  version varchar(25) NOT NULL,
  CONSTRAINT liveupdate_pk PRIMARY KEY (id)
)
WITHOUT OIDS;
-----

Inserts and updated are successfully performed with the other function, but
when trying to retrieve it I get that error.

What I'm trying to implement is to store a compressed executable in the
database, as I have many clients, to easily update their exe's remotely by
inserting/update the compressed binary and then uncompressing and
overwriting it in the clients Program Files folder.

Thank you very much in advance for any help, specially to Mike Miller for
his code.

P.S.:
vbConexion = "PROVIDER=MSDASQL;dsn=mydsnname"