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 9 8060
"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
"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.
"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
"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" <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.
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
"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
"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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Roger Withnell |
last post by:
I'm inserting a new record into an MS SQL database table
and I want to obtain the new records autonumber
immediately afterwards, as follows:
MadminRS.CursorLocation = adUseServer...
|
by: baskar |
last post by:
Hi,
I have a table with 90 fields. When I try to use the recordset with
addnew and set all the 90 fields and then update the record set it
fails the following error seen in the log,
SQL0117N...
|
by: dixie |
last post by:
I have some code that adds new records into a table for each ID in a list
box when a button on a form is clicked. This works fine. My problem now is
that I wish to be able to edit all the records...
|
by: Trevor Best |
last post by:
I don't know if this has been reported before but it appears to be a
bug with Access.
If I create two tables both with an identity column then create an
insert trigger on table1 that inserts a...
|
by: Omavlana Omav |
last post by:
Hi,
I have created a datatable, dataadapter, added the table to a dataset
and filling the table.
Here I want to add an identity column to the above datatable and Later I
will use this...
|
by: Leonardo |
last post by:
Hi. I'm trying to build my first application with database access using VB
2005. I'm a VB 6 programmer and learning everything again has been
challenging. I managed to write a code using some tips...
|
by: pb648174 |
last post by:
In a multi-user environment, I would like to get a list of Ids
generated, similar to:
declare @LastId int
select @LastId = Max(Id) From TableMania
INSERT INTO TableMania (ColumnA, ColumnB)...
|
by: Jan van Veldhuizen |
last post by:
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...
|
by: stegze |
last post by:
Hi All,
I have a problem with a DB2 server of my customer. It is a Debian
Linux running DB2 Express-C. I have an IDENTITY field as PK in a table
and I use this value as FK in another table. Two...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |