Primary key return on add row

[John_Smith4 (John Smith)]

You guys make it sound like fishing for perch!

[Craig_Boyd]

Yeah, but with air conditioning and a comfortable chair.

[Richard_Klingler]

Works just fine here with MySQL on my RPi with following snippet in a Button pressed event:

Var db As New MySQLCommunityServer
db.Host = "10.0.100.65"
db.Port = 3306
db.DatabaseName = "test"
db.UserName = "root"
db.Password = "only4me"
Try
  db.Connect
  ' Use the database
  
  Var row As New DatabaseRow
  Var pk As Integer = 0
  
  ' ID will be updated automatically
  row.Column("name") = "Richard Klingler"
  row.Column("street") = "Irgendeinestrasse 1a"
  row.Column("city") = "City"
  row.Column("zip") = "55555"
  
  Try
    pk = db.AddRow("users", row)
    MessageBox("Got primary key ID: " + Str(pk))
  Catch error As DatabaseException
    MessageBox("DB Error: " + error.Message)
  End Try
  
Catch error As DatabaseException
  ' DB Connection error
  MessageBox(error.Message)
End Try

Always MessageBox pops up with incremented ID.

In Navicat I did quickly set the DB up like:

[Richard_Klingler]

When you have that database exception error…which error number does it show?

I have in my example changed the database to a name that doesn’t exists and then it gives:

Now click on the DatabaseException link, and then you’ll see the error number and further description:

Wondering though why the exception handler didn’t trigger, but just crashes and you have to resume processing.

[John_Smith4 (John Smith)]

Richard, I typed this code in with my db info, and it DOES work, so interesting. Your method is a bit different than in Xojo documentation (well, sort of, referencing the addrow(a,b,c) with 3 parameters) -but I’ll run with it! Thanks for sticking with it, wow, I really appreciate it!

[Richard_Klingler]

Oops…forgot that one…but it also works for me with:

Try
  pk = db.AddRow("users", row, "id")
  MessageBox("Got primary key ID: " + Str(pk))
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
End Try

[Richard_Klingler]

Just tested quickly with PostgreSQL 15.

Works as well, but it only allows to return columns of type Integer.

Though PSQL can return anything you want, like:

insert into users (name, street, zip, city)
values ('Richard Klingler', 'Somestreet 1', 7707, 'Some Smalltown')
returning id, name || ', ' || zip || ' ' || city as entry

Gives:

id, entry
8, Richard Klingler, 7707 Smalltown