ADO stream question (1 Viewer)

CedarTree

Registered User.
Local time
Today, 07:31
Joined
Mar 2, 2018
Messages
404
Sorry to post here as well (in addition to sql subforum) but hoping to see if others have run into this issue...

Here's an approach to uploading an image blob where I try to get away from using Recordsets... but I keep getting errors in the line highlighted towards the bottom. I keep getting truncate right errors, or wrong parameter errors. I've tried all kinds of variations.

Any suggested fixes please?

Code:
    Dim sql As String, conn As ADODB.Connection, rst As ADODB.Recordset, mystream As ADODB.Stream
    Dim iFileSize As Long, sFullFileName As String, vImage As Variant

    Set conn = New ADODB.Connection
    conn.ConnectionString = gsADOConnection
    conn.CursorLocation = adUseClient
    conn.Open
    
    sFullFileName = fnFullFileName(pClient, pPID, pFileID, False)

    sql = "DELETE FROM usysServer_tblFileBlobs "
    sql = sql + "WHERE Client = '" + pClient + "' AND PID = " + CStr(pPID) + " AND FileID = " + CStr(pFileID)
    conn.Execute sql

    Set mystream = New ADODB.Stream
    mystream.Type = adTypeBinary
    mystream.Open
    mystream.LoadFromFile sFullFileName
    iFileSize = mystream.Size
    
    Dim strQry As String
    'sql = "INSERT INTO usysServer_tblFileBlobs (Client, PID, FileID, FileSize) VALUES (?, ?, ?, ?)"
    sql = "INSERT INTO usysServer_tblFileBlobs (Client, PID, FileID, FileSize, FileBlob) VALUES (?, ?, ?, ?, ?)"
    
    Dim cm As ADODB.Command
    Set cm = New ADODB.Command
    cm.ActiveConnection = conn
    cm.CommandText = sql
    cm.Parameters.Append cm.CreateParameter("@Client", adVarChar, adParamInput, Len(pClient), pClient)
    cm.Parameters.Append cm.CreateParameter("@PID", adInteger, adParamInput, , pPID)
    cm.Parameters.Append cm.CreateParameter("@FileID", adInteger, adParamInput, , pFileID)
    cm.Parameters.Append cm.CreateParameter("@FileSize", adInteger, adParamInput, , iFileSize)

    [B][COLOR="Red"]cm.Parameters.Append cm.CreateParameter("@FileBlob", adVarBinary, adParamInput, iFileSize, mystream.Read)[/COLOR][/B]

    cm.CommandType = adCmdText
    cm.Execute
    Set cm = Nothing
    
    Debug.Print Format(pRecord, "#,##0") + " / " + Format(pRecords, "#,##0"), iFileSize, pPID, pFileID, sFullFileName
    DoEvents
    mystream.Close
    conn.Close
 

CedarTree

Registered User.
Local time
Today, 07:31
Joined
Mar 2, 2018
Messages
404
Hi - just bumping in case anyone has any suggestions. Thanks!
 

sonic8

AWF VIP
Local time
Today, 12:31
Joined
Oct 27, 2015
Messages
998
Two suggestions:
1.)
Try using the adLongVarBinary data type for the parameter.


2.)

Read the content from the stream into a byte array and try to use that for the parameter value. You can also check its content.
 

CedarTree

Registered User.
Local time
Today, 07:31
Joined
Mar 2, 2018
Messages
404
Thanks. I'm thinking the parameter just above it should also be adBigInt (?)
 

sonic8

AWF VIP
Local time
Today, 12:31
Joined
Oct 27, 2015
Messages
998
Thanks. I'm thinking the parameter just above it should also be adBigInt (?)
No, unless you are expecting files bigger than 2GB in size.
If that is the case, you are right but it will incur a couple of related problems. The most serious of those is that 2GB is maximum size of Varbinary(max).
 

CedarTree

Registered User.
Local time
Today, 07:31
Joined
Mar 2, 2018
Messages
404
I think your first suggestion worked!!! Thanks!!!
 

Users who are viewing this thread

Top Bottom