What is the difference between Bind as Text and Bind as Blob and more?

[mikedoty]

What is the difference between Bind as Text and Bind as Blob?
Bind as Text sounds like it doesn't allow $NUL's, but it was used in an example.

I have tons of 2-byte integers, could I save them using this technique?
Is it possible to create a key or sort on a binary value?
If it is possible to save 2 and 4 byte integers should compression be turned off?

Code Select Expand


FUNCTION WriteBlob(sTableName AS STRING, sFieldName AS STRING, sFlatFileName AS STRING, rsModChars AS STRING) AS STRING
  'If any error the error message is returned.  If no error the function returns nothing

  LOCAL sBuffer    AS STRING
  LOCAL result     AS LONG
  LOCAL s          AS STRING
  DIM lsaBindValues(1) AS STRING

  s = "Create Table If not Exists " + sTableName + " (" + sFieldName + ")"
  result = slExe(s, rsModChars)
  IF result THEN FUNCTION = "WriteBlob Create table error" + STR$(result):EXIT FUNCTION

  result = slGetFile(sFlatFileName, sBuffer, rsModChars)
  IF result THEN FUNCTION = "WriteBlob slGetFile error" + STR$(RESULT):EXIT FUNCTION

  lsaBindValues(1) = "A" & sBuffer  'A=No encrypt, no compress as text,    r = yes encrypt, yes compress as blob
  result = slExeBind(slBuildInsertOrUpdate(sTableName,"?", sFieldName), lsaBindValues(), rsModChars)
  IF result THEN FUNCTION = "WriteBlob slExeBind error" + STR$(RESULT)
END FUNCTION]

[Jean-Pierre LEROY]

Hello Mike,

Thank you for the function WriteBlob; just curious to know if you have already the reverse function ReadBlob; I'm looking for a function to read back the stored data in the SQLite database and re-create the jpg file and/or pdf directly on the PC.

Jean-Pierre

[mikedoty]

When I slPutFile a 4925 byte GIF file named happy.gif only saving 350 bytes.
Have no problem with text only files either locally or remote.
This test only reads the first record of a 1-field table with a field name of images.

Code Select Expand


FUNCTION ReadBlob(sTableName AS STRING, sFieldName AS STRING, sFlatFileName AS STRING, rsModChars AS STRING, OPTIONAL sWriteToFileName AS STRING) AS STRING
  'If any error the error message is returned.  If no error the function returns nothing

  LOCAL sBuffer    AS STRING
  LOCAL result     AS LONG
  LOCAL s          AS STRING
  DIM lsaBindValues(1) AS STRING



  result = slSel("SELECT " + sFieldName + " FROM " + sTableName + ";")
  IF result THEN  s = "ReadBlob slSel error" + STR$(RESULT):GOTO ReadBlobError


 
  'This only gets the first record, needs work on getting the correct record
  LOCAL setNumber AS LONG
  SetNumber = 1
  slGetRow SetNumber
  rsModChars = "E"
  sBuffer = slfx(1,rsModChars)
  rsModChars = "C"                'close
  slGetRow SetNumber, rsModChars 'in case all rows were not read

  IF LEN(sWriteToFileName) THEN
     result = slPutFile(sWriteToFileName, sBuffer, rsModChars)
     IF result THEN s = "slPutFile error" + STR$(result):GOTO ReadBlobError
  END IF


EXIT FUNCTION

ReadBlobError:
  s = s +  $LF + "TableName: " + $DQ + sTablename    + $DQ + "  FieldName: " + $DQ + sFieldName + $DQ + _
                 " FlatFile: " + $DQ + sFlatFileName + $DQ + " WriteToFile: " + $DQ + sWriteToFileName + $DQ
  FUNCTION = s
END FUNCTION

Code Select Expand


FUNCTION WriteBlob(sTableName AS STRING, sFieldName AS STRING, sFlatFileName AS STRING, rsModChars AS STRING) AS STRING
  'If any error the error message is returned.  If no error the function returns nothing

  LOCAL sBuffer    AS STRING
  LOCAL result     AS LONG
  LOCAL s          AS STRING
  DIM lsaBindValues(1) AS STRING

  rsModChars = "E"
  result = slGetFile(sFlatFileName, sBuffer, rsModChars)
  IF result THEN  s = "WriteBlob slGetFile error" + STR$(RESULT):GOTO WriteBlobError
 
  lsaBindValues(1) = "B" & sBuffer  'A=No encrypt, no compress as text,    r = yes encrypt, yes compress as blob
  result = slExeBind(slBuildInsertOrUpdate(sTableName,"?", sFieldName), lsaBindValues(), rsModChars)
  IF result THEN s =  "WriteBlob slExeBind error" + STR$(RESULT):GOTO WriteBlobError
EXIT FUNCTION

WriteBlobError:
  s = s +  $LF + "TableName: " + $DQ + sTablename    + $DQ + "  FieldName: " + $DQ + sFieldName + $DQ + _
                 " FlatFile: " + $DQ + sFlatFileName + $DQ
  FUNCTION = s
END FUNCTION   

[Fred Meier]

From SQLite3 doc:

Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:

NULL. The value is a NULL value.
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16-LE).
BLOB. The value is a blob of data, stored exactly as it was input.

I compared the raw data in two databases.  One stored the data as Text the other stored as Blob.  There was a one byte difference which I assume is the data type code.   So unless you change the "text database encoding" using a Pragma, I would conclude there is no difference.  You may want to do your own testing.

[cj]

Suggest using slBuildBindDat(sData$,"T") to return text in expected order.
CAST AS TEXT to the rescue if bind "B" or no bind parameter was used to insert any record like slbuildbinddat(sData$)

create table T1(C1)
slExeBind "Insert into T1 values(?)",slBuildBindDat("1")     'binary bind
slExeBind "Insert into T1 values(?)",slBuildBindDat("2")     'binary bind
slExeBind "Insert into T1 values(?)",slBuildBindDat("3","B") 'binary bind
slExeBind "Insert into T1 values(?)",slBuildBindDat("11")    'binary bind
Results:
11,2,3,1  Select c1 from t1 order by c1                     binary order is not wanted
1,11,2,3  Select c1 from t1 order by (c1 CAST AS TEXT)      CAST into correct text order
'-----------------------------------------------------------------------------------------------------------------------

THREADED tsResult AS STRING                                     
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG  'BindWithCast.bas 9/18/18 CJ
slOpen ":memory:"
slexe "create table T1(C1)"
slExeBind "Insert into T1 values(?)",slBuildBindDat("1")       'binary since "T" was not used
slExeBind "Insert into T1 values(?)",slBuildBindDat("2","T")   'text
slExeBind "Insert into T1 values(?)",slBuildBindDat("3","T")   'text
slExeBind "Insert into T1 values(?)",slBuildBindDat("11","T")  'text
GetData "Select C1 from T1 order by C1"
GetData "Select C1 from T1 order by CAST(C1 AS TEXT)"
GetData "Select C1 from T1 order by CAST(C1 AS INTEGER)"
? tsResult,%MB_SYSTEMMODAL,"CAST - Mixed Binary and Text Data"
END FUNCTION

FUNCTION GetData (sql AS STRING) AS STRING
DIM  sArray() AS STRING
slselary sql,sArray(),"Q9c"
tsResult+= sql + $CR + JOIN$(sArray(),",") + $CR + $CR
END FUNCTION