2

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?

Jerome Provensal
  • 931
  • 11
  • 22
  • Did you see the group by clause you have ? it has only 1 col while select has 3. – Avi May 12 '20 at 03:17
  • 1
    Seems to be a bug/limitation of the parser. Try reaching out to the authors? – ikegami May 12 '20 at 05:46
  • 2
    I believe the SQL should be not `and t1.c1 in (11111, 22222, 33333)` but `and t1.c1 in ('11111', '22222', '33333')` -- please note that quote can be different type, consult your database documentation. – Polar Bear May 12 '20 at 08:15
  • 2
    I've found `SQL::Statement` is really finicky and doesn't handle a lot of things the documentation seems to suggest it should. – Shawn May 12 '20 at 08:42
  • Thanks for the comments guys. I did notice that using "value" worked but if the list of values is for integers, that doesn't help me. I'll do more digging (I'll take it under debugger) and see if I can find out more. – Jerome Provensal May 12 '20 at 17:04
  • 3
    Little Bobby Tables rang to say hi. – Ether May 12 '20 at 17:58
  • 1
    Hi @Ether, I had to look it up but yeah it's funny. My above example is not derived from some actual code but a simplified version meant to illustrate the issue. – Jerome Provensal May 12 '20 at 19:57

2 Answers2

1

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.

Sno
  • 179
  • 1
  • 7
1

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.

Jerome Provensal
  • 931
  • 11
  • 22