SQL statment to insert if none exits and to obtain the primary key of a record with one visit!

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

Dear All,

Sorry about the above title!

I have this sql statement

$DB->Sql("INSERT INTO Hosts(Host_Name,SAN,location,Country) 
      VALUES ('$host',
              '$clean_data{$host}'SAN'}',
              '$clean_data{$host}{'location'}',
              '$clean_data{$host}{'country'}')");
[download]

Then I follow the above with this sql statement to obtain the primary key values of the Hosts record.

die "Sql Failed" . $DB->Error( ) . "\n" if ($DB->Sql
     ("SELECT Host_ID FROM Hosts WHERE Host_Name='$host'"));
        my $rec_indx;
        while ($DB->FetchRow())
        {
            my %data = $DB->DataHash();
            $rec_indx =  $data{"Host_ID"};
            print qq(index key is $rec_indx\n);
        }
[download]

And then with the following code and sql statment, I insert the primary key value into Apps record to create a relationship between the two (I have already created two blank corresponding tables in ms-access with one-to-many relationship between hosts and apps).

        for my $info (@{$clean_data{$host}{'applications'}}) 
        {
            unless ($seen{@{$info}}++)
            {            
                           $DB->Sql("INSERT INTO Apps  
                             (App_Name,Description,Host_ID) 
                              VALUES ('$$info[0]',
                                      '$$info[11]',
                                      '$rec_indx')");
            }
        }
[download]

My question is;
1- Is there away that I could do away with the second sql statement where I can insert the primary key from Hosts record, into the Apps record without having to revisit the host record again? Similar to this suggestion (node_id=463992).
2- How can I in the first sql statement check to see it the hosts record (i.e the hosts name) doesn’t exists before inserting it? So if it exists then I will run a UPDATE statement instead of instead of creating it?

I am using Win32::ODBC and ms-access.

Thanks very much

Blackadder

Comment on SQL statment to insert if none exits and to obtain the primary key of a record with one visit! Select or Download Code

Replies are listed 'Best First'.
Re: SQL statment to insert if none exits and to obtain the primary key of a record with one visit! by Transient (Hermit) on Jun 16, 2005 at 16:28 UTC
That's really more of an MSAccess question. I know in Oracle there is a MERGE statement, and (although I forget it now), a way to retrieve the last generated sequence. I'm not sure if there is anything similar in Access, though.	[reply]
Re^2: SQL statment to insert if none exits and to obtain the primary key of a record with one visit! by jfroebe (Parson) on Jun 16, 2005 at 16:35 UTC
Agreed. This is not a perl issue at all. The solution lies with how Access works. One dbms vendor neutral solution would be to: begin tran insert row if error due to duplicate key, then update if desired. commit or abort tran I mean, there are definitely ways to do this that are vendor neutral, but in some cases the vendor DBMS (MS Access) have better solutions. I don't know about Access other than the basics. Jason L. Froebe Team Sybase member No one has seen what you have seen, and until that happens, we're all going to think that you're nuts. - Jack O'Neil, Stargate SG-1	[reply]
Re^2: SQL statment to insert if none exits and to obtain the primary key of a record with one visit! by jhourcle (Prior) on Jun 16, 2005 at 21:05 UTC
Or, for those of us who had to do this sort of thing _before_ Oracle 9i, you had do some nasty things with stored procedures. For instance, when I had to deal with sitescope, it's idea of 'logging to a server' was a 0NF table of varchar2(255) fields. So I had a stored procedure to get things from that table to 3NF, which used a whole bunch of functions like: Read more... (1027 Bytes) so I could then do: Read more... (6 kB) (which I triggered from a perl script, that maintained an error log ... therefore this is just barely clinging to being on-topic)	[reply] [d/l] [select]
Use indexes, sequences by mugwumpjism (Hermit) on Jun 16, 2005 at 21:40 UTC
OK, there are two issues here. One is that you are denormalising your data by adding a surrogate ID. However it is probably too late for you to change this. One solution would be to use a sequence rather than an auto-increment column. There is a cargo cult that like functions that return the last insert ID - and indeed it may be possible to do this with access - but sequences are your friend. In that approach, you first query the database for the next number from the sequence generator (a very fast query) and then you can insert it. You can forgoe that step by putting the sequence expression directly in the insert query, but then you have the same problem - how do you return it? It turns out that you are using the table expecting the `host` column to be the real primary key. You can guard the table from having duplicate host records by adding a unique index; `CREATE UNIQUE INDEX HOST_IDX ON Apps (Host_ID);` It is possible to further constrain the table by saying that the ID must exist in the other table, this is called a "foreign key". You sound like you are ready to move onto the next stage with your database exploration. I can highly recommend SQLite if you don't need to continue to use the MS-Access front-end, or PostgreSQL if you still need something you can access via ODBC (I assuming MS-Access can access a real database - however I never tried that). $h=$ENV{HOME};my@q=split/\n\n/,`cat $h/.quotes`;$s="$h/." ."signature";$t=`cat $s`;print$t,"\n",$q[rand($#q)],"\n"; [download]	[reply] [d/l]