Results 1 to 11 of 11

Thread: [RESOLVED] Storing BLOB in SQLCE DB from VB6

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Feb 2001
    Location
    Crossroads
    Posts
    3,047

    [RESOLVED] Storing BLOB in SQLCE DB from VB6

    I have a large binary object i need to store in a sql compact database.

    Ive done this using an Access database before and I simply put the file into a byte array and chunked it into the db:

    rst.Open strtemp, cnn, adOpenStatic, adLockOptimistic
    rst!UserVBA.AppendChunk bytData
    rst!Update

    However, SQL Compact wont let me use adLockOptimistic (I am using ADO), so I am unable to update the database like this.

    I can update simple fields in the database by using SQL Update queries with an ado command object:

    Dim cm As New ADODB.Command
    cm.CommandText = "UPDATE datTable SET fldField = 'someValue' WHERE anotherField = 'anothervalue'"
    cm.CommandType = adCmdText
    cm.ActiveConnection = rst.ActiveConnection
    cm.Execute

    This is an easy enough technique for a simple text field, but how can I get my byte array into a binary field in this database?

    Any help greatly appreciated.
    Last edited by Muddy; Mar 17th, 2011 at 08:53 AM.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Storing BLOB in SQL Compact DB

    Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

    I would expect the AppendChunk method to work, but the combination of adOpen* and adLock* have varying levels of support among different database systems (I presume you are getting an error based on that), so try different combinations.

    There is an explanation of the common values in the FAQ article What do the parameters of the recordset.Open method mean?

  3. #3

    Thread Starter
    PowerPoster
    Join Date
    Feb 2001
    Location
    Crossroads
    Posts
    3,047

    Re: Storing BLOB in SQL Compact DB

    Quote Originally Posted by si_the_geek View Post
    Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

    I would expect the AppendChunk method to work, but the combination of adOpen* and adLock* have varying levels of support among different database systems (I presume you are getting an error based on that), so try different combinations.

    There is an explanation of the common values in the FAQ article What do the parameters of the recordset.Open method mean?
    The errors are related to the cursor and lock, but I cannot find a combination that will let me use the appendchunk method. Really doenst matter to me which method I use as long as I can get my BLOB into and out of the database.

  4. #4

    Thread Starter
    PowerPoster
    Join Date
    Feb 2001
    Location
    Crossroads
    Posts
    3,047

    Re: Storing BLOB in SQL Compact DB

    just giving this a bump. This has to be possible (easy even). Hopefully someone out there can show me what I'm missing?

    Thanks!

  5. #5

    Thread Starter
    PowerPoster
    Join Date
    Feb 2001
    Location
    Crossroads
    Posts
    3,047

    Re: Storing BLOB in SQL Compact DB

    OK I'm getting closer with this:
    Code:
    cm.CommandText = "UPDATE myTable SET myField = @Parameter1 WHERE anotherField =0 " 
    
    Dim prm As New ADODB.Parameter
    
    prm.name = "Parameter1"
    prm.Size = UBound(bytData) + 1
    prm.Type = adBinary 
    prm.AppendChunk bytData
    cm.Parameters.Append prm
    cm.CommandType = adCmdText
    cm.ActiveConnection = rst.ActiveConnection
    cm.Execute
    the only problem is that it doesnt work for larger byte arrays (seems to work fine for smaller ones). The error is "A literal value in the command overflowed ..."

    I think this is because of the 8000 byte limit on binary columns in SQL CE. Granted I am writing to an Image field, but I am using the type adBinary for the parameter type. THere seem to be no options to specify "image" for an ado parameter.

    Anyone have any ideas of how to get around this? Is there an alternative to ado that might behave better with vb6/sqlce ?

    any help greatly appreciated.

  6. #6

    Thread Starter
    PowerPoster
    Join Date
    Feb 2001
    Location
    Crossroads
    Posts
    3,047

    Re: Storing BLOB in SQL Compact DB

    Think I got it going. (EDIT: I WAS WRONG!) Not the solution I was looking for, and I'm not exactly proud of it :

    I wrote a COM Class Library in VB.net, and am using it from the VB6 app. Nothing novel in the vb.net code but here it is anyway:

    Edit: By the way, don't try to directly pass the data as a Byte Array as a parameter. It won't make it from VB6 into the dotnet dll.

    Code:
    Public Sub WriteBinaryFileToDB(ByVal strSourceFile As String, ByVal strSQL As String, ByVal strCon As String)
    
            Dim da As New SqlCeDataAdapter(strSQL, strCon)
    
            Dim ds As New DataSet()
            Dim cb As New SqlCeCommandBuilder(da)
            Dim row As DataRow
    
            da.Fill(ds)
    
            Dim arr() As Byte
            ReDim arr(FileLen(strSourceFile) - 1)
            FileOpen(1, strSourceFile, OpenMode.Binary, OpenAccess.Read, OpenShare.Shared)
            FileGet(1, arr)
            FileClose(1)
    
    
    
            row = ds.Tables(0).NewRow
            row.Item(0) = arr
            ds.Tables(0).Rows.Add(row)
            da.Update(ds)
    
    
        End Sub
    Last edited by Muddy; Mar 17th, 2011 at 08:24 AM.

  7. #7

    Thread Starter
    PowerPoster
    Join Date
    Feb 2001
    Location
    Crossroads
    Posts
    3,047

    Re: Storing BLOB in SQLCE DB from VB6

    well, i was wrong. the dotnet code isnt working. i end up wiht VBNULL in the field I "wrote" to.

  8. #8

    Thread Starter
    PowerPoster
    Join Date
    Feb 2001
    Location
    Crossroads
    Posts
    3,047

    Re: Storing BLOB in SQLCE DB from VB6

    OK still getting closer. My com interface works, but the SQL inside the dot net dll doesnt. Here is what I have so far that results in the error :

    "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."

    Does that mean the query has to return a Key column?

    by the way the code in the previous post put the bytearray into the db , but it put it into a new row which isnt what i wanted. therefore I think the answer to the quedtion about key column above is no?

    Code:
            Dim da As New SqlCeDataAdapter(strSQL, strCon)
    
            Dim ds As New DataSet()
            Dim cb As New SqlCeCommandBuilder(da)
            Dim row As DataRow
    
            da.Fill(ds)
    
            Dim arr() As Byte
            ReDim arr(FileLen(strSourceFile) - 1)
            FileOpen(1, strSourceFile, OpenMode.Binary, OpenAccess.Read, OpenShare.Shared)
            FileGet(1, arr)
            FileClose(1)
    
            
    
            ' row = ds.Tables(0).NewRow
            row = ds.Tables(0).Rows(0)
            row.Item(0) = arr
            ds.Tables(0).Rows(0).Item("myColumn") = arr
            da.Update(ds)

  9. #9

    Thread Starter
    PowerPoster
    Join Date
    Feb 2001
    Location
    Crossroads
    Posts
    3,047

    Re: Storing BLOB in SQLCE DB from VB6

    sometimes i get the feeling im just talking to myself

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Storing BLOB in SQLCE DB from VB6

    If a database table doesn't have a key, you cannot be sure that an Update/Delete/etc will only work with one row, and generally you do not want to accidentally work with multiple rows. Due to that, VB warns you by not automatically generating the Update etc. While it is possible to manually add the Update, it will not be "safe", and it is likely to better all round to add a key to the table.

    In addition to that, database tables are not "meant" to have just 1 row or just 1 column, and VB cannot know if that is intentional rather than accidental, or if it will change at some point in the future.

  11. #11

    Thread Starter
    PowerPoster
    Join Date
    Feb 2001
    Location
    Crossroads
    Posts
    3,047

    Re: Storing BLOB in SQLCE DB from VB6

    Thanks Si,

    I set one of the columns (one that always has a unique value) as the key column and it now works as shown below:

    Code:
        Public Sub WriteBinaryFileToDB(ByVal strSourceFile As String, ByVal strSQL As String, ByVal strCon As String, ByVal strField As String)
    
            Dim da As New SqlCeDataAdapter(strSQL, strCon)
    
            Dim ds As New DataSet()
            Dim cb As New SqlCeCommandBuilder(da)
            Dim row As DataRow
    
            da.Fill(ds)
    
            Dim arr() As Byte
            ReDim arr(FileLen(strSourceFile) - 1)
            FileOpen(1, strSourceFile, OpenMode.Binary, OpenAccess.Read, OpenShare.Shared)
            FileGet(1, arr)
            FileClose(1)
    
            row = ds.Tables(0).Rows(0)
            row.Item(strField) = arr
    
            da.Update(ds)
    
    
        End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width