Table "Gezinnen" 3 columns: gezinsid primary key autonummer gezinsnaam varchar huwelijksdatum date (in english marriage date) Now I want to make a query with a parameter which should refer to the month of huwelijksdatum, the query reads as select "gezinsnaam", extract(month from "Gezinnen"."huwelijksdatum") from "Gezinnen"; that works OK now adding the parameter: select "gezinsnaam", extract(month from "Gezinnen"."huwelijksdatum") from "Gezinnen" where extract(month from "Gezinnen"."huwelijksdatum")=:huwmaand; When I run this, I get the dialogue to ask for the value of huwmaand, but whatever I tried, just a digit, or # around it as a date or " or ', I always get "Incorrect type for setString". I'm at loss as I read in the Firebird FAQ (or whatever it is from Firebird) that extract(month......) returns a smallint, so where do I go wrong?? I tried to get around this by saving the first select as a query, works OK and then opening a second query which takes the first as its source, and add the parameter there, but no success. BTW: I do this on Win10, but using the the same odb on Linux Mageia 6 or Mageia 7 with the same version of LibreOffice gives the same result. Casting the month expression to varchar(2) , then the query works. I couldn't find anything which indicates that input paramaters are limited to strings, and numeric is not accepted. So I consider this a bug.
Let me guess, the code for paramater substitution for Firebird only accepts char string literals, so that when an INT is returned from extract, you get the error message about setString having an incorrect type...
@herman : does this apply to embedded hsqldb database as well, or is it just Firebird embedded ? Ideally, we would need a sample ODB file to be able to try and reproduce.
Imported the same table in a hsqldb database and there the input works OK without casting to varchar. I'll make a copy of the offending firebird based database with just the needed table and attach it here.
Created attachment 154321 [details] testcase for input parameter in query firebird embedded
Could confirm the buggy behaviour with LO 6.3.2.1 on OpenSUSE 15. It's a special Firebird-bug which throws "Incorrect type for setString" when putting an integer in the parameter-dialog. Directly writing this integer into the query will work.
@herman : thanks! and thanks to Robert for confirming.
Also confirmed on Version: 6.3.1.2 Build ID: b79626edf0065ac373bd1df5c28bd630b4424273 Threads CPU : 8; OS : Mac OS X 10.14.6; UI Render : par défaut; VCL: osx; Locale : fr-FR (fr_FR.UTF-8); Langue IHM : fr-FR Calc: threaded
And also : Version: 6.4.0.0.alpha0+ Build ID: f0c832acb53326ccc9a8c1a47401fbc9e1081feb CPU threads: 8; OS: Mac OS X 10.14.6; UI render: GL; VCL: osx; TinderBox: MacOSX-x86_64@49-TDF, Branch:master, Time: 2019-09-11_05:45:47 Locale: fr-FR (fr_FR.UTF-8); UI-Language: en-US Calc: threaded
Created attachment 154335 [details] bt with debug symbols On pc Debian x86-64 with master sources updated today, I could reproduce this. I attached a bt from setString error. debugging a bit indicates that dtype = SQL_SHORT so it goes to default case: 235 default: 236 ::dbtools::throwSQLException( 237 "Incorrect type for setString", 238 ::dbtools::StandardSQLState::INVALID_SQL_DATA_TYPE, 239 *this); I got no idea how to fix this. I tried to call setShort but it expects a short value and I'm not sure that converting OUString sInput into a short is the right way.
uncc myself since I can't help here.
(In reply to Julien Nabet from comment #9) > I'm not sure that converting OUString sInput > into a short is the right way. That sounds like exactly the right way.
(In reply to Lionel Elie Mamane from comment #11) > (In reply to Julien Nabet from comment #9) > > I'm not sure that converting OUString sInput > > into a short is the right way. > > That sounds like exactly the right way. I don't know if we should convert OUString "sInput" or OString "str", in both cases, I don't know too how to convert OUString to short or OString to short. Finally, I suppose we should do the same kind of thing with Date, int, etc.
Finally found toInt32() method. I submitted https://gerrit.libreoffice.org/#/c/79314/ for review. It deals only with SQL_SHORT
Julien Nabet committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/f23e19cd15f14566832befba73fb6bbc1cffb0d7 tdf#127657: FIREBIRD error in query input param when referred field is integer It will be available in 6.4.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Julien Nabet committed a patch related to this issue. It has been pushed to "libreoffice-6-3": https://git.libreoffice.org/core/commit/3a70b4a1be0e790579cc15e9917bd575de8d5315 tdf#127657: FIREBIRD error in query input param when referred field is integer It will be available in 6.3.3. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.