#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    6
    Rep Power
    0

    Red face 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]
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    300
    Rep Power
    15

    Question


    So it doesn't send you any feedback messages?? It doesn't tell you what the error is??
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    6
    Rep Power
    0
    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.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Location
    Riverside, CA
    Posts
    63
    Rep Power
    14

    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:ODBCne','','',);

    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.

  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Location
    Riverside, CA
    Posts
    63
    Rep Power
    14
    that connection string
    is
    $db=DBI->connect('dbi:ODBC:one','','',);

  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    6
    Rep Power
    0
    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.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    300
    Rep Power
    15
    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??
  14. #8
  15. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    6
    Rep Power
    0
    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??
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    300
    Rep Power
    15
    Again, is there a way to see your whole script?? It could be possible that the error is somewhere before this block of code...
  18. #10
  19. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Location
    Boston, MA, USA
    Posts
    3
    Rep Power
    0

    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.
  20. #11
  21. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    6
    Rep Power
    0
    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!


  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2000
    Location
    Indiana
    Posts
    614
    Rep Power
    16
    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]
  24. #13
  25. No Profile Picture
    PerlGuy
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2001
    Posts
    720
    Rep Power
    42
    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
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2000
    Location
    Indiana
    Posts
    614
    Rep Power
    16
    Yes, that's exactly what I told him one post ago (other than the fact that you messed yours up).
  28. #15
  29. No Profile Picture
    PerlGuy
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2001
    Posts
    720
    Rep Power
    42

    Wink


    so you did. Except where did i mess mine up?
    - dsb -
    Perl Guy

IMN logo majestic logo threadwatch logo seochat tools logo