Created attachment 63873 [details] A screenshot of the bug occurring. Problem description: More information at the thread here: http://bit.ly/LSg9Sb I added a single parameter to a query I was writing and I expected to see a result; instead I saw a dialog box with some errors in it. I have LibreOffice Base connected to MySQL using libmyodbc on Ubuntu 12.04 Steps to reproduce: 1. Install the following on Ubuntu 12.04: 1.1. MySQL Database (Ver 14.14 Distrib 5.5.22 for debian-linux-gnu (i686) using readline 6.2) 1.2. LibraOffice Base (3.5.3.2 Build ID 350m1 (Build:2)). 2. Install a database of some sort to query... 3. Connect via ODBC, and my /etc/odbc.ini looks like this: [DESP] Driver = /usr/lib/i386-linux-gnu/odbc/libmyodbc.so Description = MyODBC 3.51 Driver DSN SERVER = localhost PORT = USER = root Password = XXXXX Database = DigitalEditionsSolution OPTION = 3 SOCKET = 4. Create a LibreOffice Base file and Configure it to connect via the ODBC DataSource you defined above... 5. With the new LibreOffice Base file opened, click "Queries" and click "Create Query in SQL View..." 6. Enter a query that contains a named parameter of the format ':parameterName' 7. Watch as the driver displays the same errors as listed in the description of this bug. Current behavior: An error dialog is display when running the query with several errors display: "The data content could not be loaded" "SQL Status: 07009 Invalid descriptor index." "You tried to set a parameter at position '1' but there is/are only '0' parameter(s) allowed. One reason may be that the property "ParameterNameSubstitution" is not set to TRUE in the data source." "The commmand leading to this error is: SELECT `dfw`.`fk_desp_id`, `dfw`.`fk_dfws_id`, `dfw`.`Features_f_id`, `desp`.`name` AS `DESP_Name`, `f`.`name` AS `FeatureName`, `dfws`.`name` AS `ResponseState` FROM `DESP_Feature_Weak_State` `dfws` RIGHT OUTER JOIN ( `DigitalEditionsSolutionProvider` `desp` INNER JOIN ( `Features` `f` INNER JOIN `DESP_Features_Weak` `dfw` ON `f`.`f_id` = `dfw`.`Features_f_id` ) ON `desp`.`desp_id` = `dfw`.`fk_desp_id` ) ON `dfws`.`dfws_id` = `dfw`.`fk_dfws_id` WHERE `desp`.`desp_id` = :despnum AND ( `f`.`name` = `auto-linking-of-email-addresses:yes` OR `f`.`name` = `auto-linking-of-urls:yes` OR `f`.`name` = `newsstandsupport:yes` ) ORDER BY `fk_desp_id` " Expected behavior: The results of the query displayed in a table. Platform (if different from the browser): Ubuntu Linux 12.04 LTS
@Robert Please: have a look at this bugreport. Can you confirm the behavior?
@Andrew : I am assuming that you did set the option "ParameterNameSubstitution" in the Advanced Properties dialog of your database connection ? Alex
(In reply to comment #2) > @Andrew : I am assuming that you did set the option "ParameterNameSubstitution" > in the Advanced Properties dialog of your database connection ? > > > Alex Forget that last comment, it seems that the option in question is no longer present, at least in LO 3.5.4 and 3.6.0.4 - hmm, I wonder when that happened ? I will have to check on an older version of OpenOffice.org. Alex
So, this feature was definitely in the OOo UI at one stage, and somehow is no longer there, see this page, where it was talked about in OOo 2.0 and then introduced : http://www.openoffice.org/dba/howto/param_subst.html Alex
As a start, the first thing to do would be to look inside the ODB file and see whether the name-value pair is present in the "Info" file : Parameter name substitution can be enabled on a per-data-source basis. For this, the "Info" property of a data source should contain a name-value-pair with Name: ParameterNameSubstituion Value: TRUE If not, then try adding it with the macro provided at the same page. Alex
Adding Lionel to CC @Lionel : Looks like the UI option to activate parameter substitution has gone AWOL, but I still see stuff in the source tree : git grep ParameterNameSubstitution connectivity/qa/complex/connectivity/HsqlDriverTest.java: ,new com.sun.star.beans.PropertyValue("ParameterNameSubstitution",0, false,PropertyState.DIRECT_VALUE) connectivity/registry/jdbc/org/openoffice/Office/DataAccess/Drivers.xcu: <node oor:name="ParameterNameSubstitution" oor:op="replace"> connectivity/registry/odbc/org/openoffice/Office/DataAccess/Drivers.xcu: <node oor:name="ParameterNameSubstitution" oor:op="replace"> connectivity/source/drivers/jdbc/JConnection.cxx: m_bParameterSubstitution = aSettings.getOrDefault( "ParameterNameSubstitution", m_bParameterSubstitution ); connectivity/source/drivers/jdbc/JDriver.cxx: ::rtl::OUString("ParameterNameSubstitution") connectivity/source/drivers/jdbc/tools.cxx: && pBegin->Name.compareToAscii( "ParameterNameSubstitution" ) connectivity/source/drivers/mysql/YDriver.cxx: ::rtl::OUString("ParameterNameSubstitution") connectivity/source/drivers/odbcbase/OConnection.cxx: const char* pParaName = "ParameterNameSubstitution"; connectivity/source/drivers/odbcbase/ODriver.cxx: ::rtl::OUString("ParameterNameSubstitution") connectivity/source/resource/conn_shared_res.src: Text [ en-US ] = "You tried to set a parameter at position '$pos$' but there is/are only '$count$' parameter(s) allowed. On dbaccess/source/core/dataaccess/ModelImpl.cxx: AsciiPropertyValue( "ParameterNameSubstitution", makeAny( (sal_Bool)sal_False ) ), dbaccess/source/inc/stringconstants.inc:IMPLEMENT_CONSTASCII_USTRING(INFO_PARAMETERNAMESUBST,"ParameterNameSubstitution"); dbaccess/source/ui/misc/dsmeta.cxx: { DSID_PARAMETERNAMESUBST, "ParameterNameSubstitution" }, offapi/com/sun/star/sdbc/ODBCConnectionProperties.idl: [optional, property] boolean ParameterNameSubstitution; Alex
It is a regression, since the option was there at least in OOo 3, from which we based the LO code on, so setting regression keyword. Alex
Changed title to reflect situation. Alex
Not sure whether this helps, but : git log --pretty=oneline -S'ParameterNameSubstitution' 7d5a3fe55622166ff26fc8bd06e09864aa025926 connectivity: convert configuration 19b8dd5dedc35f39266d8279b2761a5a8946c2d4 No need for test/user-template/user/registry. 49cec3fa6f25cdf2f24602696b6a94570cbbfc2f add user profile dir for unit tests fe4a50d598615844b61704b7beb2a86d1681627f dba34b: make tests runnable with a simple 'dmake run', and selectively runnable with a 'dmake run_<testname>' 739d57852596990fd32e7a1dd2e19ced3da6e738 dba34b: step 1 of including the complex tests herein in a default build, and making them runnable with a single command f27aef916cc611573e3e216a675364be173be6c2 during #i104329#: got rid of DataSourceUI, whose functionality can be reached easier since the database/type information is configurati 6660ab0b9990b7a44f6412306cd6508321fd0fe6 #i104329# introduce a dedicated PrimaryKeySupport property at DataSource.Settings, which determines whether a data source should (be as I don't think that the commits earlier than this would have caused the problem, but did wonder whether these : f27aef916cc611573e3e216a675364be173be6c2 during #i104329#: got rid of DataSourceUI, whose functionality can be reached easier since the database/type information is configurati 6660ab0b9990b7a44f6412306cd6508321fd0fe6 #i104329# introduce a dedicated might be the culprits... Alex
This is specific to MySQL/ODBC and MySQL/JDBC because they have their own sdbc URL namespace (sdbc:mysql:odbc: and sdbc:mysql:jdbc: instead of sdbc:odbc: and sdbc:jdbc). As MySQL does not support named parameters, we should just unconditionally enable NamedParameterSubstitution and don't show the UI for disabling it. I'm trying to do that.
That is, if in menu Edit / Database / Connection type, you select "ODBC" instead of "MySQL (ODBC)" and then select the *same* DSN, the option will be available.
(In reply to comment #10) > As MySQL does not support named parameters, we should just unconditionally > enable NamedParameterSubstitution and don't show the UI for disabling it. Err.. Apparently this is already the case: connectivity/source/drivers/mysql/YDriver.cxx:168 if ( _eType == D_ODBC ) { aProps.push_back( PropertyValue( ::rtl::OUString(RTL_CONSTASCII_USTRINGPARAM("Silent")) ,0 ,makeAny(sal_True) ,PropertyState_DIRECT_VALUE) ); aProps.push_back( PropertyValue( ::rtl::OUString(RTL_CONSTASCII_USTRINGPARAM("PreventGetVersionColumns")) ,0 ,makeAny(sal_True) ,PropertyState_DIRECT_VALUE) ); } PLUS, I cannot reproduce on *any* version of LibreOffice: I tried 3.4, 3.5, 3.6. Closing as WORKSFORME. For full clarity, Andrew: In the new database wizard, either you select "Connect to existing database: MySQL" or you select "Connect to existing database: ODBC". Equivalently, in an existing .odb file, either in menu Edit / Database / Connection type, you select "MySQL (ODBC)" or you select "ODBC". If you select the MySQL-specific support, then the "ParameterNameSubstitution" setting will NOT be available in Advanced Properties, but it will be enabled *always* because disabling it makes no sense, as it won't work with MySQL. If you select the generic ODBC support, then the "ParameterNameSubstitution" setting WILL be available in Advances Properties. As MySQL does not support named parameters, you need to set the option "Replace names parameters with '?'". If either of what I say above is not true, then reopen this bug, and give me more details. I get the same error message as you with the generic ODBC backend and the option unchecked. So I'm assuming that's your situation. Either check the option, or switch to the MySQL-specific support.