Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Cannot Reproduce
-
1.8.1
-
None
-
Groovy Version: 1.8.1 JVM: 1.6.0_26
Oracle client jar: ojdbc14-10.2.0.1.0.jar
Oracle version: 11.2.0.2
Description
I've been having real problems with trying to insert XML data to an Oracle CLOB. It seems
that I can update a CLOB, but not insert.
Example code (a little longish for completeness) follows:
sql.execute '''
create table CLOBTESTER (
id integer not null,
name varchar(50),
xml CLOB
)
'''
def hierarchyName = 'Hierarchy One'
def hierarchySrc = 'A'
def columns = ['PROJECT_NAME','PROJECT_DESCRIPTION','PROJECT_ID']
def filterList = [[columnName:'PROJECT_ID',valueList:['1234','2345','3456']]]
def sw = new StringWriter()
def builder = new groovy.xml.MarkupBuilder(sw)
builder.hierarchy(name:"${hierarchyName}", source:"${hierarchySrc}") {
columns.each {
column("${it}")
}
filters {
filterList.each { filt ->
filter(column:"${filt.columnName}") {
filt.valueList.each {
value("${it}")
}
}
}
}
}
def myId = 1001
def myXml = sw.toString()
def insertSql = """
INSERT INTO CLOBTESTER
(id, name, xml)
values ($myId, $hierarchyName, $myXml)
"""
sql.execute insertSql
assert sql.updateCount == 1
This returns the following error from Oracle:
Apr 26, 2012 9:27:08 AM groovy.sql.Sql execute WARNING: Failed to execute: INSERT INTO CLOBTESTER (id, name, xml) values (?, ?, ?) because: Invalid column type
In the past, when I've gotten this kind of error, I have had to use toString() on the
GString (insertSql) to get the values to resolve prior to doing the insert.
If you do that without quoting character data, you will get an error, since the parser
doesn't really know to do it for you.
def insertSql = """ INSERT INTO CLOBTESTER (id, name, xml) values ($myId, '$hierarchyName', '$myXml') """.toString()
Then you get the following error:
Apr 26, 2012 9:40:21 AM groovy.sql.Sql execute WARNING: Failed to execute: INSERT INTO CLOBTESTER (id, name, xml) values (1001, 'Hierarchy One', '<hierarchy name='Hierarchy One' source='A'> <column>PROJECT_NAME</column> <column>PROJECT_DESCRIPTION</column> <column>PROJECT_ID</column> <filters> <filter column='PROJECT_ID'> <value>1234</value> <value>2345</value> <value>3456</value> </filter> </filters> </hierarchy>') because: ORA-00917: missing comma
OK, that seems to make sense, since you have attributes with nasty quotes in them in
your XML. That's normal enough for any real XML. Here is the workaround that I had to
do. Thank God the xml column is nullable...
// insert partial data def insertSql = """ INSERT INTO CLOBTESTER (id, name) values ($myId, $hierarchyName) """ sql.execute insertSql assert sql.updateCount == 1 // now update the xml sql.execute "UPDATE CLOBTESTER SET xml = $myXml where name = $hierarchyName" assert sql.updateCount == 1
This works... sometimes... I have examples where if you have multiple columns referenced
in the WHERE clause, or literals in the WHERE clause, all bets are off. In those cases I
have had to resort to referencing those variables outside the statement:
def updateData = ["${myXml}".toString(), "${hierarchyName}"] sql.execute "UPDATE CLOBTESTER SET xml = ? where name = ?", updateData
Really there seems to be no way to tell which way is going to work, which makes building
a reliable standard approach very difficult. In any event, it seems that it should not
be necessary to have such gyrations to do this.
Here is my environment:
Groovy Version: 1.8.1 JVM: 1.6.0_26
Oracle client jar: ojdbc14-10.2.0.1.0.jar
Oracle version: 11.2.0.2