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:
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.
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.
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?
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.
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.