Return of field after insert for grid viewing

[smelchi]

Hi, I am testing UNIDAC 3.0.0.6 with SQLSERVER EXPRESS 2008.

I create this table
CREATE TABLE test(
NRCONTROL INTEGER NOT NULL,
NAME CHAR(20) NOT NULL,
PRIMARY KEY( NRCONTROL ) )

I put TDBGrid and TDBNavigator in my FORM.
I set UNIDAC Provider to MSSQL, and with UNIQUERY using this QUERY
SELECT NRCONTROL, NAME FROM test .
At UPDATESQL I put:

DECLARE @NUM INTEGER;
SELECT @NUM = MAX(NRCONTROL) FROM Test;
IF @NUM IS NULL
SET @NUM = 1
ELSE
SET @NUM = @NUM + 1;
INSERT INTO Test
(NRCONTROL, NAME)
VALUES
(@NUM, :NAME);

And this worked. But after inserted, column NRCONTROL in TDBGrid show without value. Refresh options didn´t work because :NRCONTROL has no value.

Is it possible to update :NRCONTROL after INSERT?

Best regards

Sergio Melchiori

[smelchi]

Hi, after searching in this forum I found the solution.

At the end of INSERTSQL statment I put

SET :NRCONTROL = @NUM

procedure TForm1.UniQuery1BeforeUpdateExecute(Sender: TDataSet;
StatementTypes: TStatementTypes; Params: TDAParams);
begin
if stInsert in StatementTypes then
Params.ParamByName('NRCONTROL').ParamType := ptInputOutput;
end;

UniQuery1.Options.ReturnParameters:= True. With False result in error.

Let me know if it is the right solution.

Best regards

Sergio Melchiori

[hughespa]

I think it would be easier to set the NRCONTROL field to be an identity type (like autoinc). You also don't need to specify any UpdateSQL in this case.

Then you can query the table and update it and NRCONTROL will be automatically incremented by 1 each time by the server.

To see the value in the grid, set the UniQuery.RefreshOptions to include roAfterInsert and roAfterUpdate. The server assigned values will then be returned and show in your grid following your insert or amend of the record.

Regards, Paul.

[Dimon]

Let me know if it is the right solution.

It is a correct way, but it is better to use identity fields, as hughespa wrote above.

[smelchi]

Hi I change my table inserting BRANCH field.
Branch will be 1, 2, 3....

So, for each branch I will have NRCONTROL increasing. I cann´t use AUTOINC fields.


CREATE TABLE test(
BRANCH SMALLINT(3) NOT NULLM
NRCONTROL INTEGER NOT NULL,
NAME CHAR(20) NOT NULL,
PRIMARY KEY( BRANCH, NRCONTROL ) )

SELECT MAX(NRCONTROL)+1 INTO @NUMBER FROM TEST WHERE BRANCH = :BRANCH;
INSERT INTO TEST (BRANCH, NRCONTROL, NAME) VALUES
(:BRANCH, @NUMBER, :NAME);
SET :NRCONTROL = @NUMBER;

I am using this at UNIDAC beforeupdateexecute
if stInsert in StatementTypes then
Params.ParamByName('NRCONTROL').ParamType := ptInputOutput;

But this SQL statement
SET :BRANCH = @NUMBER
result an error with message NULL = @NUMBER

What am I doing wrong?

How can I get :NRCONTROL back to show at dbgrid?

[AndreyZ]

Please describe why you want to rewrite the BRANCH field value recieved from a user (from the TDBGrid component) with a value that was calculated for the NCONTROL field. Also please specify the exact code and properties which you are using for this task.
Here is a code example with the functionality that I assume you want to get:

Code: Select all

procedure TMainForm.BitBtnWorkClick(Sender: TObject);
begin
  UniQuery.Options.RequiredFields := False;
  UniQuery.Options.ReturnParams := True;
  UniQuery.SQL.Text := 'SELECT * FROM TEST';
  UniQuery.SQLInsert.Clear;
  UniQuery.SQLInsert.Add('DECLARE @NUMBER INTEGER;');
  UniQuery.SQLInsert.Add('SELECT @NUMBER = MAX(NRCONTROL)+1 FROM TEST WHERE BRANCH = :BRANCH;');
  UniQuery.SQLInsert.Add('IF @NUMBER IS NULL SET @NUMBER = 1');
  UniQuery.SQLInsert.Add('INSERT INTO TEST (BRANCH, NRCONTROL, NAME) VALUES');
  UniQuery.SQLInsert.Add('(:BRANCH, @NUMBER, :NAME);');
  UniQuery.SQLInsert.Add('SET :NRCONTROL = @NUMBER;');
  UniQuery.SQLInsert.Add('SET :BRANCH = @NUMBER;');
  UniQuery.Open;
end;

procedure TMainForm.UniQueryBeforeUpdateExecute(Sender: TDataSet;
  StatementTypes: TStatementTypes; Params: TDAParams);
begin
  if stInsert in StatementTypes then begin
    Params.ParamByName('NRCONTROL').ParamType := ptInputOutput;
    Params.ParamByName('BRANCH').ParamType := ptInputOutput;
  end;
end;

[smelchi]

Sorry for my mistake. I don´t want BRANCH, only NRCONTROL.

When I put this statement

UniQuery.SQLInsert.Add('SET :NRCONTROL = @NUMBER;');

I get NULL = @NUMBER running on MYSQL 5.1

[smelchi]

As I said, I got an ERROR with message

get NULL = @NUMBER running on MYSQL 5.1

[AndreyZ]

We have fixed the problem with output parameters for MySQL server. In the next UniDAC build you will be able to return the value of the @NUMBER variable to TDBGrid in the following way:

Code: Select all

procedure TMainForm.BitBtnClick(Sender: TObject);
begin
  UniQuery.Options.RequiredFields := False;
  UniQuery.Options.ReturnParams := True;
  UniQuery.SQL.Text := 'SELECT * FROM TEST';
  UniQuery.SQLInsert.Clear;
  UniQuery.SQLInsert.Add('SELECT MAX(NRCONTROL)+1 INTO @NUMBER FROM TEST WHERE BRANCH = :BRANCH;');
  UniQuery.SQLInsert.Add('INSERT INTO TEST (BRANCH, NRCONTROL, NAME) VALUES');
  UniQuery.SQLInsert.Add('(:BRANCH, ifnull(@NUMBER, 1), :NAME);');
  UniQuery.SQLInsert.Add('SELECT CAST(@NUMBER AS SIGNED) AS ''NRCONTROL'';');
  UniQuery.Open;
end;

[smelchi]

This didn´t work, show only NRCONTROL in DBGrid.

UniQuery.SQLInsert.Add('SELECT CAST(@NUMBER AS SIGNED) AS ''NRCONTROL'';');
UniQuery.SQLInsert.Add('SELECT CAST(@NEWBRANCH AS SIGNED) AS ''BRANCH';');

This return both in DBGrid.

UniQuery.SQLInsert.Add('SELECT CAST(@NUMBER AS SIGNED) AS ''NRCONTROL'', CAST(@NEWBRANCH AS SIGNED) AS ''BRANCH'';');

Is that right?

Best regards.

[AndreyZ]

If you want to return the new value of the BRANCH field that you calculated on a server to a client, then it's a correct way to do so.