ODBC and blobs

[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

[bobobo]

don't neglect the '



SQL$ = "Insert Into Pictures Values ('?');"

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

[bobobo]

ok .. missed that .. thanks

looks buggy then

[srod]

ok .. missed that .. thanks

looks buggy then

Yes, I think so.

I'll leave it a bit longer before making a bug report.

Thanks.

[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

[netmaestro]

LONGBINARY and IMAGE both resolve to "Ole Object" when the file is created. They should be interchangeable.

[Marco2007]

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