API1: Saving a Picture

Databases
1

At Record saving time, you may or not have the Picture for that PictureColumn, as well as all the fields data.

I searched at The Update Statement and found the syntax that works fine .

Syntax:

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

But this does not works for a PictureColumn (code extract):

Issue_Cover='" + cCover.Backdrop + "', " etc.

Of course, Xojo was awaiting a String and I give it a Picture (that is what the code error told me.

How do I code that ?

PS: at Record creation time, I pass a PictureColumn, not a String and so everything is OK.

Record_DBR.PictureColumn("Issue_Cover") = cCover.Backdrop

Same kind of code apply for Reading.

Advice ?

2

Apparently, using RecordSet.Update is the solution.

I only set the image using the provided example and customizing it. I will complete it to update the whole Record.

3

BUG in 2015r1 AND 2019r1.1:

The following code write nothing:

[code]Dim rs As RecordSet

db.SQLExecute(“BEGIN TRANSACTION”) // N’écrit plus rien si pas en commentaire !

rs = db.SQLSelect(“SELECT * FROM " + mSQL.dbTABLE + " WHERE ID=’” + Str(mSQL.Record_ID) + “’;”)

rs.Edit
If Not db.Error Then[/code]

But if I comment the BEGIN TRANSACTION line, everything works fine,!

Guess: how many times did I waste until I get the idea to comment the line ?

FWIW

4

Well yeah. The commands may not be executed until you call COMMIT…

5 
" WHERE ID='" + Str(mSQL.Record_ID) + "';"
  • if id is a integer value it should look like
" WHERE ID=" + Str(mSQL.Record_ID) + ";"

at r2 we have a new feature to insert values in the query
Database.SelectSQL

6

Thank you for your answers.

@Greg:
There is no COMMIT in the example (nor BEGIN TRANSACTION). I added the later (and forget the former). Are-they mandatory ?

Removing COMMIT TRANSACTION makes the code “working”. *

@Markus:
ID is an Integer and the code works fine (even if the two single quotes are useless). Fortunately, Xojo remind me when I forget to convert the ID value to String.

2019r2: thank you, but no thank you. I am too old for this gamble and my license to build application(s) does not go to that version.
Day after day, it tooks me more time to write code (I have to look more often to the documentation / my previous projects (when I recall I do “that“ in an old project).

  • For that project use, I added a batch add Records from a text file for the core of the Record inforation (I have these information handly for a ton of subjects); the UPDATE RECORD is meant (in part) to let the user complete the Records with images and specific data (into a Listbox / a TextField and a Comments TextArea). I may eventually add the images import and save automatically (I have to explorate it.

After all, a computer is here to run by itself all repetitive tasks, when possible and I found that boring to do it manually.

7

Emile, simply put for every BEGIN TRANSACTION there should be either a COMMIT or Rollback statement. After the BEGIN TRANSACTION statement, the database system “accumulates” the commands but doesn’t actually write them to the database. (This is why on multi-user databases, one user may make a change while another user retrieves old data.) Once all of the database commands are done, the program should issue a COMMIT statement to write the changes to the database. If, however, an error occurred, a ROLLBACK statement can roll back the changes, essentially throwing out the unCOMMITted edits.

If a database command is issued before a BEGIN TRANSACTION statement, the database sees it as a standalone transaction and COMMITs it automatically with no chance of rolling it back.

In practice, the recommendations are that SELECT statements can be done without starting a transaction but any commands that alter the database in any way should be bracketed in BEGIN TRANSACTION and COMMIT statements.

8

Thanks !