XPost: comp.database.oracle, comp.databases.oracle.server, relco   
   .comp.dbms.oracle   
   From: kennedy-downwithspammersfamily@attbi.net   
      
   "Jan van Veldhuizen"  wrote in message   
   news:4168530a$0$65124$e4fe514c@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"  wrote in message   
   > news:bd0e88c6.0410070755.352fbb3e@posting.google.com...   
   > > "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.   
   > >   
   > > 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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)