You might not want to use this...
$usql=~ s/\'/\\'/g;
# print $usql;
$presql=$presql."INSERT INTO syncrecords VALUES
($urow->[0],\'$usql\');\n";
DBI has a method quote() to escape needed characters. Also \\' does not do
anything since in Oracle for example, ' is escaped by two single quotes like
this 'that''s my example'.
Use quote or placeholders...
$usql = $dbhd->quote($usql);
$presql=$presql."INSERT INTO syncrecords VALUES
($urow->[0], $usql);\n";
Also you can't use ; as the end of SQL, since that character is only used by
other programs pgsql, mysql, sqlplus as to identify the query end, it's not
used by DBI.
Ilya Sterin
-----Original Message-----
From: Michael Wray
To: [EMAIL PROTECTED]
Sent: 05/31/2001 2:41 PM
Subject: Pg Connection WOES on DEADLINE!
I just want to insert some values into a table which happen to contain
the '
character as part of the value.....the data in question being a log of
SQL
statements.....
if I remove the ' character as part of the value then it will do the
insert,
other wise I get the following strange errors:
NOTICE: identifier "INSERT INTO syncrecords VALUES (108,'insert into
synctest1
values (43,\'test\',11,\'Jan 1 2001 12:00AM\')');
INSERT INTO syncrecords VALUES (109,'insert into synctest1 values
(44,\'test\',12,\'Jan 1 2001 12:00AM\')');
INSERT INTO syncrecords VALUES (110,'insert into synctest1 values
(45,\'test\',15,\'Jan 1 2001 12:00AM\')');
INSERT INTO syncrecords VALUES (111,'insert into synctest2 values
(12,15,\'Feb
2 2001 12:00AM\')');
INSERT INTO syncrecords VALUES (112,'insert into synctest2 values
(13,18,\'Feb
2 2001 12:00AM\')');
INSERT INTO syncrecords VALUES (113,'insert into synctest2 values
(14,18,null)');
" will be truncated to "INSERT INTO syncrecords VALUES "
DBD::Pg::st execute failed: ERROR: parser: parse error at or near """
at
/root/bin/dbitesting.pl line 129.
DBD::Pg::st execute failed: ERROR: parser: parse error at or near """
at
/root/bin/dbitesting.pl line 129.
Database handle destroyed without explicit disconnect.
Database handle destroyed without explicit disconnect.
"INSERT INTO syncrecords VALUES (108,'insert into synctest1 values
(43,\'test\',11,\'Jan 1 2001 12:00AM\')');
INSERT INTO syncrecords VALUES (109,'insert into synctest1 values
(44,\'test\',12,\'Jan 1 2001 12:00AM\')');
INSERT INTO syncrecords VALUES (110,'insert into synctest1 values
(45,\'test\',15,\'Jan 1 2001 12:00AM\')');
INSERT INTO syncrecords VALUES (111,'insert into synctest2 values
(12,15,\'Feb
2 2001 12:00AM\')');
INSERT INTO syncrecords VALUES (112,'insert into synctest2 values
(13,18,\'Feb
2 2001 12:00AM\')');
INSERT INTO syncrecords VALUES (113,'insert into synctest2 values
(14,18,null)');
"
Here is the code in question:
for $drow ( @ddbai) { #loop through Source Databai
###get Destination connection info
($dproxy,$dpport,$ddsn,$ddbname,$duid,$dpass)=
split("~",$drow->[0],6); #
Get Connect String for source
##CONNECT to Destination
#$dbhd= DBI->connect("$dproxy;$dpport;$ddsn:$ddbname",$duid,$dpass,
$dbhd=
DBI->connect("dbi:Pg:dbname=PLAY;host=localhost;port=5432;","postgres","
",
{
RaiseError =>1,
AutoCommit => 0
}
) || die "Destination Database Connection Failed:
$DBI::errstr";
#### Parse UPDATES and pass to SYNC Master DB and apply to
Destination
for my $urow (@updates) {
my $usql=$urow->[1];
$usql=~ s/\'/\\'/g;
# print $usql;
$presql=$presql."INSERT INTO syncrecords VALUES
($urow->[0],\'$usql\');\n";
$sqld=$sqld."$urow->[1];\n";
} # end of Update For
#$sql=qq("$presql");
print "$sql\n";
$sql=qq{"$presql"};
$sth=$dbh->prepare($sql);
$sth->execute();
$dbh->commit;
$sqld2=qq("$sqld");
$sthd=$dbhd->prepare($sqld2);
$sthd->execute;
$dbhd->commit;
$sthd->finish();
$dbhd->disconnect();
} # end of Inner For Loop
$sths->finish();
$dbhs->disconnect();
}
$dbhs->disconnect();
$sth->finish();
$dbh->disconnect();
--
Michael Wray
Network Administrator
FamilyConnect, Inc.
