ADO Addnew and identity columns

[Jan van Veldhuizen]

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

[Hans]

"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message news:<41***********************@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'.

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]

"Hans" <fo******@gmail.com> wrote in message
news:bd**************************@posting.google.c om...

"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
news:<41***********************@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.

[Jim Kennedy]

"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
news:41***********************@news.xs4all.nl...

"Hans" <fo******@gmail.com> wrote in message
news:bd**************************@posting.google.c om...

"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
news:<41***********************@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

[Hans]

"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message news:<41***********************@news.xs4all.nl>...

"Hans" <fo******@gmail.com> wrote in message
news:bd**************************@posting.google.c om...

"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
news:<41***********************@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

[Jan van Veldhuizen]

"Jim Kennedy" <ke****************************@attbi.net> wrote in message
news:JEb9d.216386$3l3.87609@attbi_s03...

"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
news:41***********************@news.xs4all.nl...

"Hans" <fo******@gmail.com> wrote in message
news:bd**************************@posting.google.c om...

> "Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
> news:<41***********************@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.

[Jan van Veldhuizen]

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" <fo******@gmail.com> wrote in message
news:bd**************************@posting.google.c om...

"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message

news:<41***********************@news.xs4all.nl>...

"Hans" <fo******@gmail.com> wrote in message
news:bd**************************@posting.google.c om...

"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
news:<41***********************@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

[Jim Kennedy]

"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
news:41***********************@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" <fo******@gmail.com> wrote in message
news:bd**************************@posting.google.c om...

"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message news:<41***********************@news.xs4all.nl>...

"Hans" <fo******@gmail.com> wrote in message
news:bd**************************@posting.google.c om...
> "Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
> news:<41***********************@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

[Jim Kennedy]

"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
news:41***********************@news.xs4all.nl...

"Jim Kennedy" <ke****************************@attbi.net> wrote in message
news:JEb9d.216386$3l3.87609@attbi_s03...

"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
news:41***********************@news.xs4all.nl...

"Hans" <fo******@gmail.com> wrote in message
news:bd**************************@posting.google.c om...
> "Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
> news:<41***********************@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

[Jan van Veldhuizen]

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/w...0115/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" <ke****************************@attbi.net> wrote in message
news:I00ad.219970$D%.30457@attbi_s51...

"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
news:41***********************@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" <fo******@gmail.com> wrote in message
news:bd**************************@posting.google.c om...

"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message

news:<41***********************@news.xs4all.nl>...

> "Hans" <fo******@gmail.com> wrote in message
> news:bd**************************@posting.google.c om...
> > "Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
> > news:<41***********************@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