Uploaded image for project: 'Groovy'
  1. Groovy
  2. GROOVY-5424

Problem with insert statements (GSQL) and CLOB data

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Cannot Reproduce
    • Affects Version/s: 1.8.1
    • Fix Version/s: None
    • Component/s: SQL processing
    • 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

      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

        Attachments

          Activity

            People

            • Assignee:
              pschumacher Pascal Schumacher
              Reporter:
              bgardner Brian Gardner
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: