Ask Your Question
0

Query Paramaters give unknown columns error

asked 2019-10-17 02:19:27 +0100

AllanPs gravatar image

updated 2019-10-17 04:45:11 +0100

I am trying to add paramaters to libre base queries using a connection to a firebird database. If I use a paramater name like :ClientCode it asks for the value of ClientCode but then the sql errors with unknown column ClientCode.

Any ideas?

Libre version 6.2 (also didn't work on 5.1) Ubuntu 16.0.4. It is a jdbc connection to a Firebird server using the org.firebirdsql.jdbc.FBDriver class.

edit retag flag offensive close merge delete

Comments

Hello,

Are you a database which was converted from HSQLDB? Also, beside the database used you should always include the specific LO version and OS.

Ratslinger gravatar imageRatslinger ( 2019-10-17 02:26:02 +0100 )edit

Libre 6.2 Ubuntu 16.0.4 It is an existing firebird database that was never a HSQLDB. We are looking at switching reporting tools and so I am trying to get t Libre base to work. Paramaters working in the old reporting tool.

AllanPs gravatar imageAllanPs ( 2019-10-17 02:29:16 +0100 )edit

PS the reason i did not include a version originally is because it was not working on 5.1 and so I upgraded to 6.2 so it is probably not a versions issue.

AllanPs gravatar imageAllanPs ( 2019-10-17 02:30:59 +0100 )edit

So I think we are further but the database type is not clear now. Is this Firebird embedded or server and if server which version and connector type?

Ratslinger gravatar imageRatslinger ( 2019-10-17 02:50:50 +0100 )edit

It is a jdbc connection to a Firebird server using the org.firebirdsql.jdbc.FBDriver class. Your solution worked though.

AllanPs gravatar imageAllanPs ( 2019-10-17 04:44:59 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-10-17 03:26:57 +0100

Ratslinger gravatar image

updated 2019-10-17 03:32:07 +0100

Hello,

Based on a few quick tests, going to guess this is about Firebird embedded DB. Something to first note. In the LO 5.3.x release, the Firebird version was changed from v2.5 to v3.0. Please see -> LibreOffice 5.3: Release Notes. As it appears you can use your Base file it may not affect you.

There is another problem as reported here -> tdf#117053. This affects sub forms and this parameter problem you are experiencing. There is a fix in the bug report but may be easier if you see my answer in this post -> How to unzip ODB file and edit the content.xml file to fix Firebird bug?

There is no problem if a new Firebird embedded Base file is created with the current versions.

edit flag offensive delete link more

Comments

That worked perfectly. Thank you.

AllanPs gravatar imageAllanPs ( 2019-10-17 03:39:59 +0100 )edit

Given the solution you gave me, I looked into what that meant.

The 6.2 version I am using does have the setting in Edit->Database->Advanced Settings under the Special Settings tab, which is "Replace named parameters with '?'". And then read up on firebird and it does expect the connection library to replace named parameters with positional parameters.

Thanks again for your help.

AllanPs gravatar imageAllanPs ( 2019-10-17 05:07:25 +0100 )edit

@AllanPs,

This is why basic information is asked for in a question. You can see if this was an embedded DB this version of LO came into play.

Had I known it was a server could have given you the other solution. Not that it matters, you did not give the server version as this in other instances comes into play like SQL.

Bottom line, it is good to hear it is working for you.

Ratslinger gravatar imageRatslinger ( 2019-10-17 05:26:49 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-10-17 02:19:27 +0100

Seen: 27 times

Last updated: Oct 17