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]

# 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]

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]

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

Ah...there was a server ID lookup call, VERY SORRY,
Prolonged hours of starring at the PC monitor has impaired my vision, temporarily.

This bit did not work?
my $sql = "select server_id from server_tbl where server_name = '$serverName'"
Can someone inform me why please?

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.

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

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]