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

Hi all, Using localtime fn to get the Hour:min(time) & i want to update this in database, using the update scripts.. But it shows some error with the scripts...Can some pls help me.. Pls find the code below

#!/usr/bin/perl -w

use Time::Local;
use DBI;
my ($sec,$min,$hour,$day,$month,$yr19,@rest) =   localtime(time);     
+        
my $tim = "$hour:$min";
my $dbh = DBI->connect($data_source)
    or die "Can't connect to $data_source: $DBI::errstr";
my $sql = "DECLARE @log varchar(5) SET @log=$tim Update dbo.tltime set
+ logtime = @log";
my $sth = $dbh->prepare($sql)
    or die "Can't prepare statement: $DBI::errstr";
$sth->execute();
while ( my @row = $sth->fetchrow_array ) 
{
    foreach $row(@row)
       {
          print "$row";
}
}




I find error in the above query step....

[download]

I receive following errors : i) Incorrect syntax near '('. That is in Querying step.. ii)Incorrect syntax near '='.. this is also in Query step.. ii) Could not be prepared... Is this query syntactically, right? Can we execute such scripts in perl... some one plss help me...

  • Comment on SQL Query error while executing in perl. Is it possible to execute these Scripts??
  • Download Code

Replies are listed 'Best First'.
Re: SQL Query error while executing in perl. Is it possible to execute these Scripts??
by Corion (Patriarch) on Dec 02, 2010 at 16:52 UTC

    You use warnings, but you choose to ignore what Perl tells you. Once you actually look at the warnings, you will find that your SQL string does not look like what you might think it should. Printing $sql before passing it to DBI should also tell you that.

[reply]
[d/l]
Re: SQL Query error while executing in perl. Is it possible to execute these Scripts??
by NetWallah (Canon) on Dec 02, 2010 at 17:26 UTC
    Expanding on what Corion said : "@" sigils signify arrays, and double quoted (") strings are interpolated.

    One way to resolve the issue would be to use single quotes (') when setting $sql. Another way is to escape (\) the "@"s.

         Syntactic sugar causes cancer of the semicolon.        --Alan Perlis

[reply]

      Thanks for ur reply.. I'm not clear with what Corion said.. But as u said, the ineterpolated strings could be the problem... May be i'll try to execute with Single quotes.. But could u please tell, whether i can use the Perl variable in the SQL Scripts, as to extract the values from DB.. Like i used in above SQL Query...

[reply]
        YES - indeed - that is what makes using perl with Databases so powerful.

        Your line needs to change to: 

        my $sql = "DECLARE \@log varchar(5) SET \@log=$tim Update dbo.tltime s
+et logtime = \@log";

        [download]
        Notice that the "@" signs need to be escaped - because "@log" is intended for the SQL server, not perl. perl needs to be told to ignore the @, which you do by expressing it as "\@".

        On the other hand, you wan to tell SQL the value contained in the perl "$tim" variable, so you require perl's interpolation for THAT variable, so , use double-quotes.

             Syntactic sugar causes cancer of the semicolon.        --Alan Perlis

[reply]
[d/l]

Back to Seekers of Perl Wisdom