Created attachment 197727 [details] Open the database and test the queries. Input values for date, integer or string in parameter. Open the attached database. Execute the queries. There is one query for a date parameter, one for a integer parameter and one for a string parameter: SELECT * FROM "tbl_Test" WHERE "Datum" <= :qDate OR :qDate IS NULL and the same for another Integer or Varchar field. If you won't fill any content in parameter it will work. All four rows of the table will be shown. If you fill with a parameter, query for date and integer will throw SQL-Status: HY004 Incorrect type for setValue Only string would work well without any problem. This has been solved in bug130595
The point is that you cannot have two parameters with the same name. Please take a look: https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref25/fblangref25-datatypes-special.html
(In reply to m_a_riosv from comment #1) > The point is that you cannot have two parameters with the same name. > > Please take a look: > https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref25/ > fblangref25-datatypes-special.html This isn't a behavior of Firebird. It is a bug of the Base GUI. Firebird doesn't notice anything about the parameter, because it won't be executed in direct SQL. Please have a look at bug https://bugs.documentfoundation.org/show_bug.cgi?id=130595 which has also been a special bug (GUI with Firebird). GUI will do this job well with a string parameter. And both parameters are named the same there also.
By the way: If you set the code in the example queries to SELECT * FROM "tbl_Test" WHERE "Datum" <= CAST( :qDate AS VARCHAR ( 50 ) ) OR :qDate IS NULL Same with integer values. Both parameters the same name, because it is only for GUI. GUI asks only for one value and sends the code with this value to Firebird.
(In reply to Robert Großkopf from comment #2) > This isn't a behavior of Firebird. It is a bug of the Base GUI. Firebird > doesn't notice anything about the parameter, because it won't be executed in > direct SQL. > > GUI will do this job well with a string parameter. And both parameters are > named the same there also. If it is not a specific behavior of Firebird then the word FIREBIRD: should be remove from the bug title, shouldn't it?
Created attachment 197750 [details] Same queries in internal HSQLDB - works Please test the second attachment: Internal HSQLDB. All queries will work well in internal HSQLDB. Only query for String_parameter will work well in Firebird. You have to CAST date and integer parameters to VARCHAR and the other 2 queries will work also. This is a special bug of Base GUI together with internal driver of Firebird. It isn't a bug of Firebird, because Firebird doesn't know anything about the parameter. If you try to execute a query with direct SQL in HSQLDB there won't appear any ask for parameter. GUI will cut the whole code for parameters before sending the query. If you try the same with direct SQL in Firebird also the dialog for parameter won't appear. But it will throw an error: firebird_sdbc error: *Dynamic SQL Error *SQL error code = -206 *Column unknown *QSTRING *At line 1, column 42 caused by 'isc_dsql_prepare' The GUI won't cut the Code and tries to send the whole code to Firebird, which doesn't work. So: This is a special bug of the internal Firebird driver of LO, not a bug of Firebird and not a bug of every database, because other drivers will work well.
I can reproduce the reported behaviour with the test ODB file: Version: 24.2.5.2 (AARCH64) / LibreOffice Community Build ID: bffef4ea93e59bebbeaf7f431bb02b1a39ee8a59 CPU threads: 8; OS: macOS 15.1.1; UI render: Skia/Raster; VCL: osx Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR Calc: threaded
@Robert: did this ever work in previous versions of LO since Firebird was introduced ? It might well be that it was simply never implemented for the Firebird part.
(In reply to Alex Thurgood from comment #7) > @Robert: did this ever work in previous versions of LO since Firebird was > introduced ? It might well be that it was simply never implemented for the > Firebird part. No, it never worked. With solving bug 130595 it will work, if I set SELECT * FROM "tbl_Test" WHERE "Datum" <= CAST( :qDate AS VARCHAR ( 50 ) ) OR :qDate IS NULL So parameter in format "string" is allowed since bug 130595 is solved, but parameter as "value" or "date" isn't allowed.
It looks like PreparedStatement.cxx for Firebird only contains code for handling the following SQL types: SQL_TEXT SQL_LONG SQL_INT64 SQL_FLOAT SQL_BOOLEAN SQL_NULL I can't tell which of those is used to manage the following Firebird SQL types, identified at: (https://www.firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref40/fblangref40-datatypes.html) BINARY(n) CHAR(n), CHARACTER(n) DATE DECIMAL (precision, scale) DOUBLE PRECISION INTEGER, INT INT128 NUMERIC (precision, scale) REAL SMALLINT TIME [WITHOUT TIME ZONE] TIME WITH TIME ZONE TIMESTAMP [WITHOUT TIME ZONE] TIMESTAMP WITH TIME ZONE VARBINARY(n), BINARY VARYING(n) VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n) Seems like at least DATE and INT (signed 32 bit integer) are not catered for?
A similar solution to that provided for bug 130595 needs to be developed.