Ask Your Question
0

Base: get autovalue data after insert

asked 2018-11-04 17:08:41 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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).

edit retag flag offensive close merge delete

Comments

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

Ratslinger gravatar imageRatslinger ( 2018-11-04 17:23:13 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2018-11-04 21:50:38 +0200

Ratslinger gravatar image

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.

edit flag offensive delete link more

Comments

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

m.a.riosv gravatar imagem.a.riosv ( 2018-11-04 22:47:28 +0200 )edit

@m.a.riosv 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).

Ratslinger gravatar imageRatslinger ( 2018-11-04 23:09:14 +0200 )edit

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

select first 1 ID from table order by ID desc
m.a.riosv gravatar imagem.a.riosv ( 2018-11-05 01:31:14 +0200 )edit

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
Ratslinger gravatar imageRatslinger ( 2018-11-05 01:37:54 +0200 )edit

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.

m.a.riosv gravatar imagem.a.riosv ( 2018-11-05 02:35:21 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2018-11-05 02:45:17 +0200 )edit

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!

sscala gravatar imagesscala ( 2018-11-05 21:07:39 +0200 )edit

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.

m.a.riosv gravatar imagem.a.riosv ( 2018-11-07 23:03:12 +0200 )edit

@m.a.riosv 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.

Ratslinger gravatar imageRatslinger ( 2018-11-08 20:16:06 +0200 )edit
0

answered 2018-11-04 22:00:45 +0200

DrewJensen gravatar image

updated 2018-11-04 22:10:05 +0200

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.

edit flag offensive delete link more

Comments

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

Ratslinger gravatar imageRatslinger ( 2018-11-04 22:38:52 +0200 )edit

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.

DrewJensen gravatar imageDrewJensen ( 2018-11-05 00:34:12 +0200 )edit

@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 gravatar imageRatslinger ( 2018-11-05 06:10:53 +0200 )edit

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

Ratslinger gravatar imageRatslinger ( 2018-11-05 06:20:30 +0200 )edit

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 ;-)

DrewJensen gravatar imageDrewJensen ( 2018-11-05 17:03:03 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-11-04 17:08:41 +0200

Seen: 319 times

Last updated: Nov 04 '18