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.