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.
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.
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.
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.
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.
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.