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

I have many MS Sql queries in a hash that I am looping on. What I think I need to do is to be able to use quote like operators in the hash value. Here is a sample hash, 
'Calls Received'=>"SELECT COUNT(DISTINCT sessionID ) FROM AgentConnect
+ionDetail WHERE startDateTime BETWEEN '$yesterday' AND '$today'"

[download]

The $yesterday and $today variables are filled in correctly with a datetime format.
When I loop on the hash, and execute the value as a sql query, the return I get is either a 1 or a 0. Even when I had the sql queries in an array, the return was almost always a 1 or a 0 and not the correct sql query response. I do know that the actual query is correct. I tried doing, 
'Calls Received'=>qq{"SELECT COUNT(DISTINCT sessionID ) FROM AgentConn
+ectionDetail WHERE startDateTime BETWEEN '$yesterday' AND '$today'"}

[download]

but that didn't seem to work either. I got the error, 
DBD::ODBC::db prepare failed: [FreeTDS][SQL Server]Statement(s) could 
+not be prepared. (SQL-42000)
[FreeTDS][SQL Server]The identifier that starts with 'SELECT COUNT(DIS
+TINCT sessionID ) FROM AgentConnectionDetail WHERE startDateTime BETW
+EEN '2009-01-04 15:41:51.000' AND '2009-01-' is too long. Maximum len
+gth is 128. (SQL-42000) at ./voip.pl line 75.
Can't call method "execute" on an undefined value at ./voip.pl line 76
+.

[download]
Any ideas?

Replies are listed 'Best First'.
Re: Quote-like operators in a hash
by kyle (Abbot) on Jan 05, 2009 at 16:31 UTC

    Can you show us the code you use to loop over this data structure? Have you tried printing out the SQL before you execute it?

    When you do this: 

    qq{"SELECT ..."}

    [download]

    ...you wind up with a string that begins and ends with double quotes. You probably mean this: 

    qq{SELECT ...}

    [download]
[reply]
[d/l]
[select]
      I used, 
      while(my($key,$value)=each(%sqlASDOverall)) {
    print sqlExecute($value,$key)
}

      [download]

      to loop over the hash. I printed out the SQL query, and it looks to be formatted correctly. This is the SQLExecute subroutine I use to run sql queries. 
      sub sqlExecute {
    my @results;
    my $sth = $dbh->prepare($_[0]);
    $sth->execute() || die print "Could not execute SQL query :\n$_[0]
+\n",$sth->erstr(),"\n";
    while (my $result = $sth->fetchrow_array()) {
        push @results, $result;
    }
    return @results;
}

      [download]
[reply]
[d/l]
[select]
        Try simplifying the statements the hash is passing by doing something super simple like, "SELECT NOW()" and see if that works. I would also add a debugging line just about the prepare call and print out the statement (just to make sure everything looks ok). It looks like that is where your problem is, for prepare to die with that error is weird. Although, I have never used the ODBC driver with DBI but I've never seen that error with the MySQL driver.
[reply]
        ...I might be missing something, but it rather looks like prepare might be expecting a quoted string - have you tried my $sth = $dbh->prepare("$_[0]");, or similar ?

        Just a thought ...

        A user level that continues to overstate my experience :-))
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom