JDBC and ODBC driver problems querying mysql from OOo

[Alex Thurgood]

Well here I am again, with more problems querying a mysql server from OOo.

Problem 1
I have the following query :

select File_num, Ctry_short, Filing_num, Filing_Date1, MONTHNAME(Filing_Date1) as 'Mois_Ann', TRUNCATE(DATEDIFF(CURRENT_DATE,Filing_Date1)/365, 0) as 'Quantième', Client_Copy, Owner1, Ann_Fees1 as 'Délai Annuité', pay_ann_fee as 'Gestion Annuités', Comments, Summary from oldfmpro where Ctry_Short = 'FR' and pay_ann_fee = 'Y' and MONTH(Filing_Date1) = 12 ORDER BY Filing_Date1

OK, so fairly simple, a query on a single table.

If I use the JDBC connector, the query executes fine from within OOo when I activate SQL Direct mode, otherwise I get a syntax error (that OOo parser sure is limited) :

parse error, expecting `BETWEEN' or `IN' or `SQL_TOKEN_LIKE'

A similar error appears if I try to activate the Query Design Mode :
Erreur de syntaxe SQL

It is rather odd that I should get similar messages in 2 different languages, but I guess that's a translation bug.

Using the MyODBC Connector 3.51 :
The query executes in SQL Direct mode, but displays <OBJECT> instead of the desired result for the column MONTHNAME(Filing_Date1) as 'Mois_Ann'. Why ? MONTHNAME is a function within mysql that returns the name of the month corresponding to the integer value of that month in a date string, eg. 25/12/2008 returns "December". Is this behaviour a limitation of the ODBC driver or a problem with OOo ?


Problem 2
Using the same query, I wanted to substitute MONTH(Filing_Date1) by a parameter, e.g. :Mois_Choisi, or ?Mois_Choisi and allow the user to input a value from 1 to 12 representing the 12 months of the year. However, it doesn't seem to matter which way I formulate the parameter, ie. whether using the OOo way with a colon, or using the mysql way with a question mark, I always get an error message that either there is an error in my SQL statement, or that the "parameter variable was not given". OOo didn't even ask me for the parameter in the first place, but perhaps that is because it only does so when in OOo SQL parsing or Query Design mode and not in SQL Direct mode. I also tried switching the option ParamaterModeSubstitution on and off in the driver settings.

So that leads me to my second question : is there a problem with parameter substitution in OOo ?

For the record, this has been tried with NeoOffice 3.0, OpenOffice 3.1, for the Mac Intel Leopard, and using connectors JDBC 5.1.6_2 and ODBC 3.51 for Mac OSX.


Anyone confirm or deny ?
TIA,

Alex




---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Fernand Vanrie]

Alex ,
problem1:
 Make a choice between the driver you want to use, because : wath works
for JDBC not alwais works for ODBC etc....

When using Functions who are server specific like (MONTHNAME) then you
need to past native SQL and hopes that the driver support  this
functions Not all functions are suported by every driver, if not then
trye a driver specific function.
problem2:
Parameters are working but you must past them as NON-native SQL :-) and
formulate then as *like :myinput* (no space between the ":" and "myinput")

hope it helps
Fernand

ps: why not trying the native SQLconnector ?

> Well here I am again, with more problems querying a mysql server from OOo.
>
> Problem 1
> I have the following query :
>
> select File_num, Ctry_short, Filing_num, Filing_Date1, MONTHNAME(Filing_Date1) as 'Mois_Ann', TRUNCATE(DATEDIFF(CURRENT_DATE,Filing_Date1)/365, 0) as 'Quantième', Client_Copy, Owner1, Ann_Fees1 as 'Délai Annuité', pay_ann_fee as 'Gestion Annuités', Comments, Summary from oldfmpro where Ctry_Short = 'FR' and pay_ann_fee = 'Y' and MONTH(Filing_Date1) = 12 ORDER BY Filing_Date1
>
> OK, so fairly simple, a query on a single table.
>
> If I use the JDBC connector, the query executes fine from within OOo when I activate SQL Direct mode, otherwise I get a syntax error (that OOo parser sure is limited) :
>
> parse error, expecting `BETWEEN' or `IN' or `SQL_TOKEN_LIKE'
>
> A similar error appears if I try to activate the Query Design Mode :
> Erreur de syntaxe SQL
>
> It is rather odd that I should get similar messages in 2 different languages, but I guess that's a translation bug.
>
> Using the MyODBC Connector 3.51 :
> The query executes in SQL Direct mode, but displays <OBJECT> instead of the desired result for the column MONTHNAME(Filing_Date1) as 'Mois_Ann'. Why ? MONTHNAME is a function within mysql that returns the name of the month corresponding to the integer value of that month in a date string, eg. 25/12/2008 returns "December". Is this behaviour a limitation of the ODBC driver or a problem with OOo ?
>
>
> Problem 2
> Using the same query, I wanted to substitute MONTH(Filing_Date1) by a parameter, e.g. :Mois_Choisi, or ?Mois_Choisi and allow the user to input a value from 1 to 12 representing the 12 months of the year. However, it doesn't seem to matter which way I formulate the parameter, ie. whether using the OOo way with a colon, or using the mysql way with a question mark, I always get an error message that either there is an error in my SQL statement, or that the "parameter variable was not given". OOo didn't even ask me for the parameter in the first place, but perhaps that is because it only does so when in OOo SQL parsing or Query Design mode and not in SQL Direct mode. I also tried switching the option ParamaterModeSubstitution on and off in the driver settings.
>
> So that leads me to my second question : is there a problem with parameter substitution in OOo ?
>
> For the record, this has been tried with NeoOffice 3.0, OpenOffice 3.1, for the Mac Intel Leopard, and using connectors JDBC 5.1.6_2 and ODBC 3.51 for Mac OSX.
>
>
> Anyone confirm or deny ?
> TIA,
>
> Alex
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...
>  

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Alex Thurgood]

Hi Fernand,


>  Make a choice between the driver you want to use, because : wath
> works
> for JDBC not alwais works for ODBC etc....

Yes, I know, or rather have learnt the hard way, but this shouldn't be the case...The problem with using JDBC is the performance factor, because it is far, far slower than ODBC when using OOo and always has been. I still get "Nested Exception" messages due to connection timeouts using the JDBC connector over a remote connection to the my mysql server, which quite frankly sucks big time. I know that there is a parameter you can pass the JDBC driver to reconnect automatically, but if you read the MySQL documentation, they advise you not to use it as it was only meant to be used to maintain backwards compatibility with older server versions. Hence my preference for ODBC. It is a no win situation :-(


>
> When using Functions who are server specific like (MONTHNAME) then you
>
> need to past native SQL and hopes that the driver support  this
> functions Not all functions are suported by every driver, if not then
>
> trye a driver specific function.

Hmm, the difficulty is knowing which functions work with which driver...


> problem2:
> Parameters are working but you must past them as NON-native SQL :-)
> and
> formulate then as *like :myinput* (no space between the ":" and
> "myinput")
>

Thanks, I will try that out, but I didn't realise you had to use the LIKE operand, this doesn't appear to be referenced in the MySQL official documentation (unless I've looked in the wrong place).

>
> ps: why not trying the native SQLconnector ?

The native connector doesn't yet work with ports other than the default 3306 :-(( which is how I'm accessing the mysql server. The fix has been done, but obviously will have to wait for the next release of the connector to be integrated.


Thanks for your hints,

Alex

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Keith Clark-2]

On Wed, 2009-07-08 at 23:59 +0200, Alex Thurgood wrote:
> I know that there is a parameter you can pass the JDBC driver to
> reconnect automatically, but if you read the MySQL documentation, they
> advise you not to use it as it was only meant to be used to maintain
> backwards compatibility with older server versions.

I would love to know more details about that parameter!

Keith



---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[drewjensen]

Alex Thurgood wrote:
> Well here I am again, with more problems querying a mysql server from OOo.
>
> Problem 1
> I have the following query :
>
> select File_num, Ctry_short, Filing_num, Filing_Date1, MONTHNAME(Filing_Date1) as 'Mois_Ann', TRUNCATE(DATEDIFF(CURRENT_DATE,Filing_Date1)/365, 0) as 'Quantième', Client_Copy, Owner1, Ann_Fees1 as 'Délai Annuité', pay_ann_fee as 'Gestion Annuités', Comments, Summary from oldfmpro where Ctry_Short = 'FR' and pay_ann_fee = 'Y' and MONTH(Filing_Date1) = 12 ORDER BY Filing_Date1
>
>  
Hi Alex

Opened a new issue for the MySQL datediff syntax:
http://qa.openoffice.org/issues/show_bug.cgi?id=103425

I set it for the Native Connector sub_component as that was what I had
running when I read your email mail..if you want to update it for JDBC /
ODBC also.

Drew

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[drewjensen]

> Problem 2
> Using the same query, I wanted to substitute MONTH(Filing_Date1) by a parameter, e.g. :Mois_Choisi, or ?Mois_Choisi and allow the user to input a value from 1 to 12 representing the 12 months of the year. However, it doesn't seem to matter which way I formulate the parameter, ie. whether using the OOo way with a colon, or using the mysql way with a question mark, I always get an error message that either there is an error in my SQL statement, or that the "parameter variable was not given". OOo didn't even ask me for the parameter in the first place, but perhaps that is because it only does so when in OOo SQL parsing or Query Design mode and not in SQL Direct mode. I also tried switching the option ParamaterModeSubstitution on and off in the driver settings.
>
> So that leads me to my second question : is there a problem with parameter substitution in OOo ?
>
>  
Well, the problem is that parameter substitution only works with Escape
Processing turned on.
Given the problem with datediff under MySQL requires that Escape
Processing be turned off this negates your ability to use this feature.

Now, could that query be re-written in a way to allow escape processing
on the client (OO.o) side?

Drew

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[drewjensen]

Drew Jensen wrote:

>
>> Problem 2
>> Using the same query, I wanted to substitute MONTH(Filing_Date1) by a
>> parameter, e.g. :Mois_Choisi, or ?Mois_Choisi and allow the user to
>> input a value from 1 to 12 representing the 12 months of the year.
>> However, it doesn't seem to matter which way I formulate the
>> parameter, ie. whether using the OOo way with a colon, or using the
>> mysql way with a question mark, I always get an error message that
>> either there is an error in my SQL statement, or that the "parameter
>> variable was not given". OOo didn't even ask me for the parameter in
>> the first place, but perhaps that is because it only does so when in
>> OOo SQL parsing or Query Design mode and not in SQL Direct mode. I
>> also tried switching the option ParamaterModeSubstitution on and off
>> in the driver settings.
>>
>> So that leads me to my second question : is there a problem with
>> parameter substitution in OOo ?
>>  
> Well, the problem is that parameter substitution only works with
> Escape Processing turned on.
> Given the problem with datediff under MySQL requires that Escape
> Processing be turned off this negates your ability to use this feature.
>
> Now, could that query be re-written in a way to allow escape
> processing on the client (OO.o) side?
>

OK - well you can do that. Won't be as efficient (perhaps) but it works
of course.

Create 2 queries.

First query is just for that datediff column and of course must also
return your key value.
Set escape processing off (Run SQL Direct) for this query.

Now create a second query the joins your table and the first query on
the key.
Here in this second query you can have Escape Processing set to true -
so you can use the parameter substitution for the month as you desire.

HTH,

Drew

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[drewjensen]

ARRRGH - should of actually run those queries all the way - not just to
see that the parameter dialog popped up - before hitting send.

That doesn't work for a MySQL JDBC or Native connection it seems  - it
prompts for the criteria but then passes the parameter name to the
engine instead of the supplied value. At least not with the OOO310m_14
build.

Works fine for an embedded database.

Drew

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Fernand Vanrie]

Alex Thurgood wrote:

> Hi Fernand,
>
>
>  
>>  Make a choice between the driver you want to use, because : wath
>> works
>> for JDBC not alwais works for ODBC etc....
>>    
>
> Yes, I know, or rather have learnt the hard way, but this shouldn't be the case...The problem with using JDBC is the performance factor, because it is far, far slower than ODBC when using OOo and always has been. I still get "Nested Exception" messages due to connection timeouts using the JDBC connector over a remote connection to the my mysql server, which quite frankly sucks big time. I know that there is a parameter you can pass the JDBC driver to reconnect automatically, but if you read the MySQL documentation, they advise you not to use it as it was only meant to be used to maintain backwards compatibility with older server versions. Hence my preference for ODBC. It is a no win situation :-(
>
>
>  
>> When using Functions who are server specific like (MONTHNAME) then you
>>
>> need to past native SQL and hopes that the driver support  this
>> functions Not all functions are suported by every driver, if not then
>>
>> trye a driver specific function.
>>    
>
> Hmm, the difficulty is knowing which functions work with which driver...
>
>
>  
>> problem2:
>> Parameters are working but you must past them as NON-native SQL :-)
>> and
>> formulate then as *like :myinput* (no space between the ":" and
>> "myinput")
>>
>>    
>
> Thanks, I will try that out, but I didn't realise you had to use the LIKE operand,

nono it works also with other operands (= etc...) but the advantage off
"like" is that the users can use the "%" (not "*") to find wath they
wanted in a column (like %sometext%

>  this doesn't appear to be referenced in the MySQL official documentation (unless I've looked in the wrong place).
>
>  
>> ps: why not trying the native SQLconnector ?
>>    
>
> The native connector doesn't yet work with ports other than the default 3306 :-(( which is how I'm accessing the mysql server. The fix has been done, but obviously will have to wait for the next release of the connector to be integrated.
>
>
> Thanks for your hints,
>
> Alex
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...
>  

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...