in reply to Perl/SQL syntax error

Never, never, never paste values into SQL statements. Use placeholders. All DBI drivers support placeholders, even if the underlying database does not. Switch to placeholders and your problem is gone.

Alexander

--
Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)

Replies are listed 'Best First'.
Re^2: Perl/SQL syntax error
by patt (Scribe) on Jul 04, 2009 at 11:21 UTC
    Thanks for imparting your wisdom. This may seem lika a dumb question, but, can you enlighten me on what a placeholder precisely is. I've searched the MySQL docs and returned a blank. A web search is ambiguous, and more confusing. I did not print $insertData as suggested. I did not see the need to do so as all my data was correctly recovered by my regex's. I've just printed it now, and it's value is as given on the command line error report: 
    (192.168.1.1, 23/Mar/2008, 
                    04:02:02, 77.91.224.11)

    [download]
    This is just one line of data from a logfile of thousands of lines. I've used a selection of lines of the logfile as test data. I'd blow my command line up if I was to run the code over the entire logfile...and probably have to wait until next week for a result.
[reply]
[d/l]
      Your example using placeholders (or bind variables - they are the question marks): 
      my $sth = $dbh->prepare(<<sqlend

   insert into url_connect (sourceIP, dt, tm, remoteIP)
                    values (?, ?, ?, ?)
sqlend

# and then:
$sth->execute($sourceIP, $date, $time, $targetIP);

# and again:
$sth->execute($sourceIP2, $date2, $time2, $targetIP2);

      [download]
      The idea is to prepare the query once and possibly execute it many times over, just supplying new values for the bind-variables.

      The overhead of preparing the query on the database-server(parsing the sql, checking persmission, generating data-access paths etc) then just occurs once (and this is essential for scalability on systems like Oracle or DB2).

[reply]
[d/l]
        Thanks Morgon. I have done that, but now have a new error msg - I'll sort that one out myself. Data is now being inserted into my table, but there seems to be a formatting problem in the date column. Back to the drawing board. Thanks for all the invaluable advice. I have learned something new today, and I am very happy with that.
[reply]
        Thanks Morgon. I have done that, but now have a new error message - I'll sort that out myself. Data is being inserted into the table, but there is a formatting problem in the date column. Back to the drawing board. Thanks for the invaluable advice. I have learned something new today, and am very happy with that...

        Content restored by GrandFather

[reply]

In Section Seekers of Perl Wisdom