ADO Addnew and identity columns

**Hans** · Jun 27 '08, 06:48 PM

Re: ADO Addnew and identity columns

"Jan van Veldhuizen" <jan@van-veldhuizen.nlwr ote in message news:<4163d817$ 0$78279$e4fe514 c@news.xs4all.n l>...

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.Connectio n
Dim rs As New ADODB.Recordset
>
mConn.Connectio nString = "Provider=OraOL EDB.Oracle;User
ID=user;Passwor d=pwd;Data Source=dbname;"
mConn.Open
>
rs.Open "select * from testidentity where id < -1", mConn,
adOpenForwardOn ly, adLockPessimist ic
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

**Jan van Veldhuizen** · Jun 27 '08, 06:48 PM

Re: ADO Addnew and identity columns

"Hans" <forbrich@gmail .comwrote in message
news:bd0e88c6.0 410061202.250cb 86b@posting.goo gle.com...

"Jan van Veldhuizen" <jan@van-veldhuizen.nlwr ote in message
news:<4163d817$ 0$78279$e4fe514 c@news.xs4all.n l>...

>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.Connectio n
>Dim rs As New ADODB.Recordset
>>
>mConn.Connecti onString = "Provider=OraOL EDB.Oracle;User
>ID=user;Passwo rd=pwd;Data Source=dbname;"
>mConn.Open
>>
>rs.Open "select * from testidentity where id < -1", mConn,
>adOpenForwardO nly, adLockPessimist ic
>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.

**Jim Kennedy** · Jun 27 '08, 06:48 PM

Re: ADO Addnew and identity columns

"Jan van Veldhuizen" <jan@van-veldhuizen.nlwr ote in message
news:4164f533$0 $78753$e4fe514c @news.xs4all.nl ...

>
"Hans" <forbrich@gmail .comwrote in message
news:bd0e88c6.0 410061202.250cb 86b@posting.goo gle.com...

"Jan van Veldhuizen" <jan@van-veldhuizen.nlwr ote in message
news:<4163d817$ 0$78279$e4fe514 c@news.xs4all.n l>...

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.Connectio n
Dim rs As New ADODB.Recordset
>
mConn.Connectio nString = "Provider=OraOL EDB.Oracle;User
ID=user;Passwor d=pwd;Data Source=dbname;"
mConn.Open
>
rs.Open "select * from testidentity where id < -1", mConn,
adOpenForwardOn ly, adLockPessimist ic
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

**Hans** · Jun 27 '08, 06:48 PM

Re: ADO Addnew and identity columns

"Jan van Veldhuizen" <jan@van-veldhuizen.nlwr ote in message news:<4164f533$ 0$78753$e4fe514 c@news.xs4all.n l>...

"Hans" <forbrich@gmail .comwrote in message
news:bd0e88c6.0 410061202.250cb 86b@posting.goo gle.com...

"Jan van Veldhuizen" <jan@van-veldhuizen.nlwr ote in message
news:<4163d817$ 0$78279$e4fe514 c@news.xs4all.n l>...

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.Connectio n
Dim rs As New ADODB.Recordset
>
mConn.Connectio nString = "Provider=OraOL EDB.Oracle;User
ID=user;Passwor d=pwd;Data Source=dbname;"
mConn.Open
>
rs.Open "select * from testidentity where id < -1", mConn,
adOpenForwardOn ly, adLockPessimist ic
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.o racle (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

**Jan van Veldhuizen** · Jun 27 '08, 06:48 PM

Re: ADO Addnew and identity columns

"Jim Kennedy" <kennedy-downwithspammer sfamily@attbi.n etwrote in message
news:JEb9d.2163 86$3l3.87609@at tbi_s03...

>
"Jan van Veldhuizen" <jan@van-veldhuizen.nlwr ote in message
news:4164f533$0 $78753$e4fe514c @news.xs4all.nl ...

>>
>"Hans" <forbrich@gmail .comwrote in message
>news:bd0e88c6. 0410061202.250c b86b@posting.go ogle.com...

"Jan van Veldhuizen" <jan@van-veldhuizen.nlwr ote in message
news:<4163d817$ 0$78279$e4fe514 c@news.xs4all.n l>...
>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.Connectio n
>Dim rs As New ADODB.Recordset
>>
>mConn.Connecti onString = "Provider=OraOL EDB.Oracle;User
>ID=user;Passwo rd=pwd;Data Source=dbname;"
>mConn.Open
>>
>rs.Open "select * from testidentity where id < -1", mConn,
>adOpenForwardO nly, adLockPessimist ic
>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.

**Jan van Veldhuizen** · Jun 27 '08, 06:48 PM

Re: ADO Addnew and identity columns

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" <forbrich@gmail .comwrote in message
news:bd0e88c6.0 410070755.352fb b3e@posting.goo gle.com...

"Jan van Veldhuizen" <jan@van-veldhuizen.nlwr ote in message

news:<4164f533$ 0$78753$e4fe514 c@news.xs4all.n l>...

"Hans" <forbrich@gmail .comwrote in message
news:bd0e88c6.0 410061202.250cb 86b@posting.goo gle.com...

"Jan van Veldhuizen" <jan@van-veldhuizen.nlwr ote in message
news:<4163d817$ 0$78279$e4fe514 c@news.xs4all.n l>...
>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.Connectio n
>Dim rs As New ADODB.Recordset
>>
>mConn.Connecti onString = "Provider=OraOL EDB.Oracle;User
>ID=user;Passwo rd=pwd;Data Source=dbname;"
>mConn.Open
>>
>rs.Open "select * from testidentity where id < -1", mConn,
>adOpenForwardO nly, adLockPessimist ic
>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.o racle (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

**Jim Kennedy** · Jun 27 '08, 06:48 PM

Re: ADO Addnew and identity columns

"Jan van Veldhuizen" <jan@van-veldhuizen.nlwr ote 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" <forbrich@gmail .comwrote in message
news:bd0e88c6.0 410070755.352fb b3e@posting.goo gle.com...

"Jan van Veldhuizen" <jan@van-veldhuizen.nlwr ote in message

news:<4164f533$ 0$78753$e4fe514 c@news.xs4all.n l>...

"Hans" <forbrich@gmail .comwrote in message
news:bd0e88c6.0 410061202.250cb 86b@posting.goo gle.com...
"Jan van Veldhuizen" <jan@van-veldhuizen.nlwr ote in message
news:<4163d817$ 0$78279$e4fe514 c@news.xs4all.n l>...
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.Connectio n
Dim rs As New ADODB.Recordset
>
mConn.Connectio nString = "Provider=OraOL EDB.Oracle;User
ID=user;Passwor d=pwd;Data Source=dbname;"
mConn.Open
>
rs.Open "select * from testidentity where id < -1", mConn,
adOpenForwardOn ly, adLockPessimist ic
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.o racle (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

**Jim Kennedy** · Jun 27 '08, 06:48 PM

Re: ADO Addnew and identity columns

"Jan van Veldhuizen" <jan@van-veldhuizen.nlwr ote in message
news:4166609a$0 $37789$e4fe514c @news.xs4all.nl ...

>
"Jim Kennedy" <kennedy-downwithspammer sfamily@attbi.n etwrote in message
news:JEb9d.2163 86$3l3.87609@at tbi_s03...

"Jan van Veldhuizen" <jan@van-veldhuizen.nlwr ote in message
news:4164f533$0 $78753$e4fe514c @news.xs4all.nl ...

>
"Hans" <forbrich@gmail .comwrote in message
news:bd0e88c6.0 410061202.250cb 86b@posting.goo gle.com...
"Jan van Veldhuizen" <jan@van-veldhuizen.nlwr ote in message
news:<4163d817$ 0$78279$e4fe514 c@news.xs4all.n l>...
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.Connectio n
Dim rs As New ADODB.Recordset
>
mConn.Connectio nString = "Provider=OraOL EDB.Oracle;User
ID=user;Passwor d=pwd;Data Source=dbname;"
mConn.Open
>
rs.Open "select * from testidentity where id < -1", mConn,
adOpenForwardOn ly, adLockPessimist ic
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

**Jan van Veldhuizen** · Jun 27 '08, 06:49 PM

Re: ADO Addnew and identity columns

Jim,

You're still recommending the 'returning clause'. I do not know how to use
that in an Addnew method.
There's no place to specify the insert-statement that is built-in into that
method.
It's not a .Net project, but VB6. (The ADO.Net data provider allows you to
customize all sql command behind the insert, update and delete actions)

However, finally I found something which points me in the right direction:

http://www.tju.cn/docs/odb10.1.0.2/win.101/b10115/using.htm

Scroll to the paragraph about the 'Server Data on Insert Property'. I think
that's exactly what I need.
Unfortunetaly the machine with my oracle db crashed. I am going to reinstall
everything tomorrow.
In the mean time I am going to look for code examples using this property.

Jan

"Jim Kennedy" <kennedy-downwithspammer sfamily@attbi.n etwrote in message
news:I00ad.2199 70$D%.30457@att bi_s51...

>
"Jan van Veldhuizen" <jan@van-veldhuizen.nlwr ote 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" <forbrich@gmail .comwrote in message
news:bd0e88c6.0 410070755.352fb b3e@posting.goo gle.com...

"Jan van Veldhuizen" <jan@van-veldhuizen.nlwr ote in message

news:<4164f533$ 0$78753$e4fe514 c@news.xs4all.n l>...

"Hans" <forbrich@gmail .comwrote in message
news:bd0e88c6.0 410061202.250cb 86b@posting.goo gle.com...
"Jan van Veldhuizen" <jan@van-veldhuizen.nlwr ote in message
news:<4163d817$ 0$78279$e4fe514 c@news.xs4all.n l>...
>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.Connectio n
>Dim rs As New ADODB.Recordset
>>
>mConn.Connecti onString = "Provider=OraOL EDB.Oracle;User
>ID=user;Passwo rd=pwd;Data Source=dbname;"
>mConn.Open
>>
>rs.Open "select * from testidentity where id < -1", mConn,
>adOpenForwardO nly, adLockPessimist ic
>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.o racle (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
>
>

ADO Addnew and identity columns

ADO Addnew and identity columns

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment