Download it now!
Bug 68417 - Parameterized query with ADO driver fails in query editor
Summary: Parameterized query with ADO driver fails in query editor
Status: RESOLVED INSUFFICIENTDATA
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.1.0.4 release
Hardware: Other Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: BSA
Keywords:
Depends on:
Blocks:
 
Reported: 2013-08-22 09:42 UTC by pekka.korkki
Modified: 2016-12-01 09:14 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description pekka.korkki 2013-08-22 09:42:18 UTC
I have an ADO database connection and try to run a simple query (SELECT "col1" FROM "tbl1" WHERE "col2" = :x) in query editor. The result is a bunch of errors listed below.

1. The data content could not be loaded.

2. SQL Status: 42000
Error code: -2147217900
Incorrect syntax near ':'.

3. SQL Status: 42000
Error code: -2147217900
Statement(s) could not be prepared. 

This actually began as a problem with a macro I was writing. That discussion can be found at http://nabble.documentfoundation.org/SQLException-when-using-ADO-driver-and-prepared-statements-td4070605.html
Operating System: Windows XP
Version: 4.1.0.4 release
Comment 1 Alex Thurgood 2014-10-21 11:38:24 UTC
@pekka : does the query run if you switch to sql view mode and activate the "run sql directly" button ?
Comment 2 Alex Thurgood 2015-01-03 17:41:22 UTC Comment hidden (no-value)
Comment 3 QA Administrators 2015-07-18 17:36:17 UTC Comment hidden (obsolete)
Comment 4 pekka.korkki 2015-07-19 08:01:16 UTC
"Run SQL command directly" button doesn't seem to have any effect on the errors.

The original configuration I was doing this on is long gone. I made a test environment running Windows 10 Preview Build 10122 with a locally installed SQL Server 2014 and LibreOffice 4.4.4.3. The errors are still the same.
Comment 5 tommy27 2015-08-24 12:40:47 UTC
when you have some time give a try to LibO 5.0.0.5
Comment 6 pekka.korkki 2015-08-24 17:26:02 UTC
Did the test, still same results.
Comment 7 Julien Nabet 2015-10-11 19:44:13 UTC
According to https://support.microsoft.com/en-us/kb/181734, the syntax seems to be:
SELECT "col1" FROM "tbl1" WHERE "col2" = ?

But perhaps I missed something since I don't know ADO.
Comment 8 pekka.korkki 2015-10-11 20:14:42 UTC
(In reply to Julien Nabet from comment #7)
> According to https://support.microsoft.com/en-us/kb/181734, the syntax seems
> to be:
> SELECT "col1" FROM "tbl1" WHERE "col2" = ?

Thanks for the suggestion, but the change in syntax doesn't change the error messages (just tested it, same configuration as before).
Comment 9 Julien Nabet 2015-10-11 20:51:28 UTC
Just some thoughts:
I noticed ADO part uses method OPreparedStatement::replaceParameterNodeName
see http://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/ado/APreparedStatement.cxx#458

JDBC, ODBC, Firebird, commontools use substituteParameterNames method defined here http://opengrok.libreoffice.org/xref/core/connectivity/source/parse/sqlnode.cxx#1493
See http://opengrok.libreoffice.org/search?q=substituteParameterNames&project=core&defs=&refs=&path=&hist=

I thought first about trying to remove replaceParameterNodeName and replace it by substituteParameterNames
but the treatment seems slightly different, eg "parame" name.
Comment 10 Julien Nabet 2015-10-11 21:07:29 UTC
treatment of parameters also differ in the other 4 DBs than ADO:
1) Use of std::unique_ptr in odbc not for others (why?)
2) delete pNode for all DBs except ODBC (why?)
3) For jdbc test m_bParameterSubstitution
   odbc, test isParameterSubstitutionEnabled (but this is an inline function which returns m_bParameterSubstitution)
   Firebird, no specific equivalent test
   common, test "xConnection.is()" (what does this mean?)

Lionel: Are these 3 points ok or some changes are needed or may help to have something homogeneous?
Comment 11 Lionel Elie Mamane 2015-10-12 02:05:11 UTC
(In reply to Julien Nabet from comment #10)
> Lionel: Are these 3 points ok or some changes are needed or may help to have
> something homogeneous?

Err... Could you give me a pointer to the exact code lines you are referring to?
Comment 12 Julien Nabet 2015-10-12 05:11:27 UTC
(In reply to Lionel Elie Mamane from comment #11)
> (In reply to Julien Nabet from comment #10)
> > Lionel: Are these 3 points ok or some changes are needed or may help to have
> > something homogeneous?
> 
> Err... Could you give me a pointer to the exact code lines you are referring
> to?

Sorry, I forgot to tell I had put a comment before comment 10, so it's http://opengrok.libreoffice.org/search?q=substituteParameterNames&project=core&defs=&refs=&path=&hist (the different calls to substituteParameterNames)
Comment 13 Lionel Elie Mamane 2016-06-06 09:00:21 UTC
This looks like the ADO driver (and/or the database itself) does not support named parameters. To use anonymous parameters instead, change the corresponding "advanced setting".

What ADO driver to what database is used?
Comment 14 Alex Thurgood 2016-06-10 07:38:08 UTC
Per comment 13, this is needinfo, but it seems that the OP is no longer following, as there has been no input from him/her since July 2015.

@pekka : could you answer Lionel's questions in comment 13 please
Comment 15 pekka.korkki 2016-06-10 16:37:22 UTC
I am still following this, but I haven't had the time to build a proper test environment and get the details. I ran a quick test at work with a newish (if not the latest) LibreOffice 5 on a Win7 64-bit against MS SQL Server 2008. The driver was ADODB something, as in not the Native Client or ODBC. The bug was still there. I ran the query with both the syntaxes documented in this bug ("col2" = :x vs. "col2" = ?).

I'm hoping that I'll have the time to look into this during the weekend. BTW, where do I find the advanced setting for anonymous parameters? I glanced around but didn't find it yet.
Comment 16 Lionel Elie Mamane 2016-06-10 16:54:45 UTC
(In reply to pekka.korkki from comment #15)
> BTW, where do I find the advanced setting for anonymous parameters? I
> glanced around but didn't find it yet.

Edit / database / advanced settings / special settings / "replace named parameters with '?'"
Comment 17 pekka.korkki 2016-06-11 18:22:15 UTC
I just spent a couple of hours trying to install MS SQL Server 2016 on a slow-as-molasses laptop and it was a no-go. I'm thinking that since this issue doesn't seem to bother anyone else we can just close this.
Comment 18 Alex Thurgood 2016-12-01 09:14:54 UTC
Per comment 17, closing as RESOLVED INSUFFICIENTDATA