Re: ADO Addnew and identity columns

Jim,

You're still recommending the 'returning clause'. I do not know how to use that in an Addnew method.
There's no place to specify the insert-statement that is built-in into that method.
It's not a .Net project, but VB6. (The ADO.Net data provider allows you to customize all sql command behind the insert, update and delete actions)

However, finally I found something which points me in the right direction: http://www.tju.cn/docs/odb10.1.0.2/win.101/b10115/using.htm

Scroll to the paragraph about the 'Server Data on Insert Property'. I think that's exactly what I need.
Unfortunetaly the machine with my oracle db crashed. I am going to reinstall everything tomorrow.
In the mean time I am going to look for code examples using this property.

Jan

"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message news:I00ad.219970$D%.30457_at_attbi_s51...
>
> "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 Sun Oct 10 2004 - 09:24:59 CDT