-
Mar 11th, 2011, 04:07 PM
#1
Thread Starter
PowerPoster
[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.
-
Mar 11th, 2011, 04:26 PM
#2
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?
-
Mar 11th, 2011, 04:55 PM
#3
Thread Starter
PowerPoster
Re: Storing BLOB in SQL Compact DB
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.
-
Mar 14th, 2011, 05:21 AM
#4
Thread Starter
PowerPoster
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!
-
Mar 16th, 2011, 05:10 PM
#5
Thread Starter
PowerPoster
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.
-
Mar 17th, 2011, 06:17 AM
#6
Thread Starter
PowerPoster
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.
-
Mar 17th, 2011, 07:25 AM
#7
Thread Starter
PowerPoster
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.
-
Mar 17th, 2011, 08:28 AM
#8
Thread Starter
PowerPoster
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)
-
Mar 17th, 2011, 08:32 AM
#9
Thread Starter
PowerPoster
Re: Storing BLOB in SQLCE DB from VB6
sometimes i get the feeling im just talking to myself
-
Mar 17th, 2011, 08:38 AM
#10
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.
-
Mar 17th, 2011, 08:52 AM
#11
Thread Starter
PowerPoster
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|