Gnosis Transactions are managed by calling stored procedures that exist in the SQL Server database. Data is maintained in the database by executing select, insert, update and delete SQL statements or by calling generated stored procedures that perform select, insert, update and delete operations. Here are some examples with Gnosis Transactions with comparisons to conventional short transactions occuring on the database server. Familiarity with Transact-SQL is useful in this section.

Gnosis Transaction Examples

Example #1. This example demonstrates the similarity of a Gnosis Transaction to the conventional database (short) transaction. Both transactions perform the same commands from begin to commit.

exec gnosis.transaction_begin 'Location Types'

Insert into [Location Type]
   ([Location Type ID], [Name])
      values (1, 'Country')
Insert into [Location Type]
   ([Location Type ID], [Name])
      values (2, 'City')
Insert into [Location Type]
   ([Location Type ID], [Name])
      values (3, 'Suburb')

Select ([Location Type ID], [Name] 
  from [Location Type]


exec gnosis.transaction_commit 'Location Types'

Example #2. This example shows a change in the middle of the transaction that is rolled back. The subsequent changes are then committed. This example uses the generated stored procedure interface for maintaining the Location data.

Begin Transaction [Locations]

-- Country
Insert into [Location]  ([Location ID], [Name],
         [Location Type ID], [Parent Location ID])
   values (1, 'New Zealand', 1, 1)

Update [Location]
   set [When Begin] = '7 Feb 1840'
   where [Location ID] = 1

Save Transaction [Country]


-- Cities
Insert into [Location]  ([Location ID], [Name],
         [Location Type ID], [Parent Location ID])
   values (2, 'Sydney', 1, 1)


Rollback Transaction [Country]


-- Cities
Insert into [Location]  ([Location ID], [Name],
         [Location Type ID], [Parent Location ID])
   values (2, 'Auckland', 1, 1)
Insert into [Location]  ([Location ID], [Name],
         [Location Type ID], [Parent Location ID])
   values (3, 'Hamilton', 1, 1)
Insert into [Location]  ([Location ID], [Name],
         [Location Type ID], [Parent Location ID])
   values (4, 'Wellington', 1, 1)
Insert into [Location]  ([Location ID], [Name],
         [Location Type ID], [Parent Location ID])
   values (5, 'Christchurch', 1, 1)
Insert into [Location]  ([Location ID], [Name],
         [Location Type ID], [Parent Location ID])
   values (6, 'Dunedin', 1, 1)


Select * from [Location]

Commit Transaction [Locations]

exec gnosis.transaction_begin 'Locations'

-- Country
exec [Location Ins] @Location_ID=1,
   @Name='New Zealand', @Location_Type_ID=1,
   @Parent_Location_ID=1

exec [Location Upd] @Location_ID=1,
   @When_Begin='7 Feb 1840'


exec gnosis.transaction_save
   @save_point_name='Country'

-- Cities
exec [Location Ins]
   @Location_ID=2, @Name='Sydney',
   @Location_Type_ID=2,  @Parent_Location_ID=1

exec gnosis.transaction_rollback
   @save_point_name='Country'


-- Cities
exec [Location Ins]
   @Location_ID=2, @Name='Auckland',
   @Location_Type_ID=2,  @Parent_Location_ID=1
exec [Location Ins]
   @Location_ID=3, @Name='Hamilton',
   @Location_Type_ID=2,  @Parent_Location_ID=1
exec [Location Ins]
   @Location_ID=4, @Name='Wellington',
   @Location_Type_ID=2,  @Parent_Location_ID=1
exec [Location Ins]
   @Location_ID=5,@Name='Christchurch',
   @Location_Type_ID=2,  @Parent_Location_ID=1
exec [Location Ins]
   @Location_ID=6, @Name='Dunedin',
   @Location_Type_ID=2,  @Parent_Location_ID=1


Select * from [Location]

exec gnosis.transaction_commit 'Locations'

Example #3. With a conventional short transaction, the connection must be maintained throughout the life of the transaction and if the connection is disconnected or broken then the transaction is automatically rolled back.  With a Gnosis transaction the connection may be opened, closed and reopened multiple times during the life of the transaction and all changes are maintained (kept) as they were, as illustrated below.

<connect>

Begin Transaction

-- provinces
Insert into [Location]  ([Location ID], [Name],
         [Location Type ID], [Parent Location ID])
   values (10, 'Northland', 4, 1)

<disconnect>  <rollback transaction>
<connect>

Select * from [Location] where [Location ID] = 10
-- returns no row as the insert has been rolled back

<disconnect>
 
 

.

Insert into [Location]  ([Location ID], [Name],
         [Location Type ID], [Parent Location ID])
   values (12, ''Hawkes Bay', 4, 1)

<disconnect>

<connect>

exec gnosis.transaction_commit

<disconnect>