Getting error while creating store procedure by named query

Ignition
1

Hello team,
Im getting below error while creating store procedure by named query please help us with resolution.
Error

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:188)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.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: Error executing system.db.runUpdateQuery(

regards

2

Really? You're not going to show us the code?

10 Likes
3

Nice Error!

4

You need to show your code but most likely you should be using runNamedQuery, not runUpdateQuery.

1 Like
5

hello,
I have attached code for refence.
I have used runUpdateQuery

#Convert Procedure name  into SQL Format
		procedure_nameWithFormat="'"+(procedure_name)+"'"
		
		ListCombinationParam=[]
		# Convert Multiple Parameters into SQL Format
		for ParamName,ParamType in (Parameters):
			CombinationParam= ParamName +' '+ParamType
			ListCombinationParam.append(CombinationParam)
			ParamWithComma=','.join(ListCombinationParam)
		ParamWithComma=str(ParamWithComma)+ ',@Result Decimal(16,4) output'
		
		# Drop the stored procedure if it exists
		drop_sql = """
		IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = """+str(procedure_nameWithFormat)+""")
		   DROP PROCEDURE """+procedure_name+""";
		"""
				
		# Create Store Procedure with Passing dynamic paramters
		create_sql = """
		DECLARE @sql NVARCHAR(MAX);
		SET @sql = '
		CREATE PROCEDURE """+str(procedure_name)+"""
			"""+ParamWithComma+"""
			
		AS
		BEGIN
		   SET NOCOUNT ON;
		    """+str(Query)+"""
		   Set @Result =( """+str(Calculation)+""" ) """+"""
		   return @Result  """ + """
		END
		';
		EXEC (@sql);
		"""
		
		
		# First drop, then create
	
		DropSP= system.db.runUpdateQuery(drop_sql, DatabaseName)
#		DropSP= system.db.runPrepQuery(drop_sql)
		
		CreateSP= system.db.runUpdateQuery(create_sql, DatabaseName)
#		CreateSP= system.db.runPrepQuery(create_sql)
	
		print CreateSP
	   	print("Message: "+str("Stored procedure created successfully."))
		#   system.gui.messageBox("Stored procedure created successfully.")
		return True
6

You should remove "named query" from your title and description then since you're not using named queries.

You should test this by printing the drop_sql and create_sql variables to make sure they're showing the text you're expecting.

You'll also want to include your full script, possibly an example of how you're calling it, and the full error message showing the line you're getting the error on.

My guess right now is that your create_sql is overly complex. I think if you simplify it to just start with the CREATE PROCEDURE and end with the END and skip the DECLARE, SET, and EXEC commands, that may fix your problem.

4 Likes
7

Got to say this seems ill-advised. Why drop and create stored procedures on the fly? Stored procedures usually are just made once and then called as needed. Not to mention it seems like your SP is just storing the result of a single select query? Why not just use runPrepQuery or runNamedQuery? You're going to give yourself headaches doing things like this (if this issue doesn't already count as one).

4 Likes
8

I was trying to figure out the same thing, but didn't know what all they were injecting into the procedure with their Query parameter. They could be trying to automate building out a database and stored procedures for manipulating tables, so didn't question the why and only focused on what could possibly be the problem.

1 Like
9

Hello @bkarabinchak.psi ,
Thanks for suggestion..
This case we are creating store procedure by UI.
Answer for your first question is we are checking if same store procedure already created or not if created we are deleting.
We had used "runPrepQuery " but it is also not working for us.

10

There's already a UI to make stored procedures though and that's your workbench for your database, in your case SSMS and it is much more appropriate with contextual highlighting and auto complete that will be completely lacking in trying to make this in Ignition vision/perspective.

You also don't want regular users to be able to modify your database in such a way. Even if the screen is limited to only administrators - if I was a DBA on this project I would just be using SSMS anyways.

If you are just trying to autogenerate the text - that makes a bit more sense, but just save it in a .sql file, bring it to ssms, you will get any syntax errors highlighted for you and then you can run it once there.

I still highly recommend against this course of action imo.

1 Like
11

Hello @bkarabinchak.psi ,
Basically, we are generating store procedure by application. We are able to create it same script generate only, but sometimes it's getting same error, even same generated query executing & created in SSMS.

We didnt get error if you help that it will be helpfull

12

I would argue if you're able to generate the stored procedures by application its likely the stored procedure could just accept a few additional parameters to handle all your needs.

13

Hello @bkarabinchak.psi ,
Can you help us how to handle it.

Please refer the


print system.db.runUpdateQuery("		DECLARE @sql NVARCHAR(MAX);		SET @sql = '		CREATE PROCEDURE PlannedDowntime			@Int INT,@Int1 INT,@Result Decimal(16,4) output					AS		BEGIN		   SET NOCOUNT ON;		    Declare @Result_TPM Decimal(16,4)  Set@Result_TPM =(SELECT      SUM (Duration)   FROM             cm_shiftSchedule       WHERE     entityID  = 13   AND shiftDay  =       Convert(Date,(GetDate()))  AND pdtCode  = 'TPM'   AND pdt    IS NOT NULL)  Declare @Result_Break Decimal(16,4)  Set @Result_Break =(SELECT      SUM (Duration)   FROM             cm_shiftSchedule       WHERE     entityID  = 13   AND shiftDay  =       Convert(Date,(GetDate()))  AND pdtCode  = 'BRK'   AND pdt    IS NOT NULL)  Declare @Result_WGBT Decimal(16,4)  Set @Result_WGBT =(SELECT      SUM (Duration)   FROM             cm_shiftSchedule       WHERE     entityID  = 13   AND shiftDay  =       Convert(Date,(GetDate()))  AND pdtCode  = 'WGBT'   AND pdt    IS NOT NULL)		   Set @Result =( ISNULL(@Result_TPM,0) + ISNULL(@Result_Break,0) + ISNULL(@Result_WGBT,0) ) 		   return @Result  		END		';		EXEC (@sql);"		, "ignitiondb", True)

I have checked same query into SSMS & it creating store procedure


CREATE PROCEDURE PlannedDowntime			@Int INT,@Int1 INT,@Result Decimal(16,4) output					AS		BEGIN		   SET NOCOUNT ON;		    Declare @Result_TPM Decimal(16,4)  Set @Result_TPM =(SELECT      SUM (Duration)   FROM             cm_shiftSchedule       WHERE     entityID  = 13   AND shiftDay  =       Convert(Date,(GetDate()))  AND pdtCode  = 'TPM'   AND pdt    IS NOT NULL)  Declare @Result_Break Decimal(16,4)  Set @Result_Break =(SELECT      SUM (Duration)   FROM             cm_shiftSchedule       WHERE     entityID  = 13   AND shiftDay  =       Convert(Date,(GetDate()))  AND pdtCode  = 'BRK'   AND pdt    IS NOT NULL)  Declare @Result_WGBT Decimal(16,4)  Set @Result_WGBT =(SELECT      SUM (Duration)   FROM             cm_shiftSchedule       WHERE     entityID  = 13   AND shiftDay  =       Convert(Date,(GetDate()))  AND pdtCode  = 'WGBT'   AND pdt    IS NOT NULL)		   Set @Result =( ISNULL(@Result_TPM,0) + ISNULL(@Result_Break,0) + ISNULL(@Result_WGBT,0) ) 		   return @Result  		END

Error in script consol:


Java Traceback:
Traceback (most recent call last):
  File "<input>", line 14, in <module>
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:188)
	at jdk.internal.reflect.GeneratedMethodAccessor126.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: Error executing system.db.runUpdateQuery(		DECLARE @sql NVARCHAR(MAX);		SET @sql = '		CREATE PROCEDURE PlannedDowntime			@Int INT,@Int1 INT,@Result Decimal(16,4) output					AS		BEGIN		   SET NOCOUNT ON;		    Declare @Result_TPM Decimal(16,4)  Set @Result_TPM =(SELECT      SUM (Duration)   FROM             cm_shiftSchedule       WHERE     entityID  = 13   AND shiftDay  =       Convert(Date,(GetDate()))  AND pdtCode  = 'TPM'   AND pdt    IS NOT NULL)  Declare @Result_Break Decimal(16,4)  Set @Result_Break =(SELECT      SUM (Duration)   FROM             cm_shiftSchedule       WHERE     entityID  = 13   AND shiftDay  =       Convert(Date,(GetDate()))  AND pdtCode  = 'BRK'   AND pdt    IS NOT NULL)  Declare @Result_WGBT Decimal(16,4)  Set @Result_WGBT =(SELECT      SUM (Duration)   FROM             cm_shiftSchedule       WHERE     entityID  = 13   AND shiftDay  =       Convert(Date,(GetDate()))  AND pdtCode  = 'WGBT'   AND pdt    IS NOT NULL)		   Set @Result =( ISNULL(@Result_TPM,0) + ISNULL(@Result_Break,0) + ISNULL(@Result_WGBT,0) ) 		   return @Result  		END		';		EXEC (@sql);, Transaction datasource unknown, true, true)

	at org.python.core.Py.JavaError(Py.java:545)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:188)
	at jdk.internal.reflect.GeneratedMethodAccessor126.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:190)
	at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:553)
	at org.python.core.PyObject.__call__(PyObject.java:512)
	at org.python.core.PyObject.__call__(PyObject.java:517)
	at org.python.pycode._pyx236.f$0(<input>:14)
	at org.python.pycode._pyx236.call_function(<input>)
	at org.python.core.PyTableCode.call(PyTableCode.java:173)
	at org.python.core.PyCode.call(PyCode.java:18)
	at org.python.core.Py.runCode(Py.java:1703)
	at org.python.core.Py.exec(Py.java:1747)
	at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:277)
	at org.python.util.InteractiveInterpreter.runcode(InteractiveInterpreter.java:130)
	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:628)
	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:616)
	at java.desktop/javax.swing.SwingWorker$1.call(Unknown Source)
	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
	at java.desktop/javax.swing.SwingWorker.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.lang.Exception: Error executing system.db.runUpdateQuery(		DECLARE @sql NVARCHAR(MAX);		SET @sql = '		CREATE PROCEDURE PlannedDowntime			@Int INT,@Int1 INT,@Result Decimal(16,4) output					AS		BEGIN		   SET NOCOUNT ON;		    Declare @Result_TPM Decimal(16,4)  Set @Result_TPM =(SELECT      SUM (Duration)   FROM             cm_shiftSchedule       WHERE     entityID  = 13   AND shiftDay  =       Convert(Date,(GetDate()))  AND pdtCode  = 'TPM'   AND pdt    IS NOT NULL)  Declare @Result_Break Decimal(16,4)  Set @Result_Break =(SELECT      SUM (Duration)   FROM             cm_shiftSchedule       WHERE     entityID  = 13   AND shiftDay  =       Convert(Date,(GetDate()))  AND pdtCode  = 'BRK'   AND pdt    IS NOT NULL)  Declare @Result_WGBT Decimal(16,4)  Set @Result_WGBT =(SELECT      SUM (Duration)   FROM             cm_shiftSchedule       WHERE     entityID  = 13   AND shiftDay  =       Convert(Date,(GetDate()))  AND pdtCode  = 'WGBT'   AND pdt    IS NOT NULL)		   Set @Result =( ISNULL(@Result_TPM,0) + ISNULL(@Result_Break,0) + ISNULL(@Result_WGBT,0) ) 		   return @Result  		END		';		EXEC (@sql);, Transaction datasource unknown, true, true)
	... 25 more
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Connection Error: Transaction "true" is closed.
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:360)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:334)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:287)
	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runUpdateQuery(ClientDBUtilities.java:267)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:181)
	... 23 more
Caused by: java.sql.SQLException: Transaction "true" is closed.
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getTx(DatasourceManagerImpl.java:292)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getTransactionConnection(DatasourceManagerImpl.java:299)
	at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.getConnection(GatewayDBUtilities.java:99)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:50)
	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:435)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:523)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:590)
	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86)
	at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1410)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:764)
	at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1665)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:527)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:598)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1580)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1384)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:484)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1553)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1306)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
	at com.inductiveautomation.catapult.handlers.RemoteHostNameLookupHandler.handle(RemoteHostNameLookupHandler.java:121)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
	at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:301)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:51)
	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:141)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
	at org.eclipse.jetty.server.Server.handle(Server.java:563)
	at org.eclipse.jetty.server.HttpChannel$RequestDispatchable.dispatch(HttpChannel.java:1598)
	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:753)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:501)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:287)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:314)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)
	at org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.runTask(AdaptiveExecutionStrategy.java:421)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.consumeTask(AdaptiveExecutionStrategy.java:390)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.tryProduce(AdaptiveExecutionStrategy.java:277)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.run(AdaptiveExecutionStrategy.java:199)
	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:411)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:969)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.doRunJob(QueuedThreadPool.java:1194)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1149)
	at java.lang.Thread.run(null)
Traceback (most recent call last):
  File "<input>", line 14, in <module>
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:188)
	at jdk.internal.reflect.GeneratedMethodAccessor126.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: Error executing system.db.runUpdateQuery(		DECLARE @sql NVARCHAR(MAX);		SET @sql = '		CREATE PROCEDURE PlannedDowntime			@Int INT,@Int1 INT,@Result Decimal(16,4) output					AS		BEGIN		   SET NOCOUNT ON;		    Declare @Result_TPM Decimal(16,4)  Set @Result_TPM =(SELECT      SUM (Duration)   FROM             cm_shiftSchedule       WHERE     entityID  = 13   AND shiftDay  =       Convert(Date,(GetDate()))  AND pdtCode  = 'TPM'   AND pdt    IS NOT NULL)  Declare @Result_Break Decimal(16,4)  Set @Result_Break =(SELECT      SUM (Duration)   FROM             cm_shiftSchedule       WHERE     entityID  = 13   AND shiftDay  =       Convert(Date,(GetDate()))  AND pdtCode  = 'BRK'   AND pdt    IS NOT NULL)  Declare @Result_WGBT Decimal(16,4)  Set @Result_WGBT =(SELECT      SUM (Duration)   FROM             cm_shiftSchedule       WHERE     entityID  = 13   AND shiftDay  =       Convert(Date,(GetDate()))  AND pdtCode  = 'WGBT'   AND pdt    IS NOT NULL)		   Set @Result =( ISNULL(@Result_TPM,0) + ISNULL(@Result_Break,0) + ISNULL(@Result_WGBT,0) ) 		   return @Result  		END		';		EXEC (@sql);, Transaction datasource unknown, true, true)

Please help to resolve issue

Regards,
Dhiraj Pawar

14

JBDC drivers are meant for single statements and anything other than that is liable to not work as expected.

You are setting a variable (this can sometimes work but isn't supported and can fail or mess things up).

Not only that you are setting a variable and then dynamically executing SQL inside your query.

Just take this part

CREATE PROCEDURE PlannedDowntime			@Int INT,@Int1 INT,@Result Decimal(16,4) output					AS		BEGIN		   SET NOCOUNT ON;		    Declare @Result_TPM Decimal(16,4)  Set@Result_TPM =(SELECT      SUM (Duration)   FROM             cm_shiftSchedule       WHERE     entityID  = 13   AND shiftDay  =       Convert(Date,(GetDate()))  AND pdtCode  = 'TPM'   AND pdt    IS NOT NULL)  Declare @Result_Break Decimal(16,4)  Set @Result_Break =(SELECT      SUM (Duration)   FROM             cm_shiftSchedule       WHERE     entityID  = 13   AND shiftDay  =       Convert(Date,(GetDate()))  AND pdtCode  = 'BRK'   AND pdt    IS NOT NULL)  Declare @Result_WGBT Decimal(16,4)  Set @Result_WGBT =(SELECT      SUM (Duration)   FROM             cm_shiftSchedule       WHERE     entityID  = 13   AND shiftDay  =       Convert(Date,(GetDate()))  AND pdtCode  = 'WGBT'   AND pdt    IS NOT NULL)		   Set @Result =( ISNULL(@Result_TPM,0) + ISNULL(@Result_Break,0) + ISNULL(@Result_WGBT,0) ) 		   return @Result  		END

And go run it in SSMS. I don't see the utility of what you are doing here trying to do this through Ignition.

15

You are attempting to run a SQL script, and those are not supported by JDBC, even though they do work in some cases.

You should just create the Stored Procedure in SSMS. Really.

Meanwhile the error you’re getting is because you’ve provided the name “True” as the Transaction name, but that transaction doesn’t exist or is being closed.

Look closely at the function parameters and be sure to provide appropriate values for those parameters.

Again, I can’t express this enough, there is a reason that everyone is telling you not to do this the way you’re trying. Even if it does work, it will be very fragile, and subject to breaking at anytime.

1 Like
16

Hello @lrose ,
Thanks for suggestion i will check Transaction name.

As for another suggestion, This is only way because we want to create these mentioned operation in SQL query which is dynamically set by UI.
These operation or sequence of store procedure will execute at trigger point result it store required result into another DB table.

If there is anyother way please suggest

17

Using your sample, what portions are coming from the UI?

18

If there is anyother way please suggest

What exactly is your end goal? From start to finish, how is the functionality of the feature supposed to work - without menitoning stored procedures what ideally should happen here

3 Likes
19

I highly doubt that it is the “only” way. You have yet to state why you need to do this. You have only insisted it is the only way.

Of course there is another way.

You could create the SP’s by hand and then call them from a script.

You could abandon the SP’s altogether, and just create the queries dynamically and call them using system.db.runPrepQuery() and system.db.runPrepUpdate().

Or perhaps, depending on the nature of these queries you could even create Named Queries to perform the operations you need.

You’ve already spent more time trying to accomplish this unorthodox approach than it should take to implement any of those approaches.

Anytime I hear UI and database procedure manipulation I get concerned about SQL injection. I can see holes where users could potentially manipulate whole databases of data.

Your approach is wrought with danger.

2 Likes
20

I don't know if it will even be possible to do what he's asking, but I can definitely see use cases for this if you want to drop an Ignition project on a server and point it to the DB and have it set up all the necessary stored procedures automatically for you. Plus, these stored procedures may not even be for Ignition to use, but for other software to call them to do functions in the database safely.

Due to JDBC I don't know if it will be possible to even do it, but the only way to know is to try and ask others. I do believe though that the variable assignment inside the query will need to be removed and only run the CREATE PROCEDURE directly for it to even have a chance of being successful.