Page 1 of 1
ODBC and blobs
Posted: Wed Aug 11, 2010 10:33 am
by srod
Hi,
I can work with blobs with an MS Access file using ADO without a problem, but something seems afoot with ODBC!
Can anyone get the following working? (Or has anyone successfully worked with MS Access blobs using ODBC?)
[Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect
The error message reported suggests a problem with binding parameters etc.
You'll need to add an appropriate ODBC DSN before you can run the following. I just created an empty mdb file through the control panel when setting up the DSN. Everything works fine with basic number and text fields etc. You'll also need to use your own image (or any old file actually!)
Code: Select all
UseODBCDatabase()
If OpenDatabase(1, "BLOBTEST", "", "", #PB_Database_ODBC)
cmd$ = "Drop Table Pictures)"
DatabaseUpdate(1, cmd$)
cmd$ = "Create Table Pictures(Picture LONGBINARY)"
DatabaseUpdate(1, cmd$)
;Insert some data.
SetDatabaseBlob(1, 0, ?pic1, ?endPic1 - ?pic1)
SQL$ = "Insert Into Pictures Values (?);"
DatabaseUpdate(1, SQL$)
Debug DatabaseError()
;Close database.
CloseDatabase(1)
EndIf
DataSection
pic1:
IncludeBinary "image0.bmp"
endPic1:
EndDataSection
Re: ODBC and blobs
Posted: Wed Aug 11, 2010 11:33 am
by bobobo
don't neglect the '
SQL$ = "Insert Into Pictures Values ('?');"
Re: ODBC and blobs
Posted: Wed Aug 11, 2010 11:36 am
by srod
No that is incorrect. If you do that then you just end up with a ? character being inserted (I have of course tested this)! Everything works fine with SQLite (using ?, not '?').
So, back to the original problem.... beginning to look like a bug with PB.
Re: ODBC and blobs
Posted: Wed Aug 11, 2010 12:10 pm
by bobobo
ok .. missed that .. thanks
looks buggy then
Re: ODBC and blobs
Posted: Wed Aug 11, 2010 12:15 pm
by srod
bobobo wrote:ok .. missed that .. thanks
looks buggy then
Yes, I think so.
I'll leave it a bit longer before making a bug report.
Thanks.
Re: ODBC and blobs
Posted: Wed Aug 11, 2010 7:09 pm
by Rook Zimbabwe
I redid it a little and I am agreeing with srod on thiss... I changed the code and I get a weird DB not initialized error on line 27 with this:
Code: Select all
DataSection
pic1:
IncludeBinary "aVIOLET0.bmp"
endpic1:
EndDataSection
UseODBCDatabase()
If OpenDatabase(1, "BLOBTEST", "", "", #PB_Database_ODBC)
cmd$ = "Drop Table Pictures)"
DatabaseUpdate(1, cmd$)
;cmd$ = "Create Table Pictures(Picture LONGBINARY)"
cmd$ = "Create Table Pictures(Picture IMAGE)" ; MS says to use IMAGE
DatabaseUpdate(1, cmd$)
;Insert some data.
SetDatabaseBlob(1, 0, ?pic1, ?endPic1 - ?pic1)
SQL$ = "Insert Into Pictures Values (?);"
DatabaseUpdate(1, SQL$)
Debug DatabaseError()
FinishDatabaseQuery(1)
EndIf
SQL$ = "Select * from Pictures;"
DatabaseQuery(1, SQL$)
Debug "DB HAS BEEN QUERIED!!!"
result = NextDatabaseRow(1)
Debug "ROW = "+Str(result)
picsize = DatabaseColumnSize(1, 0)
Debug "looking for image!"
result = GetDatabaseBlob(1, 0, *blobreadbuffer, picsize)
Debug "*- GOT BLOB status = "+Str(result)
BLOB: CatchImage(#PB_Any, *blobreadbuffer)
FinishDatabaseQuery(1)
;Close database.
CloseDatabase(1)
End
Re: ODBC and blobs
Posted: Wed Aug 11, 2010 8:10 pm
by netmaestro
LONGBINARY and IMAGE both resolve to "Ole Object" when the file is created. They should be interchangeable.
Re: ODBC and blobs
Posted: Wed Aug 11, 2010 8:41 pm
by Marco2007
Rook Zimbabwe wrote:I changed the code and I get a weird DB not initialized error on line 27 with this:
Code: Select all
If Opendatabase()
EndIf
...
Databasequery()
...
Closedatabase()
...is very bad, because if OpenDatabase fails, you can`t query with that ID...
Better put the
Query, CloseDataBase(),.. between
If : Endif or use
IsDatabase() to avoid "..not initizialized"..
GetDataBaseBlob() works at least, when you add blobs with Adomate...
PS: Never tried Blobs before, but I like it (with Sqlite)....