Devart Forums

Posted: **Fri 18 Jun 2010 18:23**

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

Posted: **Fri 18 Jun 2010 19:19**

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

Posted: **Sat 19 Jun 2010 02:07**

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.

Posted: **Mon 21 Jun 2010 09:48**

smelchi wrote: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.

Posted: **Wed 18 May 2011 21:59**

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?

Posted: **Thu 19 May 2011 09:14**

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;

Posted: **Thu 19 May 2011 17:50**

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

Posted: **Sun 22 May 2011 14:10**

As I said, I got an ERROR with message

get NULL = @NUMBER running on MYSQL 5.1

Posted: **Wed 25 May 2011 11:26**

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;

Posted: **Fri 02 Sep 2011 18:04**

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.

Posted: **Wed 07 Sep 2011 07:16**

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.

Devart Forums

Return of field after insert for grid viewing

Return of field after insert for grid viewing