in reply to Perl DBI '?' substitution with prepare/execute

As a rule of thumb, you can only use SQL placeholders when the database can still come up with a query plan. Using a different table prevenets your database from coming up with a sensible query plan.

A more concise way to say this is that the placeholders can only stand in place for values, not for arbitrary syntax constructs.

You will have to construct the SQL by creating a string and replacing the variable parts in the string yourself.

There are some query builders, like SQL::Abstract, or DBIx::PreQL. In my experience, I found query builders to be too tedious, as I usually have to fight them too hard to get the SQL I want them to generate. I haven't used DBIx::PreQL, but it should allow you a more parametrized approach.

  • Comment on Re: Perl DBI '?' substitution with prepare/execute

Replies are listed 'Best First'.
Re^2: Perl DBI '?' substitution with prepare/execute (abstract SQL)
by LanX (Saint) on Apr 18, 2016 at 16:06 UTC
    > There are some query builders, like SQL::Abstract, or DBIx::PreQL.

    interesting any others like this and DBIx::Abstract ?

    What I especially like about SQL::Abstract is that the problem of variable numbers of placeholders is addressed, it's no fun to generate strings like (?,?,?,?) for IN statements.

    > In my experience, I found query builders to be too tedious, as I usually have to fight them too hard to get the SQL I want them to generate.

    Maybe this could be addressed with an inner DSL in Perl ( DBIx::PreQL seems to be an outer DSL) .

    Let's talk about it at our next meeting. :)

    Cheers Rolf
    (addicted to the Perl Programming Language and ☆☆☆☆ :)
    Je suis Charlie!

[reply]
[d/l]
[select]

      What I especially like about SQL::Abstract is that the problem of variable numbers of placeholders is addressed, it's no fun to generate strings like (?,?,?,?) for IN statements.

      Hmm, interesting. I'll have to take a closer look at that module. I had previously been building strings like this: 

      my $insertstring = '['.join('],[', @sqlcolumns).']';
my $valuestring  = join(',', map {'?'} @sqlcolumns);
my $query        = "INSERT INTO table ($insertstring) VALUES ($valuest
+ring)";

      [download]
      Which is, as you say, tedious.

[reply]
[d/l]
[select]
        yeah but IMHO the way operators like IN are coded into hashes doesn't improve readability..

        hopefully I'll meet ribasushi soon again to discuss further, SQL is not my core domain...

        Cheers Rolf
        (addicted to the Perl Programming Language and ☆☆☆☆ :)
        Je suis Charlie!

[reply]
[d/l]

In Section Seekers of Perl Wisdom