MSSQL: Unidac ExecSql, db trigger returning ID

[Stamp]

Hello!

We have a product that uses UniDac for MSSQL. We have triggers set up that return the ID of the last inserted row in the result dataset when an insert is performed (this is for compatibility with MS Entity Framework). When I call "ExecSql" or "Execute," on an insert query, I get the following error:

Code: Select all

Query must return exactly one result set - use Execute.

Is there any way to disable this check? I am using UniDac Professional (no source code).

Example trigger:

Code: Select all

 CREATE TRIGGER [TABLE]_BI ON [TABLE] INSTEAD OF INSERT as 
BEGIN

      declare @LOCATOR INT
      SET @LOCATOR = (SELECT [KEYFIELD] FROM INSERTED)


      IF @LOCATOR is null or @LOCATOR = 0 BEGIN
      exec UNIQUE_ID [UNIQUEID], @LOCATOR OUTPUT      
      SELECT * INTO #Inserted FROM INSERTED    
      UPDATE #Inserted SET [KEYFIELD] = @LOCATOR  
      SET NOCOUNT ON;    

      INSERT INTO [TABLE] SELECT * FROM #Inserted 

      select [KEYFIELD] as "[KEYFIELD]" from #Inserted               
      END                                                      
      ELSE                                                     
      BEGIN                                                    
      SET NOCOUNT ON;                                          
      INSERT INTO [TABLE] SELECT * FROM INSERTED 
      select [KEYFIELD] as "[KEYFIELD]" from INSERTED    

      END                                                    
END;

[AndreyZ]

Hello,

Please try creating a small sample to demonstrate the problem and send it to andreyz*devart*com , including a script to create all needed server objects (tables, triggers, etc.). Also please specify the following:
- the exact version of UniDAC. You can learn it from the About sheet of TUniConnection Editor;
- the exact version of your IDE;
- the exact version of SQL Server server and client you are working with. You can learn it from the Info sheet of TUniConnection Editor.

[Stamp]

Table:

Code: Select all

CREATE TABLE [dbo].[TestNumber](
	[TestId] [int] NOT NULL,
	[TestNumber] [int] NOT NULL,
 CONSTRAINT [PK_TestNumber] PRIMARY KEY CLUSTERED 
(
	[TestId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


 CREATE TRIGGER [dbo].[TestNumber_BI] ON [dbo].[TestNumber] INSTEAD OF INSERT as 
BEGIN
	declare @LOCATOR INT
	SET @LOCATOR = (SELECT TestId FROM INSERTED)
	IF @LOCATOR is null or @LOCATOR = 0 
	BEGIN
		declare @GENERATOR_ID INT
		SET @GENERATOR_ID = (SELECT max(TestID) FROM TestNumber) + 1
		
		IF @GENERATOR_ID is null
		BEGIN
			SET @GENERATOR_ID = 1
		END
		   
		SELECT * INTO #Inserted FROM INSERTED    
		UPDATE #Inserted SET TestId = @GENERATOR_ID
		SET NOCOUNT ON;    
		INSERT INTO TestNumber SELECT * FROM #Inserted 
		select TestId as "TestId" from #Inserted               
	END                                                      
	ELSE                                                     
	BEGIN                                                    
		SET NOCOUNT ON;                                          
		INSERT INTO TestNumber SELECT * FROM INSERTED 
		select TestId as "TestId" from INSERTED    
	END                                                    
END;

GO

Code to execute in application:

Code: Select all

procedure TForm1.TestNumber();
var
  fConnection : tUniConnection;
begin
  fConnection := TUniConnection.Create(nil);
  fConnection.Server := [DBSERVER];
  fConnection.Database := [DATABASE];
  fConnection.ProviderName := 'SQL Server';
  fConnection.Username := [DBUSER];
  fConnection.Password := [DBPASS];

  with TUniQuery.Create(nil) do
  begin
    Connection := fConnection;
    sql.Add('insert into TestNumber (TestNumber) values (:TestNumber)');
    params[0].AsInteger := 123;
    prepare;  //works when commented, doesn't work when uncommented
    ExecSql;
    Free();
  end;

  fConnection.free();
end;

Note how you do not get an error when commenting the prepare command.

[Stamp]

Versions:

Unidac: 3.50.0.12
Delphi 6, update 2
Sql Server 10.50.1600
Sql Server Client: 10.50.2500.0

[AndreyZ]

I have investigated this problem. When you prepare your query and execute it, SQL Server indicates that the query returns a result set but does not return the meta information for it. Therefore, SDAC generates the "Query must return exactly one result set - use Execute" error. We cannot influence such SQL Server behaviour. You can avoid this problem in two ways:
- do not prepare TUniQuery;
- use TUniSQL instead of TUniQuery.

[Stamp]

Ugh, sounds like I have a long road ahead of me. Thanks for looking this though guys!

+1

[AndreyZ]

I am glad to help. If any other questions come up, please contact us.