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: [email protected]
For additional commands, e-mail: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
