Updating Specific Fields in MS Access

[Anonymous Monk]

I have researched this on the Net for about a week now and after having some good and bad luck, have come to the Monks for their wisdom.

I am new to Perl and had never programmed anything until about a month ago. Additionally, I have no experience with SQL or databases in general, so please have patience.

I have a CSV file consisting of two values per line, a server name and a NIC driver version. I need to find the corresponding server name within an Access database and insert the driver value into the proper field in the same row.

When I run my script, I get the following errors:
Bareword found in conditional at C:\Perl\scripts\test\newer.pl line 43.
Use of uninitialized value in split at C:\Perl\scripts\test\newer.pl line 26.
Use of uninitialized value in concatenation (.) or string at C:\Perl\scripts\test\newer.pl line 33.
Use of uninitialized value in concatenation (.) or string at C:\Perl\scripts\test\newer.pl line 35. "DBD::ODBC::db prepare failed: [Microsoft][ODBC 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.
Can't call method "execute" on an undefined value at C:\Perl\scripts\test\newer.pl line 37."

here is my amateur code:

#! c:\perl\bin\perl.exe -w
#
# InsertDrivers.pl

#Importations and Declarations
use Win32::ODBC;
use DBI;
my $DSN='Database';
my $field1='server';
my $field2='nic1_drv_version';
my ( $dbh,
     $sth1,
     $sth2,
     $vals,
     $driver,
     $servlist,
     $row,
     $sqlstatement1,
     $sqlstatement2,
     @row);

#Open file for reading
open(INPUT,"file.txt") || die;
while (INPUT)
    {
    my ($servlist, $driver)=split(/,/);
    #Connect to database
    my $dbh=DBI->connect("dbi:ODBC:$DSN", "admin", "") or die "$DBI::e
+rrstr\n";
    $sqlstatement1=("SELECT server, nic1_drv_version FROM Servers");
    $sth1=$dbh->prepare($sqlstatement1);
    $sth1->execute || die;
    @row=$sth1->fetchrow_array();
    foreach ("$row\n" eq "$field1\n")
            {
            $sqlstatement2=("INSERT INTO Servers ($field2) VALUES ($dr
+iver)");
            $sth2=$dbh->prepare($sqlstatement2);
            $sth2->execute || die;
            }
close (INPUT);
$sth1->finish;
$sth2->finish;
$dbh->disconnect;
}

Any suggestions will be greatly appreciated.

2003-04-16 edit ybiC: replace square bracket chars with appropriate html entities, to eliminate unintended PM links

[pfaut]

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

[p0lp0t]

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

[Enlil]

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):

Read more... (751 Bytes)
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

[Chmrr]

On the line which currently reads:

while (INPUT)

..you are missing a set of angle brackets. That is, it should say..

while (<INPUT>)

You might want to consider doing your connect() call outside of the loop, rather than doing it over and over, though, That, and the voices in my head that keep chanting "use strict; use strict; use strict;"..

Update: Forgot to mention placeholders, as well.

Update 2: I keep looking at the code and seeing more things. I probably shouldn't have been as hasty in my first posting. ;> In any case, the following lines probably don't do what you want them to do:

    @row=$sth1->fetchrow_array();
    foreach ("$row\n" eq "$field1\n")
            {

That retrieves one row from your SQL query, and throws it away. It then checks if the string "$row\n" (and $row is undefined) is equal ro the string "$field1\n". Not knowing what you are trying to do, it's hard for me to suggest corrected code, but that's most probably not what you meant. ;>

perl -pe '"I lo*`+$^X$\"$]!$/"=~m%(.*)%s;$_=$1;y^`+*^e v^#$&V"+@( NO CARRIER'