[GROOVY-5424] Problem with insert statements (GSQL) and CLOB data

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Cannot Reproduce
• Affects Version/s: 1.8.1
• Component/s: SQL processing
• Labels: clob groovy gsql oracle
• 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
• Assignee: Pascal Schumacher
• Reporter: Brian Gardner
• Created: 26/Apr/12 09:17
• Updated: 03/Jul/13 16:17
• Resolved: 03/Jul/13 16:17

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:

ClobTester.groovy

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