Insert Multiple BLOBs via ODBC

[jpfiste]

Hello,

I've go a Oracle 10g database and want to store two thumbnail pictures in one row. The two thumbnails are each in a memory.

Code: Select all

*thumb1 = imagecontainer\SaveToBlob()
*thumb2 = imagecontainer\SaveToBlob()

The size of the pictures is different.

Code: Select all

thumb1_size = MemorySize(*thumb1)
thumb2_size = MemorySize(*thumb2)

Now want to attach the the thumbnails as blob to the next databaseupdate.

Code: Select all

SetDatabaseBlob(*db, 0, *thumb1, thumb1_size)
SetDatabaseBlob(*db, 1, *thumb2, thumb2_size)

As Insert String I use the following (cannot post the code because I generated this in program) :

Code: Select all

INSERT INTO IAIMAGE (IM_THUMBNAIL,IM_THUMBNAIL2,IM_IDFOLDER) VALUES(?,?,330096);

No error is reported. To get the blob through a query I need the size. The DatabaseColumnSize hasn't reflected the size of the blob, it always gives the maximum size of blob (I think oracle driver specific). This function is correct and returns the needed memory size.

Code: Select all

dbms_lob.getlength(img.im_thumbnail)
dbms_lob.getlength(img.im_thumbnail2)

But the size is always the value of thumb1_size. So the second thumbnail (IM_THUMBNAIL2) isn't displayed correctly and retrieving the picture from the database causes a unreadable picture.

Has anyone a suggestion what I have done false?

[Rook Zimbabwe]

I have a map editor that uses blobs... this is the part of the code where I save the map. in SQLLite format because ODBC was SUCH a headache!

Code: Select all

  result = CountGadgetItems(#ListIcon_SPRITES) ;+ 1
    totsprites = result- 1 ; spritenumber ; used to ID prite in our list no BLANK tile
    Debug "TOTAL SPRITES = "+Str(totsprites) ; << IT counts them OK
    
    UseSQLiteDatabase()
    
    OpenDatabase(#DB,File$,"","")
    
    DatabaseUpdate(#DB, "CREATE TABLE TILES (PIC_1 BLOB, SIZE_1 integer, TID_1 text)")
    FinishDatabaseQuery(#DB)
    
    result = CountGadgetItems(#ListIcon_SPRITES) ;+ 1
    totsprites = result-1; spritenumber ; used to ID prite in our list no BLANK tile
    
    For WHANGIT = 0 To totsprites
      TID1$ = GetGadgetItemText(#ListIcon_SPRITES,WHANGIT,0)
      TILENAME$ = GetGadgetItemText(#ListIcon_SPRITES,WHANGIT,1)
      fres = ReadFile(0, TILENAME$)
      length0 = Lof(0) : *filebuffer0 = AllocateMemory(length0) : ReadData(0,*filebuffer0,length0) : CloseFile(0)
      LONGIS$ = Str(length0)
      SetDatabaseBlob(#DB, 0, *filebuffer0, length0)
      sql$ = "INSERT INTO TILES (PIC_1, SIZE_1, TID_1) values ( ?, " + "'" + LONGIS$ + "', '"+TID1$+"');"
      DatabaseUpdate(#DB, sql$)
      FreeMemory(*filebuffer0)
    Next
    FinishDatabaseQuery(#DB)

You originally haad to finish the DB Query each image but thiss hould go faster.

Adapt and have fun!

[jpfiste]

Thanks for your reply.

As I can see you are adding one blob in a loop. But unfortunately I want to add two blobs at the same time (different size). My table definition is simplified the following:

Code: Select all

CREATE TABLE TILES (PIC_1 BLOB, PIC_2 BLOB, ...)

Have you tested your code with adding 2 blobs in one loop iteration?

If I insert only one blob at a time, all is good. But the database layout is not from me. The primary key is calulated over a trigger (beforerowinsert). Sequental insert has the problem, that I must request the primary key of my last inserted record. Due to the fact, that multiple users use this database I cannot securly say, that the last inserted id is the ID I need.