reproduction: 1. Connect base to mysql database 2. make query with the following code: Select CAST("columnname" as CHAR) from "TABLE1" end it will yield an error msgbox. The above code works fine on embedded (HSQLDB) database. It is persistent on linux and windows. Also tried with native connector and jdbc connector.
how about making things easy for those non-base enabled ( like me ) :-) with some nice easy steps to reproduce this
Unfortunately I don't know how to explain it easier :(( You should know how to connect Base to mysql it's explained here: http://wiki.services.openoffice.org/wiki/Connect_MySQL_and_Base Once it's done a simple query with above command of cast will show an error. But I am skeptical about the users who haven't done anything in Base or at least some other database engines before.
I can not reproduce this bug with beta3 on Mac OSX using the mysql native connector. I have a query that uses multiple CAST instructions. I paste this query into the Query Designer window. If you click on the button "Run SQL Command Directly" (SQL with green tick mark) and then execute the query it works just fine. If you try and use the native OOo built-in SQL parser, it baulks because it can not parse mysql specific CAST instructions to the driver, and will thus throw an error message. However, the parser has always behaved this way. The same is true within OOo at least in 3.2 this is what happens, I haven't tested in the 3.3 RC versions to see if this behaviour has been changed, but I doubt it. So, as far as I'm concerned, I would set this to resolved worksforme, or possibly even invalid. The question will inevitably arise as to whether the in-built SQL parser should be improved to allow for better support of the drivers capabilities, but that would probably involve quite a lot of development work (I'm not a Base developer). Unset NEEDINFO, setting RESOLVED WORKSFORME. Alex
Just so the OP understands that I used my own SQL query to test on my mysql server : SELECT CAST(Filing_Date1 as CHAR) as 'FD' FROM mydb This casts the date string as a character string. Alex
Created attachment 40418 [details] bug shot
Try giving your cast an alias as was indicated in my example. SELECT CAST(ID as CHAR) as 'TXTID' from customers Alex
(In reply to comment #6) > Try giving your cast an alias as was indicated in my example. > > SELECT CAST(ID as CHAR) as 'TXTID' from customers > > > Alex That trick works, but not when Cast is used in where clause, like: SELECT CAST(ID as CHAR) as 'TXTID' from customers where CAST(ID as CHAR) = '556' Oto
IMHO this is not a bug, but a lack in the current implementation of either the parser developed by Sun/Oracle, or the mysql/jdbc connector provided by Sun/Oracle. Put in for a feature request / enhancement if you like, but be prepared to be asked to help develop it ;-) Even better, put in the request with the people at Oracle, since this "feature lack" comes from the code that was developed within StarOffice initially. And yes, it annoys me too that the Base implementation isn't as "advanced" as I feel it could be, but there you go, until I develop the skills to reimplement a complete SQL parser engine for LibO in C++, it probably isn't going to happen :-)) Setting to NOTOURBUG. Alex
Nevertheless, a patch has been submitted on the similar issue in openoffice.org so if anyone of devs can review and include this in LO suite would be nice. You can check it out here: http://www.openoffice.org/issues/show_bug.cgi?id=115436