XPost: comp.database.oracle, relcom.comp.dbms.oracle   
   From: kennedy-downwithspammersfamily@attbi.net   
      
   "Jan van Veldhuizen"  wrote in message   
   news:4164f533$0$78753$e4fe514c@news.xs4all.nl...   
   >   
   > "Hans"  wrote in message   
   > news:bd0e88c6.0410061202.250cb86b@posting.google.com...   
   > > "Jan van Veldhuizen"  wrote in message   
   > > news:<4163d817$0$78279$e4fe514c@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.   
   >   
   >   
   Use the returning clause to get the values back out.   
   Jim   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)