OracleSequence

Applies to
TAstaClientDataSet
TAstaProvider

Declaration
property OracleSequence: string;

Description
Oracle sequences are different than auto increment fields in that they must be fired before an insert to retrieve a unique value. They can be set using the EditMode property of the TAstaClientDataSet from the Refetch tab or on the TAstaProvider if you are using server side SQL.

ASTA will use the OracleSequence string to call OracleSequence.NextValue before an insert statement. It will then retrieve it after the insert statement has successfully fired so that it can be fetched back to the client by calling OracleSequence.LastValue from the OnInsertAutoIncrementFetch event on the TAstaServerSocket.

ASTA supports the refetching of AutoIncrement Values on inserts.

To support this the following steps must be taken

1. Set the AutoIncrementField to the oracleSequenceField. With Oracle there is a problem at design time since ASTA is looking for Integer fields and Oracle does not have real integer fields but just Float fields that have 0 precision. So you must set this at runtime.

(we'll look to change the property editor to adjust to this)

2. Set the Edit Mode to Cached. A Transaction is needed on the server so that this is mandatory.

3. Set the OracleSequence value in the EditMode property editor on the refetchTab or set it at runtime.

4. Click on the RefetchOnInsert Property and click on the field that will be used with the OracleSequence.

Here is an example of setting up a DataSet at runtime;

procedure TForm1.AstaClientDataSet1AfterOpen(DataSet: TDataSet);

begin

with AstaClientDataSet1 do begin

 AutoIncrementfield:='FG_ORDER_NO';

 OracleSequence:='test_seq';

end;

end;

You must then call ApplyUpdates(usmServerTransaction) to post the SQL to the server.

Here is what happens next.

1. the SQL is sent to the server and a Transaction is started.

2. for each Insert Statement the Insert is fired but the field Value for the AutoIncrement field will contain OracleSequence.NextVal.

3. After the Insert is fired, the AstaServerSocket.OnAutoIncrementFetch is fired and a result set is returned that has the current value from the oracle sequence just fired. Note: You must code the AutoIncrementFetch event on the server to call the correct sequence for the correct table!

procedure TfrmMain.AstaServerSocket1InsertAutoIncrementFetch(

  Sender: TObject; ClientSocket: TCustomWinSocket; AQuery: TComponent;

  TheDatabase, TheTable, TheAutoIncField: String;

  var AutoIncrementValue: Integer);

begin

with AQuery as TOracleDataSet do begin

  sql.clear;

  //right here you will have to do an if statement to get the right

  //sequence based on TheTable

  sql.add('select test_seq.CURRVAL from dual');

  Close;

  Open;

  if not eof then AutoIncrementValue:=Fields[0].AsInteger;

end;

4. Another select is called using the sequence to get any other values fired from triggers or default values that you had defined in the refetch on insert property on the client using the autoinc value just returned from the AutoIncrementFetch.

5. On the client, the results are streamed back and matched up by bookmarks. They can be retrieved immediately after their arrival in the AfterrefetchOnInsert of the TAstaClientDataSet,