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!
[download]