|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now! |
|
#1
January 24th, 2001, 10:47 AM
|
|||
|
|||
Help with Microsoft Access Database and CGI
I need help figuring out how to write data into a Microsoft Access database through a CGI script. My database is called PMVacantDB, and there is a single table in there called PMVacant. I can get the script to read data fine, so I know the connection is working. It's just writing data to it that I can't get to work. The syntax I've been trying is:
$dbh = DBI ->connect('DBI:ODBC:PMVacantDB', {RaiseError=>1}) or die "connecting: $DBI::errstr"; $insert = "INSERT INTO PMVacant (id) VALUES ($value)"; $in = $dbh->prepare($insert); $in->execute; The script will hang unless the $in->execute line is commented out. Obviously, it cannot execute. What am I doing wrong? [Edited by DrMuziK on 01-25-2001 at 08:30 AM]
|
|
#3
January 25th, 2001, 09:31 AM
|
|||
|
|||
|
No, I get no error messages. I put a print statement after the lines of code I posted above, and it gets to that fine so I know it's getting past my database calls. I'm still stumped. I think I'm going to try a mySQL database instead.
|
|
#4
January 25th, 2001, 06:41 PM
|
|||
|
|||
|
Here is a script that I wrote that works
#!C:PERLbinperl.exe
use DBI; print "Content-type:text/htmlnn"; print "TEST CONNECTEDn"; #(DSN,USER NAME, PASSWORD) $db=DBI->connect('dbi:ODBC  ne','','',);print "Connect Successful.n"; #sql statement $sql="select * from Table1"; #preapre statement $query=$db->prepare($sql); #execute statement $query->execute; print "<html><table border=1 cellpadding=0>n"; #Fetch information to print while($lineItem=$query->fetchrow_hashref) { print "<tr>n"; print "<td>$lineItem->{'one'}</td><td>$lineItem->{'two'}</td><td>$lineItem->{'three'}</td>n"; print "</tr>n"; } print "</table></html>"; DBD::ODBC is only on a Windows system, if that is any help.
|
|
#6
January 26th, 2001, 12:14 AM
|
|||
|
|||
|
Yeah, I could get my script to read from the database just fine. It's writing that I am having the problem doing.
My script opens a comma delimited file correctly (I have print statements to show this) and I want to WRITE all that info into my database. This is what's not working.
|
|
#7
January 26th, 2001, 02:18 AM
|
|||
|
|||
|
Just a stupid thought, but did you set up the permissions correctly?? It happens to me now and then. Since Access is, basiaclly, jsut a file, you need to setup your permissions, so that the user under which the web server works can write to it...
 Of course, the problem may be in something completely different, for example a datatype mismatch or something like that...Could you please post your entire script, so we can see whre the problem lies??
|
|
#8
January 26th, 2001, 12:32 PM
|
|||
|
|||
|
Ok, I found a way to get an error message for the code I originally posted. I changed the last line from:
$in->execute; to: $in->execute or print "<br>cannot execute: $DBI::errstr"; and got the following message: cannot execute: [Microsoft][ODBC Microsoft Access 97 Driver]Wrong number of parameters (SQL-07001)(DBD: st_execute/SQLExecute err=-1) What parameters am I leaving out??
|
|
#10
January 26th, 2001, 02:47 PM
|
|||
|
|||
|
Don't use prepare/execute
$insert = "INSERT INTO PMVacant (id) VALUES ($value)";
$in = $dbh->prepare($insert); $in->execute; Try: $dbh->do($insert) || warn $dbh->errstr; You don't need the prepare/execute/finish trio with inserts.
|
|
#11
January 26th, 2001, 03:18 PM
|
|||
|
|||
|
I got it working! This is the single command I used:
$dbh->do("INSERT INTO PMVacant (building,floor) VALUES ('$building','$floor')") or print "<br>$DBI::errstr"; Thanks for all your help!
|
|
#12
January 26th, 2001, 06:38 PM
|
|||
|
|||
|
Well the problem you had in the 1st post was that you didn't quote $value in anyway.
$insert = "INSERT INTO PMVacant (id) VALUES ($value)"; should have been $insert = "INSERT INTO PMVacant (id) VALUES ('$value')"; but to be clean and safe, you should have done it like this: either: $dbh->do("INSERT INTO PMVacant (id) VALUES ('".$dbh->quote($value)."')") or die $dbh->errstr; or if you have a bunch of values to insert (maybe more than once)... $q = $dbh->prepare("INSERT INTO blah (asdf,fdas,ast) VALUE (?,?,?)") or die $dbh->errstr; $q->execute($var,$foo,$stuff) or die $dbh->errstr; this way you can avoid ANY problems with single quotes or anything else messing up the quoting system. [Edited by JonLed on 01-26-2001 at 05:47 PM]
|
|
#13
January 29th, 2001, 03:06 PM
|
|||
|
|||
|
I would have to bet that its the statement you are preparing. I have never seen an instance of variable interpolation in a SQL statement work. That is why DBI supports placeholders.
My own database inserts look like this: Code:
$dbh = DBI->connect(DBI:dbtype:db:hostname, other args); $sth = $sth->prepare( "INSERT INTO tablename (column) VALUES( ? )" ); # the question mark is a placeholder to be filled $sth->execute( $value ); # passing an argument to the 'execute' method will plug the value of the argument into the placeholder Hope this helps.
__________________
- dsb - Perl Guy
|
|
| Viewing: Dev Shed Forums > Programming Languages > Perl Programming > Help with CGI and Access Database |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
(other than the fact that you messed yours up).
Linear Mode
