Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Parse and change an SQL statement

by philgoetz (Novice)
on Mar 19, 2012 at 20:14 UTC ( #960473=perlquestion: print w/ replies, xml ) Need Help??
philgoetz has asked for the wisdom of the Perl Monks concerning the following question:

I know the module SQL::Statement can parse an SQL statement. But I need to automatically convert statements back and forth between Sybase and SQLite, so I need to parse a statement, then CHANGE parts of it (e.g., change column names to avoid reserved words or to emulate Sybase functions), and then construct a new SQL statement for the other database type to execute.

For instance, I would want to convert

"SELECT len(ec#), getdate(), user_name() FROM ident"

into

"SELECT length(ec_num), date(), $ENV{USER} FROM identity"

(SQLite does not allow the column names 'ec#' or 'ident'.)

As far as I can tell, SQL::Statement doesn't let you construct a new statement out of constituent parts. Any idea what can?

Comment on Parse and change an SQL statement
Re: Parse and change an SQL statement
by philgoetz (Novice) on Mar 19, 2012 at 20:53 UTC
    In fact, SQL::Statement appears to be unusable even for just parsing. The key function is column_defs(), which is supposed to return the columns in the statement; and it isn't defined. Trying to execute
    our $SqlParser = SQL::Parser->new() or die "Could not create an SQL::P +arser"; my $parse = SQL::Statement->new('SELECT foo FROM bar', $SqlParser); my @columns = $parse->column_defs();
    results in the error
    Can't locate object method "column_defs" via package "SQL::Statement" +at /local/ifs_projects/prok/function/src/lib/pgoetzUtils.pm line 939.
Re: Parse and change an SQL statement
by jandrew (Hermit) on Mar 19, 2012 at 21:24 UTC
    Will DBI Solve your problem?
Re: Parse and change an SQL statement
by bdo (Acolyte) on Mar 19, 2012 at 21:29 UTC
    How about SQL::Translator (https://metacpan.org/module/SQL::Translator) ?

      As documented out of the box SQL::Translator only handles CREATE and ALTER statements. The OP gave a SELECT statement as a specific example so the suggested module is unlikely to fit the bill.

      True laziness is hard work
        Oh right, I missed that distinction.
Re: Parse and change an SQL statement
by chrestomanci (Priest) on Mar 19, 2012 at 22:09 UTC

    Where is this SQL that needs translating coming from? Did you write it (or another programmer on your team). Is it from a legacy codebase? Is it being machine generated by another program that you can't modify?

    If you, or a team mate are writing the SQL, them SQL::Abstract might be an option. You write your query in a perl like syntax, and it turns it into SQL in whatever dialect you specify. It can't do everything, but does most thing fairly well, including joins of arbitrary complexity, however it is only practical if you are writing the queries, rather than something else.

    If the SQL is hard coded into a legacy codebase, or is being generated by a legacy program, then I think you will have to write a translator yourself. The good news is doing so is a finite problem. If it is old hard coded SQL, then you just need to find them all and write translation. If it is an old generator, then it is unlikely to be generating anything unexpected (like a human programmer might), so again you need to figure out how it is building it's queries, and deconstruct them, so that you can re-generate them in the other dialect.

Re: Parse and change an SQL statement
by clueless newbie (Hermit) on Mar 19, 2012 at 22:20 UTC

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://960473]
Approved by Corion
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (5)
As of 2014-12-25 17:16 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (161 votes), past polls