[groovy-user] Oracle SQL sadness in Groovy

[Brian Gardner]

I have a query that I call in Groovy that is returning an error, though the query itself runs fine when called outside of groovy.sql.Sql:

The query is as follows, and uses Oracle's analytic functions. I have obfuscated some table and column names for the sake of my employer. 

SELECT a_id, invno, hostref, goods, domain, reftime, number_row

  FROM (SELECT a.id AS a_id,  a.invno, m.hostref, f.object_content goods,

               FIRST_VALUE (c.master)

               OVER (PARTITION BY a.id, a.invno, m.hostref

                     ORDER BY c.lastref DESC)

                  AS domain,

               FIRST_VALUE ( c.lastref)

               OVER (PARTITION BY a.id, a.invno, m.hostref

                     ORDER BY c.lastref DESC)

                 AS reftime,

               ROW_NUMBER ()

               OVER (PARTITION BY a.id, a.invno, m.hostref

                     ORDER BY c.lastref DESC)

                  AS number_row

          FROM tabowner.inv a

               JOIN tabowner.stage m ON a.invno = m.invno

               JOIN tabowner.files f ON F.HOSTREF = M.HOSTREF

       JOIN tabowner.invbc c ON a.id = c.a_id

         WHERE     c.master NOT IN ('w', 'b')

               AND a.status NOT IN ('D', 'M')

               AND F.OBJECT_CATEGORY = 'details')

WHERE number_row = 1

The error that I get is as follows:

Nov 22, 2013 4:05:57 PM groovy.sql.Sql eachRow

WARNING: Failed to execute:

       SELECT

…

WHERE number_row = 1

because: Invalid column name

Caught: java.sql.SQLException: Invalid column name

java.sql.SQLException: Invalid column name

       at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3677)

       at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:2749)

       at oracle.jdbc.driver.OracleResultSet.getObject(OracleResultSet.java:458)

       at $Proxy4.getProperty(Unknown Source)

       at net.jpmc.gti.psat.load.LoadBpConfPsat$_run_closure1.doCall(Load.groovy:74)

       at net.jpmc.gti.psat.load.LoadBpConfPsat.run(Load.groovy:73)

I'm wondering what could cause a SQL statement to fail in this way, when the query I copy out of the stack trace, which has no placeholders, is used in a Sql.eachRow(). Could it be that the underlying java.sql "needs" to understand this Oracle-specific syntax? Seems like it would just send whatever you type to the Oracle driver class, and that the failure would only occur if the driver or database rejected the statement. 

Your help is appreciated.

Brian Gardner

[Nelson, Erick [HDS]]

When you say outside of groovy sql do you mean only sqlplus or does that also include jdbc in plain java ?

 

Anyways, try and giving your inline view a name and see if that works.

Here I’ve aliased my inline view with a v….

 

SELECT v.a_id, v.invno, v.hostref, v.goods, v.domain, v.reftime, v.number_row

  FROM (SELECT a.id AS a_id,  a.invno, m.hostref, f.object_content goods,

               FIRST_VALUE (c.master)

               OVER (PARTITION BY a.id, a.invno, m.hostref

                     ORDER BY c.lastref DESC)

                  AS domain,

               FIRST_VALUE ( c.lastref)

               OVER (PARTITION BY a.id, a.invno, m.hostref

                     ORDER BY c.lastref DESC)

                 AS reftime,

               ROW_NUMBER ()

               OVER (PARTITION BY a.id, a.invno, m.hostref

                     ORDER BY c.lastref DESC)

                  AS number_row

          FROM tabowner.inv a

               JOIN tabowner.stage m ON a.invno = m.invno

               JOIN tabowner.files f ON F.HOSTREF = M.HOSTREF

                       JOIN tabowner.invbc c ON a.id = c.a_id

         WHERE     c.master NOT IN ('w', 'b')

               AND a.status NOT IN ('D', 'M')

               AND F.OBJECT_CATEGORY = 'details') v

WHERE v.number_row = 1

[Brian Gardner]

Pure genius, Erick. I think that did it. 

Now, the next wrinkle is I'm wrapping that query in a transaction, using sql.withBatch(). I'm getting an error on the statement passed into that, as follows:

def assetQry = '''previous long query'''

def insertSelect = '''

insert into tabowner.external (seq, domain, server, a_id)

values (?,?,?,?)

'''

sql.withTransaction {

def insertCount = sql.withBatch (1000, insertSelect) { stmt ->

sql.eachRow(assetQry) { row ->

... more code

stmt.addBatch ([idx, row.domain, propVal[0][1], row.a_id])

}

}

}

This statement, insertSelect,  was working well before I added it to withBatch. I'm using Groovy 2.1.8, and I'm pretty sure that parameterized replacements have been supported since 1.8.something.

Thanks so much for your response. 

Brian

[Nelson, Erick [HDS]]

I’ve never used  sql.withBatch or stmt.addBatch

But you’ve piqued my interest and I will poke around with it …. later

[Dinko Srkoč]

I'm not at home with Oracle and your code generally seems fine, so I'm
just shooting in the dark.

Are you sure you're getting an error on the `sql.withBatch` line and
not on the `stmt.addBatch`? Have you tried with a lower value of batch
size? Perhaps your driver/Oracle cannot handle so many sql statements
in one batch operation.

Cheers,
Dinko

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

[Brian Gardner]

So here's a self-contained JUnit test version of the withBatch/addBatch thing. I wrote it in hopes of better understanding how these method worked. In Groovy 2.1.8 it fails…

@Test

public void batchInsert() {

       def createSql = '''create table tmp_batch_test (myvalue varchar2(20))'''

       def dropSql = '''drop table tmp_batch_test'''

       def insertSelect = '''insert into tmp_batch_test (myvalue) values (?)'''

       sql.execute(createSql)

       sql.withTransaction {

              def insertCount = sql.withBatch (10, insertSelect) { stmt ->

                     [1..20].each {val ->

                           stmt.addBatch ("value${val}")

                     }

              }

              println "inserted ${insertCount}"

       }

       sql.execute(dropSql)

}

 

 

Nov 25, 2013 10:15:30 AM groovy.sql.Sql withBatch

WARNING: Error during batch execution of '

              insert into tmp_batch_test (myvalue) values (?)

       ' with message: Unsupported feature

Nov 25, 2013 10:15:30 AM groovy.sql.Sql handleError

WARNING: Rolling back due to: Unsupported feature

 

Brian (still sad)

[Dinko Srkoč]

In order to run your example on Informix I had to do three things:

* convert GString `"value${val}"` to String before passing it to `addBatch`
* change `List<IntRange>` to just `IntRange` by replacing `[...]` with `(...)`
* put the parameter into a List (or Array)

(1..20).each { val ->
stmt.addBatch(["value${val}" as String])
}

or using array:

stmt.addBatch(["value${val}"] as String[])

Perhaps with some or all of the above modifications it could work on Oracle too?

Cheers,
Dinko

[Brian Gardner]

Dinko,

Doing the "as String conversion" was enough for this example. So the batch stuff works, just not with the query and insert I have for my real world example. I put both the query and insert into a test file, and run them, and they work fine. I use them within a batch, and get invalid column name errors.

That makes no sense to me at all. I'll take the simple test below, and start substituting (one by one) the elements of the real code until I get an error, to see where the problem really lies.

In the meantime, if anyone can confirm whether withBatch and addBatch will work with positional parameters in Groovy 2.1.8 that would be helpful.

Brian

[Dinko Srkoč]

On 25 November 2013 19:58, Brian Gardner <bgsa...@gmail.com> wrote:
> [...]

> In the meantime, if anyone can confirm whether withBatch and addBatch will work with positional parameters in Groovy 2.1.8 that would be helpful.

I can confirm that `withBatch` and `addBatch` work as advertised in
the docs[1] for Groovy 2.0.x, 2.1.x, and 2.2. This includes positional
and named parameters.

Cheers,
Dinko

[1]: http://groovy.codehaus.org/api/groovy/sql/Sql.html#withBatch(int,
java.lang.String, groovy.lang.Closure)

PS
Actual Groovy versions that I tried are: 2.0.6, 2.1.8, 2.2.0; DBMS is
IBM Informix 11.50.

[Brian Gardner]

I have resolved my issue. The problem was one of misdirection in the error message, which made it appear that something in the withBatch and/or addBatch was at fault, since both calls and SQL statements were in the error message. The real problem was a syntax error inside eachRow for the query that formed the basis for the insert. 

Anecdotally, withBatch and addBatch made a huge difference. My Groovy script selects data, iterating through the result set, and inserts data in another table. The number of inserted records in my case was somewhere between 750K and a million rows. When I did the following:

sql.eachRow(assetQry) { row ->

… data munging… 

sql.executeInsert (insertSelect, [idx.value, row.domain as String, propVal[0][1] asString, row.asset_id])

}

It took about 3 hours to load just under a million rows.

When I changed it to:

sql.withTransaction {

       def insertCount = dwconn.withBatch (1000, insertSelect) { BatchingPreparedStatementWrapper stmt ->

              sql.eachRow(assetQry) { row ->

… data munging… 

stmt.addBatch ([idx.value, row.domain as String, propVal[0][1] asString, row.asset_id])

}

}

}

The same set of records completed in 1 minute, 11 seconds.

Thanks for all your help and input. 

Brian