Bug 105220 - Firebird: Insert with direct SQL - RETURNING without values
Summary: Firebird: Insert with direct SQL - RETURNING without values
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
5.4.0.0.alpha0+
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Firebird-Default Base-Without-Java
  Show dependency treegraph
 
Reported: 2017-01-09 21:03 UTC by Robert Großkopf
Modified: 2023-03-28 14:05 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Open the database and try the code as described for get the last inserted AutoValue (2.92 KB, application/vnd.oasis.opendocument.database)
2017-01-09 21:03 UTC, Robert Großkopf
Details
Testdatabase, also with code executed by macro: Input data works, returns nothing (12.92 KB, application/vnd.oasis.opendocument.database)
2018-01-04 14:58 UTC, Robert Großkopf
Details
New Testdatabase - old database seems to be broken. (12.91 KB, application/vnd.oasis.opendocument.database)
2019-07-29 15:19 UTC, Robert Großkopf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2017-01-09 21:03:06 UTC
Created attachment 130288 [details]
Open the database and try the code as described for get the last inserted AutoValue

Open the attached Firebird 3 database.
Open Tools > SQL...
Mark "Show output of "select" statements.
Copy the following code:
INSERT INTO "Table" ("Name", "Town") VALUES ('Mike', 'Alexandria') 
RETURNING "ID", "Name", "Town";

There is no value shown as output. The status shows only "1."

Expected is in "output":
4;Mike;Alexandria

There is a function in HSQLDB named "CALL IDENTITY()" This gives you the last inserted AutoValue. I hope to find this in Firebird. There it should run with RETURNING, but doesn't run in Tools > SQL... Will test this later with macros to see, if it's a problem of internal Firebird or a problem of the SQL-dialog.
Comment 1 m_a_riosv 2017-01-10 10:23:25 UTC
Reproducible.
Version: 5.4.0.0.alpha0+
Build ID: 92a1ad1f36b6d3cc13135a8c0805508933011577
CPU Threads: 4; OS Version: Windows 6.19; UI Render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2017-01-06_23:42:59
Comment 2 Robert Großkopf 2017-01-10 14:52:34 UTC
Have tested this with a macro. Firebird returns nothing to Base except a "." So there couldn't be tested the last input and the last inserted AutoValue.

All tested with
Version: 5.4.0.0.alpha0+
Build ID: a3cf075880db31f77cd0550e0ee25eca931c6a40
CPU Threads: 4; OS Version: Linux 4.1; UI Render: default; VCL: kde4; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2017-01-05_01:21:50
Locale: de-DE (de_DE.UTF-8); Calc: group
Comment 3 Lionel Elie Mamane 2018-01-04 13:21:37 UTC
(In reply to robert from comment #2)
> Have tested this with a macro.

Could you please share the macro? Did you use executeQuery or execute? (I assume not executeUpdate...)
Comment 4 Robert Großkopf 2018-01-04 14:58:27 UTC
Created attachment 138875 [details]
Testdatabase, also with code executed by macro: Input data works, returns nothing

It's only the same way as executed in Tools > SQL. Code is executed and returns nothing, so the macro couldn't find an object, only a "."
Comment 5 Robert Großkopf 2018-01-04 15:03:44 UTC
(In reply to Lionel Elie Mamane from comment #3)

> Could you please share the macro? Did you use executeQuery or execute? (I
> assume not executeUpdate...)

Did use executeQuery, because I need a result.

By the way: very much people helping hunting and solving Base-bugs last week. Great!
Comment 6 QA Administrators 2019-01-05 03:41:29 UTC Comment hidden (obsolete)
Comment 7 Robert Großkopf 2019-01-05 07:35:08 UTC
Bug still exists. Tested with
Version: 6.2.0.1
Build ID: 0412ee99e862f384c1106d0841a950c4cfaa9df1
CPU threads: 6; OS: Linux 4.12; UI render: default; VCL: gtk3; 
Locale: de-DE (de_DE.UTF-8); UI-Language: en-US
Calc: threaded

Also with LO 6.1.4.2 on OpenSUSE 64bit rpm Linux
Comment 8 Robert Großkopf 2019-07-29 15:19:39 UTC
Created attachment 153043 [details]
New Testdatabase - old database seems to be broken.

During a crash in bugzilla the database seems to be broken.

With LO 6.2.5.2 the Update-command returns '0 rows updated' - which is totally wrong. There has been added a row. RETURNING should show the content of the added row.

With LO 6.3.0.2 the whole LO crashes immediately after sending the command to the database.
crashreport.libreoffice.org/stats/crash_details/4327a91e-a61b-4700-a574-3df6bfc3654d
crashreport.libreoffice.org/stats/crash_details/d090119c-4dd9-41bd-966e-a34319b6c537

All tested with OpenSUSE 15 64bit rpm Linux
Comment 9 Buovjaga 2021-03-02 06:45:12 UTC
Some small pointers:

The direct SQL dialog is here:
dbaccess/source/ui/dlg/directsql.cxx

About HSQLDB's IDENTITY(), search in this guide:
http://hsqldb.org/doc/1.8/guide/guide.html
Comment 10 Buovjaga 2021-03-02 06:50:30 UTC
Hmm, but according to https://wiki.documentfoundation.org/Documentation/FirebirdMigration#Data_engine_standard_functions Firebird does not have a function matching IDENTITY()
Comment 11 Julien Nabet 2022-01-02 20:17:08 UTC
Just for the record because I'm not sure it could help here, I found a 2 steps way to do this:
1) Retrieve the generator name

SELECT relfields.RDB$generator_name
        FROM RDB$RELATION_FIELDS relfields
        JOIN RDB$FIELDS fields
        on (fields.RDB$FIELD_NAME = relfields.RDB$FIELD_SOURCE)
        WHERE (1 = 1)
        AND relfields.RDB$RELATION_NAME = '<table>'

2) Use specific Firebird function
SELECT GEN_ID( "<generator name>", 0 ) FROM RDB$DATABASE;
Comment 12 Robert Großkopf 2022-01-03 06:57:55 UTC
Have tested this again. Nothing changed.

@Julien: The way you described is the workaround I have also written in Base Handbuch:

SELECT RDB$FIELD_NAME, RDB$RELATION_NAME,
RDB$GENERATOR_NAME FROM RDB$RELATION_FIELDS WHERE
RDB$GENERATOR_NAME IS NOT NULL;

You could see the name for the generator. It is the same way autovalue is implemented.

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

Generator with name RDB$1 is asked for the next generated value.

INSERT INTO "Table" ("ID", "Field") VALUES (<generated value>, 'Test');

This is the only save mode to get the right data after you inserted a new row: First the value will be blocked for AutoValue, then this blocked value will be used for insert.
Comment 13 Julien Nabet 2022-01-03 17:40:20 UTC
(In reply to Robert Großkopf from comment #12)
> Have tested this again. Nothing changed.
> ...
Ok then.

Just for the record, here's the entry point.
In dbaccess/source/ui/dlg/directsql.cxx, we got:
260                 else if (upperStatement.startsWith("INSERT"))
261                 {
262                     sal_Int32 resultCount = xStatement->executeUpdate(_rStatement);
263                     addOutputText(OUStringConcatenation(OUString::number(resultCount) + " rows inserted\n"));
264                 }
see https://opengrok.libreoffice.org/xref/core/dbaccess/source/ui/dlg/directsql.cxx?r=be73c64d#260

We only expect the number of rows inserted here.
I thought we could add a test here to check if it contains RETURNING but no idea then how to retrieve the returned values.
Comment 14 Robert Großkopf 2022-01-03 19:23:24 UTC
(In reply to Julien Nabet from comment #13)
> 
> We only expect the number of rows inserted here.
> I thought we could add a test here to check if it contains RETURNING but no
> idea then how to retrieve the returned values.

This is the same behavior in Basic macros: You could execute the INSERT with the executeQuery method and will get "*Cursor is not open" when trying to receive the result:
*Cursor is not open
caused by
'isc_dsql_fetch'
 /home/buildslave/source/libo-core/connectivity/source/drivers/firebird/Util.cxx:68.

You could start the INSERT with executeUpdate and get a number, which is count wrong for Firebird: 0. But you won't get any other result.

Seems returning content when inserting values is a special beahvior of Firebird, which can't work with this construction of executeQuery and executeUpdate.
Comment 15 Robert Großkopf 2022-01-05 11:26:58 UTC
(In reply to Robert Großkopf from comment #14)
> 
> Seems returning content when inserting values is a special behavior of
> Firebird, which can't work with this construction of executeQuery and
> executeUpdate.

Forget the last part. RETURNING is also used in PostgreSQL. It will work there also together width LO. Had tested this a time ago with BASIC:

stSql = "INSERT INTO ""Table"" (""Name"") Values('Robert') RETURNING ""ID"""
oResult = oSQL_Statement.executeQuery(stSql)
oResult.Next
loID = oResult.getLong(1)

It will work there.
Comment 16 Julien Nabet 2022-01-05 20:47:20 UTC
(In reply to Robert Großkopf from comment #15)
> (In reply to Robert Großkopf from comment #14)
> > 
> > Seems returning content when inserting values is a special behavior of
> > Firebird, which can't work with this construction of executeQuery and
> > executeUpdate.
> 
> Forget the last part. RETURNING is also used in PostgreSQL. It will work
> there also together width LO. Had tested this a time ago with BASIC:
> 
> stSql = "INSERT INTO ""Table"" (""Name"") Values('Robert') RETURNING ""ID"""
> oResult = oSQL_Statement.executeQuery(stSql)
> oResult.Next
> loID = oResult.getLong(1)
> 
> It will work there.

ok so at least we know now that it only depends on the implementation (so connectivity part) and directsql part (in dbaccess) is already ok to take into account.
Now I don't know at all how to implement this.