CAST() no longer working with OOO 330m16

classic Classic list List threaded Threaded
36 messages Options
12
Reply | Threaded
Open this post in threaded view
|

CAST() no longer working with OOO 330m16

Fernand Vanrie
Just found that queries who worked for years are halted now (OOO 330m16)
using Cast("timestampvalue" as date)
CAST("timestampvalue" as date) use to run in direct SQL mode
I think that CAST as date is no longer "loved" and by mySQL they
recommand DATE("timestampvalue")  i have some queries around with CAST ,
do not know if there are more ? andis it whort a issue ?

Greetz

Fernand
Reply | Threaded
Open this post in threaded view
|

Re: CAST() no longer working with OOO 330m16

Reizinger Zoltán
Hi Fernand,
The CAST("timestampvalue" as date) working for me in windows version of
OOo 3.3RC7(m17), with SQL direct, with built-in hsqldb.
Regards,
Zoltan

2010.12.08. 11:45 keltezéssel, Fernand Vanrie írta:

> Just found that queries who worked for years are halted now (OOO
> 330m16) using Cast("timestampvalue" as date)
> CAST("timestampvalue" as date) use to run in direct SQL mode
> I think that CAST as date is no longer "loved" and by mySQL they
> recommand DATE("timestampvalue")  i have some queries around with CAST
> , do not know if there are more ? andis it whort a issue ?
>
> Greetz
>
> Fernand
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: CAST() no longer working with OOO 330m16

Fernand Vanrie
hi Zoltán ,

Indeed with the built-in hsqldb CAST still works, but no longer with a
connection (using the native connector) to mySQL !
same situation for OOO 330m16 and m17

Fernand

> Hi Fernand,
> The CAST("timestampvalue" as date) working for me in windows version
> of OOo 3.3RC7(m17), with SQL direct, with built-in hsqldb.
> Regards,
> Zoltan
>
> 2010.12.08. 11:45 keltezéssel, Fernand Vanrie írta:
>> Just found that queries who worked for years are halted now (OOO
>> 330m16) using Cast("timestampvalue" as date)
>> CAST("timestampvalue" as date) use to run in direct SQL mode
>> I think that CAST as date is no longer "loved" and by mySQL they
>> recommand DATE("timestampvalue")  i have some queries around with
>> CAST , do not know if there are more ? andis it whort a issue ?
>>
>> Greetz
>>
>> Fernand
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: CAST() no longer working with OOO 330m16

Reizinger Zoltán
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

2010.12.08. 13:12 keltezéssel, Fernand Vanrie írta:

> hi Zoltán ,
>
> Indeed with the built-in hsqldb CAST still works, but no longer with a
> connection (using the native connector) to mySQL !
> same situation for OOO 330m16 and m17
>
> Fernand
>> Hi Fernand,
>> The CAST("timestampvalue" as date) working for me in windows version
>> of OOo 3.3RC7(m17), with SQL direct, with built-in hsqldb.
>> Regards,
>> Zoltan
>>
>> 2010.12.08. 11:45 keltezéssel, Fernand Vanrie írta:
>>> Just found that queries who worked for years are halted now (OOO
>>> 330m16) using Cast("timestampvalue" as date)
>>> CAST("timestampvalue" as date) use to run in direct SQL mode
>>> I think that CAST as date is no longer "loved" and by mySQL they
>>> recommand DATE("timestampvalue")  i have some queries around with
>>> CAST , do not know if there are more ? andis it whort a issue ?
>>>
>>> Greetz
>>>
>>> Fernand
>>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: CAST() no longer working with OOO 330m16

Alexander Thurgood
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: CAST() no longer working with OOO 330m16

Fernand Vanrie
  Alexander ,

It sould be good to send this message also to [hidden email]  
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: [hidden email]
> For additional commands, e-mail: [hidden email]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: CAST() no longer working with OOO 330m16

Alex Thurgood
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" <[hidden email]> a écrit :

> Alexander ,
>
> It sould be good to send this message also to [hidden email]
>
> 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: [hidden email]
> > For additional commands, e-mail: [hidden email]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: CAST() no longer working with OOO 330m16

Fernand Vanrie
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 Vanrie"<[hidden email]>  a écrit :
>
>> Alexander ,
>>
>> It sould be good to send this message also to [hidden email]
>>
>> 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: [hidden email]
>>> For additional commands, e-mail: [hidden email]
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: CAST() no longer working with OOO 330m16

Alex Thurgood
In reply to this post by Fernand Vanrie
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" <[hidden email]> 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 Vanrie"<[hidden email]>  a écrit :
> >
> >> Alexander ,
> >>
> >> It sould be good to send this message also to
> [hidden email]
> >>
> >> 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: [hidden email]
> >>> For additional commands, e-mail: [hidden email]
> >>
> >>
> ---------------------------------------------------------------------
> >> To unsubscribe, e-mail: [hidden email]
> >> For additional commands, e-mail: [hidden email]
> >
> ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [hidden email]
> > For additional commands, e-mail: [hidden email]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: CAST() no longer working with OOO 330m16

Fernand Vanrie
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 Vanrie"<[hidden email]>  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 Vanrie"<[hidden email]>   a écrit :
>>>
>>>> Alexander ,
>>>>
>>>> It sould be good to send this message also to
>> [hidden email]
>>>> 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: [hidden email]
>>>>> For additional commands, e-mail: [hidden email]
>>>>
>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: [hidden email]
>>>> For additional commands, e-mail: [hidden email]
>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: [hidden email]
>>> For additional commands, e-mail: [hidden email]
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: CAST() no longer working with OOO 330m16

Ocke Janssen-2
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 Vanrie"<[hidden email]> 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 Vanrie"<[hidden email]> a écrit :
>>>>
>>>>> Alexander ,
>>>>>
>>>>> It sould be good to send this message also to
>>> [hidden email]
>>>>> 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: [hidden email]
>>>>>> For additional commands, e-mail: [hidden email]
>>>>>
>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: [hidden email]
>>>>> For additional commands, e-mail: [hidden email]
>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: [hidden email]
>>>> For additional commands, e-mail: [hidden email]
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: [hidden email]
>>> For additional commands, e-mail: [hidden email]
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: CAST() no longer working with OOO 330m16

Alex Thurgood
In reply to this post by Fernand Vanrie
Hi Ocke,


----- "Ocke Janssen" <[hidden email]> 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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: CAST() no longer working with OOO 330m16

Alex Thurgood
In reply to this post by Fernand Vanrie

>
> 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.
>

More info : I have just tested with OOo 3.3rc7, same connector, same mysql server, and CAST still functions correctly for me. So my guess is that if there is a problem, it is related to latest version of mysql server, which is 5.1.53, so the won't fix is understandable because apparently it is not OOo's bug.


Alex



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: CAST() no longer working with OOO 330m16

Ocke Janssen-2
In reply to this post by Alex Thurgood
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 Janssen"<[hidden email]>  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: [hidden email]
> For additional commands, e-mail: [hidden email]
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: CAST() no longer working with OOO 330m16

Alex Thurgood
Hi Ocke,


----- "Ocke Janssen" <[hidden email]> 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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: CAST() no longer working with OOO 330m16

Ocke Janssen-2
Hi Alex,

On 09.12.2010 10:35, Alex Thurgood wrote:

> Hi Ocke,
>
>
> ----- "Ocke Janssen"<[hidden email]>  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: [hidden email]
> For additional commands, e-mail: [hidden email]
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: CAST() no longer working with OOO 330m16

Fernand Vanrie
In reply to this post by Ocke Janssen-2
  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/yyyy)
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 Vanrie"<[hidden email]> 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 Vanrie"<[hidden email]> a écrit :
>>>>>
>>>>>> Alexander ,
>>>>>>
>>>>>> It sould be good to send this message also to
>>>> [hidden email]
>>>>>> 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: [hidden email]
>>>>>>> For additional commands, e-mail: [hidden email]
>>>>>>
>>>> ---------------------------------------------------------------------
>>>>>> To unsubscribe, e-mail: [hidden email]
>>>>>> For additional commands, e-mail: [hidden email]
>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: [hidden email]
>>>>> For additional commands, e-mail: [hidden email]
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: [hidden email]
>>>> For additional commands, e-mail: [hidden email]
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: [hidden email]
>>> For additional commands, e-mail: [hidden email]
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: CAST() no longer working with OOO 330m16

Ocke Janssen-2
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/yyyy)
> 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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: CAST() no longer working with OOO 330m16

Fernand Vanrie
In reply to this post by Fernand Vanrie
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/yyyy)
> hope it helps
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: CAST() no longer working with OOO 330m16

Alex Thurgood
In reply to this post by Ocke Janssen-2

> 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

Excellent, great news, thanks :-)

Alex

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

12