"Jan van Veldhuizen" <jan_at_van-veldhuizen.nl> wrote in message
news:4168530a$0$65124$e4fe514c_at_news.xs4all.nl...
> Hans,
>
> The problem is NOT the trigger.
> The insert trigger does properly increment the sequence and puts the value
> in the desired column.
> That's exactly what I want to be happen.
>
> The problem is: the ADO Recordset does not immediately return the new
value
> after the Addnew method.
> The same source runs perfectly with SqlServer, because then the Addnew
> really return the new identity value (as it is claad in MSSQL)
> I've seen a few postings about this issue in several forums, but no one
> seems te have found a solution....:-(
>
> "Hans" <forbrich_at_gmail.com> wrote in message
> news:bd0e88c6.0410070755.352fbb3e_at_posting.google.com...
> > "Jan van Veldhuizen" <jan_at_van-veldhuizen.nl> wrote in message
> news:<4164f533$0$78753$e4fe514c_at_news.xs4all.nl>...
> > > "Hans" <forbrich_at_gmail.com> wrote in message
> > > news:bd0e88c6.0410061202.250cb86b_at_posting.google.com...
> > > > "Jan van Veldhuizen" <jan_at_van-veldhuizen.nl> wrote in message
> > > > news:<4163d817$0$78279$e4fe514c_at_news.xs4all.nl>...
> > > >> I have an application which is running fine with MS SqlServer, but
it
> > > >> should
> > > >> be working with Oracle as weel.
> > > >> At a lot of places we rely upon the ADO Recordset to return
> incremented
> > > >> identity columns.
> > > >> Oralce however returns null or zero.
> > > >> How can this be fixed easily?
> > > >>
> > > >>
> > > >> Dim mConn As New ADODB.Connection
> > > >> Dim rs As New ADODB.Recordset
> > > >>
> > > >> mConn.ConnectionString = "Provider=OraOLEDB.Oracle;User
> > > >> ID=user;Password=pwd;Data Source=dbname;"
> > > >> mConn.Open
> > > >>
> > > >> rs.Open "select * from testidentity where id < -1", mConn,
> > > >> adOpenForwardOnly, adLockPessimistic
> > > >> rs.AddNew "name", "peter"
> > > >> rs.Update
> > > >> MsgBox rs("id")
> > > >>
> > > >> mConn.Close
> > > >
> > > > It can not easily be fixed - identity is not a SQL standard. It is
a
> > > > MS SQLServer peculiarity and you will need to 'port' to get this to
> > > > work right.
> > > >
> > > > The closest you come to a quick solution is to use Oracle's
> > > > 'sequences' which serve a similar purpose (handing out unique
numbers
> > > > serially) but are implemented quite differently. A sequence is
> > > > accessed as part of a SQL statement, generally as part of the select
> > > > list, using either CURR_VAL or NEXT_VAL 'methods'.
> > > >
> > > I already have implemented sequences and triggers in the database.
> > > I have seen this working because I looked into the database right
after
> the
> > > Addnew function.
> > > Problem is still that it is not returned in the recordset.
> >
> > 1) This should be discussed in comp.databases.oracle.server, not
> > comp.database.oracle (AFAIK, no charter, not an official group)
> > comp.databases.oracle (defunct - see http://orafaq.com)
> >
> > I have added cdo.server and hopefully we will get more people looking
> > at this. In your reply PLEASE remove the two bad groups from the
> > distribution.
> >
> > 2) I'm not sure I understand the problem. You say you have created
> > sequences and triggers - how are you using them? Suggest you post the
> > trigger code.
> >
> > /Hans
>
>
What about the returning clause?
Jim
Received on Sat Oct 09 2004 - 20:13:12 CDT
