INSERT AND VIEW IMAGES IN sqlite

[tomastoteles]

Hello wonderful community =).

I had the need to insert images in my database. I think I succeeded, but I can not determine if I did correctly. This is my code to insert (including Sqlite connection, if someone serves you):

When Open the Card:

Code: Select all

on opencard
   
    global ConID
 
   #SE DEFINE LA CARPETA DONDE SE ENCONTRARÁ NUESTRA BASE DE DATOS
   put specialFolderPath("Desktop")&"/Herba.sqlite" into tDatabasePath
   
   #CONEXIÓN COMO TAL
   put revOpenDatabase("sqlite", tDatabasePath, , , ,) into tDatabasePath
   
   #VERIFICAMOS QUE LA CONEXIÓN NO TENGA ERRORES
 
      put tDatabasePath into ConID  #ASIGNAMOS ID DE CONEXIÓN
      put "Conexion ID:"& ConID into fld "CID"
   
   put "CREATE TABLE IF NOT EXISTS usuarios (id_usuario integer primary key autoincrement,imagen MEDIUMBLOB);"into tSQL
   revExecuteSQL ConID,tSQL
   put the result into resultado
   if resultado = 1 then
      answer info "Se inició la conexión correctamente"
      else
         answer error resultado
         end if
end opencard

Now, inside the card I have a button where you select the image and opens in a picture box. Within my "Save" button I have the following code, following the logic of what I want to save is the image that is within my picture box:

Code: Select all

on mouseUp
     global conID
put the text of img "image" into tLogo64
put "insert into usuarios (imagen) values (:1)" into tSQL
revExecuteSQL ConID,tSQL, "*btLogo64"
  put the result into resultado
   if resultado = 1 then
      answer info "Se insertaron los datos correctamente"
   else
      answer error resultado
      end if
end mouseUp

Everything is going well with this code, send me the data insersion done properly ... I'm following procedure to insert images are correct? Or what would you recommend? ....


If correct, how could show these images that I picture stored in another box? ... Following the same connection sqlite ....
Thank you very much and best regards!

[phaworth]

Your code looks fine to me.

The way I display images stored in a database is as follows:

- SELECT the row containing the image data. You will have to use revQueryDatabase to do this to create a database cursor.

- Use revDtabaseColumnNamed to get the contenets of your imagen column from the database cursor and put it into a variable

- Write the variable out to a file with the correct extension to identify it's type, e.g. myfile.png, or myfile.jpg

- Use the Livecode Launch command pointing to the created file. That will launch the program defined as the default on the host computer for the type of data.

If you don't already have an SQLite admin program, take a look at my SQLiteAdmin application at http://www.lcsql.com/sqliteadmin.html. Amongst other things, you will be able to use the Browse feature to view the image in your database which I implemented using the method outlined above.

Let me know if that works for you.

Pete

[Simon]

Hi tomastoteles,
I'm not sure about this part;

Code: Select all

put the text of img "image" into tLogo64
put "insert into usuarios (imagen) values (:1)" into tSQL
revExecuteSQL ConID,tSQL, "*btLogo64"

"*btLogo64" isn't tLogo64 text? (first line)

I've done this;

Code: Select all

   put the text of img "Image_Test" into tImg
   put base64encode (tImg) into gImageData

Then just uploaded it to the DB, base64Decode to get it back.

Simon

[golive]

If you don't already have an SQLite admin program, take a look at my SQLiteAdmin application at http://www.lcsql.com/sqliteadmin.html. Amongst other things, you will be able to use the Browse feature to view the image in your database which I implemented using the method outlined above.

Let me know if that works for you.

Pete

Looks interesting.
Also, how is your SQLMagic progressing?

[phaworth]

The *b before the variable name tells lc it contains binary data. No need to base64encode it if the db column is defined as BLOB.
Pete

[Simon]

Yep know about the *b
I guess I'm thrown by

put the text of img "image"...

Not really text?

Simon

[tomastoteles]

First of all thanks for answering ...

In truth I try to insert and view images and do not succeed ... you help me with a concrete example?

I need to do, but really I could not T.T

Thank You!

Thank You!

[zaxos]

Hey there Tomastoteles.
I personaly use base64encode/decode:
UPLOAD

Code: Select all

      put URL ("binfile:" & theDir & "\" & theImage) into tImage -- or put the text of image if its already in the stack.
      put base64encode(tImage) into bImage

      put "UPDATE Movies SET Image='" & bImage & "' WHERE Title='sometitle'" into theQuery
      databaseConnect theQuery

DOWNLOAD

Code: Select all

put "SELECT image FROM Movies WHERE Title='sometitle'" into theQuery
put DBConnect(theQuery) into theData
put base64decode(theData) into tImageData
set the text of img "someImage" to tImageData

[tomastoteles]

Thanks!

thank you very much , it really worked!

Greetings!