473,326 Members | 2,114 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Trying to insert a long XML string in mysql using perl

154 100+
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
3 3199
numberwhun
3,509 Expert Mod 2GB
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
jonathan184
154 100+
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
971 Expert 512MB
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

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

Similar topics

6
by: a-ok | last post by:
Hi, My client has a product database od around 20000 items. And it is updated every few days when he gets a catalog from the supplier. It's supposed to work like this: if there already is a...
0
by: Randell D. | last post by:
Folks, I have installed MySQL v4 (client, server and development rpm's). I've tried and failed to use the recommended mysqladmin to set a root password after the installation (I have another post...
8
by: Rich Grise | last post by:
I think I've finally found a tutorial that can get me started: http://www.zib.de/Visual/people/mueller/Course/Tutorial/tutorial.html and I've been lurking for awhile as well. What happened is,...
1
by: smsabu2002 | last post by:
Hi, I am facing the build problem while installing the DBD-MySql perl module (ver 2.9008) using both GCC and CC compilers in HP-UX machine. For the Build using GCC, the compiler error is...
1
by: urbansound | last post by:
Hi, I'm having trouble tracking down a PInvoke error in the MySqlDriverCS lib, which is detected even in the author's samples converted to VS v8 .Net 2.0. The calling maze is provided, but the...
6
by: TheRealDan | last post by:
Hi all. I'm having a problem with a special characters. I have php script that reads from an xml file and writes to a mysql db. It's a script called phptunest that I found on the net, although the...
3
by: Waruna | last post by:
Is there a way to block insert into mysql(5.0) using c api of mysql db.. i.e. say there is a table with 2 columns, one contains char other int then i want to insert 500 records at once,, as i...
3
by: andrewkl | last post by:
hi, I have the following Perl code that inserts a string to an Oracle DB via a stored procedure: #!/usr/local/bin/perl ## Perl v5.8.6 built for sun4-solaris use strict; BEGIN...
3
by: koti688 | last post by:
Hi Guys, I am a beginner to perl. I need a perl package which will perform basic DB operations like select , insert,update,delete operation MySql DB. i Have a Mysql DB which resides on my local...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.