in reply to Re: A Question on ADO and a comment on Microsoft
in thread A Question on ADO and a comment on Microsoft

Sorry if I was a little obscure:

I am writing a proper Perl Script which is executed in a cgi-bin style directory under PWS. I am using the Template::Toolkit and the DBI for my functionality. I am using Access as a database which is connected to via an ODBC connection.

My problem is that I have to do multiple inserts into an Access database but all the inserts after the first insert utilise the ID column from that insert (ie a Foreign Key style relationship). Thus I need to get the last inserted ID from the first insert call and use it in all the remaining calls.

My options, that I can see, are to use a timestamp or to use the ADO along the lines I indicated in my ASP snippet.

I included the ASP snippet purely as an indicator that it is possible to do using a recordset object but I do not know how to build one in Perl.

My searches have only indicated that I can use Win32::OLE.

I should reiterate that PerlScript is not an option for a variety of reasons. Firstly, if I change my SQL I can plop it on a Unix server without changing a single line of code other than the various DBI calls. I will want to get my current program to work under Apache at some point in the near future :)

Secondly, there was no option to install PerlScript when I installed the latest MSI version of ActivePerl and to be frank - due to portability I have no intention of ever using it.

As my original post said - I only use ASP if someone twists my arm - and then I use VBScript!!

I don't think I can provide more info - theres no Perl code as if I could provide the Perl code I wouldn't be asking the question in the first place!! :) oh well!
  • Comment on Re: Re: A Question on ADO and a comment on Microsoft

Replies are listed 'Best First'.
Re: Re: Re: A Question on ADO and a comment on Microsoft
by rchiav (Deacon) on Jul 20, 2001 at 21:17 UTC
    As for doing this strictly with SQL, I talked to a couple people and they cringed. The information they gave me is that without using ADO, @@IDENTITY isn't set when you're interfacing MS Access. What they actually do is requery for the record they just inserted and pull the number that way.

    As far as using ADO, you're going to make something just as unportable as writing ASP. But if you want to try it, you will infact use Win32::OLE. Example.. 

    
use Win32::OLE;

my $rs = new Win32::OLE("ADODB.Recordset");

# you're going to have to define your consts that
# you're using in the vbscript example
$rs.Open('NOTICE', etc.. );
$rs->AddNew();
$rs->{'title'}{'Value'} = $somevalue;
<....>
$rs->Update();
my $last_id = $rs->{'ID'}{'Value'};

    [download]
    written off the cuff so knowing me there's errors, but that's the basic usage under Win32::OLE.

    Hope this helps..
    Rich

[reply]
[d/l]
      Thanks for all your help guys

      I'll be trying this ASAP
[reply]
      The portabilit issue s certainly true :). But my specific code is in one function in one file and I know about it and I know how to replace it once I use better RDBMS like MySQL or SQLServer.

      Its sad to make this kind of tradeoff though :P

      BTW - apologies for the double post. This thread seems to be cursed. Guess its the Microsoft link. . . . yeah thats it . . . . /me runs and hides
[reply]
Re: Re: Re: A Question on ADO and a comment on Microsoft
by thpfft (Chaplain) on Jul 20, 2001 at 17:00 UTC

    Easy with mysql:

    $dbh->do('insert mumble');
my $id = $dbh->{'mysql_insertid'};

    [download]

    a bit harder with sql server through odbc:

    $dbh->do("set nocount on"); # so it works on SQL Server 6.5 too
my $sth = $dbh->prepare(
    "insert mumble
    SELECT @@IDENTITY ID");
$sth->execute;
my $id = $sth->fetchrow_arrayref->[0];
$sth->finish;
$dbh->do("set nocount off");

    [download]

    You may find that access has enough in common with sql server to accept this syntax. I couldn't find any conclusive information either way.

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom