Freddy,
I believe that using a stored procedure might be the way to go with this.
Should I use a Procedure or a Function though?
I was thinking that if I used a function, I could call it like this in a <CFQUERY>
statement:
<cfquery name="qryPK" datasource = "DSN">
SELECT
Add_Record( "My Name" ) AS PrimaryKey
FROM
dual
</cfquery>
Or would using a procedure with IN OUT variables be the better route?
Thanks,
Troy
freddy wrote:
> You could try:
> <cftransaction action="BEGIN">
> <cfquery name="qryCurrval" datasource = "DSN">
> SELECT
> tbl_seq.nextval as tbl_currval
> FROM
> dual
> </cfquery>
>
> <cfquery name="qryInsert" datasource="DSN">
> Insert Into tbl( name )
> values( 'My Name' )
> </cfquery>
> </cftransaction>
>
> I am not sure if this would work but it seems that it would get the nextval prior to
>it being assingned using the
> trigger.
>
> Alternately couldn't you also use a stored procedure to insert the the name and
>return the curval?
>
> Thanks,
> Frederic
>
> Troy Simpson wrote:
>
> > Dave,
> >
> > Thanks for bring the CURRVAL pseudocolumn to light.
> > I forgot to mention this part.
> > I used the NEXTVAL pseudocolumn in the first <CFQUERY> INSERT statement as
> > follows:
> >
> > <cfquery name="qryInsert" datasource="DSN">
> > Insert Into tbl( name )
> > values( 'My Name' )
> > </cfquery>
> >
> > I then use another <CFQUERY> to get the value in the CURRVAL pseudocolumn as
> > follow <cfquery name="qryCurrval" datasource = "DSN">
> > SELECT
> > tbl_seq.currval as tbl_currval
> > FROM
> > dual
> > </cfquery>s:
> >
> >
> > I get the following Oracle error:
> >
> > ORA-08002: sequence TBL_SEQ.CURRVAL is not yet defined in this session
> >
> > According to the documentation it say this (Pay close attention to the last
> > sentence):
> >
> > When you create a sequence, you can define its initial value and the
> > increment between its values. The first reference to NEXTVAL returns the
> > sequence's initial value. Subsequent references to NEXTVAL increment the
> > sequence value by the defined increment and return the new value. Any
> > reference to CURRVAL always returns the sequence's current value, which is
> > the value returned by the last reference to NEXTVAL. Note that before you
> > use CURRVAL for a sequence in your session, you must first initialize the
> > sequence with NEXTVAL.
> >
> > According to the last sentence, I can only assume that the reason CURRVAL is
> > not available is because each <CFQUERY> statement creates a new Oracle
> > Session. Therefore CURRVAL is never available. Also since it is possible
> > that ColdFusion maintains persistant connects for all web requests, this
> > might introduce problems.
> >
> > I thought that I might be able to put two statements into one <CFQUERY>.
> > I have not tried this, but I would assume that it is not possible.
> >
> > Any other ideas?
> > Thanks for your input.
> >
> > Sincerely,
> > Troy
> >
> > Dave Carabetta wrote:
> >
> > > Correct me if I'm wrong, but you should be able to get the current value
> > > by using currval. For example, from your post, your sequence name is
> > > tbl_seq. Therefore, you would just type "tbl_seq.currval" to get the
> > > current value, which should also be the max id value. This is one of
> > > those really nice feature of Oracle over SQL Server.
> > >
> > > Hope this helps,
> > > Dave.
> > >
> > > -----Original Message-----
> > > From: Troy Simpson [mailto:[EMAIL PROTECTED]]
> > > Sent: Thursday, December 06, 2001 06:12 PM
> > > To: CF-Talk
> > > Subject: Get PK value from Oracle table with AutoNumber Trigger (tds)?
> > >
> > > All,
> > >
> > > I have read the the various postings about using the MAX() function to
> > > get the last value entered into the Primary Key field.
> > > This will not do for me as you will see.
> > > Plus I believe it is proned to errors.
> > >
> > > I have a table that is defined as follows:
> > > create table tbl ( pk number(11) not null primary key, name varchar2(20)
> > > )
> > >
> > > I have created a sequences as follows:
> > > create sequence tbl_seq;
> > >
> > > The table has a trigger on the PK column for autonumbering and it is
> > > defined as follows:
> > > create trigger tbl_autonumber before insert on tbl for each row
> > > begin
> > > select tbl_seq.nextval into :NEW.pk from dual;
> > > end;
> > >
> > > After four people have inserted four seperate new records at the same
> > > time, how can I find out the Primary Key that was assigned to my new
> > > record?
> > >
> > > Here is a possible solution that I was thinking about but not sure if it
> > > is the optimal solution.
> > > Add an additional column to the table and use it as a stamp.
> > > When I insert the new record I would put a unique number into this
> > > column so that I can find the record in my next SELECT query.
> > >
> > > Ideally, I would like the INSERT statement to return the Primary Key
> > > value to me.
> > > Maybe an Oracle procedure or Oracle function would be the way to go.
> > >
> > > Sincerely,
> > > Troy
> > >
> > > --
> > > Troy Simpson | North Carolina State University
> > > NCSU Libraries | Campus Box 7111 | Raleigh | North Carolina
> > > ph.919.515.3855 | fax.919.513.3330
> > >
> > > It is better to be hated for what you are
> > > than to be loved for what you are not.
> > > -- Andre Gide
> > >
> > >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
