 | |
ADO Addnew and identity columns
comp.database.oracle comp.database.oracle
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
ADO Addnew and identity columns -
10-06-2004
, 06:31 AM
#2
| |||
| |||
|
|
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 |
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
#3
| |||
| |||
|
|
"Jan van Veldhuizen" <jan (AT) van-veldhuizen (DOT) nl> wrote in message news:<4163d817$0$78279$e4fe514c (AT) news (DOT) 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. |
Addnew function.
Problem is still that it is not returned in the recordset.
#4
| |||
| |||
|
|
"Hans" <forbrich (AT) gmail (DOT) com> wrote in message news:bd0e88c6.0410061202.250cb86b (AT) posting (DOT) google.com... "Jan van Veldhuizen" <jan (AT) van-veldhuizen (DOT) nl> wrote in message news:<4163d817$0$78279$e4fe514c (AT) news (DOT) 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. |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.