Table type input parameter for stored procedure calls

Ignition Version 7.9.5
Sql Server 2014
Good afternoon everyone. Does anyone know how to pass a table type input parameter to the “createSProcCall” object when scripting? I’ve used the system.db.execSProcCall(call) quite a few times but only with one or two input parameters of datatype VARCHAR or INTEGER. But I’d like to pass an entire table/dataset to a stored procedure now, and it looks like it isn’t supported by these system.db commands.

	call = system.db.createSProcCall("dbo.UpsertRecipe","myDatabase")
	call.registerInParam("RecipeID", system.db.INTEGER, RecipeID)
	call.registerInParam("RecipeName", system.db.VARCHAR, RecipeName)
	call.registerInParam("SoftwareNumVer", system.db.VARCHAR, SoftwareNumVer)
	call.registerInParam("PartID", system.db.INT, PartID)
	call.registerInParam("RecipeParams", ------------, RecipeParams)
	call.registerInParam("UserID", system.db.VARCHAR, UserID)
	
	system.db.execSProcCall(call)
	resultSet = call.getResultSet()

The Ignition help file for “system.db.createSProcCall” shows the supported type codes, and I don’t see anything that looks like a dataset.
Here

You can’t store an arbitrarily complex type like a dataset or array into (most) SQL flavors. You’ll have to insert each row of the dataset individually.

I wonder if I’m trying to pass “table-valued parameters”. Here is a link.
Using table valued parameters
I’m not sure I follow what you were meaning PGriffith. Stored procedures seem to be able to accept input parameters that are tables, but are you saying that it needs to be a consistent/set dimension?
If so, lets assume that my ignition dataset, (that somehow needs to be altered and passed to the stored procedure) is always a 6 x 6.

It’s theoretically possible with access under the hood to the JDBC driver. I don’t know that you’d be able to do it in Ignition, and even if so, only from gateway scope. Good Luck.

1 Like

Thanks for the link Phil. So I tried to lobby for not getting under the hood, but unfortunately the customer would like to keep their stored procedure input in the table-valued parameter format. Assuming I can google my way through writing the Java code, could someone explain where I should paste the code? Also, why does it have to be Gateway scoped? All my other stored procedures using the “system.db.createSProcCall()” are in Client scoped tag change scripts.

Because you have to access the classloader for the MS JDBC driver to get at the classes you need, and those only exist in the gateway.

Can you show me a quick example of how to “insert each row of the dataset individually”? I don’t see a datatype that could work for a row in the list of usable datatypes for “createSProcCall” (ex: list, touple, array).

        newRow = [1, "string1",300098,"string2"]
	call = system.db.createSProcCall("dbo.UpsertRecipe","myDB")
	call.registerInParam("newRow", system.db.ARRAY, newRow)

Something like this (although ARRAY is not included in the list of datatypes that are usable with createSProcCall).

Would it be possible to accomplish this in Jython instead of Java?

def testSPCall():
	#java.lang.Object
	#org.python.core.PyObject
	from com.ziclix.python.sql.Procedure import zxJDBC
	
	#create database connection
	d,u,p,v = "jdbc:sqlserver://10.15.4.246\SQLEXPRESS", "sa", "password123", "com.microsoft.sqlserver.jdbc.SQLServerD‌​river"
	db = zxJDBC.connect(d,u,p,v)
	
        #Go get my table
        #Pull out Array 1, Array 2, Array 3 to be used as input parameters

	#Open a cursur
	c = db.cursor()
	c.callproc(("SensorManufacturing","dbo","dbo.UpsertRecipe"), ["Array1","Array2","Array3"])

	#close connection
	c.close()

In the IA manual it mentions other datatypes like Other, Java-Object and Array. So I’m thinking this might be possible.
IA manual CreateSProcCall()

Sorry - my terminology was a bit misleading (this whole realm is full of overloaded terms).
What I meant at the time was "insert each row into the db as a separate insert statement" - I hadn't really noticed the way your stored procedure was set up. So, just loop through all of the rows of your table, running the stored procedure call multiple times (and tag each column to insert as a separate stored procedure param). That's still going to be the simplest solution, by far.

If you absolutely have to get this done, your next simplest solution is going to be writing a custom module. Yes, really. Failing that, it's theoretically possible to get your own connection object to construct a stored procedure call yourself, but it's far from trivial - you'll need to get the root GatewayContext, then get the DatasourceManager, then create your own connection object to use.

Our design services department could definitely help you out with the latter two options, but, really, just modifying the procedure (or creating a new one) and calling it successively is by far the best option.

It's worse than that... The Datasource Manager doesn't give out "real" connections, but connections from its pooling mechanism. (Apache, iirc). I've poked at that for other DB-vendor-specific features and found that the underlying connection is wrapped up so tightly that you have to use reflection to get at protected fields. Seriously unfun and fragile.

Ok thanks guys. We’re changing the procs instead.