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
@pekka : does the query run if you switch to sql view mode and activate the "run sql directly" button ?
Adding self to CC if not already on
Dear Bug Submitter, This bug has been in NEEDINFO status with no change for at least 6 months. Please provide the requested information as soon as possible and mark the bug as UNCONFIRMED. Due to regular bug tracker maintenance, if the bug is still in NEEDINFO status with no change in 30 days the QA team will close the bug as INVALID due to lack of needed information. For more information about our NEEDINFO policy please read the wiki located here: https://wiki.documentfoundation.org/QA/FDO/NEEDINFO If you have already provided the requested information, please mark the bug as UNCONFIRMED so that the QA team knows that the bug is ready to be confirmed. Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team This NEEDINFO message was generated on: 2015-07-18
"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.
when you have some time give a try to LibO 5.0.0.5
Did the test, still same results.
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.
(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).
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.
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?
(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?
(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)
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?
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
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.
(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 '?'"
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.
Per comment 17, closing as RESOLVED INSUFFICIENTDATA