Doyle has asked for the wisdom of the Perl Monks concerning the following question:

I have looked and tried many of the examples in the super search but nothing seems to be working to insert a record into an access database. Here is my code... 
    #!/usr/bin/perl -w
    #################################################
    ## This is an Example of how to Connect to an
    ## Access Database using a DSN-less Connection
    #################################################
    use POSIX;
    use strict;
    use CGI qw(:standard);
    use CGI::Carp qw(fatalsToBrowser);
    use DBI;
    print header;
        
    #########################################
    ## Create the Connection String 
    ## To use a DSN just replace the string
    ## with the DSN name.
    #########################################
    my $DSN = 'driver=Microsoft Access Driver      (*.mdb);dbq=c:\\Ine
+tpub\\domains\\rvnuccio.com\\applications\\Test\\Clients.mdb';
    my $dbh = DBI->connect("dbi:ODBC:$DSN", '','') 
        or die "$DBI::errstr\n"; 
        
    ###############################
    ## Generate SQL Statement
    ###############################    
   #  my $sql = "SELECT ClientName,ClientEmail FROM billing";
   my $sql ="INSERT INTO Billing VALUES ('Doyle','doyle.com','1010','1
+00','Yes')";

    ###############################
    ## Prepare and execute the 
    ## SQL Statement
    ###############################
    
    my $loadHandle = $dbh->prepare($sql);
    $loadHandle->execute|| 
       die "Could not execute SQL statement ... maybe invalid?";
                
  #  my @row;            
    
  #   while (@row=$loadHandle->fetchrow_array)
  #    { print "@row\n\n" }    
  
    
    #########################################
    ## Close the connection when finished:
    #########################################
    $dbh->disconnect;

[download]
any help please....

Retitled by davido from 'Access'.

Replies are listed 'Best First'.
Re: Inserting a record into an Access database
by ikegami (Patriarch) on Feb 21, 2005 at 17:28 UTC
    What error do you get? Why don't you check if prepare returns an error? Why don't you print $DBI::errstr in the second die?
[reply]
[d/l]
[select]
      Disregard the last half of my code, I have that commented out. The error I am getting is this...Software error: Could not execute SQL statement ... maybe invalid? at c:\Inetpub\domains\rvnuccio.com\applications\Test\tbl.pl line 46. thanks for the feedback
[reply]
        I guessed as much, which is why I also asked you to do two things. Could you do those two things and tell us what you get then?
[reply]
Re: Inserting a record into an Access database
by eric256 (Parson) on Feb 21, 2005 at 17:27 UTC

    Do you get your error message? Do you get any message? It looks like you have some code in there to print out all the rows, did that work? Try turning RaiseError on and see if that gives you better info.

    As a side note, you can use a MySQL database (or any other) as the backend for Access. I've found that doing that makes the Access database faster, and much easier to work with. It also means that when the Access mdb file becomes corrupt (which happens all too often) you don't have to worry about your data being corrupted.


    ___________
    Eric Hodges
[reply]
Re: Inserting a record into an Access database
by Jenda (Abbot) on Feb 21, 2005 at 22:57 UTC 
    my $sql ="INSERT INTO Billing VALUES ('Doyle','doyle.com','1010','100'
+,'Yes')";

    [download]

    Don't do this! Maybe it's not the problem just in this case, but it's dangerous anyway. What if someone, a year later changes the schema of the database. Slightly, just by adding a column into this table, booooooooom.

    Always specify the columns you intend to insert to. Please!

    Jenda
    We'd like to help you learn to help yourself
    Look around you, all you see are sympathetic eyes
    Stroll around the grounds until you feel at home
       -- P. Simon in Mrs. Robinson

[reply]
[d/l]
      Without a specific error message (call ->errstr() in your die statement), I'd have to blindly say that Jenda is onto your problem. Are you sure that you are inserting the correct number of values into the table and that you are inserting the correct type of values into the table?
[reply]
Re: Inserting a record into an Access database
by dbwiz (Curate) on Feb 21, 2005 at 17:41 UTC
[reply]
Re: Inserting a record into an Access database
by larryp (Deacon) on Feb 21, 2005 at 17:29 UTC

    Hi Doyle,

    Have you tried selecting records? I mean, is the problem with the connection or with the DB operations? Since you're using the CGI modules, I assume you're working with a web server...which one?

    In addition, you must ensure the user that the process is running under has both read AND write permission for the Access file. If you can select records, but not insert them, I'd bet user permissions are your problem.

    HTH,

    Larry

    Update: Fixed a typo. :)

[reply]
      I have no problem selecting the records that works. Only when I want to insert is when the problem arises. I am checking to see if I have read/write permissions. thanks
[reply]
Re: Inserting a record into an Access database
by CountZero (Bishop) on Feb 21, 2005 at 20:02 UTC
    I had good success using DBD::ADO with Access databases:
    my $ado_dsn='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=x:\Path\To\D
+atabase.mdb';
my $ado_usr='my username';
my $ado_pwd='my pasword';
my $ado_dbh = DBI->connect("dbi:ADO:$ado_dsn", $ado_usr, $ado_pwd ) or
+ die $DBI::errstr;

    [download]
    Note: it will work only on Win32-machines!

    CountZero

    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[reply]
[d/l]
Re: Inserting a record into an Access database
by injunjoel (Priest) on Feb 21, 2005 at 17:51 UTC
    Greetings all,
    I am currently working with Access myself though I am using a System DSN instead of the DSN-less approach.
    Though this is not related to your posting I would suggest using $dbh->{RaiseError} = 1 which will report your errors to you without having to call  || die $handle->errstr all the time for every method call. This may help you figure out where your issue is (db handle vs. statement handle, etc)
    One other thing to keep in mind is that the return values from any given query are not what you might be expecting (at least they were not for me). For instance a successful Select query returns me -1 (which in VB is true) even if there was nothing returned, whereas a successful Insert statement returns me 1 (which Im assuming is how many rows were affected); all of this is coming from the method calls on prepared statment handles.
    Coming from MySQL mainly this took some getting use to. That being said, use the RaiseError property and post what errors come back, we should be able to help you more after that.

    -InjunJoel
    "I do not feel obliged to believe that the same God who endowed us with sense, reason and intellect has intended us to forego their use." -Galileo
[reply]
[d/l]
[select]
Re: Inserting a record into an Access database
by phaylon (Curate) on Feb 21, 2005 at 17:26 UTC
    I have no idea of access, but you may want to try to commit your query. perldoc DBI should know more.
    Ordinary morality is for ordinary people. -- Aleister Crowley
[reply]
Re: Inserting a record into an Access database
by holli (Abbot) on Feb 21, 2005 at 18:35 UTC
[reply]

Back to Seekers of Perl Wisdom