DBI Query

[bknox]

I am developing a web page that will create purchase orders for me using HTML and Perl. I am using the DBI:ODBC module to interface into an Access DB that holds all the purchase order information. I am running IIS on my local machine to test and debug the code before I place it into production. My problem is this: I have a single script that cannot write to the DB. I keep getting this error:

Software error:
ERROR: [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query. (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)

I know that nothing is set up wrong on my Access DB, b/c I can write to it via other scripts. The code in the script that is failing is:

######################Code Begins Below####################
#!c:\Perl\bin\perl.exe -w
use DBI;
use CGI ':standard';
use CGI::Carp qw(fatalsToBrowser);

require 'functions.lib';

print "content-type: text/html \n\n";

my $db;

$od = param('lineitemkey');

connecttodsn($db);

$sqlstmnt = $db->prepare("INSERT INTO Removals (DelID) VALUES ($od)");
$sqlstmnt->execute() or die "ERROR: $DBI::errstr\n\n";
$db->disconnect();
###############End Code####################################

I know I can read from the DB in this script b/c I have tested it with simple SELECT statements. However, I am baffled at why I cannot write to the DB, since I did it in the script before this one. I close the connection to the DB in every script (I have check multiple times). The function.lib library simple contains the connection string to the database in question.

HELP!!!!

Ben

 

[Rieekan]

Try separating the SQL statement from your Prepare and see what happens. It could be something within the statement that is causing the issue.

$sql = qq|INSERT INTO Removals (DelID) VALUES ($od)|;

$sqlstmnt = $db->prepare($sql) or die "PREPARE ERROR: $DBI::errstr\n\n";

$sqlstmnt->execute() or die "EXECUTE ERROR: $DBI::errstr\n\n";

- Rieekan

 

[bknox]

I tried the code you posted, and I am still getting the same error. It is in the Execute phase that it throws the error.

Ben

 

[Rieekan]

What's the parameter you're passing into the program? I'd bet it has something to do with syntax on the SQL itself.

Try this:

$sql = qq|INSERT INTO Removals (DelID) VALUES (?)|;

$sqlstmnt = $db->prepare($sql) or die "PREPARE ERROR: $DBI::errstr\n\n";

$sqlstmnt->bind_param(1, $od);

$sqlstmnt->execute() or die "EXECUTE ERROR: $DBI::errstr\n\n";

- Rieekan

 

[bknox]

I copied you code into the script and I still got the same error. I have printed out the resulting SQL statment as just a plain text string and it shows the value is getting inserted fine. I have even copied this printed SQL statement and placed it into my Access DB as a Query and it executed correctly.

Ben

 

[Rieekan]

Hmmm, that's odd. I'm not sure what's going on with this then.

Sorry!

- Rieekan

 

[tviman]

How are you defining the ODBC connection? I see this:

my $db;
connecttodsn($db);

But I don't see where $db is assigned any values. Are you doning this in the $connecttodsm() function? If so, isn't this like passing a local variable with a null value to an external file - at which time the variable is no longer in scope?

Maybe I'm all wet but I'd take a look at it anyway.

There's always a better way. The fun is trying to find it!

 

[bknox]

The connecttodsn function assigns the DSN to $db. I use is throughout my scripts (about 8 or so). It has worked up until know, so I don't think this is the problem.

Thanks for the observation,

Ben

 

[Leland123]

Hi bknox,

Try using the exact error message in a google search and see if anything turns up.

Regards,

LelandJ


Leland F. Jackson, CPA
Software - Master (TM)

http://www.smvfp.com

Nothing Runs Like the Fox

 

[bknox]

Everyone:

Thanks for your help on the problems that I have been facing. I have solved the problem posted in this request. Evidently, all of my scripts have been accesing the DB under my user account on my local machine, except the one that was giving me the problems. I modified the access rights for the default user that logs in to the computer itself instead of the domain that I am attached to. I gave this user full rights to the DB, and it solved the problem. I appreciate all you help.

Ben