LastRow ID From PostgeSQL using Xojo DB API

All of my DB apps have used Sqlite but this time I need true Client Server so I will be using PostgreSQL really for the first time. I have not moved of of API 1 yet an would rather not have to now as it would slow me down and I need to get this done ASAP.

In Sqlite I tend to often use the Xojo DB API (well for API 1) to do inserts and an used haven the last RowID provide… But the PostgreSQL plug does not provide that…

From the PostgreSQL docs the only way (without having to have a way to select the record I just entered) is doing an SQL INSERT with the RETURNING clause…

But if I do:
DB.InsertRecord(theTable, theDBRecord) for API 1
Or
DB.AddRow(theTable, theDBRow) API

That does not allow me to do that as far as I can see.

I there a way to do it with prepared statements so I could return the row ID?

Doing a direct SQL INSERT with user entered strings is not a good idea…

So how do people get the “LastRowID” from PostgresSQL using Xojo after inserting a row?

-Karen

You use the returning clause see https://www.postgresql.org/docs/9.5/dml-returning.html

Send your inserts with “RETURNING id” and using a selectSQL instead of executeSQL and you get a rowset back with the id.

var oRowSet as Rowset = selectSQL("INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;")

PostgreSQL: Documentation: 10: 6.4. Returning Data From Modified Rows

@Brandon_Warlick

I know that… In fact I mentioned it in my post…

But that means I can’t use Xojo prepared statements or The Xojo Database API… Which means I have to sanitize user inputs myself?

That is my concern.

-Karen

If you’re using API2 you would use SelectSQL instead of ExecuteSQL and you get a rowset back with the returned value(s). API2 uses prepared statements under the hood, so no data sanitizing needed.

2 Likes

API2 database access is easier/faster and I would suggest trying it.

I can’t remember how to do prepared statements in API 1 but the point is to use a method that returns a recordset or rowset rather than doing your insert with an execute statement. Combine this with the postgreSQL returning clause and you get back the id of the record that you inserted.

@Wayne_Golding

Thanks both of you… Looking at the Xojo PreparedPreparedStaemeny docs I realize I can do what I want with either API1 or API2… I just had never used an Insert PreparedStatement with Select.

A different question…

As I said I prefer to stay with API 1 so although I have a current license I prefer to stay with 2019R1.1.

Would there be any issue with using the PostgreSQL plug-IN with the current PostgreSQL distribution (14.1) or would I need to use a new version of the plugin?

Have any significant Postgre plugin bug fixes been made since 20191.1 that are likely to matter?

-Karen

There was an update to the PostgreSQL plugin recently, but the plugin from 2019r1.1 worked fine for me when I was using that version. As far as I am aware, the Xojo plugin works with any version of PostgreSQL.

Thanks. Glad to hear that… Do you know what the newer PostgreSQL plugin changes are? It may be possible to use a newer version with Xojo 2019r1.1 so I don’t have to worry about API 2 (besides having to learn API 2 , I like to maintain backward compatibility just in case)

I also have a different question about using Postgres but I’ll do that in another thread.

-Karen