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<BR>\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_execute/SQLExecute err=-1)
!! ERROR: -1 '[Microsoft][ODBC Microsoft Access Driver] Syntax error in
INSERT INTO statement. (SQL-37000)(DBD: st_execute/SQLExecute err=-1)'
<- execute= undef at ADDTOF~1.PL line 28.
-> errstr in DBD::_::common for DBD::ODBC::db
(DBI::db=HASH(0x1b38790)~0x1b3a194)
<- errstr= ( '[Microsoft][ODBC Microsoft Access Driver] Syntax error in
INSERT INTO statement. (SQL-37000)(DBD: st_execute/SQLExecute err=-1)' ) [1
items] at ADDTOF~1.PL line 28.
-- DBI::END
-> disconnect_all for DBD::ODBC::dr (DBI::dr=HASH(0x1b7dda8)~0x1b387e4)
<- disconnect_all= '' at DBI.pm line 450.
-> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x1b05a94)~INNER)
<- DESTROY= undef during global destruction.
-> DESTROY for DBD::ODBC::db (DBI::db=HASH(0x1b3a194)~INNER)
<- DESTROY= undef during global destruction.
-> DESTROY in DBD::_::common for DBD::ODBC::dr
(DBI::dr=HASH(0x1b387e4)~INNER)
<- DESTROY= undef during global destruction.
-David
