That really wouldn't work : )
Here we go:-
# I'm assuming a DBI connection open with a handler called $dbh!
while (<ThisFile>) {
chomp;
# Make each line a hash
my %hash;
@hash{qw(firstname lastname title phone email age location address
address2 city state zip zip2 inst school_type grades_taught
subjectarea_1 subjectarea_2 subjectarea_3 subjectarea_4 subjectarea_5
subjectarea_6 how_heard Occupation)} = map $dbh->quote($_),
split(/\|/,$_);
# Now yr hash has keys (columns) and values (that are DBI quoted)
# Make them arrays (for ease of printing)
my (@columns,@values) = keys %hash, values %hash;
local $" = ",";
my $SQL_UP = "INSERT INTO $DB_Table (@columns) VALUES (@values)";
# $dbh->do($SQL_UP), or whatever
}
Dave
borakovej wrote:
>
> Hey David
> i tryed to pass the entire SQL statement through the
> dbh->quote like this and printed to see what i got and it is not quoting the
> input any ideas here is the code
>
> while(<ThisFile>)
> {
> chomp;
> my
> ($id,$firstname,$lastname,$title,$phone,$email,$age,$location,$address,$addr
> ess2,$city,$state,$zip,$zip2,$inst,$school_type,$grades_taught,$subjectarea_
> 1,$subjectarea_2,$subjectarea_3,$subjectarea_4,$subjectarea_5,$subjectarea_6
> ,$how_heard,$Occupation) = split(/\|/,$_);
>
> my $SQL_UP = qq`
> Insert into $DB_Table
> (firstname,lastname,title,phone,email,age,location,address,address2,city,sta
> te,zip,zip2,inst,school_type,grades_taught,subjectarea_1,subjectarea_2,subje
> ctarea_3,subjectarea_4,subjectarea_5,subjectarea_6,how_heard,Occupation)
>
> Values
> ($firstname,$lastname,$title,$phone,$email,$age,$location,$address,$address2
> ,$city,$state,$zip,$zip2,$inst,$school_type,$grades_taught,$subjectarea_1,$s
> ubjectarea_2,$subjectarea_3,$subjectarea_4,$subjectarea_5,$subjectarea_6,$ho
> w_heard,$Occupation)`;
>
> my $sth= "$SQL_UP";
> $str= $dbh->quote($sth);
> print $str;
> exit;
> ----- Original Message -----
> From: "David Wood" <[EMAIL PROTECTED]>
> To: "borakovej" <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Wednesday, July 18, 2001 7:23 AM
> Subject: Re: Think there is something DBI
>
> > DBI has a built in method, DBI->quote;
> >
> > eg:
> >
> > my $dbh = DBI->connect(...........);
> >
> > my $str = "Haven't I got lot's of badl'y used apostrophy's";
> > $str = $dbh->quote($str);
> >
> > # $str is now "Haven\'t I got lot\'s of badl\'y used apostrophy\'s"
> > # But it won't sort out intentionally bad English : )
> >
> >
> > While we're on the subject, this also saves a lot of hassle when forming
> > SQL statements.
> >
> > # Build a hash with the stuff to insert...
> >
> > my %to_insert = ( column_name => 'unquoted value'
> > more_columns => more_values... );
> >
> > my @columns = keys %to_insert; # gets the columns
> > my @values = map $dbh->quote($_), values %to_insert; # DBI->quote's
> > each value
> >
> > local $" = ","; # changes a printed array to be comma (rather than
> > space) seperated
> >
> > my $SQL ="INSERT INTO table (@columns) VALUES (@values)";
> >
> > Hope this helps,
> >
> > Dave
> >
> > borakovej wrote:
> > >
> > > OK here is an overview of my problem, I am running a script to take a
> pipe delimited flat file and pump the data into a database. Due to the users
> putting extraneous apostrophes I am getting errors that there are unclosed
> apostrophes in the fields. I am looking for a way to escape these
> apostrophes as simply as possible. Can someone help?
> > >
> > > Judd Borakove
> >
> > --
> > <!doctype html public "-//w3c//dtd html 4.0 transitional//en">
> > <html>
> > David Wood, Web Developer<br>
> > [a] Clickmusic Ltd, 99c Talbot Road, London W11 2AT<br>
> > [t] 020 7727 7500
> > <br>[w] www.clickmusic.co.uk<br>
> > <br>"There are three types of people in the world; those who can count,
> > and those who can't."
> > <br> </html>
> >
> > --
> > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > For additional commands, e-mail: [EMAIL PROTECTED]
> >
> >
--
<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
David Wood, Web Developer<br>
[a] Clickmusic Ltd, 99c Talbot Road, London W11 2AT<br>
[t] 020 7727 7500
<br>[w] www.clickmusic.co.uk<br>
<br>"There are three types of people in the world; those who can count,
and those who can't."
<br> </html>
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
