Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Re^4: SQL query using elements from array

by AllPaoTeam (Acolyte)
on Oct 08, 2014 at 19:32 UTC ( #1103209=note: print w/replies, xml ) Need Help??


in reply to Re^3: SQL query using elements from array
in thread SQL query using elements from array

Thanks guys, It seems to fail if I use it in a union where there are multiple select stmts. Is it because I am using too many bind variables? I am not sure why but here is the sql code that it fails on. All the other code is the same, only sql code is different, The ERROR I wrote on the bottom of the code:
my @intervals = ( ["{ts'2012-07-01 00:00:00.000'}", "{ts'2012-07-02 00:00:00.000'}","{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'}","{ts +'2012-07-02 00:00:00.000'}", "{ts'2012-07-03 00:00:00.000'}"]); my $dbh = DBI-> connect('dbi:ODBC:DSN=WCPM-DB1;UID=TEST;PWD=pass') or +die "CONNECT ERROR! :: $DBI::err $DBI::errstr $DBI::state $!\n"; my $sql2 = <<EOSQL; SELECT *, DATEDIFF(n,MIN_TIME,MAX_TIME) AS MINS_DIFF FROM (SELECT Login_ID, MIN(TimeStamp) AS MIN_TIME,MAX(TimeStamp +) AS MAX_TIME FROM (SELECT Login_ID, AuditChrt_TimeStamp AS TimeStamp, Patient_ID FROM (SELECT Login_ID, AuditChrt_TimeStamp, Patient_ID FROM TopsData.dbo.AUDT_AuditChrt WHERE Login_ID IN ('ZZZZZ0004R','ZZZZZ00050','ZZZZ +Z0006J','ZZZZZ0006P','ZZZZZ0007A') AND AuditChrt_TimeStamp > ? AND AuditChrt_TimeStamp < ?) AS A UNION ALL SELECT Login_ID, AuditMedication_TimeStamp AS TimeStamp, P +atient_ID FROM (SELECT Login_ID, AuditMedication_TimeStamp, Patient_I +D FROM TopsData.dbo.AUDT_AuditMedication WHERE Login_ID IN ('ZZZZZ0004R','ZZZZZ00050','ZZZZZ00 +06J','ZZZZZ0006P','ZZZZZ0007A') AND AuditMedication_TimeStamp > ? + AND AuditMedication_TimeStamp < ?) + AS B ) AS A GROUP BY Login_ID) AS A; EOSQL my $sth = $dbh->prepare($sql2); foreach my $interval (@intervals) { $sth->execute(@$interval); my @row; while (@row = $sth->fetchrow_array) { # retrieve one row at a tim +e print join(", ", @row), "\n"; ERROR: DBD::ODBC::st failed: Conversion failed when converting data an +d/or time from character string.

Replies are listed 'Best First'.
Re^5: SQL query using elements from array
by mje (Curate) on Oct 09, 2014 at 07:40 UTC

    You can safely use a lot more parameteres than you are using. That error string does not look complete. It should contain an ODBC status code and a set of strings in square brackets showing what is reporting the error. Can we see the full string.

      Yes, I would really appreciate it someone could help me solve this issue, below is the full error:
      DBD::ODBC::st execute faile: [Microsoft}[ODBC SQL Server Driver][SQL S +erver Conversion failed when converting data and/or time from charact +er string. (SQL-22007) at test10.pl line 40. DBD::ODBC::st fetchrow_a +rray failed: no select statement currently executing (SQL-HY000) at t +est10.pl line 42.
      It repeats that twice. Thanks again!!!

        Can you show us the schema for the table you are selecting from and the contens of $h->{ParamValues} after the execute (where $h is your statement handle). You might want to "use Data::Dumper" and "print Dumper($h->{ParamValues})". If you have RaiseError set you'll have to wrap the fetchrow_array in an eval or disable RaiseError to capture the ParamValues.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (7)
As of 2020-12-01 16:10 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    How often do you use taint mode?





    Results (12 votes). Check out past polls.

    Notices?