Interpolating DBI/SQL placeholders

[abclex]

Dear monks, I just wanted to ask if someone has a quick'n'fast solution(tm) or hint for my problem:

I generated some SQL string including the related bind values on the fly using SQL::Abstract to query Sybase. Everything works fine for normal queries, but now I have to execute a batch SQL string (I need to select data into a temporary table and then do another select out of there) and it seems that combining placeholders with multiple select statements/batch queries is not supported.

Does anyone have an idea how to manually "fill out" the placeholders of the SQL string with the values from the bind array? Or maybe there already exists a module for this...

Many thanks in advance!

Update: Added some example code:

use DBI;
use SQL::Abstract;

my $sql = SQL::Abstract->new();

# SQL::Abstract uses this to build our where clause
my @where = (
    [
        { 'tbl.name' => {'LIKE', "%foo%"} },
        { 'tbl.name' => {'LIKE', "%bar%"} }
    ],
    {
        folder => "foobar"
    }
);

# SQL::Abstract returns the where clause and the related values to bin
+d
my ($sql_where, @bind_values) = $sql->where(\@where);

# because sybase does not support selecting only a range of results (f
+.e. LIMIT)
# we have to select first into a temporary table, assign a counter var
# and then and fetch our range from there. finally we delete the temp 
+table
# see: http://www.isug.com/Sybase_FAQ/ASE/section6.2.html#6.2.12

my $cnt_offset = 10;
my $cnt_num_results = 20;
my $cnt_max = $cnt_offset+$cnt_num_results;

$sql_string =
    "SELECT TOP $cnt_max pseudo_key = identity(3),id INTO #temp FROM t
+bl WHERE $sql_where ORDER BY id ASC;" .
    "SELECT id,name FROM tbl JOIN #temp ON tbl.id = #temp.id WHERE pse
+udo_key BETWEEN $cnt_offset AND $cnt_max;" .
    "DELETE #temp;";

# assume we have a valid DBI connection to sybase
# $dbh = DBI->connect();

# = ERROR =
# DBD::ASAny::db selectall_arrayref failed:
# Host variables may not be used within a batch (DBD: open cursor fail
+ed)
#my $data = $dbh->selectall_arrayref($sql_string, { Slice => {} }, @bi
+nd_values);

# d'oh!

[blokhead]

I don't think I fully understand the placeholder problem you're seeing. It may be Sybase-specific, and if you post the placeholder usage that didn't do what you wanted, you may get some better help.

But if it turns out you really do need to "fill out" the placeholder values by hand, here's how I've done it in the past:

my $sql   = "insert into foo values (?,?,?)";
my @binds = qw/alpha beta gamma/;
{  
    my $i = 0;
    $sql  =~ s/\?/ $dbh->quote( $binds[$i++] ) /ge;
}
# "insert into foo values ('alpha','beta','gamma')"

Someone could probably golf this shorter, but you must be careful not to replace question marks that appear in the bind values. For instance, I've seen code on CPAN that would go wrong on this:

my $sql   = "insert into foo values (?, ?)";
my @binds = ("Huh?", "What?");

# correct:  "insert into foo values ('Huh?', 'What?')"
# mysqlPP:  "insert into foo values ('Huh'What?'', ?)"

Update: Got rid of \G business.

[abclex]

Great! Your code does exactly what I want. :)

Many thanks,
abclex

ps: Nevertheless I think I'll investigate some more time to check out the problem with my code...

[mpeppler]

You're using ASAnywhere rather than Enterprise, with which I'm more familiar. However, I suspect that ASAny has the same limitation as ASEnt in that placeholders can only be used for single-statement batches.

In DBD::Sybase you could work around this by creating a stored procedure that runs your query, and then calling the proc with placeholders (and the appropriate parameters). I don't know if DBD::ASAny supports this.

Otherwise, your current code should work correctly, albeit with direct interpolation which may have certain issues (security, in particular, in case of quoting errors).

Michael

[abclex]

Hi Michael,

thanks for you comprehensive answer. I'll check out if I can use stored procedures or maybe DBD::Sybase in this particular case.

Alex