in reply to Inserting values into MS Access sub tables

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.

Replies are listed 'Best First'.
Re: Re: Inserting values into MS Access sub tables
by blackadder (Hermit) on Jul 09, 2002 at 11:43 UTC
    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]
Re: Re: Inserting values into MS Access sub tables
by blackadder (Hermit) on Jul 09, 2002 at 13:56 UTC
    Ah...there was a server ID lookup call, VERY SORRY,
    Prolonged hours of starring at the PC monitor has impaired my vision, temporarily.
[reply]
Re: Re: Inserting values into MS Access sub tables
by blackadder (Hermit) on Jul 09, 2002 at 15:25 UTC
    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]

In Section Seekers of Perl Wisdom