INSERT INTO table (...) VALUES (...) RETURNING oid;

[angusj]

I'm trying to perform an "INSERT INTO table (...) VALUES (...) RETURNING oid" statement using an IZStatement.
Is this possible (and importantly getting the returned value too)?
I know I can do this with a TZquery component, but I'd prefer to avoid that overhead.

[marsupilami]

Hello angusj,

as in the other thread - it is possible. Have a look on how TZQuery works if you want to know how to do it. The DBC layer is not for the faint of heart. Use the JDBC documentation to find out how to do these things. If you have problems, post the source code that you use to get the job done.
Best regards,

Jan

[angusj]

Hi again Jan.
Before posting here I did try to step through (using the debugger) to see what the TZQuery component did to process this statement.
Unfortunately I couldn't follow the logic there. Nevertheless I'll give it another go and try and be a bit more patient.
Also, apart from this particular query, I've found the DBC layer surprisingly easy to use.

Edit: OK, I tried again stepping through this with the debugger and what I'd gleaned, and thought I'd tried before, now seems to work just fine.
Anyhow, for anyone else wondering how to do this, the following seems to work ...

Code: Select all

var
  Connection: IZConnection;
  Statement: IZPreparedStatement;
  ResultSet: IZResultSet;
const
  SQL = 'INSERT INTO transactions (field1, field2) VALUES (''val1'', 'val2'') RETURNING oid;';
begin
  Connection := DriverManager.GetConnectionWithParams(Url, nil);
  Connection.SetTransactionIsolation(tiReadCommitted);
  Connection.SetAutoCommit(false);
  Connection.Open; 
  try
    Statement := Connection.PrepareStatement(SQL);
    ResultSet := Statement.ExecuteQueryPrepared;
    Connection.Commit;
  except
    Connection.Rollback;
  end;
  if Assigned(ResultSet) and ResultSet.Next then
    caption := inttostr(ResultSet.GetInt(1));


[marsupilami]

Hello angusj,

I am glad, it worked out for you. Thank you for sharing your solution.
Best regards,

Jan