simon.proctor has asked for the wisdom of the Perl Monks concerning the following question:

I am having to use Access with Perl on a Win95 machine for my development work :/

As I code in ASP and Perl (though ASP by twisted arm and Perl by pure pleasure) I already have the solution to my problem in ASP. Namely, grabbing the last insert ID in Access. 

        Dim rs,last_ID
        Set rs = Server.CreateObject("ADODB.Recordset")
        rs.Open "NOTICE", db_connection, adOpenKeySet, adLockPessimist
+ic, adCmdTable
        rs.AddNew
        rs("title").Value = title
        rs("message").Value = message
        rs("date_raised").Value = Date &" "& Time
        rs("project_ID").Value = projectID
        rs("issuer_ID").Value = userID
        rs("notice_type").Value = ntype
        rs.Update
        last_ID = rs("ID")
        rs.Close

[download]


Where 'rs' is my recordset and 'last_ID' is the value of the column 'ID' in that row. In other words, the column 'ID' is an autonumber column and the recordset does (what appears to be) some db cursor wizardry to get it back.

Now I could just modify the database and use a program generated timestamp and then select it back out - but on reading the Perl ADO docs and the ODBC docs for Perl, I ge the impression I can use OLE to create my object and do it that way. However, having never done this before I was hoping someone could shed some light on the subject.

Interestingly, in my searches on Google and such, I came across this link:

http://msdn.microsoft.com/library/default.asp?URL=/library/en-us/dnclinic/html/scripting012299.asp

Which I found most interesting - especially at the bottom of the post :)

As I am advocating Perl strongly where I work, I want to be able to port this directly rather than performing a work around. Hope someone can help!

Replies are listed 'Best First'.
Re: A Question on ADO and a comment on Microsoft
by rchiav (Deacon) on Jul 20, 2001 at 16:16 UTC
    I'm not exactly sure what your question is. Is it how you take the above code and turn it into PerlScript? If so, you should read the activestate documentation. They have quite a few examples of how to write ASP with perlsript.

    You also haven't included and perl code, which makes it harder to figure out what exactly you're struggling with here.

    You don't have to use Win32::OLE with perlscript in the ASP environment. You can use the same methods you're using here. Like..

    <%@Language=PerlScript%>
<%
my $rs = Server->CreateObject('ADODB.Recordset');
%>

    [download]
    Beyond that, you're going to have to explain a little more about where you're struggling..

    Hope this helps..
    Rich

[reply]
[d/l]
      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!
[reply]
        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]

        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]

Back to Seekers of Perl Wisdom