Inserting data into tables (COMMIT/ROLLBACK)

[Dino M. Buljubasic]

I have to insert data into several data tables in my application and would
like to be able to commit / rollback changes. The simplified process is
like this:

1. Begin Transaction
2. Inserti into Table1
3. Get the ID of inserted record
4. Insert into Table2
5. Insert into Table3
etc.
6. commit or rollback

My problem is that all data table operations are treated as a transacton.
That means, I will not be able to get the record ID after first insert
because it is not commited yet. I guess uncommited records are kept in
memory, and therefore my application does not work properly.
What would be correct way to do this?

Any help will be appreciated.

 

[Bob Grommes]

Dino,

This hasn't been my experience. I have actually wrapped a couple dozen
tables in a transaction as you describe, and there is no problem getting the
PK back; the only (minor) problem is that if there is a rollback, the IDs
assigned to the inserted records are discarded and will never be used.

The scenario I discuss above is with SQL Server 2000 as the back-end. In
order to be of any help we'll need to know what your database is, and see a
sample of your code the does the transactions and insertions. Also, if
you're not using a managed provider that's part of the standard .NET
distribution we'll need to have some details about that as well.

You should also be sure to include code that illustrates how you are doing
(3) -- "getting the ID of the inserted record".

--Bob

 

[David Browne]

I have to insert data into several data tables in my application and would
like to be able to commit / rollback changes. The simplified process is
like this:

1. Begin Transaction
2. Inserti into Table1
3. Get the ID of inserted record
4. Insert into Table2
5. Insert into Table3
etc.
6. commit or rollback

My problem is that all data table operations are treated as a transacton.
That means, I will not be able to get the record ID after first insert
because it is not commited yet. I guess uncommited records are kept in
memory, and therefore my application does not work properly.
What would be correct way to do this?

Uncommited changed are always visible to the session which made the changes.

When trying to view rows containing uncommite changes from another session
you will either
-block until the changes are commited or rolled back (SQLServer)
or
-See the data as it was before the changes were made (Oracle)

So just make sure to make all the changes and select all the id's from the
same connection and you should be fine.


David

 

[Dino M. Buljubasic]

Thank you Bob,

I am also getting the PK now. My problem is that when my application adds a
record to the database, everything seams to be OK. I can find it using my
application as well as using the SQL Pane by writing a query.

However, if I right click the table name then go to 'Open Table', then to
'Return All Rows', some of the records added are not visible.
They are obviously in the table since my application finds them as well as
SQL Pane does.

Please find below, the specifics of our SQL Sever @@version Query.

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

and my columns are of several different data types such as int, date,
string, bit, byte (for images) etc.

Thank you

 

[Dino M. Buljubasic]

Thank you David,

I am also getting the PK now. My problem is that when my application adds a
record to the database, everything seams to be OK. I can find it using my
application as well as using the SQL Pane by writing a query.

However, if I right click the table name then go to 'Open Table', then to
'Return All Rows', some of the records added are not visible.
They are obviously in the table since my application finds them as well as
SQL Pane does.

Please find below, the specifics of our SQL Sever @@version Query.

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

and my columns are of several different data types such as int, date,
string, bit, byte (for images) etc.

Thank you

 

[Dino M. Buljubasic]

Thank you David,

I am also getting the PK now. My problem is that when my application adds a
record to the database, everything seams to be OK. I can find it using my
application as well as using the SQL Pane by writing a query.

However, if I right click the table name then go to 'Open Table', then to
'Return All Rows', some of the records added are not visible.
They are obviously in the table since my application finds them as well as
SQL Pane does.

Please find below, the specifics of our SQL Sever @@version Query.

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

and my columns are of several different data types such as int, date,
string, bit, byte (for images) etc.

Thank you