Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

Re: Updating Specific Fields in MS Access

by pfaut (Priest)
on Apr 17, 2003 at 01:40 UTC ( #251101=note: print w/replies, xml ) Need Help??


in reply to Updating Specific Fields in MS Access

Bareword found in conditional at C:\Perl\scripts\test\newer.pl line 43.

In your while statement, I think you want to read from INPUT so put it in brackets like

while (<INPUT>)

Use of uninitialized value in split at C:\Perl\scripts\test\newer.pl line 26.

You get this error because of the one above. Since you didn't read from INPUT into $_, it was undefined when you attempted to use it as input to split.

Use of uninitialized value in concatenation (.) or string at C:\Perl\scripts\test\newer.pl line 33.

You've never assigned anything to $row or $field. Also, why are you interpolating these into strings to compare them? You could have just done $row eq $field1.

Update:$row and @row are two different variables. Your SELECT returned data to @row which is an array. $row is a scalar that just happens to share a name with the @row array but is otherwise a different variable.

Use of uninitialized value in concatenation (.) or string at C:\Perl\scripts\test\newer.pl line 35.

Again, you haven't assigned any value to $field2. Also, since you never read from the file, $driver is also undefined.

"DBD::ODBC::db prepare failed: MicrosoftODBC Microsoft Access Driver Syntax error in INSERT INTO statement. (SQL-42000)(DBD: st_prepare/SQLPrepare err=-1) at C:\Perl\scripts\test\newer.pl line 36.

Since $field2 doesn't have a value, the field list in your insert statement equates to an empty list which is not allowed.

Can't call method "execute" on an undefined value at C:\Perl\scripts\test\newer.pl line 37."

Since the prepare failed, $sth2 is undefined.

Non-syntax errors:

Move your database connect and statement prepares outside the loop. They only have to be done once.

You stated that you wanted to update a field. INSERT adds records to the database. I think you want an UPDATE statement instead.

Unless you include a WHERE clause in your SQL statements, they will act on all rows in the table. Your SELECT statement will return every row from the table. I think you want to add a WHERE clause to limit the returned data to the server that you just read from the file. The same applies to the UPDATE.

Also, the SELECT isn't necessary. You don't need to read a record before updating it.

90% of every Perl application is already written.
dragonchild

Replies are listed 'Best First'.
Re: Re: Updating Specific Fields in MS Access
by p0lp0t (Initiate) on Apr 17, 2003 at 21:29 UTC
    Thank you for the help. Some misconceptions I had have been cleared up.

    Here's my new script:

    #! c:\perl\bin\perl.exe -w # # InsertDrivers.pl #Importations and Declarations use Win32::ODBC; use DBI; use strict; my ( $dbh, $sth, $vals, $driver, $servlist); my $DSN='Database'; my $field1='server'; my $field2='nic1_drv_version'; #Connect to database $dbh=DBI->connect("dbi:ODBC:$DSN", "admin", "") or die "$DBI::errstr\n +"; #Open file for reading open(INPUT,"file.txt") || die; while (<INPUT>) { #use comma delimiter ($servlist, $driver)=split(/,/); #statement, prepare, execute, close handle my $sqlstatement=("UPDATE Servers SET nic1_driver_version=$driver +WHERE '$field1 eq $servlist\n'"); $sth=$dbh->prepare($sqlstatement); $sth->execute || die; $sth->finish; } close (INPUT); $dbh->disconnect;
    When run, the output is:

    DBD::ODBC::st execute failed: MicrosoftODBC Microsoft Access Driver Syntax error in number in query expression '5.0.67.0'. (SQL-42000)(DBD: st_execute/SQLExecute err=-1) at C:\Perl\scripts\test\ne w.pl line 31, <INPUT> line 1.
    Died at C:\Perl\scripts\test\new.pl line 31, <INPUT> line 1.

    5.0.67.0 is the first $driver value in file.txt.

    Regards,
    jw
      Your almost there but, there is a problem with your SQL statement (it is not valid). The statement should be something like:
      my $sqlstatement=("UPDATE servers SET nicl_driver_version = '$driver' +WHERE '$field1' = '$servlist'");
      So you need to put single quotes around $driver as it is not all numeric, nor the SQL keyword NULL, I have also changed you WHERE clause to reflect what I think you want it to do. One good resource for SQL syntax is w3schools

      you also don't appear to be using $field2 or $vals for anything, and don't need the use Win32::ODBC; line for anything as well (you are not using that module in your code at all).

      One last thing is that you might want to prepare your SQL statement outside your loop, and then execute it on the inside, so you don't have to prepare the same statement time and time again unnecessarily). So your code might look something like this(untested):

      update:added line to script because I neglected to initialize $field1 (so that line is now there), which would be the reason for the failure, p01p0t was having in Re: Re: Re: Re: Updating Specific Fields in MS Access

      -enlil

        Your response is appreciated.

        When I try to place the prepare before the loop, it complains about uninitialized values.
        I tried using placeholders, to no avail, so I kept the prepare in the loop. Now I have:

        #! C:\perl\bin\perl.exe -w use DBI; #use strict; my $DSN='Database'; my $driver; my $servlist; my $field='Server'; #connect to database my $dbh=DBI->connect("dbi:ODBC:$DSN", "admin", "") || die; #Open file for reading open(INPUT,"file.txt") || die; while (<INPUT>) { chomp; my ($servlist, $driver)=split(/,/); my $sqlstatement=("UPDATE Servers SET nic1_driver_version='$driver +' WHERE '$field' = '$servlist\n'"); my $sth=$dbh->prepare($sqlstatement); $sth->execute || die; } close (INPUT); $dbh->disconnect;
        Spits back:
        DBD::ODBC::st execute failed: MicrosoftODBC Microsoft Access Driver Too few parameters. Expected 1. (SQL-07002)(DBD: st_execute/SQLExecute err=-1) at C:\Perl\scripts\test\newer.pl line 22, <INPUT> line 1.
        Died at C:\Perl\scripts\test\newer.pl line 22, <INPUT> line 1.

        I am confused about the parameters error.
        Regards,
        jw

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://251101]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (5)
As of 2021-04-17 09:07 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?