How SDAC Components support SQLCA in Delphi?

[Japhar]

Hi,

1. How SDAC Components support SQLCA in Delphi?

Can anybody provide examples on this?

Thanks
japhar

[Jackson]

SDAC does not support SQLCA

[Japhar]

Hi,

Below record set is used to get the sqlSerKey, sqlErrm, etc... with IDAC (Informix) Components. This is our existing source code. We are using SDAC componets now and SDAC doesn't support SQLCA. So, How can we achieve below functionality? Please can any one provide few comments on below source code which helps me to move ahead.

Your inputs will really be appreciated....thanks for your previous replys...

1) SQLCA record Structure declaration

rSQLCA = record
SErcode: LongInt;
SqlErrm: array[1..72] of char;
Sqlerrp: array[1..8] of char;
SqlSerKey: LongInt;
SqlRowsProcessed: LongInt;
SqlCost: LongInt;
SqlRowsReturned: Longint;
SqlRowID: LongInt;
end;

2) function which returns above record set

function SQLCA(DataSet: TDataSet): rSqlCa;
begin

with (Dataset as TQuery).Connection do
begin
result.SqlSerKey := SQLCA^.sqlerrd[1];
result.SerCode := SQLCA^.sqlcode ;
Move(SQLCA^.sqlerrm[0], Result.SqlErrm[1],
SizeOf(Result.SqlErrm) ) ;
Move(SQLCA^.sqlerrp[0], result.SqlErrp[1],
Sizeof(result.SqlErrp[1])) ;
Result.SqlRowsProcessed := RowsAffected ;
Result.SqlRowsReturned := 0;
Result.SqlCost := 0;
end;
end;

3) SQLCA() function is calling as shown below.
When dataset is in insert state, we are getting serial key from sqlca function by passing the dataset.

if (UpdateKind = ukInsert) then
begin
DataSet.FieldByName('Empid').NewValue :=
sqlca(dataset).sqlSerKey;
end;


Thanks

[Jackson]

You can cache results of previous SQL statement execution manually.
For example:

Code: Select all

SQLError := 0;
try
  MSConnection1.ExecSQL('select * from TableName', []);
except
  on E: EMSError do
    SQLError := E.ErrorCode;
end;

Then add value of SQLError variable to array or list and reuse later.

[Japhar]

As per your example, when it executes sql statement, if we get any error then it comes into 'on E: EMSError do' block and then it set the error code.

But as per the code which i have provided it is getting:
1. Serial key with sqlca^.sqlerrd[1]
2. It is getting serial key in such a way that,
a. First it is executing an insert statement which is having
serial key in a table. Ex: updateObject.Apply(updateKind);

b. After executing above code, it is calling 'sqlca(dataset).sqlSerKey'

c. This function is returning the last generated serial key
of the last executed insert statement.

3. So in the similar manner how we can get the serial key for the last executed insert statement?

Can you please provide few details on this..

[Jackson]

What do you mean under Serial key?
As MSDN says, SQLCA.sqlerrd[1] contains MS SQL Server 2000 error number.

[Japhar]

Hi,

SQLCA.sqlerrd[1](ie. in Informix): If the last SQL statement was successful, then sqlca.sqlerrd[1] contains the OID of the processed row. After an INSERT, sqlca.sqlerrd[1] contains the value that the database server has generated for a SERIAL column.

I'm strugling to get the value of Serial key for the last executed insert statement with SDAC component.

I'm looking for a generialized function to get the serial key for the last executed insert statement. Can you please tell me the steps...

[Jackson]

To get last identity value (as we understood it is analog of Informix Serial key) use following statement:
SET :LastIdentity = SCOPE_IDENTITY()
For more information about IDENTITY columns please see MSDN.

[Japhar]

Thanks for your help.

I have tried to get the identity value with the below code but no luck...

1. I have taken a TMSQuery component on a form and name to qryTest:

a. qryTest.SQL having 'Select empid, empname from emp'.
Ex: empid is Identity key in a table. I have set the persistence field of
empid to 'arAutoInc'.

b.UpdateObject.Insert having 'INSERT INTO EMP (empname) values('test')' statement

c. Set the query component qryTest.UpdateObject property to 'UpdateObject';

On button click:
procedure TForm1.Button1Click(Sender: TObject);
var
LastIdentityVal : Integer;
begin
qryTest.Close;
qryTest.Open;
qryTest.UpdateObject.Apply(ukInsert);
//Executed insert statement here.
qryTest.Execute;

//Get the Last Executed Insert query Identity value here from EMP table.
LastIdentityVal := GetSerialKey(qryTest).LastIdentity;
end;

2. Calling 'GetSerialKey' function to get LastIdentity value.

function GetSerialKey(DataSet: TDataSet): LastIdentity;
var
i: integer;
IdentityFieldName: string;
begin
//Get identity field name.
for i := 0 to (DataSet AS TMSQuery).Fields.Count - 1 do
if (DataSet AS TMSQuery).Fields.AutoGenerateValue = arAutoInc
then begin
IdentityFieldName := (DataSet AS TMSQuery).Fields.FieldName;
break;
end;

//Set the paramtype of the Identity field
(DataSet AS TMSQuery).ParamByName(IdentityFieldName).ParamType := ptInputOutput;
end;

Result.LastIdentity := //How to get value of the identity field of 'qryTest' here???

end;

Here do we set 'SET :LastIdentity = SCOPE_IDENTITY()'? Please help me to get the value here. Thanks in advance.....

[Jackson]

We recommend you not to use UpdateObject property and TMSUpdateSQL component.
It is present only for compatibility.
Use SQLInsert, SQLUpdate, SQLDelete, SQLRefresh properties instead.
Following code illustrates getting value of identity field after INSERT executed.

Code: Select all

procedure TForm1.FormCreate(Sender: TObject);
var
  LastIdentity: integer;
begin
  MSQuery1.Options.ReturnParams := True;
  MSQuery1.SQLInsert.Clear;
  MSQuery1.SQLInsert.Add('INSERT INTO EMP');
  MSQuery1.SQLInsert.Add('  (empname)');
  MSQuery1.SQLInsert.Add('VALUES');
  MSQuery1.SQLInsert.Add('  (:empname)');
  MSQuery1.SQLInsert.Add('SET :empid = SCOPE_IDENTITY()');

  MSQuery1.BeforeUpdateExecute := MSQuery1BeforeUpdateExecute;

  MSQuery1.Insert;
  MSQuery1.FieldByName('empname').AsString := 'Test';
  MSQuery1.Post;

  LastIdentity := MSQuery1.FieldByName('empid').AsInteger;
  ShowMessage(IntToStr(LastIdentity));
end;

procedure TForm1.MSQuery1BeforeUpdateExecute(Sender: TCustomMSDataSet;
  StatementTypes: TStatementTypes; Params: TMSParams);
begin
  if stInsert in StatementTypes then
    Params.ParamByName('empid').ParamType := ptInputOutput;
end;

But in most cases you do not have to specify SQLInsert, SQLUpdate, SQLDelete, SQLRefresh properties.
SDAC SQL generator generates it automatically and takes care about identity field.
You just need to set MSQuery1.Options.ReturnParams and MSQuery1.Options.QueryIdentity to True.

[Japhar]

Hi EvgeniyM,

I really thanks to you....at last i can able to get the identity key value.

Great....thanks a lot...for your help...

But i read your statement 'We recommend you not to use UpdateObject property and TMSUpdateSQL component. It is present only for compatibility. '.

I'm working on a Migration project from IDAC to SDAC conversion. My project having nearly 4000 files. In existing project, all DML operations are handling through UpdateObject of IDAC. SDAC UpdateObject is very similar to IDAC.

With SDAC trial version of 'UpdateObject', we didn't face any problems as of now for the files which we have modified in the project. Everything is working fine.

Now as per your statement, if we should not use UpdateObject then it includes lot's of code changes.

Please let us provide clear details that do we get any issues with UpdateObject? Can you please provide any references to the site that says the issues/problems for UpdateObject. Why UpdateObject is not recommend by CoreLabs? [/b]

[Jackson]

There are no known issues in UpdateObject property and TMSUpdateSQL component.
In your case using of TMSUpdateSQL component is justifiable due to migration from other library.
This component was design exactly for such tasks.
But if you develop new application using SDAC, we are recommend to
use SQLUpdate, SQLInsert etc. properties, because they are handier in use.

[Jackson]

There are no known issues in UpdateObject property and TMSUpdateSQL component.
In your case using of TMSUpdateSQL component is justifiable due to migration from other library.
This component was design exactly for such tasks.
But if you develop new application using SDAC, we are recommend to
use SQLUpdate, SQLInsert etc. properties, because they are handier in use.

[Japhar]

It's very good news to us. We can use UpdateObject.....

Thanks a lot for your support...