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

Forum related to the ZDBC API-Layer

Moderators: gto, cipto_kh, EgonHugeist

angusj
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 05.01.2017, 01:01

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

Postby angusj » 17.01.2017, 14:33

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
Silver Boarder
Silver Boarder
Posts: 464
Joined: 17.01.2011, 14:17

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

Postby marsupilami » 17.01.2017, 16:43

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
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 05.01.2017, 01:01

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

Postby angusj » 17.01.2017, 18:52

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
Silver Boarder
Silver Boarder
Posts: 464
Joined: 17.01.2011, 14:17

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

Postby marsupilami » 19.01.2017, 10:48

Hello angusj,

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

Jan


Return to “ZDBC (ZEOS Database Connectivity)”

Who is online

Users browsing this forum: No registered users and 1 guest