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]

In reply to Interpolating DBI/SQL placeholders by abclex

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.