[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.

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?

Quote:

---

Originally Posted by si_the_geek

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.

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

Thanks!

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.

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

---

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

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)

---

sometimes i get the feeling im just talking to myself :) :D

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.

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

---