Re: INSERTing problems
> "Dave" == Dave Newton <[EMAIL PROTECTED]> writes: Dave> Randal said: >> Placeholders are even more cool, and require less thinking. Dave> If they're supported, which they aren't under all platforms! They're supported by DBI, so it's *all* platforms. Perhaps you're thinking of the ":1"-style placeholders. I'm talking about "?" placeholders. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 <[EMAIL PROTECTED]> http://www.stonehenge.com/merlyn/> Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
RE: INSERTing problems
Liz said: > I've not done a great deal of Perl, but with VB we always stripped the > apostrophes out before putting variables into SQL statements. I just escape them-why take them out? They do serve a purpose. Randal said: > Placeholders are even more cool, and require less thinking. If they're supported, which they aren't under all platforms! Jeff then Randal said: >> DBI is really cool! > Agreed. Yeah :) Dave
Re: INSERTing problems
At 05:28 AM 6/8/01 -0700, Randal L. Schwartz wrote:
> > "Jeff" == Jeff Yoak <[EMAIL PROTECTED]> writes:
>
>Jeff> I'm coming in on the middle of a thread here, but generally the best
>Jeff> thing to do when working with databases in Perl is to us DBI, and if
>Jeff> you are doing that, database handles have a method called quote() that
>Jeff> will ensure that the value in question is properly quoted for the
>Jeff> database you are using. That way, you don't have to loose things like
>Jeff> apostrophes. For instance, omitting error checking for brevity:
>
>I wouldn't say best. Placeholders are even more cool, and require
>less thinking.
What do you mean by placeholders?
>$dbh->do("insert into foo (bar, baz) values (?, ?)", undef,
> $bar, $baz);
>
>Does the same thing as quote, with a lot less typing!
True, but my purpose what to display quote() explicitly in case it might be
needed in more complex constructions involving statement handlers and the
like. I guess, come to think of it, you can always get around explicit
quoting and this shows another feature that is generally useful.
Cheers,
Jeff
Re: INSERTing problems
> "Jeff" == Jeff Yoak <[EMAIL PROTECTED]> writes:
Jeff> I'm coming in on the middle of a thread here, but generally the best
Jeff> thing to do when working with databases in Perl is to us DBI, and if
Jeff> you are doing that, database handles have a method called quote() that
Jeff> will ensure that the value in question is properly quoted for the
Jeff> database you are using. That way, you don't have to loose things like
Jeff> apostrophes. For instance, omitting error checking for brevity:
I wouldn't say best. Placeholders are even more cool, and require
less thinking.
Jeff> my $dbh = DBI->connect("DBI:mysql:$dbname:$hostname",$dbuser,$dbpass);
Jeff> my $query = "insert into foo (bar,baz) values
Jeff> (".$dbh->quote($bar).",".$dbh->quote($baz).")";
Jeff> $dbh->do($query);
$dbh->do("insert into foo (bar, baz) values (?, ?)", undef,
$bar, $baz);
Does the same thing as quote, with a lot less typing!
Jeff> DBI is really cool!
Agreed.
--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<[EMAIL PROTECTED]> http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
Re: INSERTing problems
At 09:07 AM 6/8/01 +0100, Liz Keogh wrote:
>For my own personal curiosity;
>
>Would you have to somehow protect the apostrophe in something
>like "Kelly's Trucking" before putting it into an SQL string?
I'm coming in on the middle of a thread here, but generally the best thing
to do when working with databases in Perl is to us DBI, and if you are
doing that, database handles have a method called quote() that will ensure
that the value in question is properly quoted for the database you are
using. That way, you don't have to loose things like apostrophes. For
instance, omitting error checking for brevity:
my $dbh = DBI->connect("DBI:mysql:$dbname:$hostname",$dbuser,$dbpass);
my $query = "insert into foo (bar,baz) values
(".$dbh->quote($bar).",".$dbh->quote($baz).")";
$dbh->do($query);
DBI is really cool!
Cheers,
Jeff
Re: INSERTing problems
For my own personal curiosity; Would you have to somehow protect the apostrophe in something like "Kelly's Trucking" before putting it into an SQL string? I've not done a great deal of Perl, but with VB we always stripped the apostrophes out before putting variables into SQL statements. Cheers, Liz.
Re: INSERTing problems
It's been some time since I've done any access programming but the error your getting is consistent with using a reserved world for a table or field name. According to the below reference from Microsoft's site the world 'Full' is a indeed a reserved word in access and could be the cause of your problem. If so then your going to have to rename that table. http://support.microsoft.com/support/kb/articles/Q209/1/87.asp If that's not it I'm stumped since everything else your doing looks good. SAWMaster wrote: > -- Perl, because 600 billion oysters can't be wrong Canadian Consulting Services' pet perl hacker David Labatte [EMAIL PROTECTED]
RE: INSERTing problems
I have been very successful using Perl's debug feature ...
I use this for both CGI programs and "normal" Perl scripts... I always test
my cgi programs out on the Unix until I get the expected results THEN
go to the browser ...
For DBI I usually code my "selects/inserts/updates" as separate modules
(".pm"'s) and make them look like a "cursor" (fetching until "undef" is
returned) ... then test them from the command prompt with a small program
that does nothing but call and return... You can put $var[n]=<>; in for each
variable you wish to pass then on the return print each var you expect
back... If the program doesn't run (or doesn't run as wanted) then use the
debug feature Perl has a VERY good debug feature... but like anything
else it requires an investment of time (very little !) ...
This way I build little "blocks" of code ... with the added benefit of
reusability ... that then get incorporated into the "main" script ...
I tried the "trace" but personally liked debug better because most of the
time it was some I was doing, not DBI, CGI, or Perl...
John W Moon
-Original Message-
From: SAWMaster [mailto:[EMAIL PROTECTED]]
Sent: June 07, 2001 16:20
To: [EMAIL PROTECTED]
Subject: INSERTing problems
Curtis Poe said:
>As for your code problem, after instantiating a DBI object, try adding the
following line of code:
>DBI->trace( 2, 'errors.txt' );
>From the documentation:
>DBI->trace($trace_level)
>DBI->trace($trace_level, $trace_filename)
>DBI trace information can be enabled for all handles using the
>`trace' DBI class method. To enable trace information for a specific
>handle, use the similar `$h-'>`trace' method described elsewhere.
>Trace levels are as follows:
>0 - Trace disabled.
>1 - Trace DBI method calls returning with results or errors.
>2 - Trace method entry with parameters and returning with results.
>3 - As above, adding some high-level information from the driver
>and some internal information from the DBI.
>4 - As above, adding more detailed information from the driver.
> Also includes DBI mutex information when using threaded Perl.
>5 and above - As above but with more and more obscure information.
>Personally, I never need to go above trace level two or three.
>After you run your program, look at the error log that this generates and
you will find the exact
>SQL statement that caused the issue. I find this easier than constantly
adding "print" statements
>to my code.
I did this, and it looks like it could be handy, but I'm only 2 weeks into
this perl stuff, and the output is a little confusing to me. I'm wondering
a couple possibilites.
First, maybe my program is having trouble because some of the fields can be
null in the database table.
Second, my table actually has 8 fields, but my html form only accepts 7.
This is because the 8'th field is an autonumber generated by access to
prevent duplicate entries. I need this because I'm using freq as my primary
key, but there can be more than one owner for any given frequency.
Again, here is my current code, in case someone can spot something I'm doing
wrong here. And also the output to my errors.txt file.
#!c:\perl\bin\perl -w
##
#
#
#
#
# Add new frequencies to database
#
# By David
Williams #
#
#
##
use cgi;
use DBI;
$dbh = DBI->connect('dbi:ODBC:freq');
DBI->trace( 2, 'errors.txt' );
$co = new CGI;
print $co->header, $co -> start_html(title=>'Canadian Online Radio Frequency
Database'),
$co->center($co->h1('Thanks using our database!')), $co->h3('Here is what
you submitted...'),$co->hr;
print $co->hr;
print "Data Added\n";
$newfreq=$co->param('txtFREQ');
$newloc=$co->param('txtLOC');
$newdesc=$co->param('txtDESC');
$newfreqtype=$co->param('txtFREQTYPE');
$newcat=$co->param('txtCAT');
$newcall=$co->param('txtCALL');
$newtx=$co->param('txtTX');
$sqlstatement = "INSERT INTO Full (freq, loc, desc, freqtype, cat, call, tx)
VALUES (?,?,?,?,?,?,?)";
$sth = $dbh->prepare($sqlstatement) || die $dbh->errstr;
$sth->execute($newfreq, $newloc, $newdesc, $newfreqtype, $newcat, $newcall,
$newtx) || die $dbh->errstr;
print "Thank you for submitting a frequency!";
print $co->end_html;
###errors.txt#
INSERTing problems
Curtis Poe said:
>As for your code problem, after instantiating a DBI object, try adding the
following line of code:
>DBI->trace( 2, 'errors.txt' );
>From the documentation:
>DBI->trace($trace_level)
>DBI->trace($trace_level, $trace_filename)
>DBI trace information can be enabled for all handles using the
>`trace' DBI class method. To enable trace information for a specific
>handle, use the similar `$h-'>`trace' method described elsewhere.
>Trace levels are as follows:
>0 - Trace disabled.
>1 - Trace DBI method calls returning with results or errors.
>2 - Trace method entry with parameters and returning with results.
>3 - As above, adding some high-level information from the driver
>and some internal information from the DBI.
>4 - As above, adding more detailed information from the driver.
> Also includes DBI mutex information when using threaded Perl.
>5 and above - As above but with more and more obscure information.
>Personally, I never need to go above trace level two or three.
>After you run your program, look at the error log that this generates and
you will find the exact
>SQL statement that caused the issue. I find this easier than constantly
adding "print" statements
>to my code.
I did this, and it looks like it could be handy, but I'm only 2 weeks into
this perl stuff, and the output is a little confusing to me. I'm wondering
a couple possibilites.
First, maybe my program is having trouble because some of the fields can be
null in the database table.
Second, my table actually has 8 fields, but my html form only accepts 7.
This is because the 8'th field is an autonumber generated by access to
prevent duplicate entries. I need this because I'm using freq as my primary
key, but there can be more than one owner for any given frequency.
Again, here is my current code, in case someone can spot something I'm doing
wrong here. And also the output to my errors.txt file.
#!c:\perl\bin\perl -w
##
#
#
#
#
# Add new frequencies to database
#
# By David
Williams #
#
#
##
use cgi;
use DBI;
$dbh = DBI->connect('dbi:ODBC:freq');
DBI->trace( 2, 'errors.txt' );
$co = new CGI;
print $co->header, $co -> start_html(title=>'Canadian Online Radio Frequency
Database'),
$co->center($co->h1('Thanks using our database!')), $co->h3('Here is what
you submitted...'),$co->hr;
print $co->hr;
print "Data Added\n";
$newfreq=$co->param('txtFREQ');
$newloc=$co->param('txtLOC');
$newdesc=$co->param('txtDESC');
$newfreqtype=$co->param('txtFREQTYPE');
$newcat=$co->param('txtCAT');
$newcall=$co->param('txtCALL');
$newtx=$co->param('txtTX');
$sqlstatement = "INSERT INTO Full (freq, loc, desc, freqtype, cat, call, tx)
VALUES (?,?,?,?,?,?,?)";
$sth = $dbh->prepare($sqlstatement) || die $dbh->errstr;
$sth->execute($newfreq, $newloc, $newdesc, $newfreqtype, $newcat, $newcall,
$newtx) || die $dbh->errstr;
print "Thank you for submitting a frequency!";
print $co->end_html;
###errors.txt#
DBI 1.14-nothread dispatch trace level set to 2
-> prepare for DBD::ODBC::db (DBI::db=HASH(0x1b38790)~0x1b3a194 'INSERT
INTO Full (freq, loc, desc, freqtype, cat, call, tx) VALUES
(?,?,?,?,?,?,?)')
dbd_preparse scanned 7 distinct placeholders
dbd_st_prepare'd sql f32113660
INSERT INTO Full (freq, loc, desc, freqtype, cat, call, tx) VALUES
(?,?,?,?,?,?,?)
<- prepare= DBI::st=HASH(0x1b38880) at ADDTOF~1.PL line 27.
-> execute for DBD::ODBC::st (DBI::st=HASH(0x1b38880)~0x1b05a94 '165.03'
'MB' 'Kelly's Trucking' 'VHF' 'TRUCKING_CO' 'XNO99' '')
bind 1 <== '165.03' (attribs: )
bind 1 <== '165.03' (size 6/7/0, ptype 4, otype 1)
bind 1: CTy=1, STy=VARCHAR, CD=80, Sc=0, VM=6.
bind 2 <== 'MB' (attribs: )
bind 2 <== 'MB' (size 2/3/0, ptype 4, otype 1)
bind 2: CTy=1, STy=VARCHAR, CD=80, Sc=0, VM=2.
bind 3 <== 'Kelly's Trucking' (attribs: )
bind 3 <== 'Kelly's Trucking' (size 16/17/0, ptype 4, otype 1)
bind 3: CTy=1, STy=VARCHAR, CD=80, Sc=0, VM=16.
bind 4 <== 'VHF' (attribs: )
bind 4 <== 'VHF' (size 3/4/0, ptype 4, otype 1)
bind 4: CTy=1, STy=VARCHAR, CD=80, Sc=0, VM=3.
bind 5 <== 'TRUCKING_CO' (attribs: )
bind 5 <== 'TRUCKING_CO' (size 11/12/0, ptype 4, otype 1)
bind 5: CTy=1, STy=VARCHAR, CD=80, Sc=0, VM=11.
bind 6 <== 'XNO99' (attribs: )
bind 6 <== 'XNO99' (size 5/6/0, ptype 4, otype 1)
bind 6: CTy=1, STy=VARCHAR, CD=80, Sc=0, VM=5.
bind 7 <== '' (attribs: )
bind 7 <== '' (size 0/1/0, ptype 4, otype 1)
bind 7: CTy=1, STy=VARCHAR, CD=80, Sc=0, VM=0.
dbd_st_execute (for sql f32113660 after)...
st_execute/SQLExecute error -1 recorded: [Microsoft][ODBC Microsoft Access
Driver] Syntax error in INSERT INTO statement. (SQL-37000)(DBD:
st_exe
