I'm trying to do a lookup and I'm using the upper method to upper case the name and DB2 is throwing Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0418N A statement contains a use of a parameter marker that is not valid. SQLSTATE=42610. Is there something that I'm doing wrong? Thanks.


Hibernate version: 2.1.6

Mapping documents: Artist.hbm.xml
<hibernate-mapping>

<class name="com.oreilly.hh.Artist" table="ARTIST">
<meta attribute="class-description">
Represents an artist who is associated with a track or album.
</meta>

<id name="id" type="int" column="ARTIST_ID">
<meta attribute="scope-set">protected</meta>
<generator class="native"/>
</id>

<property name="name" type="string">
<meta attribute="use-in-tostring">true</meta>
<column name="NAME" not-null="true" unique="true" index="ARTIST_NAME"/>
</property>

<set name="tracks" table="TRACK_ARTISTS" inverse="true">
<meta attribute="field-description">Tracks by this artist</meta>
<key column="ARTIST_ID"/>
<many-to-many class="com.oreilly.hh.Track" column="TRACK_ID"/>
</set>

</class>

<query name="com.oreilly.hh.artistByName">
<![CDATA[
from com.oreilly.hh.Artist as artist
where upper(artist.name) = upper(:name)
]]>
</query>

</hibernate-mapping>




Code between sessionFactory.openSession() and session.close():

Transaction tx = null;
try {
// Create some data and persist it
tx = session.beginTransaction();

Track track = new Track("Russian Trance",
"vol2/album610/track02.mp3",
Time.valueOf("00:03:30"), new Date(),
(short)0, new HashSet());

Query query = session.getNamedQuery(
"com.oreilly.hh.artistByName");
query.setString("name", "PPK");
Artist found = (Artist)query.uniqueResult();
if (found == null) {
found = new Artist("PPK", new HashSet());
session.save(found);
}
// We're done; make our changes permanent
tx.commit();

} catch (Exception e) {
if (tx != null) {
// Something went wrong; discard all partial changes
tx.rollback();
}
throw e;
} finally {
// No matter what, close the session
session.close();
}

Full stack trace of any exception that occurs:

[java] 09:50:56,946 INFO Environment:469 - Hibernate 2.1.6
[java] 09:50:56,966 INFO Environment:503 - loaded properties from resource hibernate.properties: {hibernate.connection.username=db2admin, hibernate.connection.password=db2admin, hibernate.cglib.use_reflection_optimizer=true, hibernate.dialect=net.sf.hibernate.dialect.DB2Dialect, hibernate.connection.url=jdbc:db2:MKTAUTO1, hibernate.connection.driver_class=COM.ibm.db2.jdbc.app.DB2Driver}
[java] 09:50:56,966 INFO Environment:529 - using CGLIB reflection optimizer
[java] 09:50:56,986 INFO Configuration:350 - Mapping resource: com/oreilly/hh/Track.hbm.xml
[java] 09:50:57,547 DEBUG DTDEntityResolver:20 - trying to locate http://hibernate.sourceforge.net/hibern ... ng-2.0.dtd in classpath under net/sf/hibernate/
[java] 09:50:57,557 DEBUG DTDEntityResolver:29 - found http://hibernate.sourceforge.net/hibern ... ng-2.0.dtd in classpath
[java] 09:50:57,788 INFO Binder:229 - Mapping class: com.oreilly.hh.Track -> TRACK
[java] 09:50:57,958 DEBUG Binder:486 - Mapped property: id -> TRACK_ID, type: integer
[java] 09:50:57,988 DEBUG Binder:486 - Mapped property: title -> TITLE, type: string
[java] 09:50:57,988 DEBUG Binder:486 - Mapped property: filePath -> filePath, type: string
[java] 09:50:57,988 DEBUG Binder:486 - Mapped property: playTime -> playTime, type: time
[java] 09:50:57,988 DEBUG Binder:486 - Mapped property: added -> added, type: date
[java] 09:50:57,988 DEBUG Binder:486 - Mapped property: volume -> volume, type: short
[java] 09:50:57,998 INFO Binder:571 - Mapping collection: com.oreilly.hh.Track.artists -> TRACK_ARTISTS
[java] 09:50:58,018 DEBUG Binder:486 - Mapped property: artists, type: java.util.Set
[java] 09:50:58,028 DEBUG Binder:1280 - Named query: com.oreilly.hh.tracksNoLongerThan ->
[java] from com.oreilly.hh.Track as track where track.playTime <= :length
[java]
[java] 09:50:58,038 INFO Configuration:350 - Mapping resource: com/oreilly/hh/Artist.hbm.xml
[java] 09:50:58,058 DEBUG DTDEntityResolver:20 - trying to locate http://hibernate.sourceforge.net/hibern ... ng-2.0.dtd in classpath under net/sf/hibernate/
[java] 09:50:58,058 DEBUG DTDEntityResolver:29 - found http://hibernate.sourceforge.net/hibern ... ng-2.0.dtd in classpath
[java] 09:50:58,108 INFO Binder:229 - Mapping class: com.oreilly.hh.Artist -> ARTIST
[java] 09:50:58,108 DEBUG Binder:486 - Mapped property: id -> ARTIST_ID, type: integer
[java] 09:50:58,108 DEBUG Binder:486 - Mapped property: name -> NAME, type: string
[java] 09:50:58,108 INFO Binder:571 - Mapping collection: com.oreilly.hh.Artist.tracks -> TRACK_ARTISTS
[java] 09:50:58,118 DEBUG Binder:486 - Mapped property: tracks, type: java.util.Set
[java] 09:50:58,118 DEBUG Binder:1280 - Named query: com.oreilly.hh.artistByName ->
[java]
[java] from com.oreilly.hh.Artist as artist
[java] where upper(artist.name) = upper(:name)
[java]
[java]
[java] 09:50:58,118 INFO Configuration:627 - processing one-to-many association mappings
[java] 09:50:58,118 DEBUG Binder:1353 - Second pass for collection: com.oreilly.hh.Track.artists
[java] 09:50:58,138 DEBUG Binder:1368 - Mapped collection key: TRACK_ID, element: ARTIST_ID, type: com.oreilly.hh.Artist
[java] 09:50:58,138 DEBUG Binder:1353 - Second pass for collection: com.oreilly.hh.Artist.tracks
[java] 09:50:58,138 DEBUG Binder:1368 - Mapped collection key: ARTIST_ID, element: TRACK_ID, type: com.oreilly.hh.Track
[java] 09:50:58,138 INFO Configuration:636 - processing one-to-one association property references
[java] 09:50:58,168 INFO Configuration:661 - processing foreign key constraints
[java] 09:50:58,168 DEBUG Configuration:678 - resolving reference to class: com.oreilly.hh.Artist
[java] 09:50:58,168 DEBUG Configuration:678 - resolving reference to class: com.oreilly.hh.Track
[java] 09:50:58,208 INFO Dialect:82 - Using dialect: net.sf.hibernate.dialect.DB2Dialect
[java] 09:50:58,208 INFO SettingsFactory:63 - Use outer join fetching: true
[java] 09:50:58,218 INFO DriverManagerConnectionProvider:42 - Using Hibernate built-in connection pool (not for production use!)
[java] 09:50:58,218 INFO DriverManagerConnectionProvider:43 - Hibernate connection pool size: 20
[java] 09:50:58,469 INFO DriverManagerConnectionProvider:77 - using driver: COM.ibm.db2.jdbc.app.DB2Driver at URL: jdbc:db2:MKTAUTO1
[java] 09:50:58,469 INFO DriverManagerConnectionProvider:78 - connection properties: {user=db2admin, password=db2admin}
[java] 09:50:58,489 INFO TransactionManagerLookupFactory:33 - No TransactionManagerLookup configured (in JTA environment, use of process level read-write cache is not recommended)
[java] 09:50:58,489 DEBUG DriverManagerConnectionProvider:84 - total checked-out connections: 0
[java] 09:50:58,489 DEBUG DriverManagerConnectionProvider:100 - opening new JDBC connection
[java] 09:50:58,599 DEBUG DriverManagerConnectionProvider:106 - created connection to: jdbc:db2:MKTAUTO1, Isolation Level: 2
[java] 09:50:58,599 DEBUG DriverManagerConnectionProvider:120 - returning connection to pool, pool size: 1
[java] 09:50:58,599 INFO SettingsFactory:103 - Use scrollable result sets: true
[java] 09:50:58,599 INFO SettingsFactory:106 - Use JDBC3 getGeneratedKeys(): false
[java] 09:50:58,609 INFO SettingsFactory:109 - Optimize cache for minimal puts: false
[java] 09:50:58,609 INFO SettingsFactory:118 - Query language substitutions: {}
[java] 09:50:58,609 INFO SettingsFactory:129 - cache provider: net.sf.hibernate.cache.EhCacheProvider
[java] 09:50:58,609 INFO Configuration:1116 - instantiating and configuring caches
[java] 09:50:58,849 INFO SessionFactoryImpl:118 - building session factory
[java] 09:50:58,849 DEBUG SessionFactoryImpl:124 - instantiating session factory with properties: {java.runtime.name=Java(TM) 2 Runtime Environment, Standard Edition, hibernate.connection.password=db2admin, sun.boot.library.path=C:\Work\Sun\AppServer\jdk\jre\bin, java.vm.version=1.4.2_02-b03, hibernate.connection.username=db2admin, java.vm.vendor=Sun Microsystems Inc., java.vendor.url=http://java.sun.com/, path.separator=;, java.vm.name=Java HotSpot(TM) Client VM, file.encoding.pkg=sun.io, user.country=US, sun.os.patch.level=Service Pack 3, java.vm.specification.name=Java Virtual Machine Specification, user.dir=C:\Work\WSADWorkspaces\Hibernate\Hibernate, java.runtime.version=1.4.2_02-b03, java.awt.graphicsenv=sun.awt.Win32GraphicsEnvironment, java.endorsed.dirs=C:\Work\Sun\AppServer\jdk\jre\lib\endorsed, os.arch=x86, java.io.tmpdir=C:\WINNT\TEMP\, line.separator=
[java] , java.vm.specification.vendor=Sun Microsystems Inc., user.variant=, os.name=Windows 2000, sun.java2d.fontpath=, java.library.path=C:\Work\Sun\AppServer\jdk\jre\bin;.;C:\WINNT\system32;C:\WINNT;C:\PROGRA~1\RATIONAL\RATION~1\NUTCROOT\bin;C:\PROGRA~1\RATIONAL\RATION~1\NUTCROOT\bin\x11;C:\Progra~1\Rational\common;C:\Progra~1\Rational\ClearQuest;C:\Progra~1\Rational\Rose\TopLink\;C:\Progra~1\Rational\Rational Test;C:\WINNT\system32;C:\WINNT;C:\WINNT\System32\Wbem;c:\progra~1\sybase\OCS-12_0\dll;c:\progra~1\sybase\OCS-12_0\bin;C:\Progra~1\Common Files\Adaptec Shared\System;C:\Progra~1\Executive Software\DiskeeperWorkstation\;C:\Progra~1\IBM\SQLLIB\BIN;C:\Progra~1\MyPrograms\editors\Visual Slick Edit\win;C:\Work\dev\cygwin\bin;C:\Progra~1\MyPrograms\dev\jakarta-ant-1.5\bin;C:\Progra~1\Compuware\DevPartner Java Edition\bin;C:\Progra~1\MyPrograms\dev\jikes;C:\Work\Sun\AppServer\jdk\bin;C:\Work\Sun\AppServer\bin, java.specification.name=Java Platform API Specification, java.class.version=48.0, java.util.prefs.PreferencesFactory=java.util.prefs.WindowsPreferencesFactory, os.version=5.0, user.home=\\Wincs-006B-01\clientdata$\n0098840, user.timezone=America/New_York, java.awt.printerjob=sun.awt.windows.WPrinterJob, file.encoding=Cp1252, java.specification.version=1.4, hibernate.connection.driver_class=COM.ibm.db2.jdbc.app.DB2Driver, java.class.path=C:\Work\WSADWorkspaces\Hibernate\Hibernate\classes;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\ant-1.5.3.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\ant-optional-1.5.3.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\c3p0-0.8.4.5.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\cglib-full-2.0.2.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\Common.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\commons-collections-2.1.1.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\commons-collections-2.1.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\commons-dbcp-1.2.1.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\commons-lang-1.0.1.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\commons-logging-1.0.4.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\commons-pool-1.2.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\concurrent-1.3.3.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\connector.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\db2fs.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\db2java.zip;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\db2jcc.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\db2jcc_license_cu.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\dom4j-1.4.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\ehcache-0.9.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\hibernate-tools.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\hibernate2.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\hsqldb.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\jaas.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\jboss-cache.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\jboss-common.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\jboss-jmx.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\jboss-system.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\jcs-1.0-dev.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\jdbc2_0-stdext.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\jdom.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\jgroups-2.2.3.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\jta.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\junit-3.8.1.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\log4j-1.2.8.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\odmg-3.0.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\oscache-2.0.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\proxool-0.8.3.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\sqlj.zip;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\swarmcache-1.0rc2.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\velocity-1.3.1.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\xalan-2.4.0.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\xerces-2.4.0.jar;C:\Work\WSADWorkspaces\Hibernate\Hibernate\lib\xml-apis.jar, user.name=n0098840, java.vm.specification.version=1.0, java.home=C:\Work\Sun\AppServer\jdk\jre, sun.arch.data.model=32, hibernate.dialect=net.sf.hibernate.dialect.DB2Dialect, hibernate.connection.url=jdbc:db2:MKTAUTO1, user.language=en, java.specification.vendor=Sun Microsystems Inc., awt.toolkit=sun.awt.windows.WToolkit, hibernate.cglib.use_reflection_optimizer=true, java.vm.info=mixed mode, java.version=1.4.2_02, java.ext.dirs=C:\Work\Sun\AppServer\jdk\jre\lib\ext, sun.boot.class.path=C:\Work\Sun\AppServer\jdk\jre\lib\rt.jar;C:\Work\Sun\AppServer\jdk\jre\lib\i18n.jar;C:\Work\Sun\AppServer\jdk\jre\lib\sunrsasign.jar;C:\Work\Sun\AppServer\jdk\jre\lib\jsse.jar;C:\Work\Sun\AppServer\jdk\jre\lib\jce.jar;C:\Work\Sun\AppServer\jdk\jre\lib\charsets.jar;C:\Work\Sun\AppServer\jdk\jre\classes, java.vendor=Sun Microsystems Inc., file.separator=\, java.vendor.url.bug=http://java.sun.com/cgi-bin/bugreport.cgi, sun.io.unicode.encoding=UnicodeLittle, sun.cpu.endian=little, sun.cpu.isalist=pentium i486 i386}
[java] 09:50:59,510 DEBUG SessionFactoryObjectFactory:39 - initializing class SessionFactoryObjectFactory
[java] 09:50:59,520 DEBUG SessionFactoryObjectFactory:76 - registered: 8aee3c74fe7c76e000fe7c76e3ac0000 (unnamed)
[java] 09:50:59,520 INFO SessionFactoryObjectFactory:82 - Not binding factory to JNDI, no JNDI name configured
[java] 09:50:59,520 DEBUG SessionFactoryImpl:195 - instantiated session factory
[java] 09:50:59,660 DEBUG SessionImpl:555 - opened session
[java] 09:50:59,670 DEBUG JDBCTransaction:37 - begin
[java] 09:50:59,670 DEBUG DriverManagerConnectionProvider:84 - total checked-out connections: 0
[java] 09:50:59,670 DEBUG DriverManagerConnectionProvider:90 - using pooled JDBC connection, pool size: 0
[java] 09:50:59,670 DEBUG JDBCTransaction:41 - current autocommit status:false
[java] 09:50:59,700 DEBUG SessionImpl:1527 - find:
[java]
[java] from com.oreilly.hh.Artist as artist
[java] where upper(artist.name) = upper(:name)
[java]
[java]
[java] 09:50:59,710 DEBUG QueryParameters:112 - named parameters: {name=PPK}
[java] 09:50:59,730 DEBUG QueryTranslator:147 - compiling query
[java] 09:50:59,770 DEBUG SessionImpl:2246 - flushing session
[java] 09:50:59,770 DEBUG SessionImpl:2439 - Flushing entities and processing referenced collections
[java] 09:50:59,770 DEBUG SessionImpl:2780 - Processing unreferenced collections
[java] 09:50:59,770 DEBUG SessionImpl:2794 - Scheduling collection removes/(re)creates/updates
[java] 09:50:59,770 DEBUG SessionImpl:2270 - Flushed: 0 insertions, 0 updates, 0 deletions to 0 objects
[java] 09:50:59,780 DEBUG SessionImpl:2275 - Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
[java] 09:50:59,780 DEBUG SessionImpl:1818 - Dont need to execute flush
[java] 09:50:59,780 DEBUG QueryTranslator:207 - HQL:
[java]
[java] from com.oreilly.hh.Artist as artist
[java] where upper(artist.name) = upper(:name)
[java]
[java]
[java] 09:50:59,780 DEBUG QueryTranslator:208 - SQL: select artist0_.ARTIST_ID as ARTIST_ID, artist0_.NAME as NAME from ARTIST artist0_ where (upper(artist0_.NAME)=upper(?))
[java] 09:50:59,780 DEBUG BatcherImpl:200 - about to open: 0 open PreparedStatements, 0 open ResultSets
[java] 09:50:59,800 DEBUG SQL:226 - select artist0_.ARTIST_ID as ARTIST_ID, artist0_.NAME as NAME from ARTIST artist0_ where (upper(artist0_.NAME)=upper(?))
[java] 09:50:59,800 DEBUG BatcherImpl:249 - preparing statement
[java] 09:50:59,810 DEBUG StringType:46 - binding 'PPK' to parameter: 1
[java] 09:50:59,830 DEBUG JDBCExceptionReporter:36 - SQL Exception
[java] COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0418N A statement contains a use of a parameter marker that is not valid. SQLSTATE=42610

[java] at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
[java] at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
[java] at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(Unknown Source)
[java] at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(Unknown Source)
[java] at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeQuery(Unknown Source)
[java] at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
[java] at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
[java] at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
[java] at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
[java] at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
[java] at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
[java] at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
[java] at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1544)
[java] at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
[java] at net.sf.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:550)
[java] at com.oreilly.hh.CreateTest.getArtist(CreateTest.java:31)
[java] at com.oreilly.hh.CreateTest.main(CreateTest.java:69)
[java] 09:50:59,851 WARN JDBCExceptionReporter:38 - SQL Error: -418, SQLState: 42610
[java] 09:50:59,851 ERROR JDBCExceptionReporter:46 - [IBM][CLI Driver][DB2/NT] SQL0418N A statement contains a use of a parameter marker that is not valid. SQLSTATE=42610

[java] 09:50:59,861 DEBUG BatcherImpl:207 - done closing: 0 open PreparedStatements, 0 open ResultSets
[java] 09:50:59,861 DEBUG BatcherImpl:269 - closing statement
[java] 09:50:59,861 DEBUG JDBCExceptionReporter:36 - SQL Exception
[java] COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0418N A statement contains a use of a parameter marker that is not valid. SQLSTATE=42610

[java] at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
[java] at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
[java] at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(Unknown Source)
[java] at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(Unknown Source)
[java] at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeQuery(Unknown Source)
[java] at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
[java] at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
[java] at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
[java] at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
[java] at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
[java] at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
[java] at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
[java] at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1544)
[java] at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
[java] at net.sf.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:550)
[java] at com.oreilly.hh.CreateTest.getArtist(CreateTest.java:31)
[java] at com.oreilly.hh.CreateTest.main(CreateTest.java:69)
[java] 09:50:59,921 WARN JDBCExceptionReporter:38 - SQL Error: -418, SQLState: 42610
[java] 09:50:59,921 ERROR JDBCExceptionReporter:46 - [IBM][CLI Driver][DB2/NT] SQL0418N A statement contains a use of a parameter marker that is not valid. SQLSTATE=42610

[java] 09:50:59,921 ERROR JDBCExceptionReporter:38 - Could not execute query
[java] COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0418N A statement contains a use of a parameter marker that is not valid. SQLSTATE=42610

[java] at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
[java] at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
[java] at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(Unknown Source)
[java] at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(Unknown Source)
[java] at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeQuery(Unknown Source)
[java] at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
[java] at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
[java] at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
[java] at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
[java] at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
[java] at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
[java] at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
[java] at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1544)
[java] at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
[java] at net.sf.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:550)
[java] net.sf.hibernate.JDBCException: Could not execute query
[java] at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1547)
[java] at com.oreilly.hh.CreateTest.getArtist(CreateTest.java:31)
[java] at com.oreilly.hh.CreateTest.main(CreateTest.java:69)
[java] 09:51:00,001 DEBUG JDBCTransaction:82 - rollback
[java] 09:51:00,001 DEBUG SessionImpl:585 - transaction completion
[java] 09:51:00,001 DEBUG SessionImpl:573 - closing session
[java] at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
[java] 09:51:00,001 DEBUG SessionImpl:3336 - disconnecting session
[java] at net.sf.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:550)
[java] at com.oreilly.hh.CreateTest.getArtist(CreateTest.java:31)
[java] 09:51:00,001 DEBUG DriverManagerConnectionProvider:120 - returning connection to pool, pool size: 1
[java] 09:51:00,001 DEBUG SessionImpl:585 - transaction completion
[java] at com.oreilly.hh.CreateTest.main(CreateTest.java:69)
[java] Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0418N A statement contains a use of a parameter marker that is not valid. SQLSTATE=42610

[java] at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
[java] at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
[java] at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(Unknown Source)
[java] at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(Unknown Source)
[java] at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeQuery(Unknown Source)
[java] at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
[java] at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
[java] at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
[java] at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
[java] at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
[java] at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
[java] at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
[java] at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1544)
[java] ... 4 more
[java] Exception in thread "main"
[java] Java Result: 1


Name and version of the database you are using:
IBM DB2 v8.1.3

Debug level Hibernate log excerpt:
log4j.rootLogger=warn, stdout

log4j.logger.net.sf.hibernate=debug

### log just the SQL
log4j.logger.net.sf.hibernate.SQL=debug

### log JDBC bind parameters ###
log4j.logger.net.sf.hibernate.type=debug

### log schema export/update ###
log4j.logger.net.sf.hibernate.tool.hbm2ddl=debug

Looks like a DB2 driver bug, doesn't it.

This is some information I found on the problem. Could Hibernate add the cast?

"The problem is in UPPER(?). If you read the rules for function resolution
in the SQL Reference, you'll see that they are very sensitive to the data
types of the parameters. Unfortunately, a parameter marker doesn't have a
type when it is precompiled, so DB2 doesn't know what type to use and you
get the error.

The solution is to use a CAST around the parameter marker to tell DB2 what
type to expact. For example:
UPPER(CAST(? AS CHAR(10))
UPPER(CAST(? AS VARCHAR(50))
The Db2 knows what type to use for the parameter marker and the function
can be properly resolved.

Note that the type in the CAST and the actual type bound in to the
parameter marker do _not_ have to be identical - they just have to be compatible.
So,
if I have UPPER(CAST(? AS VARCHAR(50)), I can bind in a VARCHAR(50) or a
CHAR(10) or any other type that is compatible with a VARCHAR(50). If I try
to bind in a VARCHAR(51), however, I'll get a string too long error."

My experience with DB2 is that it prefers UCASE instead of UPPER...

So is there a resoultion for this? Can we use the upper function with db2 in HQL?

Thanks,
Scott

Is it possible to use ucase in criterias?