SQL Error about Subquery when exec stored procedure

Ignition
, ,
1

Hello,

I have a button on perspective who fire a named query.
The named query execute a stored procedure.
The stored procedure consist of an update and an insert in a log table.

It works but I have the following warning in the gateway log.

Error running action 'component.onActionPerformed' on partials/DetailsRun@C$0:0/root/RunPresent/Buttons/BtnFinDeRun: Traceback (most recent call last): File "<function:runAction>", line 2, in runAction at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:407) at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor.execute(NamedQueryExecutor.java:173) at com.inductiveautomation.ignition.gateway.db.namedquery.GatewayNamedQueryManager.execute(GatewayNamedQueryManager.java:92) at com.inductiveautomation.ignition.common.db.namedquery.SecuredNamedQueryManager.execute(SecuredNamedQueryManager.java:78) at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.runNamedQuery(GatewayDBUtilities.java:418) at jdk.internal.reflect.GeneratedMethodAccessor44.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: java.sql.SQLException: La sous-requête a retourné plusieurs valeurs. Cela n'est pas autorisé quand la sous-requête suit =, !=, <, <= , >, >= ou quand elle est utilisée en tant qu'expression.

If I execute the same stored procedure in SQL Management Studio I don't get any error.

I don't understand the problem. Anyone as an idea ?

Thank you

2

The error message translates to,

The subquery returned multiple values.
This is not allowed when the subquery follows
= , != , < , <= , > , >= or when used as an expression.

Can you think of any reason the subquery is returning only one record in SQL Management Studio but returns multiples when run from an Ignition named query?

3

I understood where is the error but I don't understand why I get an error with Ignition and not with SqlServer.

In fact, I check the parameter value in the beginning of the procedure. It's here I have a subquery.

image
image594×148 8.01 KB

Is the Java SQL driver who cause ths error ?
How can I get work around ?

4

Can you post the runAction code that calls the named query? (Post formatted code - not pictures of code!)

5

There is nothing special I think, the logic is rather in the stored procedure


	system.db.runNamedQuery("FinDeRun",{
		"IdPack": self.view.params.Line,
		"Utilisateur":self.session.props.address
	})

And below is the named query

EXEC IHM_Packing_FinDeRun  :IdPack , :Utilisateur

The query type is on "Update Query"
image

The database is on SQL Server 2005

6

I've never had to use a stored procedure in an Ignition application so I might not be able to help much. I looked through the forum at related posts and I wonder if you should set the named query Query Type to "Query" rather than "Update". It should be a simple test.
[Edit] I thought the SQL script was in the stored procedure. It looks like it was in the named query so my answer wasn't relevant.

7

That isn't a SQL query. It is a SQL script. Java's vanilla JDBC doesn't support SQL scripts, just single SQL statements.

Because of this, testing in SSMS is not valid for JDBC in Ignition. Some drivers will do it (with caveats for named queries), but if you are actually putting @variable or BEGIN and END, or any similar structures in Ignition, you are on your own.

Also note that stored procedures must have a consistent return. Either a single query status (normally a row count for an update/insert/delete) or a single dataset. If your SP logic is running an insert then a final SELECT, that will make two returns.

8

I tried to set the query type to "Query" but I have the same issue.

It's not ideal but I hard coded the possible value of my parameter in the stored procedure to solve the problem.

Thanks for your help

9

Ok, I understand.
Thank you for the clarification.