Positioned insert dont give autoincrement value

[Pierre Vacher]

Hi all,

I'm currently trying to integrate Jaybird 6 with the LibreOffice jdbcDriverOOo extension.

Until now, jdbcDriverOOo used SQL INSERT queries with Jaybird in LibreOffice Base instead of ResultSet positioned inserts due to issue#818.

Now with Jaybird 6 the positioned inserts are well managed because they are inserted at the end of the resultset as Base requires.

On the other hand, I lose the ability to be able to retrieve the value of autoincrement columns during insertions.

I'm experiencing this problem with several JDBC drivers, and I'm wondering if this is a JDBC limitation between SQL Inserts and  positioned insert on ResultSets?

And if so, is there a way around it?

Thank you for your advice.

[Mark Rotteveel]

On 16/04/2025 12:18, Pierre Vacher wrote:
> I'm currently trying to integrate Jaybird 6 with the LibreOffice
> jdbcDriverOOo extension.
>

> Until now, jdbcDriverOOoused SQL INSERT queries with Jaybird in

> LibreOffice Base instead of ResultSet positioned inserts due to

> issue#818 <https://github.com/FirebirdSQL/jaybird/issues/818>.

>
> Now with Jaybird 6 the positioned inserts are well managed because they
> are inserted at the end of the resultset as Base requires.
>
> On the other hand, I lose the ability to be able to retrieve the value
> of autoincrement columns during insertions.
>
> I'm experiencing this problem with several JDBC drivers, and I'm
> wondering if this is a JDBC limitation between SQL Inserts and
> positioned insert on ResultSets?
> And if so, is there a way around it?

The problem essentially is that

1) AFAIK, most JDBC drivers need to implement updatable result sets
client-side, especially when it comes to inserts, so usually there are
some oddities and deficiencies.

2) In practice, updatable result set are - at least in my experience -
hardly ever used in Java, so they don't get much use and thus little
attention.

3) JDBC does not specify (or at least, not clearly) what happens after
`insertRow()`, e.g. should you be able to use `getXXX` to get values
that were just inserted? Or will it behave as if `movetoInsertRow()` was
called (and that behaviour itself is also not clearly defined)?
For Jaybird, after `insertRow()`, the row content is reset so it is a
fresh empty row again.

4) JDBC does not define how you can identify which row in the result set
has the data that was just inserted. It just provides methods so you can
identify which rows were inserted.

5) For Jaybird, we simply record the row we sent to the server to be
inserted, and use that when you try to access that row later through the
result set. This means that generated values like ids are not reflected
in that row, so even if points 3 and 4 were defined, you still wouldn't
be able to get the generated id.

Now, I could probably address point 5 by using RETURNING to collect the
data, but because of point 2, I'm not sure if that is good use of my
time, and because of points 3 and 4, it might not be of much use to you.

Now my question to you is, *if* addressing point 5 would be sufficient
for you. That is, do you have enough information if I use INSERT ...
RETURNING ... to populate the (missing columns of the) row that is
stored in the result set?

Mark
--
Mark Rotteveel

[Pierre Vacher]

Hi Mark,

> Now my question to you is, *if* addressing point 5 would be sufficient
> for you. That is, do you have enough information if I use INSERT ...
> RETURNING ... to populate the (missing columns of the) row that is
> stored in the result set?

Yes, by the way that's what I was doing when using SQL INSERT with the help of Statement.getGeneratedKeys() and that's what I'm missing if I try to use a positionable insert instead of a SQL INSERT query.

I admit that in this case we are going beyond the JDBC specifications and that certainly there are not many people asking for such functionality.

But perhaps this is also the reason why ResultSets are rarely used for data modification?

Pierre

[Mark Rotteveel]

On 16/04/2025 13:57, Pierre Vacher wrote:
> Hi Mark,
>
> > Now my question to you is, *if* addressing point 5 would be sufficient
> > for you. That is, do you have enough information if I use INSERT ...
> > RETURNING ... to populate the (missing columns of the) row that is
> > stored in the result set?
>
> Yes, by the way that's what I was doing when using SQL INSERT with the
> help of Statement.getGeneratedKeys() and that's what I'm missing if I
> try to use a positionable insert instead of a SQL INSERT query.
>
> I admit that in this case we are going beyond the JDBC specifications
> and that certainly there are not many people asking for such functionality.
> But perhaps this is also the reason why ResultSets are rarely used for
> data modification?

It is not really that it goes beyond the JDBC specification, but that
specification is - possibly intentionally - vague on this, e.g. so that
it is easy to comply, even if it's incomplete or deficient.

Or maybe they simply assumed too much of an ideal world, where the JDBC
driver can simply get the information from a server-side cursor, and
thus can automatically report the data that was generated by the server
on insert.

In any case, I have created
https://github.com/FirebirdSQL/jaybird/issues/863, but I haven't
scheduled it for a version yet, and I make no promises whether it will
land at all.

Mark
--
Mark Rotteveel

[Pierre Vacher]

>  In any case, I have created

> https://github.com/FirebirdSQL/jaybird/issues/863, but I haven't

> scheduled it for a version yet, and I make no promises whether it will
> land at all.

Thank you for considering this new feature.

The delay is not really important because I see that I am the only one requesting such functionality and that for the moment nothing prevents me from continuing to use SQL for inserts.

It is only a boolean in the Jaybird configuration file in jdbcDriverOOo.

Pierre

[Pierre Vacher]

Just to clarify that I now use my own implementation of javax.sql.rowset.CachedRowSet and that I can therefore very easily make a ResultSet editable even without a primary key and which therefore overcomes this problem...