Re: ADO Addnew and identity columns

"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'.

Decent discussion around this is available in Thomas Kyte's "Expert One on One Oracle" book.

HTH
/Hans

BTW: newsgroup comp.databases.oracle is officially defunct and fewer ISPs are carrying it. The replacement is the heirarchy 'comp.databases.oracle.*' as discussed at http://orafaq.com Received on Wed Oct 06 2004 - 22:02:57 CEST