LiveCode Forums.

Posted: **Mon Oct 22, 2007 11:41 am**

I want to directly insert files of any type into a sqlite db. I have used various bits of information available on the forums etc, but no success.

If I insert a simple, small text file then it looks intact in the database. I cannot extract it intact- if I retrieve it from the database it is essentially unreadable (although I can see some text).

Advice on the forum suggests using base64encode/decode- no improvement.

I know I can manipulate some files (eg simple text files) with some success. I can read a file, see the file by simply using "answer fileData, encode the file and see that it is now different (again using answer) , then decode the data and see that the file is unchanged.

Using a more complex file (eg pdf) is more difficult to assess. I can see it, then see that it is different with encoding and it seems to be back to normal with decoding. However, the decoded data will not display correctly if I save it as a file (using put fileData into URL "file:***") and then open it with the relevant application (eg preview for pdf).

That is as far as I can go.

I guess this makes 2 questions
1) am I doing something wrong with the encode/decode?
2) if that can be solved, then how do I insert a file of any type into a database AND RETRIEVE IT INTACT?

I am about 1.5 days behind my schedule because of this. I will be very grateful for any help.

sm

Posted: **Mon Oct 22, 2007 12:33 pm**

Dear SM,

Please, post the relevant parts of your scripts.

When you read from or write to files, use binfile.

Use revQueryDatabaseBLOB to retrieve large chunks of data.

You really should save data as base 64, unless you want to escape a lot of special symbols.

Best,

Mark

Posted: **Mon Oct 22, 2007 2:26 pm**

Thank you. I had so many little bits of code trying various options that I did not know what to post. This seems to be the most relevant approach. I have modified it from my original to incorporate the advice you gave about "binfile".

To insert:

Code: Select all

answer file "Select"
   
    put "*b" & URL ("binfile:" & it ) into tFileData
    put base64encode(tFileData) into tFileData64
    put revOpenDatabase("sqlite","/**/fileTest",,,,) into dbID
    put "insert into main (file) values ('" & tFileData64 & "')" into tSQL
    put revdb_execute(dbID,tSQL) into tTmp
    revCloseDatabase dbID

To retrieve:

Code: Select all

put revOpenDatabase("sqlite","/**/fileTest",,,,) into dbID
    put "select * from main where id = '1234'" into tSQL
    put revQueryDatabaseBLOB(dbID,tSQL) into rsID
    put revDatabaseColumnNamed(rsID,"file") into tFileData64
    put base64decode(tFileData64) into tFileData
    put tFileData into URL "file:/**/zzzTest64"
    revCloseDatabase dbID

The insert works, or at least something gets into the database. When I try to retrieve the data (a simple text file), nothing is put into tFileData (ie after decoding). (I understand that this is a small file, not of the size you would normally use the Blob technique, but I am trying to keep it simple at the moment).A small PDF file has something in tFileData, but it is not the same as the original (orig pdf = 40k, decoded pdf = 4k) and the file I try to write out (zzzTest64) seems to end up as a small text file.

Thanks again. I appreciate the assistance. Do I need to write to a binfile? I'll try that in the meantime.

SM

Posted: **Mon Oct 22, 2007 2:40 pm**

Hi SM,

You are encoding "*b". Try:

Code: Select all

put URL ("binfile:" & it ) into tFileData
put "*b" & base64encode(tFileData) into tFileData64

for a start.

I asume that ** stands for a long path.

The retrieving part of your script searches for id 1234, but the storing part doesn't create this ID. Are you sure you need to search for this ID number?

You don't use binfile when saving the retrieved file:

Code: Select all

put tFileData into URL "binfile:/**/zzzTest64"

Best,

Mark

Posted: **Wed Oct 24, 2007 8:06 am**

Thank you, Mark, for your suggestions and please excuse my tardy reply. I did not have much time to try things.
It came down to a matter of trial & error to some extent, but I have something that works.
You correctly noted that "**" refers to the long path to the file. The database is a test file with only 2 fields and it is set to automatically insert & increment the ID for each record. For this part I have simply edited the ID manually.
To insert:

Code: Select all

answer file "Select"
    
    
    put URL ("binfile:" & it ) into tFileData
    
    put base64encode(tFileData) into tFileData64
    
    put revOpenDatabase("sqlite","/**/fileTest",,,,) into dbID
    put "insert into main(file) values (:1)" into tSQL
    revExecuteSQL dbID,tSQL,"*b" & "tFileData64"
    revCloseDatabase dbID

To retrieve:

Code: Select all

put revOpenDatabase("sqlite","/**/fileTest",,,,) into dbID
    put "select * from main where id = '1234'" into tSQL	 
    put revQueryDatabaseBLOB(dbID,tSQL) into rsID
    put revDatabaseColumnNamed(rsID,"file") into tFileData64
  	 
    put base64decode(tFileData64) into tFileData	 
  
    put tFileData into URL "binfile:/**/testFile.txt"	 
    revCloseDatabase dbID

Using "*b" for the encode/decode does not work and reading the documentation again made no reference to using this to pass a file name of a binary file to any function other than database functions. I therefore took it out and things started to look better.
I also started to use parameters to insert into the database and it worked. I prefer to use revdb_execute as you can use the returned value to check that it has worked. The SQLite documentation notes a general limit of 1,000,000 bytes for the length of a SQL string ( this can be increased significantly, but I do not understand how to do it) and simply inserting the encoded binary file into the string could exceed that amount. Passing by parameter seems the best choice.

Thanks again for the help. It took me down the correct path. I am already seeing other things that I will probably need help with so I have no doubt I will be asking the forum for more assistance.

SM

LiveCode Forums.

insert file into sqlite db

insert file into sqlite db