Here is something for a very similar case:
I am reading in lines from a file, and writing them into a database.
Before I do, I do these two lines:
$_ =~ s/\'/\''/g; #Replace any single quotes with a pair of single
quotes.
$_ =~ s/\"/\'/g; #Replace any double quotes with single quotes
(SQL likes them better that way).
In practice (full working script, no substitutions or snips):
#!/usr/bin/perl
use DBI;
$data_source = "dbi:ODBC:rtsales";
$driver_name = "ODBC";
@driver_names = DBI->available_drivers;
@data_sources = DBI->data_sources($driver_name, \%attr);
$dbh = DBI->connect($data_source, $username, $auth, \%attr);
#The following lines decide what to use
#as a log file. If one was supplied as an
#argument at the command line, we'll use
#it. If not, default to "db_log.log"
$log = "db_log.log";
if (@ARGV[0] ne ""){
$log = @ARGV[0];
}
open (LOG, ">>$log");
print LOG "Database write script launched.\n";
@inFile = ("rtstr", "rtslsd", "rtsal", "rtsales", "rtweek", "rtwhist",
"rtrates");
foreach $inFile (@inFile){
open (IN, "<$inFile" . ".csv") || die "Could not open $inFile.\n";
$count = 0;
$errors = 0;
print LOG "\nBeginning insert into table $inFile... \n";
while (<IN>){
$_ =~ s/\'/\''/g;
$_ =~ s/\"/\'/g;
#Note: The following 'insert' sytax works ONLY if you are trying
to
#insert the same number of fields that exists in the table.
$statement = "insert into $inFile values ($_)";
$result = $dbh->do($statement);
if ($result != 1){
print LOG "Result of $statement is: $result.\n";
$errors++;
}
$count++;
break;
}
print LOG "Completed insert of $count records into table $inFile,
$errors errors.\n";
close IN;
}
#This statement populates two fields of the table RTSAL with the values
of two fields in RTSALES. In the application I replaced, a fat client
#(Rumba software) downloaded the RTSAL data file with the two fields
using a 'JOIN' from the DB2 tables RTSAL and RTSALES. Since
#I use FTP to get this data, I don't have that option.
$statement = "UPDATE RTSAL INNER JOIN RTSALES ON (RTSAL.STORE =
RTSALES.STORE) AND (RTSAL.FISMM = RTSALES.FISMM) AND (RTSAL.FISYY =
RTSALES.FISYY) SET RTSAL.SALESTY = [rtsales].[salesty], RTSAL.SALESBUDTY
= [rtsales].[salesbudty]";
$result = $dbh->do($statement);
print LOG "Result of $statement is: $result.\n";
$dbh->disconnect;
#The rest of this stuff is part of the rest of the application, which
is a series of scripts which each do their
#part, then call the next part. The first script of the lot opens LOG
with a single '>', the rest with a '>>', so that
#each time the application is started, I create a new log file, but
each segment of the app appends to the same.
print LOG "Writing rttime.html now.\n";
$result = `rttime.pl`;
print LOG "Launching Epix & AS/400 FTPs now.\n";
$result = `epix_ftp.pl $log`;
#end of code
Hope this is helpful to someone. I'm looking forward to all the "here's a
better way to do such-and-such" e-mails I know I'll get. :o) I really
appreciate those things.
Shawn
felix_geerinckx@h
otmail.com To: [EMAIL PROTECTED]
cc:
10/18/2002 02:24 bcc:
PM Subject: Re: Single Quote marks in a
string
on do, 17 okt 2002 08:01:07 GMT, Andrew Hubbard wrote:
> I'm having problems with a text file I'm working on. I need to
> get
> the descriptions in from the text file and into a
> string for use in an SQL statement however some of the descriptions
> use a single quote mark in the description.
> This of course stops the sql statement from working.
>
> How can I get around this? I don't want to remove the quote.
Use placeholders, like so:
my $sth = $dbh->prepare("INSERT INTO atable VALUES(?,?)");
my $value1 = "Looks like 'single' quotes";
my $value2 = "No single quotes here";
$sth->execute($value1, $value2);
my $rset = $dbh->selectall_arrayref( qq{
SELECT
atable.afield,
atable.anotherfield
FROM atable
WHERE
atable.anotherfield = ?
}, undef, $avalue);
--
felix
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
**********************************************************************
This e-mail and any files transmitted with it may contain
confidential information and is intended solely for use by
the individual to whom it is addressed. If you received
this e-mail in error, please notify the sender, do not
disclose its contents to others and delete it from your
system.
**********************************************************************
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
