Parser: (SELECT) Complicated CAST(.... AS TYPE) fails with exception.

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Parse the following query: SELECT CAST((CONCAT(table1.col1,' ',time_start)) 
AS DATETIME) FROM table1

What is the expected output? What do you see instead?
Expected: Parsed query
Result: Parse error: cannot calculate position of table1.col1 within AS 
DATETIME) BETWEEN '2012-06-18 00:00:00' AND '2012-06-18 23:59:00'

What version of the product are you using? On what operating system?
Downloaded from Trunk Rev 288

Please provide any additional information below.

Regards,
Marco

Original issue reported on code.google.com by Marco6...@gmail.com on 21 Jun 2012 at 4:54

[GoogleCodeExporter]

This is a problem with the CAST() or CONVERT() function. Both functions are not 
parsed at the moment, so all SQL statements with these functions go wrong.

Original comment by pho...@gmx.de on 2 Jul 2012 at 8:29

• Changed state: Accepted

[GoogleCodeExporter]

Check out r343, it seems to work now. 

Original comment by pho...@gmx.de on 30 Aug 2012 at 6:42

[GoogleCodeExporter]

Thanks for telling me it should be fixed now.

Unfortunately i needed both the parser and the creator for a project and i did 
not have time to wait.

I have attached a zip file that contains a list of all the issues i encountered 
in the parser (trunk rev. 288) and creator (trunk rev. r274), and the files as 
i use them now (based on those versions) with my modifications to them.

It is maybe not a nice formatted report, and probably many things already 
outdated with new versions, but maybe some of the reported bugs or the way i 
fixed them can still be of use to you.

Regards,
Marco

Original comment by Marco6...@gmail.com on 30 Aug 2012 at 7:04

Attachments:

• PHP_SQL_parser_creator.zip

[GoogleCodeExporter]

Thanks a lot! I'll check, which parts I can copy to the main code. Nice to see, 
that the parser is useful to someone :-)

Original comment by pho...@gmx.de on 30 Aug 2012 at 8:09

• Changed state: Started

[GoogleCodeExporter]

It is very useful in the project i was working on.

The project was about modifying an existing open source software package (over 
2.000 PHP files, almost 500 MySQL tables). The goal was to use the software 
(single installation/database) for multiple clients, where the data of each 
client needed to be totally seperated from other clients (no data leaks).

In order to achieve this most tables had to get a new client_id column and all 
queries needed to be modified to use the client_id when inserting new data or 
in the WHERE clause when retrieving data, etc..

Doing this the old fashioned way by going over all sources and editing all 
queries would be undoable and would probable have led to missed queries and by 
that data leaks. Fortunately the software was using 1 (actually 2) low-level 
routines for all access to the MySQL database. So the solution was to intercept 
all queries before they get executed, parse them, modify them, rebuild them and 
then execute.

The PHP-SQL parser & Creator gave me a good starting point for that.

(Thought it would be nice to share a story on how it can be used)

Original comment by Marco6...@gmail.com on 30 Aug 2012 at 10:03

[GoogleCodeExporter]

I have included all your uploaded changes into the main sourcecode. Thanks a 
lot!

Original comment by pho...@gmx.de on 2 Apr 2014 at 1:12

• Changed state: Fixed