SQLiteDatabase UPDATE

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:

[code] 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[/code]

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 ?

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

Thanks.

Will try right away.

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 !

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

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.

[code]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
[/code]

decode the file with

[code]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
[/code]

actually… no you don’t

Thanks all.

@Dave: care to explain ?

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

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

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… :frowning:

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).

@Graham Busch code above should work.

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.

Hi @Emile Schwarz …
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

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.

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.

PictureColumn

Look at DatabaseRecord . Last Method.

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 :frowning:

@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).

[quote=368527:@Emile Schwarz]PictureColumn

Look at DatabaseRecord . Last Method.[/quote]

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.