Hi ericlee,

It seems to me that you're doing the insertion within a loop to extract data from the flat file source and convert them into database via do method. If this is the case, the code you show might incomplete because we need to know how the loop goes. As for the statement itself, there should be nothing that can cause the insert to skip certain records.

Apart from that, I don't know what modules you're using in your code, I just want to let you know that the common technique is to use DBI module, and use the placeholder facility.

use strict;
use warnings;
use DBI;

# some info
my $driver = 'mysql'; # this requires DBD::mysql
my $db_name = 'mydb';
my $db_user = 'user';
my $db_pass = 'pass';
my $dsn = "DBI:$driver:$db_name";

# get the source file from command line
my $source_file = shift or die "Please supply source filename\n";

# connect to the db, we rely on the default "on"
# of AutoCommit and PrintError
my $dbh = DBI->connect($dsn, $db_user, $db_pass, {ShowErrorStatement =
+> 1});
die $DBI::errstr unless $dbh;

# prepare the statement, the number of question marks
# must match the number of column to be filled in
my $sql = 'INSERT INTO news (url, header, ...., state) values (?, ?, .
+.., ?)';
my $sth = $dbh->prepare($sql);

# enter the loop after opening the source file
open my $fh, '<', $source_file or die "Can't open $source_ile: $!\n";

my $i = 0;
while (<$fh>) {
  chomp;
  my @column_data = split /\|/;

  $sth->execute(@column_data) or
    die "Failed to insert data at line $. (data: [@column_data]): ", $
+sth->errstr;
  $i++;
}

close $fh;
[download]

By using placeholders (the question mark, "?"), we don't have to worry about quoting the values. We simply call the execute method on the prepared statement handler with a bunch of data for the columns. We check for the result each time we execute the statement, and die with error message of the last execution. As we set ShowErrorStatement attribute to true value, the actual SQL statement will be shown as well.

See the DBI manual for more information, also pay a visit to the Tutorials section on Database Programming.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.