Add record in access for a file

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

I have the following code and it fails on Sql and also what is q! suggested previously stand for.

my($tdater, $filen, $ttot, $ttot2, $logfilen, $tots, $diff) = split(/\
+|/);
        chomp ($tdater);
        chomp ($filen);
        chomp ($ttot);
        chomp ($ttot2);
        chomp ($logfilen);
        chomp ($tots);
        chomp ($diff);
        my $theyear = 2000 + substr $tdater,6,2;
        my $themo = substr $tdater, 0, 2;
        my $thedt = substr $tdater, 3, 2;
        my $thedate = $theyear . "/" . $themo . "/" . $thedt;
        my $quer1="INSERT into logfile 
    ('Date Send', 'Certificate file', 'No of Certificate', PDFNO, 'log
+ file', 'Notice sent', Difference)
        VALUES ({d '$thedate'}, '$filen', '$ttot', '$ttot2', '$logfile
+n', '$tots', '$diff')";
          my $query = q! 
          INSERT INTO logfile ("Date Send", "Certificate file")
          VALUES ({d '$thedate'}, $filen)
            !;
            
         if ( $db->Sql($query))
             {
                print "error executing query [ \"$query\" ]\n";
                print "Error: " . Win32::ODBC::Error() . "\n";

        }
    
}
[download]

Comment on Add record in access for a file Download Code

Replies are listed 'Best First'.
Re: Add record in access for a file by dreadpiratepeter (Priest) on Feb 12, 2002 at 17:31 UTC
#1: spaces in column names = bad mojo. You will get bit all over the place. Most DBs will not allow it, and I'm pretty sure it isn't ANSI-SQL. #2: the column name block in the insert statement shouldn't have quotes around the column names. (Of course, here's an example of where spaces in column names gets wierd). #3: you should be using placeholders, see DBI. #4: I'm not sure where the $db->Sql call is coming from. What package are you using for db access? It doesn't look like DBI. -pete Entropy is not what is used to be.	[reply]
Re: Add record in access for a file by dash2 (Hermit) on Feb 12, 2002 at 17:39 UTC
I --ed you, partly because I am feeling grumpy. But also: "It falls on Sql" is not very helpful. What failure message does it give? What is the $db object? You show it in the code, but we can't tell what it is. The first stop for documentation is the perl manpages. For `q!`, checkout `man perlop`. (I know the answer; but you'll learn a lot more from reading the man page.) Oh well, at least you put in <code> tags, maybe I was a bit harsh. Here is a bonus hint. `q!` (or `q:`, or `q"`, or `q£` AFAIK...) does single quotes. You probably want your variables to be substituted, so you need `qq!` (or `qq:` or...) Now get along and read some manpages. dave hj~	[reply] [d/l] [select]