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: - #!/usr/bin/perl
-
-
require "db.pl";
-
-
$dbh-> {'LongTruncOk'} = 1;
-
$dbh-> {'LongReadLen'} = 4000;
-
-
-
$hisSQL = qq{
-
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
-
FROM testdata_log
-
WHERE trunc(created_date) = trunc(sysdate -7)
-
};
-
-
$sth = $dbh->prepare($hisSQL);
-
$sth->execute() or die $DBI::errstr;
-
-
$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);
-
-
while( $sth->fetch() ) {
-
$insertinmysql = qq{
-
INSERT INTO testdata_log
-
values
-
('$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')
-
};
-
$sth2 = $dbh2->prepare("$insertinmysql");
-
$sth2->execute() or die $DBI::errstr;
-
$sth2->finish();
-
# print "Record $id Inserted \n";
-
}
-
print "****** Script Ended ****** \n";
3 3199
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
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.
Edit:
After looking at your code closer. I have reworked it. Give this a try and see if you still have problems.
Try this. - $insertinmysql = qq{
-
INSERT INTO testdata_log (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)
-
VLAUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) };
Then when you execute the statement use this. - $sth2->execute($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) or die $DBI::errstr;
I wrote it the way I did for readability.
--Kevin
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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,...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
|
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...
| |