Working with system.db.SQLXML

thecesrom · January 13, 2018, 2:22am

I am experimenting with inserting XML data via a stored procedure as a proof of concept.

First I read the dataset from a table and convert it into a very simple XML format in order to insert the data into a database table.

The XML representation of the dataset looks like this:

<root>
    <row>
        <name>Art Institute of Chicago</name>
        <address>111 S Michigan Ave</address>
        <address_2></address_2>
        <city>Chicago</city>
        <state_province>IL</state_province>
        <zip>60603</zip>
        <country>United States</country>
        <phone></phone>
        <website>http://www.artic.edu/</website>
    </row>
</root>

Now, I would like to execute a stored procedure call and insert the data into a table. I thought that it would be as simple as doing the following:

call = system.db.createSProcCall('sp_insert_xml')
call.registerInParam('pi_xml', system.db.SQLXML, xml)
system.db.execSProcCall(call)

Where xml is what I posted above, <root><row>...</row></root>.

But when I run the code I get the following error:

Cannot coerce value <root><row>...</row></root> into type: interface java.sql.SQLXML.

Should I format the XML representation in a different way?

I've tried looking for examples but have not found anything on the online docs.

If you know how to work with SQLXML, please share your insights.

PGriffith · January 16, 2018, 4:07pm

java.sql.SQLXML is an interface - you're meant to set the value of the object after you get one from the DB connection, but as an alternative try using this Python class in your SProcCall:

from java.sql import SQLXML

class SQLXMLObject(SQLXML):
	def __init__(self, string):
		self.setString(string)

xml = SQLXMLObject("<root><key>Value</key></root>")

thecesrom · January 16, 2018, 7:43pm

Thanks, @PGriffith.

But after trying your suggestion I get the following error:

Traceback (most recent call last):
  File "<event:actionPerformed>", line 45, in <module>

java.lang.Exception: java.lang.Exception: Error executing system.db.execSProcCall()

	caused by Exception: Error executing system.db.execSProcCall()
	caused by GatewayException: Error serializing parameters.
	caused by NotSerializableException: org.python.proxies.__builtin__$SQLXMLObject$6

Ignition v7.9.5 (b2017111615)
Java: Oracle Corporation 1.8.0_152

This is the [simplfied version of the] code I’m running on the button click event.

from java.sql import SQLXML

class SQLXMLObject(SQLXML):
    def __init__(self, string):
        self.setString(string)

xml = """<root>
    <row>
        <name>Art Institute of Chicago</name>
        <address>111 S Michigan Ave</address>
        <address_2></address_2>
        <city>Chicago</city>
        <state_province>IL</state_province>
        <zip>60603</zip>
        <country>United States</country>
        <phone></phone>
        <website>http://www.artic.edu/</website>
    </row>
</root>"""

xml_object = SQLXMLObject(xml)

call = system.db.createSProcCall('sp_venue_insert_xml')
call.registerInParam('pi_xml', system.db.SQLXML, xml_object)
call.registerOutParam('po_return_code', system.db.INTEGER)
system.db.execSProcCall(call)

While that threw an exception, I changed the input parameter type from system.db.SQLXML to system.db.VARCHAR and it worked:

call = system.db.createSProcCall('sp_venue_insert_xml')
call.registerInParam('pi_xml', system.db.VARCHAR, xml_object)
call.registerOutParam('po_return_code', system.db.INTEGER)
system.db.execSProcCall(call)

Why wouldn’t system.db.SQLXML work?

This is my stored procedure:

ALTER PROCEDURE [dbo].[sp_venue_insert_xml] 
    @pi_xml XML, 
    @po_return_code INT OUT 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SET @po_return_code = 0; 

	BEGIN TRY
		INSERT INTO  [dbo].[venue] 
			(
				[name], 
				[address], 
				[address_2], 
				[city], 
				[state_province], 
				[zip], 
				[country], 
				[phone], 
				[website]
			) 
		SELECT 
			'name' = NULLIF(x.v.value('name[1]', 'VARCHAR(45)'), ''),
			'address' = NULLIF(x.v.value('address[1]', 'VARCHAR(45)'), ''),
			'address_2' = NULLIF(x.v.value('address_2[1]', 'VARCHAR(45)'), ''),
			'city' = NULLIF(x.v.value('city[1]', 'VARCHAR(45)'), ''),
			'state_province' = NULLIF(x.v.value('state_province[1]', 'VARCHAR(45)'), ''),
			'zip' = NULLIF(x.v.value('zip[1]', 'VARCHAR(45)'), ''),
			'country' = NULLIF(x.v.value('country[1]', 'VARCHAR(45)'), ''),
			'phone' = NULLIF(x.v.value('phone[1]', 'VARCHAR(45)'), ''),
			'website' = NULLIF(x.v.value('website[1]', 'VARCHAR(45)'), '')
		FROM @pi_xml.nodes('root/row') x(v);
	END TRY
	BEGIN CATCH
		SET @po_return_code = 1033; --0: No error; 1033: An error occurred
	END CATCH
END

Thanks.

pturmel · January 16, 2018, 8:37pm

You can’t serialize jython classes. Subclassing won’t work. I would expect you’d have to perform a query that returned a SQLXML object from your database, use .getClass() to obtain its implementation class, and tuck it away in a code module somewhere. Then figure out what that class’s constructor requires so that you can make more for other purposes.
Makes my head hurt.

PGriffith · January 16, 2018, 9:10pm

Yeah - this is the issue. The client isn't actually running the query directly against the database - what's happening is that the client packages up the statement, arguments, etc, and dispatches that as a message to the gateway asking it to run the procedure and return the results. The serialization exception is because the subclass created isn't properly serializable.

It's not working in this context because the client doesn't know how to package things for the gateway to consume - but the gateway should be able to package things into a format the client can read (I know this is confusing, but without testing it myself I'm assuming you can return results as java.sql.SQLXML objects.

chi · January 16, 2018, 9:17pm

Have you already found the Java documentation for SQLXML? You need direct access to the database connection to get an instance of SQLXML. This will be a problem in Ignition unless the scripting experts in this forum have an idea.

thecesrom · January 16, 2018, 9:52pm

I believe I see what you mean, @PGriffith.

Taken from Using SQLXML Objects:

Creating SQLXML Objects
In the following excerpt, the method Connection.createSQLXML is used to create an empty SQLXML object. The SQLXML.setString method is used to write data to the SQLXML object that was created.

Connection con = DriverManager.getConnection(url, props);
SQLXML xmlVal = con.createSQLXML();
xmlVal.setString(val);

On this proof of concept I was attempting to insert new data into the database, rather than reading from a table. In other words, I wanted the SQLXML parameter to be an INPUT parameter, rather than an OUT parameter, and for clarity purposes stating that my input parameter was of type system.db.SQLXML when registering the parameter. And while it's not "elegant", setting the parameter type to VARCHAR helped me accomplish my goal.

But before putting this to rest, I ask the following:

How should we work with system.db.SQLXML within Ignition?

I am no Java/Jython expert, so I would definitely appreciate if someone could help me here.

Thanks again.

Now. For the purpose of sharing, this is the method I developed (which will be part of the soon to be published GitHub repo of functions extending Ignition's libraries) for converting a Dataset into XML:

def to_xml(dataset, root='root', element='row'):
    """Returns a string XML representation of the Dataset.

    Args:
        dataset (Dataset): The input dataset.
        root (Optional[str]): The value of the root. If not provided, it defaults to 'root'.
        element (Optional[str]): The value of the row. If not provided, it defaults to 'row'.

    Returns:
        str: The string XML representation of the dataset.
    """
    headers = system.dataset.getColumnHeaders(dataset)
    data = system.dataset.toPyDataSet(dataset)
    new_line = '\n'
    tab = ' ' * 4
    ret_str = '<%s>%s' % (root, new_line)

    for row in data:
        ret_str += '%s<%s>%s' % (tab, element, new_line)
        for header in headers:
            ret_str += '%s<%s>%s</%s>%s' % (tab * 2, header, row[header], header, new_line)
        ret_str += '%s</%s>%s' % (tab, element, new_line)
    ret_str += '</%s>' % root

    return ret_str

NOTE: It works as long as the column headers share the same name as your columns in the database table.