SQLiteDatabase UPDATE

  1. 6 months ago

    Emile S

    10 Jan 2018 Europe (France, Strasbourg)

    I have hard times (in fact, I cannot) trying to UPDATE a Record WITH a Picture.

    That is a PictureType Field. When I put it in my SET… string, I get at compile time a type error (basically: this is not a String…)

    So, I tried to made a second UPDATE, but it too failed:

     sql_Update = "UPDATE Accueillis SET Photo_ID='" + cPhoto.Backdrop + "' WHERE ID='" + TF_ID.Text + "';"

    So, I converted the Picture (PNG) as string:

      Dim mb As MemoryBlock
      
      If cPhoto.Backdrop = Nil Then
        // Do nothing
        Return
      End If
      
      // Get the image data
      mb = cPhoto.Backdrop.GetData(Picture.FormatPNG, Picture.QualityMax)
      
      
      sql_Update = "UPDATE Accueillis SET Photo_ID='" + mb.StringValue(0,mb.Size) + "' WHERE ID='" + TF_ID.Text + "';"  // Special Case: PictureColumn
      
      mSQLite.gAccueillis_DB.SQLExecute(sql_Update)
      If mSQLite.gAccueillis_DB.Error Then
        MsgBox "[2] Modification de la fiche (Photo) dans la base “Accueillis”." + EndOfLine + EndOfLine +_
        "Erreur " + Str(mSQLite.gAccueillis_DB.ErrorCode) + ": " + mSQLite.gAccueillis_DB.ErrorMessage
        
        Return
      End If

    I got an error message with the first bytes of the image (the replacement character, PNG, etc.).

    Do you use to UPDATE a Record with an image, and if so, how ?

  2. Jean-Yves P

    10 Jan 2018 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

    you must encodebase64 the string of the picture before inserting it into an sql query.

  3. Emile S

    10 Jan 2018 Europe (France, Strasbourg)

    @Jean-YvesPochez encodebase64

    Thanks.

    Will try right away.

  4. Emile S

    10 Jan 2018 Europe (France, Strasbourg)
    Edited 6 months ago

    Tried: it works fine, do what I wrote, but not what I want.

    When I add a Record, I store the image (in this case a Photo) in a PictureColumn and I get it back as a PictureColumn.

    String Picture(s) are not involved here.

    I think that the last time I wanted to do that (according to code I saw but not really look at), I create a new Record that I save and delete the previous one. Strange idea.

    I fired Feedback, but lost my time (not found a clue).

    Beside the above strange idea, someone have a clue ?

    Edit: used code:

    EncodeBase64(mb.StringValue(0,mb.Size),0) // I do not display the whole line; read above…
    Looks like an entry for one liners !

  5. Jean-Yves P

    10 Jan 2018 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

    use a string column and not a picture column, they are useless (to me at least)

  6. Alexander v

    10 Jan 2018 Europe (Houten, The Netherland...
    Edited 6 months ago

    Convert the file to a String and store the string as you would store any string. I use this to store any file regardless of its length.

    Function FileToString(f as FolderItem) As String
      'reads a file as BinaryStream
      'and saves as string to be used in string field type
      'no need for Blobs and fast
      
      dim b as BinaryStream
      dim data as String
      b = BinaryStream.Open(f,false)
      app.DoEvents
      data = b.Read(b.length)
      if b.ReadError then
        ShowMsg("Error reading file " + f.Name")
      end if
      b.close
      return data  
    End Function

    decode the file with

    Function StringToFile(data as String, fn as String) As FolderItem
     
      'initiate binarystream
      'and folderitem
      
      dim bs as BinaryStream
      
      'create the actual file
      dim f as Folderitem = GetFolderItem(fn)
      
      bs = BinaryStream.Create(f,true)
      
      'write the file
      bs.Write(data)
      bs.Close
      
      return f
    End Function
  7. Dave S

    10 Jan 2018 San Diego, California USA

    @Jean-YvesPochez you must encodebase64 the string of the picture before inserting it into an sql query.

    actually.... no you don't

  8. Emile S

    10 Jan 2018 Europe (France, Strasbourg)

    Thanks all.

    @Dave: care to explain ?

  9. Dave S

    10 Jan 2018 San Diego, California USA

    this is why there is a BLOB datatype... to handle any "binary" type data

    This is how Tadpole as well as most other SQLite Managers deal with Images as well as "document" and other file types to be stored IN the database as opposed to stored as external links

  10. Graham B

    10 Jan 2018 Pre-Release Testers, Xojo Pro The Canada's
    Edited 6 months ago

    If you define the column as a BLOB you can put a binary string into it.

    To add

    dim dbr as new DatabaseRecord()
    dbr.IntegerColumn("id") = myID
    dbr.BlobColumn("imageData") = stringData
    mDb.InsertRecord("tablename",dbr)

    To Update

    rs = mDB.SQLSelect("SELECT imageData FROM tablename WHERE id = " + str(myID) ,false)
    if rs <> nil AND not rs.EOF then
      rs.Edit()
      rs.IdxField(1).setString(stringData)
      rs.Update()
      rs.Close()
      if mDB.Error then
        mDB.Rollback()
      end if
    end if

    Where rs is a record set

    I use JPEGExporterMBS to export image to binary string

  11. Emile S

    10 Jan 2018 Europe (France, Strasbourg)
    Edited 6 months ago

    Hi all,

    I certainly was not clear enough.

    I created the Field as Picture. I open Picture and save it to the proper Field in the Record.

    I open the SQLiteDatabase and display the Picture in a Canvas with PictureValue.

    Get an eye here: DatabaseField.PictureValue .

    In short: using PictureValue allow me to Store an image (from an image file or Paste int he Canvas) in a Record that I can save to disk. I also am able to read the RecordSet that have an image (as PictureValue) and assign it to my Canvas.

    My only problem is to update a RecordSet with an image. I tried PictureValue but I failed.

    Also: using code shared above, I can store an image converted to string in a MemoryBlock and Encoded 64 bits… But code like that is too error prone and I will have to change the Add a Record / Read a Record code and this is… pretty stupid IMHO.

    My own SQLite data base reader is able to display PictureValue fields too without any trouble.

    So, either there is a bug, a lack in the documentation, a bug or people use an Atomic Bomb to kill ants (convert an image to string, BASE64 in bot read and write from/to the Database…

    Xojo ?

    Edit:
    BTW, I do not want to store an URL or a Path or… whatever examples I found in a Google quest… :(

  12. Emile S

    10 Jan 2018 Europe (France, Strasbourg)

    FWIW: I even add an Export a Record that… Export a Record in a .txt file and export the image in an image file.

    Nota: I do not have many Records (4 .sqlite files) and the Pictures are Photo IDs and the size near 300 x 400 pixels…
    I still have to add an Import a Record method for obvious reasons (import data into a different .sqlite file if needed).

  13. Tim H

    11 Jan 2018 Pre-Release Testers Portland, OR USA
    Edited 6 months ago

    @Emile S My only problem is to update a RecordSet with an image. I tried PictureValue but I failed.

    @Graham B code above should work.

  14. Emile S

    11 Jan 2018 Europe (France, Strasbourg)

    Hi Tim,

    thanks for your answer.

    But Graham code will force me to change my Read / Write code. I think that I will go back to Insert a new Record (with all the data) and delete the previous Record (the one I want to modify). Not so hard to write (I only have to store the Unique ID and few lines to delete it at the end of the current write Method).

    BTW: yes, it is a solution.

  15. Hi @Emile S ..
    I look on the code you have post but I don't see the rs.edit ?
    check this.. Call Edit prior to performing modifications to the current record

  16. Graham B

    11 Jan 2018 Pre-Release Testers, Xojo Pro The Canada's

    How have you defined your Photo_ID column in your database?
    Null, integer, real, text or blob? If you have used blob then you should be able to use my above code to update using the picturevalue property instead of setstring / string value (which pretty much does the same thing behind the scenes)

    If you have it set to text then the data is going to be encoded utf8 etc and more than likely will corrupt your data. Unless you convert your image to a string and encode correctly.

    Updating binary data with a sql command in xojo is hard and using database classes to do it for you is best option.

  17. Tim H

    11 Jan 2018 Pre-Release Testers Portland, OR USA

    FWIW, I reviewed my code and for whatever reason that I do not recall now, we delete the previous record and insert a new one.

  18. Emile S

    11 Jan 2018 Europe (France, Strasbourg)
    Edited 6 months ago

    @Graham B How have you defined your Photo_ID column in your database?

    PictureColumn

    Look at DatabaseRecord . Last Method.

    @LoannisKolliageorgas I look on the code you have post but I don't see the rs.edit ?

    I never use it (and I do not know why). Maybe absent in the shared examples for SQLSelect or SQLExecute ?

    Edit: and I read that conversation. So, I probably not understant what it was about :(

  19. Emile S

    11 Jan 2018 Europe (France, Strasbourg)

    @Tim: you may end with that solution because you do not found a better one (as I do too) ?
    That solution is in the mood of time, not specific to you and me.

    Digression:
    When I was young (50 years ago or so), some people told me about existing pyramides in Aegypt and America (Central / South) letting the door open to suggest Aliens where behind them.
    The time and photos have proved otherwise: the former is used once and as a thumb, the latter is used outside, often, and to kill people (for the "gods".
    Yes, the base idea was similar: a square building in stone, but not its use.

    There are other examples of similar ideas, different implementations (78RPM --> 33RPM: SPs and LPs; and in that case the two survived until the Compact Disc).

  20. Graham B

    11 Jan 2018 Pre-Release Testers, Xojo Pro The Canada's
    Edited 6 months ago

    @Emile S PictureColumn

    Look at DatabaseRecord . Last Method.

    You miss understood. There is no concept of a "PictureColumn" in a sqlite database.

    https://www.sqlite.org/datatype3.html

    The column is defined as NULL, INTEGER, REAL, TEXT, BLOB.

    PictureColumn is a Xojo property / method to allow easily read and write a xojo picture to a database.

    Generally you do not update binary data in a sql command, you use the classes to update it. Binary data will contain control characters that will screw up your sql commands.

    This is the code, or something very simular (not tested at all), to add or update into your tables.

    dim sql as string = "SELECT * FROM Accueillis WHERE ID='" + TF_ID.Text + "'"
    
    dim rs as recordset = mSQLite.gAccueillis_DB.SQLSelect(sql)
    
    if rs = nil or rs.eof then
        //No record found - add a new one
    	dim dbr as new DatabaseRecord()
    	dbr.Column("ID") = TF_ID.Text
    	dbr.PictureColumn("Photo_ID") = cPhoto.Backdrop //will default to Picture.FormatPNG, Picture.QualityDefault
    	mSQLite.gAccueillis_DB.InsertRecord("Accueillis",dbr)
    	if mSQLite.gAccueillis_DB.Error then
    		//failed
    	else
    		//Worked	
    	end if
    else
        //found a record - update it
    	rs.Edit()
      	rs.Field("Photo_ID").pictureColumn = cPhoto.Backdrop //will default to Picture.FormatPNG, Picture.QualityDefault
      	rs.Update()
      	rs.Close()
      	rs = nil
      	if mSQLite.gAccueillis_DB.Error then
      		//failed
        	mSQLite.gAccueillis_DB.Rollback()
    	else
    		//worked
      	end if
    end if

    Note: This assumes that ID is the tables UID and there is only ever one row with that ID.

  21. Newer ›

or Sign Up to reply!