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

PLEASE HELP BROTHERS
I have been requested to write a Perl script that inserts new tables or values into an MS Access 2000 database, the object is to have a database containing servers and their drive information (for eg: Server1001 has 4 drives – C$, D$, E$ and F$, information in regards to the size and free space for each drive are required). I started in MS-Access by creating a table, which I called servers_tbl, using the tables wizard. The table contains 2 field only, the index key field and the server name. Next, I created drives_tbl, which contains the following fields: drv_letter, total_disk_size, free_space_remaining. By means of the wizard I created a relationship with servers_tbl in a one-to-many relationship (i.e based on my understanding that one server entry can have a number related drives with their size and space information). The following code works only as far as inserting entries into the servers_tbl and inserts drive information into the drives_tbl also but as a separate non-related table. When I checked back in MS-Access, in the servers_tbl, I can saw a plus sign ‘+’ in the very first column (before the Index key ‘auto counter’ column) which indicates to me that each servers_tbl has a sub table associated with it which contains the drive information. My question is : How can I insert data into those sub record/tables while still maintaining the relationships? Your enlightenment is highly appreciated and a simple example can go a long way brothers. And I have looked around faq section and did a search on ms access and read most of the stuff there (this is how I managed to get this far)
use strict;
use warnings 'all';
use Win32;
use Win32::ODBC;
use Win32::NetAdmin;
my $dsn = "SRV_DRV";
my $srv_tbl = "servers_tbl";
my $drives_tbl = "drives_tbl";
my $perms_db = new Win32::ODBC($dsn) || die "\nError=>\t$^E : $!\n";
print "\nError:  $^E\n" if (! $perms_db);
if ( $perms_db->Sql("SELECT * FROM $srv_tbl")){
print "Error!\n";}
else{
    my $srv = shift @ARGV || Win32::NodeName();
    print "\nProcessing Server: '$srv'\n";
    $perms_db->Sql ("INSERT INTO $srv_tbl (Server_Name) VALUES ('$srv'
+)");
    if (Win32::NetAdmin::GetServerDisks($srv, \ my @disks))    {
        print "\nAvailable logical drives\n";
        foreach my $disk (@disks){
            next if ($disk =~ /a|z/i );
            $disk =~ s/:/\$/;
            print "Drive\t$disk\n";
            if ( $perms_db->Sql("SELECT * FROM $drives_tbl")){
                print "Error!\n"}
            else{
$perms_db->Sql ("INSERT INTO $drives_tbl (drive_letter) VALUES ('$disk
+')");    }
        }}
    else{
        print "\n\nError! $^E:$!.\n"; }}
$perms_db->Close( );

[download]

Replies are listed 'Best First'.
Re: Inserting values into MS Access sub tables
by LanceDeeply (Chaplain) on Jul 08, 2002 at 18:17 UTC
    Is it necessary to use Access to manage the relationship?

    If your tables are: 
    servers_tbl
-----------
 server_id   (autonumber)
 server_name (text)

drives_tbl
----------
 drive_id             (autonumber)
 server_id            (number)
 drv_letter           (text)
 total_disk_size      (number)
 free_space_remaining (number)

    [download]
    then why not try inserting the server_id yourself?
    
# lookup the server id
my $server_id = LookupServerID($srv);

$perms_db->Sql ("INSERT INTO $drives_tbl (server_id,drv_letter,total_d
+isk_size,free_space_remaining) VALUES ( $server_id, '$drv_letter', $t
+otal_disk_size,$free_space_remaining)");

    [download]
    and code up the appropriate lookup call... 
    sub LookupServerID
{
    my $serverName = shift;
    my $sql = "select server_id from server_tbl where server_name = '$
+serverName'"

    #  run the sql through your $perms_db and return the id
    #  you'll probably want to cache these values later...
}

    [download]

    Hope this helps.
[reply]
[d/l]
[select]
      Dear Brother LanceDeeply
      $srv_id = LookupServerID($srv) bit doesn't work "error : Undefined subroutine &main::LookupServerID called at C:\Perl\odbctest.pl line 12.",...I am sure one all debugged will do the job, but at the moment I can't progress any further.
      Any Idea why LookupServerID doesn't work. And is there away (function) that each time I insert a new entry into the servers table will return then index key?
      Many Thanks for you help in advance (please remember: an example -might take you a couple of minutes - but it can go along way for the unworthy and the needy -weeks and sometime months;-)
[reply]
      Ah...there was a server ID lookup call, VERY SORRY,
      Prolonged hours of starring at the PC monitor has impaired my vision, temporarily.
[reply]
      This bit did not work?
      my $sql = "select server_id from server_tbl where server_name = '$serverName'"
      Can someone inform me why please?
[reply]
        blackadder-

        what was the error message you received?
        if you're having trouble coding up it up, here's a shot at it: 
        sub LookupServerID
{
    # get the servername that was passed into the function
    my $serverName = shift;

    # build the sql to select the server_id
    my $sql = "select server_id from server_tbl where server_name = '$
+serverName'"

    # execute the sql with the open $perms_db object
    if ($perms_db->Sql($sql))
    {
        # error during sql!
        print "Error:" . $perms_db->Error() . "\n";

        # return a value anyway and let the caller check for -1 
        return -1;
    }

    # process the result set
    $perms_db->FetchRow();

    # get the first row within the result set
    my(%data) = $perms_db->DataHash();

    # return the server_id to the caller
    return $data{'server_id'};

    # PLEASE NOTE:  this code does not watch out for the case where th
+e server_name does not exist.  you should code for this case.
}

        [download]

        You should really take a look at this page for a good tutorial for Win32::ODBC.
        There's a couple of examples there from the guys who wrote the module.

        OK there champ- take another whack at it.
[reply]
[d/l]
Re: Inserting values into MS Access sub tables
by sedhed (Scribe) on Jul 08, 2002 at 18:23 UTC

    Please correct me if I'm misunderstanding your question. Also, I'm not familiar with Win32::ODBC, I use DBI. But this is more of a database design thing, not database or connection specific.

    While many database systems do have the functionality to specify tables' relationships, one relies on that functionality only to enforce referential integrity on a DBMS level. Your code still needs to account for those same relationships.

    To that end, one generally places a corresponding key field in each table, so that the tables can be JOINed in statements. So your servers_tbl already has an key field, which is a unique primary key. So far so good. Put a corresponding field in your drives table, called 'server_id' or something, and use that field to join on.

    Your basic data would look something like so.... 

    servers_tbl:                drives_tbl:
_ID____NAME_____            _LETTER__SERVERID____FOO___
1     SomeServer            C        2          blahblah   
2     OtherServer           D        2          ahblahbla
956   YetAnotherServer      C        956        foobar
    And a select to get all of OtherServer's drives might look something like: 
    SELECT
   drives_tbl.LETTER,
   drives_tbl.FOO,
   servers_tbl.NAME
FROM
   servers_tbl
   LEFT OUTER JOIN drives_tbl
     on servers_tbl.ID = drives_tbl.SERVERID
WHERE
   servers_tbl.NAME = "OtherServer"

    [download]
    Hope this helps.
[reply]
[d/l]

Back to Seekers of Perl Wisdom