Ask Your Question
0

LO 6.2 and SQL parameter query

asked 2019-03-07 11:27:34 +0200

KrpaN gravatar image

In all verions of LO up to LO 6.1 a parameter query could be started in base by SELECT "ID", "Name", "Calc" FROM "Table1" WHERE "Name" = :name Starting LO 6.2 such a parameter query is no longer valid, LO 6.2 complains about colon (:name). Is there any other way to enter above SELECT command and get the result.

edit retag flag offensive close merge delete

Comments

Hello @KrpaN, Have not seen any problem through LO v6.2.1.2 (TDF version) on Mint 18.3. Just verified this is working on HSQLDB & Firebird embedded, MySQL and PostgreSQL.

Insure Run SQL command directly is off. This would bypass looking for the parameter.

If problem persists, please specify what OS you are using and where you got LO from. It would also help if you provided a sample.

Ratslinger gravatar imageRatslinger ( 2019-03-07 17:07:12 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-03-07 23:35:51 +0200

Ratslinger gravatar image

Hello,

While waiting for a reply regarding my comment, it occurred to me to look at your past questions. It appears you are using Suse Leap with MySql Native (SDBC) connector. My testing did not include that connector.

In release 6.2.0.x this was changed. While my Linux versions did not have a SDBC connector for some time, I had been using Apache v1.2 extension without a problem (mentioned in my answer to you here -> Mysql Connector Extension 4-3 no longer works with current LibreOffice). Starting with LO 6.2.0.x the SDBC connector is 'MariaDB C connector'. See release notes here -> ReleaseNotes/6.2. I abandoned this and moved to a JDBC connector since it affected my macros - certain functions do not work under that connector (like moveToInsertRow()).

So now I have tested using a parameter with MySQL DB & a parameter. Using the colon prefixed parameter (:name) does present a syntax error. Using ? instead does work but presents another problem.

The Parameter input dialog associates the ? with the item compared to in the Where clause. So:

SELECT * FROM "AMOUNTS" WHERE "Amount1" > ? or "Amount2" > ?

would present you with entering parameters for "Amount1" and "Amount2".

However:

SELECT * FROM "AMOUNTS" WHERE "Amount1" > ? or "Amount1" < ?

would only present you with entering a parameter for "Amount1" thus not allowing for checking the same field for different conditions.

This should be reported as a bug.

edit flag offensive delete link more

Comments

Hi: Tank you for your kind reply. Since I am not a LO expert additional questions came to me. How do you use MariaDB C connector with LO. I checked SuSE repo and there is only source of said connector. For the time being I am still using Apache v1.2 extension, and now replacing : with ? it works fine. Nevertheless, Apache extension is very old and I would prefer to use some up to date connector. My priority is SDBC since changing to JDBC would mean days of work. Also, reporting bug is beyond my capabilities, would you be so kind and do it.

KrpaN gravatar imageKrpaN ( 2019-03-08 07:52:56 +0200 )edit

You are already using MariaDB C connector if you use LO v6.2.x (or newer) and the SDBC connector. Even if you have Apache v1.2 extension installed it is overridden. This is evident by the change in how you need to enter a parameter. Changing to JDBC should not take days, just a little work on your part to learn.

You should learn also to help by reporting your own bugs. If you can ask a question here, you can report a bug here. Click for Bugzilla.

Ratslinger gravatar imageRatslinger ( 2019-03-08 16:28:35 +0200 )edit

Further checking revealed that the actual bug is a missing setting in Advanced Settings - Replace named parameters with '?'. This can be changed with a macro:

Sub FixQueryParameter
  Dim oDataSource As Variant
  Dim oSettings As Variant
  oDataSource = ThisComponent.DataSource
  oSettings = oDataSource.Settings
  oSettings.ParameterNameSubstitution = true
End Sub

attached to the Open Document event of the Base file. Then you can once again use : type parameters avoiding the situation.

Ratslinger gravatar imageRatslinger ( 2019-03-15 00:47:59 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-03-07 11:27:34 +0200

Seen: 52 times

Last updated: Mar 07