Named Query: Error - A result set was generated for update

Ignition
1

Not able to return/insert value in database using dynamic named query .
Error - A result set was generated for update.
Working fine in MSSQL server

DECLARE @Sql AS NVARCHAR(MAX),
@Sql1 AS NVARCHAR(MAX),
@ParmDefinition AS NVARCHAR(MAX),
@cnt INT,
@Fld varchar(max)='serial_number',
@var int,
@Fld1 varchar(max)='wi_id' ,
@Fld2 varchar(max)='equipment_id' ,
@LookupValue NVARCHAR(MAX)= :serial_number ,
@LookupValue1 NVARCHAR(MAX)=convert(nvarchar(max), :wi_id ),
@LookupValue2 NVARCHAR(MAX)=convert(nvarchar(max),  :equipment_id ),
@LookupValue3 NVARCHAR(MAX)= :start_time  ,
@LookupValue4 NVARCHAR(MAX)= :end_time ,
@LookupValue5 NVARCHAR(MAX)=convert(nvarchar(max), :actual_ct ),
@LookupValue6 NVARCHAR(MAX)=convert(nvarchar(max), :overshoot ),
@LookupValue7 NVARCHAR(MAX)=  :CPIL ,
@LookupValue8 NVARCHAR(MAX)= :Operator ,
@LookupValue9 NVARCHAR(MAX)=convert(nvarchar(max), :target_ct ),
@Tbl varchar(max);
if @LookupValue2 >200 and @LookupValue2<250
begin
	set @Tbl='Table1'
END 
if @LookupValue2 >400 and @LookupValue2<450
begin
	set @Tbl='Table2'
END 
 set nocount on; 

SET @Sql = N'SELECT @cnt = COUNT(*) FROM ' + @Tbl +
' WHERE ' + @Fld + ' = ''' + @LookupValue + ''' and ' + @Fld1 + ' = ''' + @LookupValue1 + ''' and ' + @Fld2 + ' = ''' + @LookupValue2 + '''';

SET @ParmDefinition=N'@cnt int OUTPUT';

set nocount on;
EXEC sp_executesql @Sql,@ParmDefinition,@cnt = @var OUTPUT;
select @var as VarCount

If @var=0
Begin
SET @Sql1 = N' INSERT INTO ' + @Tbl + ' VALUES ('''+@LookupValue+''','''+@LookupValue1+''','''+@LookupValue2+''','''+@LookupValue3+''','''+@LookupValue4+''','''+@LookupValue5+''','''+
@LookupValue6+''','''+@LookupValue7+''','''+@LookupValue8+''','''+@LookupValue9+''')'
EXEC sp_executesql @Sql1
end
2

I don’t think the JDBC driver can handle this kind of query. Most likely, you’re going to have to make this a stored procedure, then call it from Ignition.

system.db.createSProcCall - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)

1 Like
3

I would also say that making a generic query like you have there to insert into multiple tables is not really a good idea for a named query anyways.

You should just make two named queries an INSERT statement for table1 and an INSERT statement for table2 and in your jython you could do something like

params = {'lookupValue1':1, 'lookupValue2':225, ...}
if params['lookupValue2'] >200 and params['lookupValue2']<250:
    namedQuery = 'insertTable1'
elif params['lookupValue2'] > 400 and params['lookupValue2']<450:
    namedQuery = 'insertTable2'
system.db.runNamedQuery(namedQuery, params)

so you use jython to figure out what named query to run.

Trouble shooting dynamically made sql when something goes wrong is a real headache and should be avoided when possible imo.

1 Like
4

That isn't a query. That is a SQL script. JDBC doesn't support scripts. Use single statements from the Ignition side.

{ Some DBs, like MS SQL Server, support some aspects of SQL scripts in their JDBC drivers, but it is prone to errors when used in a generic JDBC framework like Ignition. }

SQL Server Management Studio (and other SQL management tools) support scripts. Just because something runs in SSMS doesn't mean it must work in JDBC.

4 Likes