Postgres INSERT ... RETURNING

So I want to handle my own INSERTs and UPDATEs and have a statement that works perfectly from pgAdmin but does nothing (including not throwing an exception) in Xojo. It just returns a Nil rowset. The SQL is:

INSERT INTO rules.rule_table (name_matches, name_match_type, action_value, is_active, action_type, remarks) VALUES ($1,$2,$3,$4,1,$5) RETURNING id"

And yes the literal 1 passed to action_type is deliberate, it’s a placeholder for future functionality.

I call this with db.Select(), passing 5 values and am expecting a rowset back with one row and one column containing the integer primary key. But I get a Nil rowset and no exception thrown.

Can anyone explain what is going on here? I can experiment of course, remove the RETURNING clause (maybe Xojo doesn’t understand or support it), try a non-parameterized version, etc. But I’m thinking someone has to have encountered and dealt with this issue already.

Are you using db.SQLSelect? If so, you have to check db.Error.

If db.SelectSQL, I have no explanation as we do this all the time.

Edit: I should say, we do this with db.SQLSelect all the time. I think we do it in some places with db.SelectSQL too, but can’t say for sure.

1 Like

db.SelectSQL is API 2

db.SQLSelect is API 1

I’m using SelectSQL(). I can see that those would be easy to mix up.

You might try ExecuteSQL instead. The Docs say it has Parameters like you are sending.

1 Like

They should have used db.SQLSelect2 for Api2 to avoid mixing them up

That won’t return a result, so not in this case.

Postgres will return a result, due to the RETURNING clause. That’s why I’m using SelectSQL() instead of ExecuteSQL(). In the pgAdmin UI, it returns a single row with 1 column containing the requested primary key value.

In the Sql Server world we do a similar thing, the syntax is just different:

INSERT tableName (fieldlist) VALUES (valuelist);SELECT SCOPE_IDENTITY()
… and you get back a value that you either retrieve as a row or as an ordinal value.

Surely there is a way to do this in Xojo. Simplistically, one could just do the INSERT and then query MAX(primaryKeyName) in a separate select, but that isn’t guaranteed to work with other users editing the same table concurrently. The other problem is that whatever is going wrong is failing silently, which is not a Good Thing either. If Xojo is confused by RETURNING then it should say so, not just quietly do nothing.

Please test with db.SQLSelect and check db.Error to see if there is a different result. There is no reason this should not work.

this code i used some xojo versions ago and it works

Var row As RowSet = MyDatabase.SelectSQL("insert into dienstleistung (arbeitsort_id) values ($1) returning dienstleistung_id", Me.Arbeitsort_ID)
		    
Me.Dienstleistung_ID = row.Column("dienstleistung_id").IntegerValue

I don’t see an Error property on PostgreSQLDatabase … maybe that is an API 1 thing? I’m already well along using API 2 for this app.

Yep that is exactly what I am doing. Gives back a Nil rowset and doesn’t raise an exception.

It’s there, just hidden.

I’m not suggesting you change your app, just perform a test for comparison.

hmm, i remember xojo can behave different at 32/64 bit,
have you test it also at 32 bit?
is this app event unhandled exceptions unused?
do you use the same user in pgAdmin and xojo?

this example was used with postgres 12.x

Yes it’s the latest Postgres install and 64 bit. Target here is MacOS M1. I don’t have an environment to test Wintel at this time though I could maybe try MacOS Intel. But that is a lot of work for a long shot guess. Maybe I will break down and figure out the built-in Edit / Save although the docs are pretty thin on that.

O,IC … I’ll give that a try then.

The Error property on PostgreSQLDatabase is a boolean. It is false on return from the select.

Maybe the whole snippet in context will suggest something:

var rsid As RowSet
Try
rsId = App.DBConnection.SelectSQL(“INSERT INTO rules.rule_table (name_matches, name_match_type, action_value, is_active, action_type, remarks) VALUES ($1,$2,$3,$4,1,$5) RETURNING id”,_
txtNamePS.Text,_
cbMatchTypePS.SelectedRowValue,_
txtReplacementPS.Text,_
chkActive.Value,_
txtRemarks.Text)
Catch ex As RuntimeException
MessageBox("DB insert failed: " + ex.Message)
End Try

If App.DBConnection.Error Then
MessageBox(“There was a DB error.”)
End If

Please try this test code. (This works just fine here.)

App.DBConnection.ExecuteSQL "CREATE TEMP TABLE tmp_tester (" + EndOfLine + _
"id BIGSERIAL PRIMARY KEY," + EndOfLine + _
"name_matches TEXT, " + EndOfLine + _
"name_match_type TEXT, " + EndOfLine + _
"action_value TEXT, " + EndOfLine + _
"is_active BOOLEAN, " + EndOfLine + _
"action_type INTEGER, " + EndOfLine + _
"remarks TEXT" + EndOfLine + _
")"

var rs as RowSet = App.DBConnection.SelectSQL( _
"INSERT INTO tmp_tester (" + EndOfLine + _
"name_matches, " + EndOfLine + _
"name_match_type, " + EndOfLine + _
"action_value, " + EndOfLine + _
"is_active, " + EndOfLine + _
"action_type, " + EndOfLine + _
"remarks" + EndOfLine + _
") VALUES ($1, $2, $3, $4, 1, $5) RETURNING id", _
"some_name", _
"some_type", _
"some_action", _
true, _
"some_remarks" _
)

MessageBox rs.Column( "id" ).StringValue

App.DBConnection.ExecuteSQL "DROP TABLE tmp_tester"

I get a NilObjectException on the MessageBox call. Same behavior as I’m seeing in my code.

I’m new-ish to Postgres but would expect to see tmp_tester as a table from pgAdmin but can’t find it before the DROP TABLE statement, even if I put it in a specific schema (entity_rules in my case). What I’d like to verify is that the row isn’t added, but am guessing that is the case.

I set that up as a TEMP table. If you want to leave it around, you can change the first part to:

CREATE TABLE IF NOT EXISTS tmp_tester

and comment out the line that drops the table.

As mentioned, this code works just fine here, so something else is going on there.