Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

SQL query using elements from array

by AllPaoTeam (Acolyte)
on Oct 03, 2014 at 17:11 UTC ( #1102757=perlquestion: print w/replies, xml ) Need Help??

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

Hello Monks, I was wondering if someone could help me with trying to pass array elements into multiple sql statements. Any help will be greatly appreciated. It is complaining about my variable $date1 in the query. Below is the code:

#!/usr/bin/perl use DBI; use strict; my @date1 = ("'2013-08-01 00:00:00.000'","'2013-08-02 00:00:00.000'" +); my @date2 = ("'2013-08-02 00:00:00.000'","'2013-08-03 00:00:00.000'" +); for (my $i = 0; $i < @date1; $i++) { my $dbh = DBI-> connect('dbi:ODBC:DSN=TEST-DB1;UID=SA;PWD=pass') o +r die "CONNECT ERROR! :: $DBI::err $DBI::errstr $DBI::state $!\n" my $sql2 =q/SELECT Login_ID, AuditChrt_TimeStamp, Patient_ID FROM TopsData.dbo.AUDT_AuditChrt WHERE master.dbo.fn_sqlvarbasetostr(AuditChrt_TimeStamp) >= $d +ate1[$i] AND master.dbo.fn_sqlvarbasetostr(AuditChrt_TimeStamp) <= +$date2[$i]/; my $sth = $dbh->prepare($sql2); $sth->execute(); my @row; while (@row = $sth->fetchrow_array) { # retrieve one row at a t +ime print join(", ", @row), "\n"; } $dbh->disconnect; }

Error code: DBD::ODBC::st execution failed: Microsoft ODBC SQL Server Driver SQL SERVER [Ivalid pseudocolumn "$date1". (SQL-42000)

Replies are listed 'Best First'.
Re: SQL query using elements from array
by kennethk (Abbot) on Oct 03, 2014 at 19:39 UTC
    Rather than interpolating values directly into your SQL, you should probably get into the habit of using placeholders. In addition to improving security, it handles escaping for you. It also lets you only prepare your statement once, and use it multiple times. By moving that and your connect statement outside the loop, you'll likely find my rewrite runs substantially faster.
    #!/usr/bin/perl use DBI; use strict; my @date1 = ('2013-08-01 00:00:00.000','2013-08-02 00:00:00.000'); my @date2 = ('2013-08-02 00:00:00.000','2013-08-03 00:00:00.000'); my $dbh = DBI-> connect('dbi:ODBC:DSN=TEST-DB1;UID=SA;PWD=pass') or di +e "CONNECT ERROR! :: $DBI::err $DBI::errstr $DBI::state $!\n" my $sql2 = <<EOSQL; SELECT Login_ID, AuditChrt_TimeStamp, Patient_ID FROM TopsData.dbo.AUDT_AuditChrt WHERE master.dbo.fn_sqlvarbasetostr(AuditChrt_TimeStamp) >= ? AND master.dbo.fn_sqlvarbasetostr(AuditChrt_TimeStamp) <= ? EOSQL my $sth = $dbh->prepare($sql2); foreach my $i (0 .. @date1 - 1) { $sth->execute($date1[$i], $date2[$i]); my @row; while (@row = $sth->fetchrow_array) { # retrieve one row at a tim +e print join(", ", @row), "\n"; } } END { $dbh->disconnect if $dbh; }
    Other changes I made:
    • I used a heredoc (Quote and Quote like Operators in perlop) to separate the SQL from the Perl code; IMHO, it makes things more legible

    • I swapped to a foreach loop, since that structure is less bug prone than C-style loops -- fewer moving parts.

    • I moved the disconnect to an END block (with conditional) so that no matter how your code exits, you won't get that pesky Issuing rollback warning.

    Update: Corrected copy&paste bug, as per McA's comment below. I concur that an AoA is a better data structure here, but wanted to not get too far too fast...

    #11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.

      Hi

      you've a little Copy&Paste bug in your code. After pointing out that placeholder variables are the better solution (++) you missed changing the timestamp variables to strings without single quotation mark which is only necessary when you do string interpolation while generating valid SQL statements:

      my @date1 = ('2013-08-01 00:00:00.000','2013-08-02 00:00:00.000');

      I would go one step further for clarification of the intention of the code. Why not doing the following to emphasis on the fact that the author wants to interate over several time intervals:

      my @intervals = ( ['2013-08-01 00:00:00.000', '2013-08-02 00:00:00.000'], ['2013-08-02 00:00:00.000', '2013-08-03 00:00:00.000'], ); ... foreach my $interval (@intervals) { $sth->execute(@$interval); ... }

      And as soon as you look at the intervals and at the SQL statement you see that AllPaoTeam made a litte mistake concerning interval boundaries IMHO. Make one end of the interval inclusive the other end exclusive:

      WHERE master.dbo.fn_sqlvarbasetostr(AuditChrt_TimeStamp) >= ? AND master.dbo.fn_sqlvarbasetostr(AuditChrt_TimeStamp) < ?

      otherwise you would count entries on 2013-08-02 00:00:00.000 twice (except for being intended).

      Regards
      McA

        True, I did make that error, nice catch! Thanks to everyone with help me with this, Kudos to all!!
Re: SQL query using elements from array
by mr_mischief (Monsignor) on Oct 03, 2014 at 19:46 UTC

    Read this, then look at your code.

    push @date1, "'; drop table TopsData.dbo.AUDT_AuditChrt; --"

    Now, take placeholders seriously.

Re: SQL query using elements from array
by toolic (Bishop) on Oct 03, 2014 at 17:35 UTC
    Single quotes (q) prevent variable interpolation. $sql2 contains the literal string $date1[$i], not its value ('2013-08-01 00:00:00.000'). Tip #2 from the Basic debugging checklist (print). Try qq
    my $sql2 = qq/SELECT Login_ID, AuditChrt_TimeStamp, Patient_ID FROM TopsData.dbo.AUDT_AuditChrt WHERE master.dbo.fn_sqlvarbasetostr(AuditChrt_TimeStamp) >= $d +ate1[$i] AND master.dbo.fn_sqlvarbasetostr(AuditChrt_TimeStamp) <= +$date2[$i]/;
Re: SQL query using elements from array
by vinoth.ree (Monsignor) on Oct 03, 2014 at 17:22 UTC

    Hi,

    Please post your complaint about the $date1 array so that we can help you better.


    All is well
Re: SQL query using elements from array
by mje (Curate) on Oct 06, 2014 at 13:55 UTC

    As an aside to the problem you are posting about (which is answered) why are you converting (with fn_sqlvarbasetostr) what looks like a timestamp to a string to compare with a string timestamp? Why not forget fn_sqlvarbasetostr and simply make your timestamps like "{ts '1998-05-02 01:23:56.123'}". You can omit the milliseconds.

      How would I incorporate into the Perl code? Using code from McA, it tried the code below, but errors. I also tried using double quotes around it between the brackets. Below is the code and the error:
      my @intervals = ([{ts'2012-07-01 00:00:00.000'},{ts'2012-07-02 00:00:0 +0.000'}], [{ts'2012-07-02 00:00:00.000'},{ts'2012-07-03 00:0 +0:00.000'}]); Error:"Bad Name after ts' at line 17" which is the line above.
        try
        my @intervals = ( ["{ts'2012-07-01 00:00:00.000'}", "{ts'2012-07-02 00:00:00.000'}"], ["{ts'2012-07-02 00:00:00.000'}", "{ts'2012-07-03 00:00:00.000'}"]);
        poj

        poj answered your problem above but I thought about this a bit more afterwards and realised there is a warning I should have made. The {ts 'xx'} is really an ODBC SQL escape sequence so it is really intended for your SQL. However, many drivers parse this in parameters (which you are using) as well.

      I am assuming this is in perl and not using the function within SQL Server? I can try it and see if it works. Thanks for the suggestion!

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1102757]
Approved by vinoth.ree
Front-paged by toolic
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (9)
As of 2020-12-01 15:44 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    How often do you use taint mode?





    Results (11 votes). Check out past polls.

    Notices?