Perl and SQL -

[celfyn]

Hello,
I am having a few problems while inserting into a MS-SQL database through Perl. The script works, and inserts the data into the database, but doesn't insert all the data. Say I have the following information to pass into the database:

user_name name Surname
12345 Max Mad
12346 Peter Roberts

All the information from the first record (Max Mad) will be inserted successfully. But the second record will not. The user_name 12346 will be inserted fully, but the name and surname will not. They will appear in the DB as Pet Rob. So the information that will appear in the DB is:

user_name name Surname
12345 Max Mad
12346 Pet Rob

It appears to only take the length of the first records column - 3 in this instance. So if I would to rename Max to Maxwell, and re-run the script, Perter's name will appear fully in the DB.

I have checked the length of my DB columns, and they appear to be ok.

Here is the Perl code (I have only included the relevant parts):

my $file_name = 'file.xml';
my $open_file = XML::XPath->new(filename => $file_name);

my $dbh = DBI->connect("DBI:ADO:Provider=SQLOLEDB.1;Persist Security Info=False;User ID=db_id;Password=db_pwd;Initial Catalog=db_name;Data Source=db_source;") ;
$dbh->{PrintError} = 1;
$dbh->{AutoCommit} = 0;

my $query = $dbh->prepare(qq{INSERT INTO tbl_name (user_login, first_name, surname) VALUES (?, ?, ?)})

foreach my $record ($open_file->find('/report/workgroup/user')->get_nodelist)
{
	$course_completed	= $record->find('data/@course_completed')->string_value();

	if($course_completed =~ /yes/)
	{
		$user_login			= $record->find('@user_login')->string_value();
		$user_first_name	= $record->find('@user_firstname')->string_value();
		$user_last_name		= $record->find('@user_lastname')->string_value();
		$course_title		= $record->find('data/@co_name')->string_value();

		print ' (' . $user_login . ') ';
		print ' (' . $user_first_name . ') ';
		print ' (' . $user_last_name . ') ';
		print ' (' . $course_title . ') ';
		print "\n";

		$query->execute($user_login, $user_first_name, $user_last_name);

		$cownter++;
	}
}
$dbh->commit();
$dbh->disconnect;

Any help will be grateful!!
Thank you!!

 

[celfyn]

Sorry, found what I was doing wrong...

Because I was preparing the sql outside the loop, the length of the column was determined by the lenght of the first row. I have now moved the prepare statement just above the line

$query->execute($user_login, $user_first_name, $user_last_name);

and it seems to be working.

Thanks!