Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

Interpolating DBI/SQL placeholders

by abclex (Monk)
on May 11, 2004 at 03:49 UTC ( #352302=perlquestion: print w/replies, xml ) Need Help??

abclex has asked for the wisdom of the Perl Monks concerning the following question:

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!

Replies are listed 'Best First'.
Re: Interpolating DBI/SQL placeholders
by blokhead (Monsignor) on May 11, 2004 at 04:45 UTC
    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.

    blokhead

      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...

Re: Interpolating DBI/SQL placeholders
by mpeppler (Vicar) on May 11, 2004 at 11:20 UTC
    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

      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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://352302]
Approved by sgifford
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (6)
As of 2021-01-25 14:38 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?