SQLite Database BLOB is identified as STRING

[n.allan]

I am having trouble reading BLOB data from an SQLite database file.

In my sqlite database there is a table called tiles. The columns in it are zoom_level (INTEGER), tile_row (INTEGER), tile_column (INTEGER) and tile_data (BLOB).

For anyone "in the know" these are industry standard raster ".mbtiles". They have PNG images stored as BLOB in the "tile_data" column. I can view these images in SQLIteBrowser application so I can confirm the db structure is correct and the images are there. I am using this test database from here https://www.google.com/url?sa=t&rct=j&q ... Hqj_QwldeC it is around 9.6 mb.

I have placed the MBTiles file beside the stack and the connection ID is an integer (ok). I can query other text from the database no problem.

Here is what I have tried...

Code: Select all

on test
   local tQuery, tRecordSet, tImage, tResult, tID , tTypes -- some variables to aid in debugging
   
   set the defaultFolder to specialFolderPath("resources") -- place the database file beside the stack
   put revOpenDatabase("sqlite","countries-raster.mbtiles") into tID -- open the database
   if tID is not a number then -- In my test tID is a valid integer meaning the connection was fine
      answer error "Unable to open Database"
      return empty
   end if
   -- build the test Query
   put "select tile_data from tiles where ((tile_column = 0) and (zoom_level = 0) and (tile_row = 0))" into tQuery
   put revQueryDatabase(tID,tQuery) into tRecordSet -- in my test the record set is a valid integer
   
   put revDatabaseColumnTypes(tRecordSet) into tTypes -- should be "BLOB" but live code returns "STRING"
   
   put revDatabaseColumnNamed(tRecordSet,"tile_data","tImage") into tResult -- some binary data (7 bytes) in here
   -- put revDatabaseColumnNamed(tRecordSet,"tile_data","*btImage") into tResult -- tried prepending *b to the name of the variable, tImage is empty if I do this
   
   -- basic tidy up
   revCloseCursor tRecordSet
   revCloseDatabase tID
   
   -- this tells me the tImage variable is 7 bytes long but should be 13.48 kb according to the database
   -- It also tells me the column has been identified as STRING
   put "Data Length =" && the length of tImage && "Type(s) =" && tTypes
end test

So I think LiveCode is incorrectly identifying my column as STRING when it is a BLOB and the data is being chopped at the first NULL byte

I have tried going back back to version 9.5.1 stable but same results.

[bangkok]

Keep it simple.

And start without a recordset. But a plain and square query.

Code: Select all

on test
   local tQuery, tRecordSet, tImage, tResult, tID , tTypes -- some variables to aid in debugging
   
   set the defaultFolder to specialFolderPath("resources") -- place the database file beside the stack
   put revOpenDatabase("sqlite","countries-raster.mbtiles") into tID -- open the database
   if tID is not a number then -- In my test tID is a valid integer meaning the connection was fine
      answer error "Unable to open Database"
      return empty
   end if
   -- build the test Query
   put "select tile_data from tiles where ((tile_column = 0) and (zoom_level = 0) and (tile_row = 0))" into tQuery

   put  revDataFromQuery(,, tID, tQuery) into tRecordSet    ----- the data are now in tRecordSet    

   revCloseDatabase tID
  
   put "Data Length =" && the length of tRecordSet
end test

That's the easiest part.

Next step : you need to check the way you write the image (binary data) into your sqllite db. And the way you deal with it, after reading the db.

[LCMark]

@n.allan: The sqlite driver originated with SQLite2 which did not support binary data... This meant that binary data had to be explicitly encoded as text and then decoded (when using the *b). If your database is coming from another source (i.e. not made with LC's sqlite driver) then you need to do:

Code: Select all

   put revOpenDatabase("sqlite","countries-raster.mbtiles", "binary") into tID -- open the database

The 'binary' option turns off the encoding LC would do otherwise, and should mean BLOBs are stored and retrieved as BLOBs.

[n.allan]

Thank you both very much for your replies...

@LCMark, Your solution has solved the problem. The data type is now showing as BLOB and the full chunk of data is now in the tImage variable.

my final handler with your solution...

Code: Select all

on test
   local tQuery, tRecordSet, tImage, tResult, tID , tTypes -- some variables to aid in debugging
   
   set the defaultFolder to specialFolderPath("resources") -- place the database file beside the stack
   
   -- @LCMark's solution is to open the db as "binary" and this has corrected the issue
   put revOpenDatabase("sqlite","countries-raster.mbtiles", "binary") into tID -- open the database
   
   if tID is not a number then -- tID should be a valid db connection (integer)
      answer error "Unable to open Database"
      return empty
   end if
   
   -- build the test Query
   -- tile_data column contains png data as BLOB
   put "select tile_data from tiles where ((tile_column = 0) and (zoom_level = 0) and (tile_row = 0))" into tQuery
   put revQueryDatabase(tID,tQuery) into tRecordSet -- in my test the record set is a valid integer
   
   put revDatabaseColumnTypes(tRecordSet) into tTypes -- Livecode correctly returns BLOB for this column
   
   put revDatabaseColumnNamed(tRecordSet,"tile_data","tImage") into tResult
   -- binary data is now stored in tImage variable at 13 odd kb

   -- basic tidy up
   revCloseCursor tRecordSet
   revCloseDatabase tID
   
   -- put the length of the blob and confirm data type in the message box
   put "Data Length =" && the length of tImage && "Type(s) =" && tTypes
   
   -- do stuff with tImage data...
end test