Getting computed value after insert/update/delete.

[richardpauljones]

SQL Server seems to have the following Syntax for returning the computed values after an insert.

INSERT INTO “tableInsertOSX” (“a”, “b”)
OUTPUT INSERTED.a,INSERTED.b <-------- need this to return values from an insert.
VALUES (‘apricot’, ‘3’)

Suggest adding a new property to kquery.insert .prevalues

UPDATE "tableInsert2OSX"
set b='3'
OUTPUT INSERTED.b <----- need this to return values from update.
WHERE a = 'apricot'

Suggest adding a new property to kquery.update .prewhere

DELETE FROM "tableUpdateOSX"
OUTPUT DELETED.b <----- need this to return valued from a a delete
WHERE "tableUpdateOSX"."b" = '2'

Suggest adding a new property to kquery.delete .prewhere

This should follow the same sort of structure as suffix.

[kilnerm]

@richardpauljones, Sorry it's taken so long to get back to you on this.

Can you help me understand what values are being returned in these cases? I am trying to work out whether there is similar function in the existing plugins.

[richardpauljones]

So the normal behaviour of SQL server for an insert/update/delete is just to return the number of rows affected. This is different in PostgreSQL, where the default behaviour is to return the record you have just inserted/updated/deleted.

To get the same behaviour you need to added the OUTPUT statement as I have shown in my examples above.

I found this issue while trying to run the PostgreSQL test project against my experimental ODBC database driver. The test that breaks is func testInsertID() {

[kilnerm]

I believe the default behavior in PostgreSQL is to just return the number of rows affected. In the case where you are doing an insert and have the returnID property set to true then the buildQuery function in PostgreSQLConnection will add a suffix to the built query to indicate we want the id column returning.

If the positioning of the OUTPUT clause is not important I would suggest mirroring this approach in your plugin.

If the positioning is important then it becomes a little more difficult. You could either patch the clause into the built query or perhaps implement a build method that accepts the query instance and generates the desired output.

[richardpauljones]

Thanks for that.

Yes the positioning of the Output statement is important.

[kilnerm]

@richardpauljones Do you require any further assistance on this issue?

[kilnerm]

I am going to mark this issue as closed. If you require anything further please reopen.