[groovy-user] "This function is not supported" when using Sql executeInsert

[Rick]

When I try to call groovy's sql.executeInsert passing in an insert statement and parms I get
java.sql.SQLException: This function is not supported
If I switch to sql.execute the insert works. Any idea what could be wrong? I posted the entire stacktrace at the end of this email.

(Also, as a side note, I think the groovy sql docs are a bit wrong on this method? http://groovy.codehaus.org/gapi/groovy/sql/Sql.html#executeInsert%28String%29 They show an example calling...

  def keys = sql.insert("insert into test_table (INT_DATA, STRING_DATA) "
                           + "VALUES (1, 'Key Largo')")

I don't recall an 'insert' method on groovy.sql.Sql?




java.sql.SQLException: This function is not supported
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.Util.notSupported(Unknown Source)
    at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
    at groovy.sql.Sql.createPreparedStatement(Sql.java:1797)
    at groovy.sql.Sql.getPreparedStatement(Sql.java:1790)
    at groovy.sql.Sql.executeInsert(Sql.java:930)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite$PojoCachedMethodSiteNoUnwrapNoCoerce.invoke(PojoMetaMethodSite.java:229)
    at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite.call(PojoMetaMethodSite.java:52)
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:40)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:117)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:129)
    at com.clevertide.employee.persistence.groovysql.EmployeeDaoGroovySql.updateOrSave(EmployeeDaoGroovySql.groovy:35)
    at com.clevertide.employee.persistence.EmployeeDao$updateOrSave.call(Unknown Source)
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:40)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:117)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
    at com.clevertide.test.employee.persistence.EmployeeGroovySqlTest.testInsertAndDelete(EmployeeGroovySqlTest.groovy:53)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at junit.framework.TestCase.runTest(TestCase.java:168)
    at junit.framework.TestCase.runBare(TestCase.java:134)
    at junit.framework.TestResult$1.protect(TestResult.java:110)
    at junit.framework.TestResult.runProtected(TestResult.java:128)
    at junit.framework.TestResult.run(TestResult.java:113)
    at junit.framework.TestCase.run(TestCase.java:124)
    at junit.framework.TestSuite.runTest(TestSuite.java:232)
    at junit.framework.TestSuite.run(TestSuite.java:227)
    at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:79)
    at org.apache.maven.surefire.junit4.JUnit4TestSet.execute(JUnit4TestSet.java:62)
    at org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.executeTestSet(AbstractDirectoryTestSuite.java:140)
    at org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.execute(AbstractDirectoryTestSuite.java:127)
    at org.apache.maven.surefire.Surefire.run(Surefire.java:177)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.maven.surefire.booter.SurefireBooter.runSuitesInProcess(SurefireBooter.java:338)
    at org.apache.maven.surefire.booter.SurefireBooter.main(SurefireBooter.java:997)

[Rick]

I looked at the source code and I bet it's somehow related to using an Identity column in HSQLDB. Basically I want a way to get the id of the identity column after the insert takes place.

This is how I'm creating my HSQLDB tables:

CREATE TABLE Department (
 "id" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1)  NOT NULL,
  "name" VARCHAR(256),
  PRIMARY KEY ("id")
);

--
Rick R

[Paul King]

Yep, that javadoc had a typo and was fixed a week or two ago.
View source or latest CI artifacts for correction, e.g.:
http://bamboo.ci.codehaus.org/download/GROOVY-GROOVY160J14/artifacts/build-165/documentation/gapi/groovy/sql/Sql.html

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

http://xircles.codehaus.org/manage_email

[John Bito]

Hi Rick,

I'm really not familiar with HSQLDB, but I'm curious about the
statement that's triggering the error, as I have some Oracle issues
that are in the same vein. The way the error comes from HSQLDB makes
me wonder if it's something about the INSERT statement in your code
that's considered invalid by HSQLDB. If it's something you can post,
I'd be interested to have a look at it.

Good Luck!
John

[Rick]

On Mon, Aug 17, 2009 at 11:17 AM, John Bito <jwb...@gmail.com> wrote:

 
I'm really not familiar with HSQLDB, but I'm curious about the
statement that's triggering the error, as I have some Oracle issues
that are in the same vein.  The way the error comes from HSQLDB makes
me wonder if it's something about the INSERT statement in your code
that's considered invalid by HSQLDB.  If it's something you can post,
I'd be interested to have a look at it.

Here's what I ended up doing (I know the below should be in a transaction which I'll end up doing):

//quotes are because I have case-sensetive column names, which are annoying in HSQLDB
String INSERT_EMPLOYEE = """INSERT INTO employee ("firstName", "lastName", "age", "departmentId")
VALUES (?,?,?,?) """

//get back the id inserted in HSQLDB:

 sql.execute(INSERT_EMPLOYEE, params)
 def results = sql.firstRow("CALL IDENTITY()")
 return results[0] 

[John Bito]

Yeah - that's just the kind of thing I need to get out of my code by
using executeInsert - I don't want to make two requests of the DB if
there's a way to avoid it.

I'm curious how the IDENTITY function would interact with transactions
in HSQLDB. In Oracle, I would have to use the highest level of
transaction isolation ('serializable'/'snapshot') which seems that it
would put my code at risk of having performance problem if it avoids
the race condition. You can avoid that by requesting the new, unused
sequence number first and supplying it in the INSERT, as long as you
don't care that some sequence number could be skipped due to
exceptions.

In Oracle, that looks like.

sql.execute('INSERT INTO employee (ID, "firstName", "lastName", "age",
"departmentId") VALUES (?,?,?,?,?)',
[sql.firstRow("SELECT sequence_name.next_val FROM dual")[0], fn,
ln, age, dept])

I'm just beginning to think about out how the executeInsert method
will work with Oracle. My ojdbc6 driver returns true for
supportsGetGeneratedKeys. What does your HSQLDB driver say?

---------------------------------------------------------------------

[Rick]

On Mon, Aug 17, 2009 at 5:06 PM, John Bito <jwb...@gmail.com> wrote:

 
I'm just beginning to think about out how the executeInsert method
will work with Oracle.  My ojdbc6 driver returns true for
supportsGetGeneratedKeys.  What does your HSQLDB driver say?

 

I hope I went about this correctly, I made this call in my code:

Connection conn = mydb.sql.getDataSource().getConnection()
DatabaseMetaData metadata = conn.getMetaData()
println "***** supportsGetGeneratedKeys ${metadata.supportsGetGeneratedKeys()}"

and it returned false.

The docs are confusing http://hsqldb.org/doc/src/org/hsqldb/jdbc/jdbcDatabaseMetaData.html#supportsGetGeneratedKeys%28%29 since it says "Up to and including 1.7.2," and I'm using 1.8. The doc above seems to imply that at least through 1.7 it always returns false. (Why not just say "For now, this always returns false" - until they update the driver and then modify the docs accordingly.)
 

[John Bito]

The appearance of getGeneratedKeys on the HSQLDB 1.9 features list
<http://hsqldb.org/web/features190.html> leads me to believe that it's
not available in current release versions.

---------------------------------------------------------------------