Re: ADO Addnew and identity columns

"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 Received on Thu Oct 07 2004 - 10:55:20 CDT