Base: get autovalue data after insert

sscala · November 4, 2018, 4:08pm

I have a firebird database (LO 6.1.2.1) with n:m connections.

When adding records in a macro, in order to maintain the n:m table I need to know the ID (=autovalue primary key) of the record I just inserted.

Adding a record works fine vial SQL:

Connection = ThisDatabaseDocument.DataSource.getConnection("","") 
Statement = Connection.createStatement()
SQL = "INSERT INTO ""Sections"" (""Section"") VALUES ('ABC')"
ResultSet = Statement.executeQuery(SQL)

The Firebird documentation says that the most recent ID can be retrieved by adding the RETURNING command:

SQL = "INSERT INTO ""Sections"" (""Section"") VALUES ('Anita') RETURNING ""ID"""

The problem is: this does add the data, but it does not return the ID (at least I can not find in in the returned ResultSet).

Ratslinger · November 4, 2018, 4:23pm

@sscala Please do not check community wiki when posting questions or answers. It helps no one. Thank You.

Ratslinger · November 4, 2018, 8:50pm

Hello @sscala,

Working with Base Firebird embedded is a bit different than Firebird server. There is a point to this.

First, there is no auto increment type in Firebird. Auto increment is done through Generators which use a sequence to create a next sequential number. This Generator is used by a Trigger (BEFORE insert) and obtains the next number to be used for the value.

With this you can use SQL to obtain the current value in the Generator (this is also the last issued value):

 SELECT GEN_ID( CUSTOMER_ID_GEN, 0 ) FROM RDB$DATABASE

Now using server this is easy to find since you can easily view Triggers & Generators using a tool such a FlameRobin. But this is not the case when using Firebird embedded in Base. In Base first you need to get the name of the generator. This need be done only once so you can use it in the SQL. That can be done using the SQL found on this page → RE: [firebird-support] reset autoincrement field. My tests show it to produce the name of the Generator you need. Then just use it in the Select statement above (replacing CUSTOMER_ID_GEN with it) to get the last issued value.

So if your returned generator name is RDB$1 then the statement you need is:

SELECT GEN_ID( RDB$1, 0 ) FROM RDB$DATABASE

You can run this in your macro after your INSERT statement and the result set will return the current value which is the ID from your INSERT.

Finally, I do agree with you on the RETURNING portion of SQL. Have not taken a lot of time looking into this but will when I have some free time. Not so sure this really applies to the situation regarding the use of Generators. Will post if I find something relevant.

mariosv · November 4, 2018, 9:47pm

FB 3 version is used by LibreOffice, it has Identity Column Type, so no need of triggers.

Ratslinger · November 4, 2018, 10:09pm

@mariosv Sorry if not clear. Not saying to create a trigger in the answer but only the process behind it. The answer only describes how to obtain the generator name to get the current value. Identity is still associated with a sequence (Generator).

mariosv · November 5, 2018, 12:31am

Well, like the question is formulated the last added should have max ID, maybe:

select first 1 ID from table order by ID desc

Ratslinger · November 5, 2018, 12:37am

Don’t believe that is trustworthy. If records have been deleted and the sequence generator reset (have had others questions where people do this) then Max is not valid.

Aside from that, once you have the Generator name (just need to get it once), isn’t it just as easy to use:

SELECT GEN_ID( RDB$1, 0 ) FROM RDB$DATABASE

mariosv · November 5, 2018, 1:35am

I know, but a primary key should be never used as numerator, usually they end with troubles. I don’t know if can be issues with a multi-user access and how transactions are managed.Firebird faq243

So maybe can be safer as in the faq, prior to added the record, getting first the max ID, and add the record including the ID field (max+1), so the ID it’s no duplicated and no need to be retrieved.

Ratslinger · November 5, 2018, 1:45am

This raises two points -

-1 Both @drewjensen & I have looked at RETURNING values & they don’t seem to work in this case. If they did, all this would not be necessary. Original question would never even have been asked.

-2 Are you stating that Base with Firebird embedded can be used in a multi-user environment? If so, how? I believed this was single user only.

Also, I do not disagree with your comment.

sscala · November 5, 2018, 8:07pm

thanks to all of you for your contributions - it actually works
I had hoped for a solution that would not go so deep into the entrails and specifics of firebird and instead stick to some more common techniques that dont need much attention when upscaling to a multi-user environment, but I guess I’ll just have to wait until the RETURNING feature is properly handled by OL in a future version.
thanks!

mariosv · November 7, 2018, 10:03pm

When I find time, I’ll test about RETURNING, maybe it’s an issue on how the result it’s returned.
I think he didn’t mention about an embedded or not database, It’s anticipate nothing.

Ratslinger · November 8, 2018, 7:16pm

@mariosv stating:

 "...he didn't mention about an embedded or not database, It's anticipate nothing."

you are absolutely correct. Will put more effort into looking at questions in this manner. Certainly with that, your answer (in comment) is the preferred method. Would still prefer RETURNING to work.

drewjensen · November 4, 2018, 9:00pm

Well, you are correct it seems that the RETURNING feature does not seem to be returning an actual row. (btw, I’m running LO 6.2Alpha)

So, there is the ability to query the engine for the last value created by a generator

The thing is you need to know which generator you want the value for.

For instance, I created a new ODB file with embedded firebird database and a single table named “Sections” and two columns “id” autovalue integer and “Section” varchar.

If I query for all generators in the system tables afterwards with this bit of SQL in the SQL window

SELECT RDB$GENERATOR_NAME
 FROM RDB$GENERATORS
 WHERE RDB$SYSTEM_FLAG IS NULL;

I find one generator named “RDB$1” has been created.

I can then get the last value that generator with this SQL statement

select gen_id(RDB$1, 0) from rdb$database;

Putting that together then:

Connection = ThisDatabaseDocument.DataSource.getConnection("","") 
Statement = Connection.createStatement()
SQL = "INSERT INTO ""Sections"" (""Section"") VALUES ('ABC')"
ResultSet = Statement.executeQuery(SQL)
SQL = "select gen_id(RDB$1, 0) from rdb$database"
ResultSet = Statement.executeQuery(SQL)
ResultSet.first()
LastValue = ResultSet.getInt(1)

LastValue is what you are looking for.

Now, what you need to do is to find how to alter that fist SQL statement which finds the system created generators for a particular table.

Ratslinger · November 4, 2018, 9:38pm

@drewjensen The SQL to find the necessary Generator for a particular table is found in the link posted in my answer.

drewjensen · November 4, 2018, 11:34pm

indeed.

Just used that in the SQL window, changing CUSTOMER to Sections and there she is.

This is a good candidate for a little custom macro to pass in the Table name and return the PK generator name, as part of small library I started assembling for firebird ODB files.

I want to share that basic library - I suppose put a little oxt wrapper around it with the Basic Addon Builder extension and push it up the extension site would be the way to go.

Ratslinger · November 5, 2018, 5:10am

@drewjensen Just an FYI. Using Firebird server, tried RETURNING in Tools->SQL. Tran worked and message back inStatus was:

1: Update statement returned results.

Tran was successful but no way of getting return. Also ran in macro with tran completed but got error message on the exec stmt:

BASIC runtime error.
An exception occurred 
Type: com.sun.star.sdbc.SQLException
Message: Update statement returned results..

Again cannot figure method to get result.

Ratslinger · November 5, 2018, 5:20am

Finally, ran in FlameRobin and got the value of the ID returned which I verified against the table data in Base.

drewjensen · November 5, 2018, 4:03pm

I suspect the RETURNING syntax for the insert statement is to support multi-table transaction control and most useful inside stored procedures. To be honest I have not tried to use explicit transaction control with an embedded Firebird file (engine12 of any type, let alone Base). Support is there, when used in LO scripts, for other SDBC drivers (including embedded HSQL). It would be a fun afternoon giving it a whirl. But that is something for tomorrow as they say

ztminhas · February 20, 2023, 6:34pm

Good Evening,
Here is a what I think could qualify as an answer:

Sub Main
Dim strSQL As String
	strSQL = "execute block (name varchar(50) = ?) returns (NAME_ID int) as " & _
"declare LOCNAME_ID int = 0; " & _
"Begin " & _
"	INSERT INTO TABLE1(NAME) VALUES(:name) RETURNING ID INTO :LOCNAME_ID; " & _
"	SELECT :LOCNAME_ID from RDB$DATABASE INTO :NAME_ID; " & _
"	suspend; " & _
"End"
	con = getDbConnection(ThisDatabaseDocument)
	set fp =con.prepareStatement(strSQL)
	fp.setString(1,"FREDO")
	rs =fp.executeQuery()
	rs.first
	Print rs.getInt(1)
	rs.Close 
	rs = Nothing
	fp.Close
	fp =Nothing
	
End Sub

Ratslinger · February 20, 2023, 7:34pm

This code will not work as posted. there is an error at:

con = getDbConnection(ThisDatabaseDocument)

Replaced with:

con = Thisdatabasedocument.CurrentController.ActiveConnection

and it then works. Although it works, I believe the “RETURNING” process would be much better (and certainly less code). However, have lost hope I’ll see this any time soon.