Win32::ODBC insert question

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

Greetings, I am trying to insert records into an MS Access 2K database. I am having problems with a table that has a primary key with the data type of "AutoNumber" which is a Access-driven auto-sequenced key. What I want is to pass along the fields and have Access or the ODBC driver generate the next sequence number.

I have tried leaving the field out, in which I get the "Too few parameters" error. I've tried an blank variable, which gives me a "Data Type mismatch" error. Also, I've tried the number 0, which will write a record where the sequential key is 0, but of course if I try to write another record, I get a duplicate key error. The table looks like:

order    <---  table name
-------
seq      <---  primary key, autonumber
item
desc
qty
date_time
user
[download]

Here's my test code:

# Make Database connection
$db = new Win32::ODBC("DSN=$DATA_SOURCE_NAME;
                       UID=$USER_ID;PWD=$PASSWORD;")
     or die Win32::ODBC::Error();

# Test Variables
$new_item = "Test";
$new_desc = "Test description";
$new_qty = 2099;
$new_user = "TESTUSER";
# get current time
$now_time = gmtime; 
$new_seq = 0;

# Insert row into database
if ($db->Sql("INSERT INTO order VALUES 
                    ($new_seq,'$new_item', '$new_desc', $new_qty,'$now
+_time','$new_user')" ))
   {
   # Print out any ODBC/SQL error
   print $db->Error();
   }
# Close the database               
$db->Close();
[download]

Has anyone had experience in doing this? Any help will be much appreciated. thanks.

Comment on Win32::ODBC insert question Select or Download Code

Replies are listed 'Best First'.
Re: Win32::ODBC insert question by dws (Chancellor) on Mar 22, 2001 at 03:10 UTC
To insert a subset of the fields in a record, you need to explicitly list the field names in the query. Try `"INSERT INTO orders (item, desc, qty, date_time, user) VALUES('$new_item','$new_desc','$new_qty','$now_time','$new_user')"` [download]	[reply] [d/l]
(crazyinsomniac) Re: Win32::ODBC insert question by crazyinsomniac (Prior) on Mar 22, 2001 at 01:35 UTC
Check out this tutorial for examples: http://www.sa-mart.com/ftp/pub/perl/Docs/Win32/ODBC/odbc-Tutorial.htm and http://www.roth.net/perl/odbc/ I don't think Win32::ODBC allows for primary keys, at least not autonumber(java does), you'd probably have to figure out what the greatest key is, and then increment, but if your database is updated while you're doing this, you might run into trouble.\ Anyway i don't like order as a table name, please change it to purchase_order or something ;-) Anyway check this out too: http://www.sa-mart.com/ftp/pub/perl/Docs/Win32/ODBC/example6.cgi.htm Here's a snipped: `$db->Sql("SELECT DISTINCT seq FROM purchase_order" );` DBI has this: $new_id = $sth->{insertid}; update: aparently insert id is a mysql specific function, too bad. An oh yeah, dont forget: http://dbi.symbolstone.org/ and http://www.perldoc.com/cpan/DBD/ODBC.html update:Here's a better snippe:D $db->Sql("SELECT MAX(seq) FROM purchase_order"); It'll fetch the lartest unique id, and since it's autonumber, it must be an integer(or long), so you just increment it by 1 and you have your next primary key. ___crazyinsomniac_______________________________________ `Disclaimer: Don't blame. It came from inside the void` `perl -e "$q=$_;map({chr unpack qq;H;,$_}split(q;;,qH*));print;$q/$q;"`	[reply] [d/l]


Think about Loose Coupling
	PerlMonks