i like u use the DatabaseRow instead of string querys and i need a hint to get the primary key in return after the use of AddRow. my id is a serial field in a postgre database.
should i do a query inside of a transaction?
If you’re using SQLite, you can get the last inserted ID from the class:
https://documentation.xojo.com/api/databases/sqlitedatabase.html#sqlitedatabase-lastrowid
1 Like
its a multi user enviroment (with postgre database) where i fear i got the wrong id if someone else insert something at the “same” time.
my current solution is selectsql with insert into and returing id (just a empty row) and then i select this id and edit this data.
so i have all field assignment only once.
this “returing =” is a feature in postgre and can only used in a query.
I think this is the best and only way to do it (for postgres)
I made a generic database class, that calls various db engines depending on the one you need
I created a sqlselect method for this class, but that returns the lastrowid. it switches different method depending on the db engine, but always returns the id. so I have the same call for every database kind.
Your other choice is to add a UUID column (or something) and query on that.
Another option: start a transaction, then SELECT NOW(), then do your insert. If your records have a column that records the creation timestamp, you can query on that since NOW() will be the same value within the transaction.
But honestly, I recommend using some ORM class or a string INSERT with Returning through a prepared statement.
1 Like
my habit is to put the database row data into a class with properties and the class have something like load/save method. that make handling the data easier.
generally i use a transaction outside.
because of rules what can be null and what not, foreign key etc
its not easy to make a standardized insert query which return the row id.
i made a feature request that DatabaseRow.AddRow get a optional return value argument.
Last insert IDs are per connection, so you should always see the right ID for your connection.
6 Likes
Our ORM constructs the INSERT and UPDATE strings on the first Save through Introspection on the class and raising events that translate property names to column names, if necessary.
Save will determine if it’s an INSERT or UPDATE, and use the appropriate string in a prepared statement. (UPDATE will only update columns that have changed.) If INSERT, it will store the returned ID in the Id property.
Our code looks something like this:
var o as new DbRec
// o.Id will be a constant, currently -32464
o.Prop1 = 1
o.Prop2 = true
o.Save db // o will now have an id
o = new DbRec(db, 1) // Loads it from the db and populates the properties
o.Prop3 = "hi there"
o.Save db // Issues an update only for Prop3
FWIW.
1 Like
yes, Introspection could help to copy the field data back and forth.
i not do a 1:1 mapping exactly. as example if i have a adress struct i map this into a adress class
and use a property adress as adressclass. for the automatization idea i need to rethink.
with 2020r1 i have also trouble to use double & currency with insert/update statements and a german os.
feedback no. 61761.
currenty i have more luck with DatabaseRow or RowSet.
For your use (PostGres only) I would use the INSERT INTO … Returning id, but for my cross SQL platform I use:
- INSERT INTO XXX (…, RandomToken) VALUES (…, …)
- SELECT id FROM XXX WHERE RandomToken = ‘…’
- UPDATE XXX SET RandomToken = ‘’ WHERE RandomToken = ‘…’
Hi, the framework Im using has a extra table and a function for it. Benefit It works also in a multiuser environment, and it is more abstract to the databases you are using.
You can also do a reread or select(max…) but I don’t think this is a good idea, in case of batch processing or nested transactions.
BR Rainer
This may be a bit late but if you use a sequence instead of a serial you can:
a) query for the next value: SELECT nextval(‘serial’);
b) and then use that value with the DatabaseRow
Info on sequences:
2 Likes