Re: [dba-dev] Re: CAST() no longer working with OOO 330m16
Moin, I just have to set the issue to wontfix. :-( The problem seems to be the database. Even with odbctest I get the same results = error and I tested a StarOffice 9.2 which is equal to OOo 3.2, same error as in OOo 3.2.1 and 3.3. Did anyone did an upgrade to a newer MySQL version before the statement stop working? - oj On 08.12.2010 19:04, Fernand Vanrie wrote: Alex , As always, we gave good reasons to use a technique, so let hope the CAST problem will be fixed soon =-) Fernand, Well I also use EXTRACT(), but have found that I still have to CAST the type. If I just use the DATE/TIME functions on their own, the results are inconsistent across the months, especially when adding or subtracting. I haven't kept any of my old queries that did direct application of date addition / substraction because they were of no use to me, so I rewrote them all with CAST and EXTRACT. An example of a current one that works is given below : SELECT CAST(EXTRACT(YEAR FROM CURDATE()) as SIGNED)-CAST(EXTRACT(YEAR FROM Date1) as SIGNED)+1 as 'Renewal Num' FROM mybase where MONTH(Date1) = 12 I do not consider this to be an optimised way of obtaining the result I wanted, but I found it to be the only way that worked reliably for each month of the year, independently of the date provided by CURRENT_DATE. In a more simplified expression, the results were wrong by +/- 1 dependent on the MONTH given and the date provided by CURRENT_DATE. It may be that the underlying cause to this is a problem with date handling by Mysql, and not OOo at all, but I haven't checked this out. Alex - Fernand Vanries...@pmgroup.be a écrit : Alex , its also a fact that there are (better) alternatives for cast Date() convert() or even left() and right() Hi Fernand, Done, but whether it will make any difference ? Its not a blocker because it doesn't cause a crash, or lose your data, but it is surely a regression from 3.2. Alex - Fernand Vanries...@pmgroup.be a écrit : Alexander , It sould be good to send this message also to relea...@openoffice.org there they decide over live and dead :-) greetz Fernand Hi all, Le 08/12/10 13:38, Reizinger Zoltán a écrit : Hi Fernand, It feels to me as I touched in some days ago, and find it, as a known issue with cast in MySQL: http://qa.openoffice.org/issues/show_bug.cgi?id=115436 Zoltan Hmm, this would not be good for me if this is the case because I use a lot of cast statements with date values in mysql. The reason is simple, default typing of the values in date strings used to lead to funny behaviour for me on OOo, which meant that calculations I had in my queries based on the default types led to incorrect results. I was thus forced into using CAST to ensure correct handling. If that functionality has now gone away with the latest dev release, whereas it works in 3.2, and connector 1.0.0, I see no incentive to move to 3.3. Alex - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org
Re: [dba-dev] Re: CAST() no longer working with OOO 330m16
Hi Ocke, - Ocke Janssen ocke.jans...@oracle.com a écrit : I just have to set the issue to wontfix. :-( The problem seems to be the database. Even with odbctest I get the same results = error and I tested a StarOffice 9.2 which is equal to OOo 3.2, same error as in OOo 3.2.1 and 3.3. As I mentioned, CAST works for me with OOo 3.2.1. It also works for me in LibreOffice (just so you know 3.3 rc1, not sure which OOo_m that is based on). Did anyone did an upgrade to a newer MySQL version before the statement stop working? My actual version of mysql server is : Server version: 5.1.50 MySQL Community Server (GPL) My actual version of the mysql connector extension is : 1.0.0 I'm going to test my actual setup with the latest OOoRC, if I can get it to install nicely beside my current OOo 3.2.1 on MacOSX. Alex - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org
Re: [dba-dev] Re: CAST() no longer working with OOO 330m16
Hi Alex,
First of all if I could I would like to fix it.
On 09.12.2010 09:34, Alex Thurgood wrote:
Hi Ocke,
- Ocke Janssenocke.jans...@oracle.com a écrit :
I just have to set the issue to wontfix. :-(
The problem seems to be the database. Even with odbctest I get the
same
results = error and I tested a StarOffice 9.2 which is equal to OOo
3.2, same error as in OOo 3.2.1 and 3.3.
As I mentioned, CAST works for me with OOo 3.2.1. It also works for me in
LibreOffice (just so you know 3.3 rc1, not sure which OOo_m that is based on).
One more interesting thing
when executing
select CAST('2001-01-01' as DATE)
works, but when executing
select CAST('2001-01-01' as DATE) from ´testdb´.´i115436´
an error is thrown.
Could you please add some sample data to the issue?
- oj
Did anyone did an upgrade to a newer MySQL version before the
statement
stop working?
My actual version of mysql server is :
Server version: 5.1.50 MySQL Community Server (GPL)
My actual version of the mysql connector extension is : 1.0.0
I'm going to test my actual setup with the latest OOoRC, if I can get it to
install nicely beside my current OOo 3.2.1 on MacOSX.
Alex
-
To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org
For additional commands, e-mail: dev-h...@dba.openoffice.org
-
To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org
For additional commands, e-mail: dev-h...@dba.openoffice.org
Re: [dba-dev] Re: CAST() no longer working with OOO 330m16
Hi Ocke,
- Ocke Janssen ocke.jans...@oracle.com a écrit :
As I mentioned, CAST works for me with OOo 3.2.1. It also works for
me in LibreOffice (just so you know 3.3 rc1, not sure which OOo_m that
is based on).
One more interesting thing
when executing
select CAST('2001-01-01' as DATE)
works, but when executing
select CAST('2001-01-01' as DATE) from ´testdb´.´i115436´
an error is thrown.
You need to create an alias for the CASTed field. If you use this statement
instead, it should work :
SELECT CAST('2001-01-01' as DATE) as 'MyDate' from 'testdb'.'i115436'
Could you please add some sample data to the issue?
If I get a bit of time today, I'll add some sample data to the issue.
Alex
-
To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org
For additional commands, e-mail: dev-h...@dba.openoffice.org
Re: [dba-dev] Re: CAST() no longer working with OOO 330m16
Hi Alex,
On 09.12.2010 10:35, Alex Thurgood wrote:
Hi Ocke,
- Ocke Janssenocke.jans...@oracle.com a écrit :
As I mentioned, CAST works for me with OOo 3.2.1. It also works for
me in LibreOffice (just so you know 3.3 rc1, not sure which OOo_m that
is based on).
One more interesting thing
when executing
select CAST('2001-01-01' as DATE)
works, but when executing
select CAST('2001-01-01' as DATE) from ´testdb´.´i115436´
an error is thrown.
You need to create an alias for the CASTed field. If you use this statement
instead, it should work :
SELECT CAST('2001-01-01' as DATE) as 'MyDate' from 'testdb'.'i115436'
Thanks. I found the issue and it can be fixed. Look at the issue for the
case.
- oj
PS: A space can make a big difference.
Could you please add some sample data to the issue?
If I get a bit of time today, I'll add some sample data to the issue.
Alex
-
To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org
For additional commands, e-mail: dev-h...@dba.openoffice.org
-
To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org
For additional commands, e-mail: dev-h...@dba.openoffice.org
Re: [dba-dev] Re: CAST() no longer working with OOO 330m16
Ocke, It has certainly nothing to sea with the SQL version or server type. I work in a company envoriment, everyone uses the same SQL server, same native OO-connector. Everyone works on OO 3.2 without problems, i test 3.3 and have the CAST problem: cast(`mag_news_export`.`DATUM` as date) AS `DATUM` where `mag_news_export`.`DATUM` has a imported data (dd/mm/) hope it helps Fernand Moin, I just have to set the issue to wontfix. :-( The problem seems to be the database. Even with odbctest I get the same results = error and I tested a StarOffice 9.2 which is equal to OOo 3.2, same error as in OOo 3.2.1 and 3.3. Did anyone did an upgrade to a newer MySQL version before the statement stop working? - oj On 08.12.2010 19:04, Fernand Vanrie wrote: Alex , As always, we gave good reasons to use a technique, so let hope the CAST problem will be fixed soon =-) Fernand, Well I also use EXTRACT(), but have found that I still have to CAST the type. If I just use the DATE/TIME functions on their own, the results are inconsistent across the months, especially when adding or subtracting. I haven't kept any of my old queries that did direct application of date addition / substraction because they were of no use to me, so I rewrote them all with CAST and EXTRACT. An example of a current one that works is given below : SELECT CAST(EXTRACT(YEAR FROM CURDATE()) as SIGNED)-CAST(EXTRACT(YEAR FROM Date1) as SIGNED)+1 as 'Renewal Num' FROM mybase where MONTH(Date1) = 12 I do not consider this to be an optimised way of obtaining the result I wanted, but I found it to be the only way that worked reliably for each month of the year, independently of the date provided by CURRENT_DATE. In a more simplified expression, the results were wrong by +/- 1 dependent on the MONTH given and the date provided by CURRENT_DATE. It may be that the underlying cause to this is a problem with date handling by Mysql, and not OOo at all, but I haven't checked this out. Alex - Fernand Vanries...@pmgroup.be a écrit : Alex , its also a fact that there are (better) alternatives for cast Date() convert() or even left() and right() Hi Fernand, Done, but whether it will make any difference ? Its not a blocker because it doesn't cause a crash, or lose your data, but it is surely a regression from 3.2. Alex - Fernand Vanries...@pmgroup.be a écrit : Alexander , It sould be good to send this message also to relea...@openoffice.org there they decide over live and dead :-) greetz Fernand Hi all, Le 08/12/10 13:38, Reizinger Zoltán a écrit : Hi Fernand, It feels to me as I touched in some days ago, and find it, as a known issue with cast in MySQL: http://qa.openoffice.org/issues/show_bug.cgi?id=115436 Zoltan Hmm, this would not be good for me if this is the case because I use a lot of cast statements with date values in mysql. The reason is simple, default typing of the values in date strings used to lead to funny behaviour for me on OOo, which meant that calculations I had in my queries based on the default types led to incorrect results. I was thus forced into using CAST to ensure correct handling. If that functionality has now gone away with the latest dev release, whereas it works in 3.2, and connector 1.0.0, I see no incentive to move to 3.3. Alex - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org
Re: [dba-dev] Re: CAST() no longer working with OOO 330m16
Hi Fernand, On 09.12.2010 15:28, Fernand Vanrie wrote: Ocke, It has certainly nothing to sea with the SQL version or server type. I work in a company envoriment, everyone uses the same SQL server, same native OO-connector. Everyone works on OO 3.2 without problems, i test 3.3 and have the CAST problem: cast(`mag_news_export`.`DATUM` as date) AS `DATUM` where `mag_news_export`.`DATUM` has a imported data (dd/mm/) hope it helps I already fixed the issue and better it looks like it will make it into 3.3 :-) The problem was the space CAST ( doesn't work CAST( - works. - oj PS: cws is dba33m Fernand - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org
Re: [dba-dev] Re: CAST() no longer working with OOO 330m16
Ocke, I was just wondering, my problems with CAST started (i think) after i instaled the very latest (oracle-report-builder-OOO330-m3.oxt)for testing . Now i tried to remove this ORB version but i get a error so i can not test without ORB loaded. just a hint PS. how do i remove this unremovable ORB ?:-) Ocke, It has certainly nothing to sea with the SQL version or server type. I work in a company envoriment, everyone uses the same SQL server, same native OO-connector. Everyone works on OO 3.2 without problems, i test 3.3 and have the CAST problem: cast(`mag_news_export`.`DATUM` as date) AS `DATUM` where `mag_news_export`.`DATUM` has a imported data (dd/mm/) hope it helps - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org
Re: [dba-dev] Re: CAST() no longer working with OOO 330m16
Ocke, sorry in my code no space , then i typed cast in uppercase(CAST), no difference.. cast(`mag_news_export`.`DATUM` as date) AS `DATUM`, On 09.12.2010 15:28, Fernand Vanrie wrote: Ocke, It has certainly nothing to sea with the SQL version or server type. I work in a company envoriment, everyone uses the same SQL server, same native OO-connector. Everyone works on OO 3.2 without problems, i test 3.3 and have the CAST problem: cast(`mag_news_export`.`DATUM` as date) AS `DATUM` where `mag_news_export`.`DATUM` has a imported data (dd/mm/) hope it helps I already fixed the issue and better it looks like it will make it into 3.3 :-) The problem was the space CAST ( doesn't work CAST( - works. - oj PS: cws is dba33m Fernand - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org
Re: [dba-dev] Re: CAST() no longer working with OOO 330m16
Alexander , It sould be good to send this message also to relea...@openoffice.org there they decide over live and dead :-) greetz Fernand Hi all, Le 08/12/10 13:38, Reizinger Zoltán a écrit : Hi Fernand, It feels to me as I touched in some days ago, and find it, as a known issue with cast in MySQL: http://qa.openoffice.org/issues/show_bug.cgi?id=115436 Zoltan Hmm, this would not be good for me if this is the case because I use a lot of cast statements with date values in mysql. The reason is simple, default typing of the values in date strings used to lead to funny behaviour for me on OOo, which meant that calculations I had in my queries based on the default types led to incorrect results. I was thus forced into using CAST to ensure correct handling. If that functionality has now gone away with the latest dev release, whereas it works in 3.2, and connector 1.0.0, I see no incentive to move to 3.3. Alex - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org
Re: [dba-dev] Re: CAST() no longer working with OOO 330m16
Hi Fernand, Done, but whether it will make any difference ? Its not a blocker because it doesn't cause a crash, or lose your data, but it is surely a regression from 3.2. Alex - Fernand Vanrie s...@pmgroup.be a écrit : Alexander , It sould be good to send this message also to relea...@openoffice.org there they decide over live and dead :-) greetz Fernand Hi all, Le 08/12/10 13:38, Reizinger Zoltán a écrit : Hi Fernand, It feels to me as I touched in some days ago, and find it, as a known issue with cast in MySQL: http://qa.openoffice.org/issues/show_bug.cgi?id=115436 Zoltan Hmm, this would not be good for me if this is the case because I use a lot of cast statements with date values in mysql. The reason is simple, default typing of the values in date strings used to lead to funny behaviour for me on OOo, which meant that calculations I had in my queries based on the default types led to incorrect results. I was thus forced into using CAST to ensure correct handling. If that functionality has now gone away with the latest dev release, whereas it works in 3.2, and connector 1.0.0, I see no incentive to move to 3.3. Alex - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org
Re: [dba-dev] Re: CAST() no longer working with OOO 330m16
Alex , its also a fact that there are (better) alternatives for cast Date() convert() or even left() and right() Hi Fernand, Done, but whether it will make any difference ? Its not a blocker because it doesn't cause a crash, or lose your data, but it is surely a regression from 3.2. Alex - Fernand Vanries...@pmgroup.be a écrit : Alexander , It sould be good to send this message also to relea...@openoffice.org there they decide over live and dead :-) greetz Fernand Hi all, Le 08/12/10 13:38, Reizinger Zoltán a écrit : Hi Fernand, It feels to me as I touched in some days ago, and find it, as a known issue with cast in MySQL: http://qa.openoffice.org/issues/show_bug.cgi?id=115436 Zoltan Hmm, this would not be good for me if this is the case because I use a lot of cast statements with date values in mysql. The reason is simple, default typing of the values in date strings used to lead to funny behaviour for me on OOo, which meant that calculations I had in my queries based on the default types led to incorrect results. I was thus forced into using CAST to ensure correct handling. If that functionality has now gone away with the latest dev release, whereas it works in 3.2, and connector 1.0.0, I see no incentive to move to 3.3. Alex - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org
Re: [dba-dev] Re: CAST() no longer working with OOO 330m16
Fernand, Well I also use EXTRACT(), but have found that I still have to CAST the type. If I just use the DATE/TIME functions on their own, the results are inconsistent across the months, especially when adding or subtracting. I haven't kept any of my old queries that did direct application of date addition / substraction because they were of no use to me, so I rewrote them all with CAST and EXTRACT. An example of a current one that works is given below : SELECT CAST(EXTRACT(YEAR FROM CURDATE()) as SIGNED)-CAST(EXTRACT(YEAR FROM Date1) as SIGNED)+1 as 'Renewal Num' FROM mybase where MONTH(Date1) = 12 I do not consider this to be an optimised way of obtaining the result I wanted, but I found it to be the only way that worked reliably for each month of the year, independently of the date provided by CURRENT_DATE. In a more simplified expression, the results were wrong by +/- 1 dependent on the MONTH given and the date provided by CURRENT_DATE. It may be that the underlying cause to this is a problem with date handling by Mysql, and not OOo at all, but I haven't checked this out. Alex - Fernand Vanrie s...@pmgroup.be a écrit : Alex , its also a fact that there are (better) alternatives for cast Date() convert() or even left() and right() Hi Fernand, Done, but whether it will make any difference ? Its not a blocker because it doesn't cause a crash, or lose your data, but it is surely a regression from 3.2. Alex - Fernand Vanries...@pmgroup.be a écrit : Alexander , It sould be good to send this message also to relea...@openoffice.org there they decide over live and dead :-) greetz Fernand Hi all, Le 08/12/10 13:38, Reizinger Zoltán a écrit : Hi Fernand, It feels to me as I touched in some days ago, and find it, as a known issue with cast in MySQL: http://qa.openoffice.org/issues/show_bug.cgi?id=115436 Zoltan Hmm, this would not be good for me if this is the case because I use a lot of cast statements with date values in mysql. The reason is simple, default typing of the values in date strings used to lead to funny behaviour for me on OOo, which meant that calculations I had in my queries based on the default types led to incorrect results. I was thus forced into using CAST to ensure correct handling. If that functionality has now gone away with the latest dev release, whereas it works in 3.2, and connector 1.0.0, I see no incentive to move to 3.3. Alex - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org
