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

Please Brethren HELP
I am very new to SQL and ODBC (so please do help me). I have written the following simple code, which inserts an entry (record) into an access database. In order to establish and maintain referential integrity, I need to know the Index key value of this entry to tie it up with its sub records.
use strict;
use Win32;
use warnings 'all';
use Win32::ODBC;
my $dsn = "test";
my $srv_tbl = "server_tbl";
my $drv_tbl = "drives_tbl";
my $srv = shift @ARGV; 
my $loc = shift @ARGV; 
my $notes = pop @ARGV;
my $db = new Win32::ODBC( $dsn ) || die "\nError! $^E:$!\n";
$db->Sql("INSERT INTO $srv_tbl (srv_name, location, notes) VALUES ('$s
+rv','$loc','$notes')");
my $sql= “select srv_id from server_tbl where srv_name='$srv'”; #this 
+bit doesn’t work
print "Index Key: $sql\n"

[download]
This bit doen't work (my $sql= “select srv_id from server_tbl where srv_name='$srv'”); Can someone please get me out of my misery, and inform me why.

Replies are listed 'Best First'.
Re: MS Access entry index key values
by Rich36 (Chaplain) on Jul 09, 2002 at 16:22 UTC

    Your SQL looks fine (although I think the words SELECT, FROM, and WHERE should be capitalized). The problem is that you need to execute the SQL statement, then assign the returned information to a data structure.

    The code below will push the data that gets returned into a hash for each row, which you can then use to get a number. In this case, it looks like you would only have one row returned but the code below allows for multiple instances of the value. 

    my @rows;
die qq(SQL failed: ), $db->Error(), qq(\n) if ($db->Sql("SELECT srv_id
+ FROM server_tbl WHERE srv_name='$srv'"));

while ($db->FetchRow()) {
     my %data = $db->DataHash();
     print qq(Index key is $data{"srv_id"}\n); #srv_id value
     # If you want to store information from multiple rows
     push @rows, {%data}; 
}

    [download]

    You should also check to make sure that your first SQL statement happened correctly. Check out Web Deployment Schemes for some examples on how to work with Win32::ODBC.

    «Rich36»
[reply]
[d/l]
Why not DBI and ODBC DBD?
by aufrank (Pilgrim) on Jul 09, 2002 at 18:11 UTC
    This definitely applies to DBI, but looking over the win32:ODBC docs it seems applicable here too. Generally speaking, you need to execute any sql statement (and most statements can be prepared before they are executed) in order to use it. in terms of select statements, once executed you can perform a fetch to get at the data.

    I guess my real question (not just for you, but for any monk with an opinion) is why to use win32::ODBC in the first place? This admittedly biased presentation makes it seem like a bad idea.

    I also am quite new to perl, having started just two months ago, and the only reason I even started with perl was because I had been hired to do a bunch of database management type stuff and I saw that as an excuse to learn something new and make the process fun. and so far it has been.

    I did not use the win32 module even though I also am dealing with access (and coding on an NT box). Frankly, I didn't find out about win32::ODBC until after I had already started writing using DBI and the ODBC DBD. At that point I was too lazy to go after another module when the one I had seemed to be working just fine. I must say I've been mighty pleased with my (blindly made) choice.

    The best part of working with DBI, you see, has been the documentation-- it could not have been more helpful or easy to use. I mention it specifically because of its explanation of the general prepare, execute, fetch process for sql queries which might help you figure out what your win32::ODBC calls should look like.

    Another option is apparently DBIx, though I know very little about it. Check out this article if you are interested, though.

    If you do decide to pursue DBI at all, this article (written by chromatic) was a huge help to me and clued me in on some techniques that turned out to be really important (especially using transactions and trapping the execution of certain types of sql statements (like updates and inserts) inside eval blocks to make sure that no incorrect changes get made). Actually, all of the database Tutorials here at Perlmonks were really helpful, and that's actually how I came here in the first place.

    hope this helps at all,
    --au

[reply]
[d/l]
Re: MS Access entry index key values
by Moonie (Friar) on Jul 09, 2002 at 16:16 UTC
    Hmm..why not try a SQL statement like this: 
    SELECT MAX(srv_id) as Id
FROM server_tbl

    [download]
    That's with the assumption that the srv_id is auto-incremented.
    - Moon
[reply]
[d/l]
Re: MS Access entry index key values
by LanceDeeply (Chaplain) on Jul 09, 2002 at 22:09 UTC
[reply]
      Its all coming along nicely now (still trying to get my head around it a little bit, but I have lots to go on)...Thanks very much.
      Actually I am just about to make a donation, as a mark of gratitude.
      PS: Where is the best place to pick up a good comprehensive tutorial on Perl Tk? with loadsa examples?
[reply]

Back to Seekers of Perl Wisdom