By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,373 Members | 1,984 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,373 IT Pros & Developers. It's quick & easy.

Trying to insert a long XML string in mysql using perl

100+
P: 154
The code seems to be working fine for some records but I am thinking it is finding some other records with special characters and so on. I am looking for a way to insert the xml string with escaping a bunch of chars


This is the message i got
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your M
ySQL server version for the right syntax to use near 'd to test@email.com
<pre>this is a test sentence, PM000' at line 3 at test.pl line 85.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'd to test@email.com
<pre>this is a test sentence, PM000' at line 3 at test.pl line 85.

Note: The $message variable is the xml string I am having the issues with.

This is the code I am using:

Expand|Select|Wrap|Line Numbers
  1. #!/usr/bin/perl
  2.  
  3. require "db.pl";
  4.  
  5.     $dbh-> {'LongTruncOk'} = 1;
  6.     $dbh-> {'LongReadLen'} = 4000;
  7.  
  8.  
  9. $hisSQL = qq{ 
  10.                     SELECT id, customername, queue, audited_time, component_name, process_name, prepostflag, description, loglevel, subject, global_trans_id, activityname, stepid, eventid, objectid, status, event_data, created_by, created_date, message
  11.                   FROM testdata_log 
  12.                   WHERE trunc(created_date) = trunc(sysdate -7)
  13.             };
  14.  
  15.    $sth = $dbh->prepare($hisSQL);
  16.    $sth->execute() or die $DBI::errstr;
  17.  
  18.    $sth->bind_columns( undef, \$id, \$customername, \$queue, \$audited_time, \$component_name, \$process_name, \$prepostflag, \$description, \$loglevel, \$subject, \$global_trans_id, \$activityname, \$stepid, \$eventid, \$objectid, \$status, \$event_data, \$created_by, \$created_date, \$message);
  19.  
  20.    while( $sth->fetch() ) {                               
  21.   $insertinmysql = qq{
  22. INSERT INTO testdata_log
  23.     values
  24.   ('$id', '$customername', '$queue', '$audited_time', '$component_name', '$process_name', '$prepostflag', '$description', '$loglevel', '$subject', '$global_trans_id', '$activityname', '$stepid', '$eventid', '$objectid', '$status', '$event_data', '$created_by', '$created_date', '$message')
  25.   };
  26.    $sth2 = $dbh2->prepare("$insertinmysql");
  27.    $sth2->execute() or die $DBI::errstr;
  28.    $sth2->finish();
  29.   # print "Record $id Inserted \n";
  30.               }
  31.    print "****** Script Ended ****** \n";              
Nov 27 '08 #1
Share this Question
Share on Google+
3 Replies


numberwhun
Expert Mod 2.5K+
P: 3,503
I know this is a silly question, but have you tried the SQL statement inside of the database (manually) to ensure it returns the results as you expect?

Regards,

Jeff
Nov 29 '08 #2

100+
P: 154
Yes i did try but some rows will go through and the problem it seems to be its detecting characters in the xml string

So I am not sure if perl has a function that will escape an xml string so i would not have to create conditions for every character.

or maybe it might a function for mysql to do this.
Nov 29 '08 #3

eWish
Expert 100+
P: 971
Edit:
After looking at your code closer. I have reworked it. Give this a try and see if you still have problems.

Try this.

Expand|Select|Wrap|Line Numbers
  1. $insertinmysql = qq{
  2.                     INSERT INTO testdata_log (id, 
  3.                                               customername, 
  4.                                               queue, 
  5.                                               audited_time, 
  6.                                               component_name, 
  7.                                               process_name, 
  8.                                               prepostflag, 
  9.                                               description, 
  10.                                               loglevel, 
  11.                                               subject, 
  12.                                               global_trans_id, 
  13.                                               activityname, 
  14.                                               stepid, 
  15.                                               eventid, 
  16.                                               objectid, 
  17.                                               status, 
  18.                                               event_data, 
  19.                                               created_by, 
  20.                                               created_date, 
  21.                                               message)
  22.                     VLAUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) };

Then when you execute the statement use this.

Expand|Select|Wrap|Line Numbers
  1. $sth2->execute($id, 
  2.                $customername, 
  3.                $queue, 
  4.                $audited_time, 
  5.                $component_name, 
  6.                $process_name, 
  7.                $prepostflag, 
  8.                $description, 
  9.                $loglevel, 
  10.                $subject, 
  11.                $global_trans_id, 
  12.                $activityname, 
  13.                $stepid, 
  14.                $eventid, 
  15.                $objectid, 
  16.                $status, 
  17.                $event_data, 
  18.                $created_by, 
  19.                $created_date, 
  20.                $message) or die $DBI::errstr;
I wrote it the way I did for readability.


--Kevin
Nov 29 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.