SQL Anywhere Mobile - Oracle Lite to IAnywhere migration

Author

Oracle Lite to IAnywhere migration

Tim

2005-07-19, 8:25 pm

We are implementing a migration from Oracle Lite 10g to iAnywhere. The
business and data access layers I'm working with use Oracle's sequences and
Oracle's automatic partitioning to deal with primary keys. This means that I
can grab the next ID and store it in my Save method when I insert a record
with children. This way it is easy to populate the foreign key values in the
child tables.

Now, with the ASA db on the client (and Oracle still on the back end), we
have chosen the GlobalAutoincrement method of remote pri key handling. It is
my (possibly incomplete) understanding that this works by configuring the
pri key (ID) column to GlobalAutoincrement and doing the insert without a
value for the ID. To re-use existing code, it would be nice to be able to
get the value of the next primary key so I can handle it in the same manner.

Is this possible?

If not, what is the recommended way to populate the foreign key values of
the child tables on an insert?

Tim

Shuchit

2005-07-19, 8:25 pm

> If not, what is the recommended way to populate the foreign key values
> of the child tables on an insert?
>

You can use @@IDENTITY variable to get you the value of the most recent
insert using autoincrement or global autoincrement.
So you could
1)insert into the parent table
2) use @@IDENTITY to get the new primary key
3) use this to insert into the child tables.

Shuchit

Breck Carter [TeamSybase]

2005-07-19, 8:25 pm

Welcome to SQL Anywhere! You're gonna have some fun. (Especially if
you buy my book... see bottom of message :)

Have a look at the GET_IDENTITY function. Here is a demo you can run
in dbisql...

SET OPTION PUBLIC.GLOBAL_DATABASE_ID = '2';
CREATE TABLE x (
pkey BIGINT NOT NULL DEFAULT GLOBAL AUTOINCREMENT ( 10000000 ),
data1 INTEGER NOT NULL,
PRIMARY KEY ( pkey ) );
INSERT x ( data1 ) VALUES ( 1 );
COMMIT;
SELECT * FROM x;

-- pkey,data1
-- 20000001,1

BEGIN
DECLARE @pkey BIGINT;
SET @pkey = GET_IDENTITY ( 'x' );
INSERT x VALUES ( @pkey, 2 );
COMMIT;
SELECT * FROM x;
END;

-- pkey,data1
-- 20000001,1
-- 20000002,2

Breck

On 19 Jul 2005 13:25:15 -0700, "Tim" <nospam@nospam.com> wrote:

>We are implementing a migration from Oracle Lite 10g to iAnywhere. The
>business and data access layers I'm working with use Oracle's sequences and
>Oracle's automatic partitioning to deal with primary keys. This means that I
>can grab the next ID and store it in my Save method when I insert a record
>with children. This way it is easy to populate the foreign key values in the
>child tables.
>
>Now, with the ASA db on the client (and Oracle still on the back end), we
>have chosen the GlobalAutoincrement method of remote pri key handling. It is
>my (possibly incomplete) understanding that this works by configuring the
>pri key (ID) column to GlobalAutoincrement and doing the insert without a
>value for the ID. To re-use existing code, it would be nice to be able to
>get the value of the next primary key so I can handle it in the same manner.
>
>Is this possible?
>
>If not, what is the recommended way to populate the foreign key values of
>the child tables on an insert?
>
>Tim
>
>
>
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Sponsored Links