From: kennedy-downwithspammersfamily@attbi.net   
      
   "Jan van Veldhuizen"  wrote in message   
   news:4166609a$0$37789$e4fe514c@news.xs4all.nl...   
   >   
   > "Jim Kennedy"  wrote in message   
   > news:JEb9d.216386$3l3.87609@attbi_s03...   
   > >   
   > > "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   
   > >   
   > >   
   > The returning clause is used with the INSERT statement. I use the ADO   
   Addnew   
   > function.   
   >   
   >   
   Add new is an insert statement; it is just a proprietary API to do an   
   insert.   
   Jim   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)