w3school.com and every other web site says for update:
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
But I have a Photo to store and the above syntax is rejected (of course).
Edit: I use SQLite (sorry, I forgot).
Ideas ? Clues ? Something ? Anything ? Please !
PS: In an older project, I created a specific TABLE with three fields (First_Name_Last_Name and Photo_ID) and store the image that way. Here I defined the Photo_ID in a single TABLE and that is good for New Record and Open .
[code]dim Photo_ID as new picture(100,100)
dim mb as MemoryBlock = Photo_ID.GetData(picture.FormatJPEG,50)
dim mbs as string = mb.StringValue(0,mb.Size)
dim encoded_Photo_ID as string = EncodeHex(mbs)
sql=“UPDATE Customers Set Photo_ID = '”+ encoded_Photo_ID+"’" WHERE CustomerID=1"
[/code]
Make sure the type of Photo_ID is Text(not varchar) so it’s not limited with respect to size.
Also make sure you are escaping the any text such as name if updating / inserting using a string command. Test with a name such as O’Leary . The apostrophe can break stuff.
Adding an image into the database like this is not very efficient, but unless there are 1000s of Customers it’s probably not a problem.
Thanks Lee, makes sense, but I declared the Photo as Picture and use PictureColumn to deal with (in RecordSet and DatabaseRecord (I think, Xojo is off and I have to go).
I do have far less 100 users to store their data (Name address, Phone, etc.) So, no problem here.
Or in case of error. You might want to read Sqlite documentation. Transactions eighter happen completely or don’t happen at all.
So if something happens inbetween. The transaction might be as if it never happened, preventing data loss and corruption.
If you have a power failure, hardware failure, i/o error etc. the above applies.
Use “BEGIN TRANSACTION”, “SQL QUERY”, “END TRANSACTION”. You might want to subclass the SQLiteDatabase and add 2 methods.
BeginTransaction
Self.SQLExecute("BEGIN TRANSACTION")
EndTransaction
Self.SQLExecute("END TRANSACTION")
There is no need to commit as END TRANSACTION will do that for you, but keep checking errors as normal. SQLite Transaction Documentation
SQLiteDatabase said:
SQLite supports transactions, both for schema changes and for data changes. A transaction is started automatically when you make any change to the database and is ended by calling either the Commit or Rollback methods of the database class.
And read some lines below the Transactions entry.
That is how Xojo behaves. Sometimes it is a bit different than what sqlite.com says (or not).
