IN in WHERE-clause is not being parsed correctly using SQL::Statement

user_1189819 0
Viewed 142

I'm using a slightly modified version of the example provided here: https://metacpan.org/pod/distribution/SQL-Statement/lib/SQL/Statement/Structure.pod 

use SQL::Statement;
use Data::Dumper;

my $sql = q{
SELECT c1
     , col2 as c2
     , c3
FROM table1 t1, table2 t2
WHERE t1.c1 = t2.c2
 and  t1.c1 in (11111, 22222, 33333)
GROUP by t1.c1
};

my $parser = SQL::Parser->new('ANSI');
$parser->{RaiseError}=1;
$parser->{PrintError}=0;
my $stmt = SQL::Statement->new($sql, $parser);
print Dumper($stmt->where_hash());

But when I do so, I'm getting this error which doesn't make sense since it's a pretty common construct:

Bad table or column name: '11111,22222,33333' has chars not alphanumeric or underscore! at /home/palert/perl5/perlbrew/perls/perl-5.28.1/lib/site_perl/5.28.1/SQL/Statement.pm line 90.

What am I missing?

dbd sql-parser dbi perl sql
2 Answers

Mailing the maintainer privately instead of checking open issues or reading https://metacpan.org/pod/SQL::Statement#Where-can-I-go-for-help? carefully doesn't show how much time you invests on your own solving the issue.

Polar Bear gave you the right answer from SQL::Statement point of view. I know it has quirks but it's a 20 year old Perl5 module and thus it doesn't use new fancy abstractions like type system via Moose or wtf.

OTOH - you can use placeholders as it's recommended and Ether told you. Than you pass the values on execute, not during parse.

user_110142570

Bottom-line is that SQL::Statement is fairly limited and can handle only basic SQL. The lack of support for "in (numeric list)" is only one of them. Not being able to handle subqueries is another big one for me.

I had high hope for SQL::Statement for the project I'm working on where I wanted to translate Sybase SQL into MySQL SQL statements on-the-fly. But it will not work.

I decided to create a Perl wrapper around https://github.com/dmtolpeko/sqlines instead. It's not perfect but it gets me closer to my goal.

It's not a full-Perl solution but so far it seems to work and it's pretty fast.

Thanks to all for their comments/answers.

user_11898190
Related