Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Server Data on Insert property

Re: Server Data on Insert property

From: Jan van Veldhuizen <jan_at_van-veldhuizen.nl>
Date: Tue, 12 Oct 2004 20:14:46 +0200
Message-ID: <416c1e4a$0$78749$e4fe514c@news.xs4all.nl>


And the fun is:

there's only *ONE* difference with the SqlServer behaviour here: SqlServer requires a ClientSide cursor for this functionality. So I still am able to make minor changes to my source to let it work with both databases.

"Jan van Veldhuizen" <jan_at_van-veldhuizen.nl> wrote in message news:416be207$0$25965$e4fe514c_at_news.xs4all.nl...
>I found the solution myself:
>
> The connection.cursorlocation should be adUseServer and you must use
> adOpenKeyset at the Open method.
>
> So this is working:
>
> Dim mConn As New ADODB.Connection
> Dim rs As New ADODB.Recordset
>
> mConn.ConnectionString = "Provider=OraOLEDB.Oracle;User
> ID=jan;Password=test;Data Source=test;
> mConn.CursorLocation = adUseServer
> mConn.Open
>
> rs.Open "select * from test where id < -1", mConn, adOpenKeyset,
> adLockOptimistic
> rs.AddNew "name", "newname"
> rs.Update
>
> MsgBox rs("id")
>
> mConn.Close
>
>
>
> "Jan van Veldhuizen" <jan_at_van-veldhuizen.nl> wrote in message
> news:416b9d6a$0$36861$e4fe514c_at_news.xs4all.nl...
>>I try to use the Server Data on Insert property to return a column value
>>swhich is set in a trigger.
>> But it does not work. Why not? What am I doing wrong?
>> (BTW I'm using Oracle version 10)
>>
>> My VB source is:
>>
>> Dim mConn As New ADODB.Connection
>> Dim rs As New ADODB.Recordset
>>
>> mConn.ConnectionString = "Provider=OraOLEDB.Oracle;User
>> ID=jan;Password=test;Data Source=test;"
>> mConn.Open
>>
>> rs.ActiveConnection = mConn
>> rs.CursorLocation = adUseServer
>> rs.Properties("Server Data on Insert").Value = True
>>
>> rs.Open "select * from test where id < -1", mConn, adOpenForwardOnly,
>> adLockOptimistic
>> rs.AddNew "name", "newname"
>> rs.Update
>>
>> MsgBox rs("id")
>>
>> mConn.Close
>>
>> The test table has an ID column which is filled in a trigger by getting a
>> sequence nextvalue.
>>
>> I am using this source because I use this way of insert all over the
>> application with a SqlServer database. The ID column in de Sql2000
>> database is an Identity column which immediately returns it's new value
>> after the AddNew.
>> I am looking for a way to make my application working at an Oracle
>> database without a lot of source modifications.
>> I found this article:
>> http://www.tju.cn/docs/odb10.1.0.2/win.101/b10115/using.htm where the
>> 'Server Data on Insert' property is described.
>>
>> If anybody wants to test it in his own database, this is the definition
>> of the table:
>>
>> CREATE TABLE Test (
>> id NUMBER,
>> name VARCHAR2(20)
>> );
>>
>> CREATE SEQUENCE seqTest;
>>
>> CREATE TRIGGER newTest
>> BEFORE INSERT ON Test
>> FOR EACH ROW
>> WHEN (NEW.id IS NULL)
>> BEGIN
>> SELECT seqTest.NEXTVAL INTO :new.id FROM DUAL;
>> END;
>>
>>
>
>
Received on Tue Oct 12 2004 - 13:14:46 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US