ADO command question with ComatePlus

[phonecollector]

I am sure its an easy question, but I didn't find a solution or an exampel.

Code to convert:

Code: Select all

Set adoStream = CreateObject("ADODB.Stream") 
.....
adoStream.Type = 1 
adoStream.Open() 
.....
.....
.....
adoStream.Close 
Set adoStream = Nothing
.....

Converted for PB with ComatePlus:

Code: Select all

A_Stream = COMate_CreateObject("ADODB.Stream")
    If A_Stream
     A_Stream\SetProperty("Type=1")
     A_Stream\Invoke("Open")
     ........
     ........
     ........
     A_Stream\Invoke("Close")     
     ; *** here is the missing line for 'Set adoStream = Nothing'


     A_Stream\Release()
    Endif

'Close' will only close the stream, but not free the memory, but 'Set adoStream = Nothing' will do so.

A_Stream\Release() does not free stream memory.
How do write the code line for 'Set adoStream = Nothing' with ComatePlus?

Thanks for your help.
Jo

[srod]

You've already done it with A_Stream\Release().

Are you trying to work with blobs by any chance?

[phonecollector]

Thank you for your hint, you are right, its for Blob handling.

In the meentime I have seen a code from Kiffi solving the problem - thanks.

I have to use ADO because SetDatabaseBlob() will not run with Microsoft SQL Server - the "?" is not accepted in the command sequence.

Jo

[srod]

There is the ADOmate library which works with ADO and is based upon COMatePLUS etc. However I have not added support for blobs as yet. I'd be very grateful if you could post your working code so that I may, with your permission, add it to ADOmate.

**EDIT : ah never mind, Kiffi's code looks to fit the bill nicely.

[Kiffi]

@srod:

here is the adjusted code:

Code: Select all

IncludePath #PB_Compiler_Home + "srod\comateplus\" 
IncludeFile "comateplus.pbi"

EnableExplicit

#adTypeBinary          = 1
#adOpenKeyset          = 1
#adLockOptimistic      = 3
#adSaveCreateOverWrite = 2

Procedure WriteImageToDatabase(ConnectionString.s, Foto_Nummer, Foto_Image.s)
  
  Protected ADODB_Connection.COMateObject  ; Connection-Object
  Protected ADODB_Recordset.COMateObject   ; Recordset-Object
  Protected ADODB_Stream.COMateObject      ; Stream-Object
  Protected *varStreamRead.VARIANT
  
  If FileSize(Foto_Image) < 0
    Debug "File not found: '" + Foto_Image + "'"
    ProcedureReturn
  EndIf
  
  ADODB_Connection = COMate_CreateObject("ADODB.Connection")
  
  If ADODB_Connection 
    
    If ADODB_Connection\Invoke("Open('" + ConnectionString + "')") = #S_OK
      
      ADODB_Stream = COMate_CreateObject("ADODB.Stream")
      
      If ADODB_Stream
        
        ADODB_Stream\SetProperty("Type=" + Str(#adTypeBinary))
        ADODB_Stream\Invoke("Open")
        ADODB_Stream\Invoke("LoadFromFile('" + ReplaceString(Foto_Image, "'", "$0027") + "')")
        
        ADODB_Recordset = COMate_CreateObject("ADODB.Recordset")
        
        If ADODB_Recordset
          
          If ADODB_Recordset\Invoke("Open('SELECT Foto_Nummer, Foto_Image FROM gamepics Where 1=2', " + Str(ADODB_Connection) + " As COMateObject, " + Str(#adOpenKeyset) + ", " + Str(#adLockOptimistic) + ")") = #S_OK
            
            ADODB_Recordset\Invoke("AddNew")
            
            ADODB_Recordset\SetProperty("Fields('Foto_Nummer')\Value=" + Str(Foto_Nummer))
            
            *varStreamRead = ADODB_Stream\GetVariantProperty("Read")
            
            ADODB_Recordset\SetProperty("Fields('Foto_Image')\Value="  + Str(*varStreamRead) + " As Variant")
            
            VariantClear_(*varStreamRead)
            
            ADODB_Recordset\Invoke("Update")
            ADODB_Recordset\Invoke("Close")
            
          Else
            Debug "!ADODB_Recordset.Open(): " + COMate_GetLastErrorDescription()
          EndIf
          
          ADODB_Recordset\Release()
          
        Else
          Debug "!ADODB_Recordset: " + COMate_GetLastErrorDescription()
        EndIf
        
        ADODB_Stream\Invoke("Close")
        ADODB_Stream\Release()
        
      Else
        Debug "!ADODB_Stream: " + COMate_GetLastErrorDescription()
      EndIf
      
      ADODB_Connection\Invoke("Close")
      
    Else
      Debug "!ADODB_Connection.Open(): " + COMate_GetLastErrorDescription()
    EndIf
    
    ADODB_Connection\Release()
    
  Else
    Debug "!ADODB_Connection: " + COMate_GetLastErrorDescription()
  EndIf
  
EndProcedure

Procedure ReadImageFromDatabase(ConnectionString.s, Foto_Nummer, Foto_Image.s)
  
  Protected ADODB_Connection.COMateObject  ; Connection-Objekt
  Protected ADODB_Recordset.COMateObject   ; Recordset-Object
  Protected ADODB_Stream.COMateObject      ; Stream-Object
  Protected *varStreamRead.VARIANT
  
  ADODB_Connection = COMate_CreateObject("ADODB.Connection")
  
  If ADODB_Connection 
    
    If ADODB_Connection\Invoke("Open('" + ConnectionString + "')") = #S_OK
      
      ADODB_Stream = COMate_CreateObject("ADODB.Stream")
      
      If ADODB_Stream
        
        ADODB_Stream\SetProperty("Type=" + Str(#adTypeBinary))
        ADODB_Stream\Invoke("Open")
        
        ADODB_Recordset = COMate_CreateObject("ADODB.Recordset")
        
        If ADODB_Recordset
          
          If ADODB_Recordset\Invoke("Open('SELECT Foto_Nummer, Foto_Image FROM gamepics Where Foto_Nummer=" + Str(Foto_Nummer) + "', " + Str(ADODB_Connection) + " As COMateObject, " + Str(#adOpenKeyset) + ", " + Str(#adLockOptimistic) + ")") = #S_OK
            
            *varStreamRead = ADODB_Recordset\GetVariantProperty("Fields('Foto_Image')\Value")
            
            ADODB_Stream\Invoke("Write(" + Str(*varStreamRead) + " As Variant)" )
            
            VariantClear_(*varStreamRead)
            
            ADODB_Stream\Invoke("SaveToFile('" + ReplaceString(Foto_Image, "'", "$0027") + "', " + Str(#adSaveCreateOverWrite) + ")")
            
            ADODB_Recordset\Invoke("Close")
            
          Else
            Debug "!ADODB_Recordset.Open(): " + COMate_GetLastErrorDescription()
          EndIf
          
          ADODB_Recordset\Release()
          
        Else
          Debug "!ADODB_Recordset: " + COMate_GetLastErrorDescription()
        EndIf
        
        ADODB_Stream\Invoke("Close")
        ADODB_Stream\Release()
        
      Else
        Debug "!ADODB_Stream: " + COMate_GetLastErrorDescription()
      EndIf
      
      ADODB_Connection\Invoke("Close")
      
    Else
      Debug "!ADODB_Connection.Open(): " + COMate_GetLastErrorDescription()
    EndIf
    
    ADODB_Connection\Release()
    
  Else
    Debug "!ADODB_Connection: " + COMate_GetLastErrorDescription()
  EndIf
  
EndProcedure

Define.s SqlServer, SqlServerDatabase, SqlServerUsername, SqlServerPassword
Define.s ConnectionString

SqlServer         = "mySqlServer"
SqlServerDatabase = "mySqlServerDatabase"
SqlServerUsername = "myUsername" ; SQL Server-Authentification
SqlServerPassword = "myPassword" ; SQL Server-Authentification
  
; Windows-Authentification:
ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=" + SqlServerDatabase + ";Data Source=" + SqlServer
  
; SQL Server-Authentification:
; ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;Password=" + SqlServerPassword + ";User ID=" + SqlServerUsername + ";Initial Catalog=" + SqlServerDatabase + ";Data Source=" + SqlServer

WriteImageToDatabase (ConnectionString, 2, "C:\MyInImage.png")

ReadImageFromDatabase(ConnectionString, 2, "C:\MyOutImage.png")

Debug "Ready"

Greetings ... Kiffi

[srod]

Thanks Kiffi; I should be able to add something along those lines when I get a spare moment or two.