CREATE TABLE users(id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name VARCHAR(50) NOT NULL, passwd CHAR(254), status VARCHAR(255), hostname VARCHAR(255), firstname VARCHAR(100), lastname VARCHAR(150), title VARCHAR(50), address VARCHAR(255), zip VARCHAR(15), city VARCHAR(80), state VARCHAR(50), source_of_interest VARCHAR(255), scc VARCHAR(50), team_size VARCHAR(10), division_size VARCHAR(10), company VARCHAR(255), country VARCHAR(255), email VARCHAR(255), email_client VARCHAR(255), phone VARCHAR(255), mobil VARCHAR(255), date_format VARCHAR(255), datetime_format VARCHAR(255), timezone VARCHAR(255), downloadlimit INTEGER DEFAULT -1 NOT NULL, workspace_id INTEGER, browser VARCHAR(255), skills VARCHAR(4000), wiki_homepage_id INTEGER, registrydate TIMESTAMP, lastlogin TIMESTAMP, indexed CHAR(1) DEFAULT '0' NOT NULL, unused0 VARCHAR(255), unused1 VARCHAR(255), unused2 VARCHAR(255), UNIQUE(name)) ;
<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd";> <!-- $Revision: 19823 $ $Date: 2007-08-15 15:04:57 +0200 (Mi, 15 Aug 2007) $ --> <sqlMap namespace="User"> <cacheModel id="account-cache" type="LRU" readOnly="false" serialize="true"> <flushOnExecute statement="createUser" /> <flushOnExecute statement="updateUser" /> <flushOnExecute statement="deleteUser" /> <property name="size" value="50" /> </cacheModel> <typeAlias alias="userDto" type="com.intland.codebeamer.persistence.dto.UserDto"/> <resultMap id="userResult" class="userDto"> <result property="id" column="id"/> <result property="name" column="name"/> <result property="status" column="status"/> <result property="hostName" column="hostname"/> <result property="firstName" column="firstname"/> <result property="lastName" column="lastname"/> <result property="title" column="title"/> <result property="address" column="address"/> <result property="zip" column="zip"/> <result property="city" column="city"/> <result property="state" column="state"/> <result property="sourceOfInterest" column="source_of_interest"/> <result property="scc" column="scc"/> <result property="teamSize" column="team_size"/> <result property="divisionSize" column="division_size"/> <result property="company" column="company"/> <result property="country" column="country"/> <result property="email" column="email"/> <result property="emailClient" column="email_client"/> <result property="phone" column="phone"/> <result property="mobile" column="mobil"/> <result property="dateFormatPattern" column="date_format"/> <result property="dateTimeFormatPattern" column="datetime_format"/> <result property="timeZonePattern" column="timezone" /> <result property="downloadLimit" column="downloadlimit" /> <result property="browser" column="browser"/> <result property="skills" column="skills"/> <result property="wikiHomepageId" column="wiki_homepage_id" /> <result property="registryDate" column="registrydate" /> <result property="lastLogin" column="lastlogin" /> <result property="indexed" column="indexed"/> <result property="unused0" column="unused0"/> <result property="unused1" column="unused1"/> <result property="eauthid" column="unused2"/> </resultMap> <insert id="createUser" parameterClass="userDto"> INSERT INTO users (${PRE_ID_COMMENT}id,${POST_ID_COMMENT} name, passwd, status, hostname, firstname, lastname, title, address, zip, city, state, source_of_interest, scc, team_size,division_size, company, country, email, email_client, phone, mobil, date_format, datetime_format, timezone, downloadlimit, browser, skills, registrydate, lastlogin, unused0, unused1, unused2, indexed) VALUES (${PRE_ID_COMMENT}#id#, ${POST_ID_COMMENT} #name#, ${PASSWORD_ENCRYPT}(#password#), #status#, #hostName#, #firstName#, #lastName#, #title#, #address#, #zip#, #city#, #state#, #sourceOfInterest#, #scc#, #teamSize#, #divisionSize#, #company#, #country#, #email#, #emailClient#, #phone#, #mobile#, #dateFormatPattern#, #dateTimeFormatPattern#, #timeZonePattern#, #downloadLimit#, #browser#, #skills#, #registryDate#, #lastLogin#, #unused0#, #unused1#, #eauthid#, #indexed#) <selectKey resultClass="java.lang.Integer" keyProperty="id" type="${SELECTKEY_TYPE}" > ${SELECTKEY_BEFORE_TABLE_NAME_SQL_PART}users${SELECTKEY_AFTER_TABLE_NAME_SQL _PART} </selectKey> </insert> <update id="updateUser" parameterClass="userDto"> UPDATE users SET <isNotEmpty property="password"> passwd=${PASSWORD_ENCRYPT}(#password#),unused1=#unused1#, </isNotEmpty> firstname=#firstName#, lastname=#lastName#, title=#title#, address=#address#, zip=#zip#, city=#city#, state=#state#, company=#company#, country=#country#, email=#email#, phone=#phone#, mobil=#mobile#, date_format=#dateFormatPattern#, datetime_format=#dateTimeFormatPattern#, timezone=#timeZonePattern#, wiki_homepage_id=#wikiHomepageId#, <isNotNull property="name"> name=#name#, </isNotNull> <isNotNull property="emailClient"> email_client=#emailClient#, </isNotNull> <isNotNull property="sourceOfInterest"> source_of_interest=#sourceOfInterest#, </isNotNull> <isNotNull property="scc"> scc=#scc#, </isNotNull> <isNotNull property="teamSize"> team_size=#teamSize#, </isNotNull> <isNotNull property="divisionSize"> division_size=#divisionSize#, </isNotNull> <isNotNull property="browser"> browser=#browser#, </isNotNull> <isNotNull property="skills"> skills=#skills#, </isNotNull> <isNotNull property="status"> status=#status#, </isNotNull> <isNotNull property="downloadLimit"> downloadlimit=#downloadLimit#, </isNotNull> <isNotNull property="unused0"> unused0=#unused0#, </isNotNull> <isNotNull property="eauthid"> unused2=#eauthid#, </isNotNull> <isNotNull property="hostName"> hostname=#hostName#, </isNotNull> <isNotNull property="lastLogin"> lastlogin=#lastLogin#, </isNotNull> indexed='0' WHERE id = #id# </update> <update id="updateUserLastLogin" parameterClass="userDto"> UPDATE users SET lastlogin=#lastLogin# <isNotNull property="hostName" prepend=","> hostname=#hostName# </isNotNull> WHERE id=#id# </update> <delete id="deleteUser" parameterClass="java.lang.String"> DELETE FROM users WHERE $userIdInClause$ </delete> <update id="updateUserAsIndexed" > UPDATE users SET indexed='1' WHERE id=#value:INTEGER# </update> <update id="updateUserNotIndexed" > UPDATE users SET indexed='0' WHERE id=#value:INTEGER# </update> <select id="findAllUser" resultMap="userResult"> SELECT * FROM users </select> <select id="findNotIndexedUser" parameterClass="java.util.Map" resultMap="userResult"> SELECT * FROM users <dynamic prepend="WHERE"> <isPropertyAvailable property="indexed" prepend="AND"> indexed=#indexed# </isPropertyAvailable> </dynamic> </select> <select id="findUser" parameterClass="java.util.Map" cacheModel="account-cache" resultMap="userResult"> SELECT * FROM users <dynamic prepend="WHERE"> <isNotEmpty property="id" prepend="AND"> id IN <iterate property="id" open="(" close=")" conjunction=","> #id[]# </iterate> </isNotEmpty> <isNotEmpty property="name" prepend="AND"> name=#name# </isNotEmpty> <isNotEmpty property="firstname" prepend="AND"> firstname=#firstname# </isNotEmpty> <isNotEmpty property="lastname" prepend="AND"> lastname=#lastname# </isNotEmpty> <isNotEmpty property="company" prepend="AND"> company=#company# </isNotEmpty> <isPropertyAvailable property="passwd" prepend="AND"> RTRIM(passwd)=${PASSWORD_ENCRYPT}(#passwd#) </isPropertyAvailable> <isNotEmpty property="email" prepend="AND"> email=#email# </isNotEmpty> <isNotEmpty property="status" prepend="AND"> status IN <iterate property="status" open="(" close=")" conjunction=","> #status[]# </iterate> </isNotEmpty> <isNotEmpty property="eauthid" prepend="AND"> unused2=#eauthid# </isNotEmpty> </dynamic> ORDER BY name </select> <select id="findMostRecentUserByFistNameAndLastName" parameterClass="java.util.Map" cacheModel="account-cache" resultMap="userResult"> SELECT * FROM users WHERE <isNotEmpty property="firstName"> firstname=#firstName# AND </isNotEmpty> lastname=#lastName# AND status='activated' AND unused2 IS NULL ORDER BY lastlogin DESC <isNotEmpty property="LIMIT"> $LIMIT$ </isNotEmpty> </select> </sqlMap> Zsolt > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf > Of Larry Meadors > Sent: Monday, September 03, 2007 5:51 AM > To: user-java@ibatis.apache.org > Subject: Re: Not working with derby 10.3.1.4 > > Freaky. Can you share the SQL Map and the bean (if there is one)? > > Larry > > > On 9/2/07, Zsolt Koppany <[EMAIL PROTECTED]> wrote: > > > > > > > > > > VARCHAR(255) > > > > > > > > > > Zsolt > > > > > > ________________________________ > > > > > > From: Brandon Goodin [mailto:[EMAIL PROTECTED] > > Sent: Friday, August 31, 2007 4:39 PM > > To: user-java@ibatis.apache.org > > Subject: Re: Not working with derby 10.3.1.4 > > > > > > > > > > What is your column type in derby? Apparently the JDBC driver is > reporting > > it as a type of <UNKNOWN>. > > > > Brandon > > > > > > On 8/31/07, Zsolt Koppany < [EMAIL PROTECTED]> wrote: > > > > Hi, > > > > I tried to upgrade to derby 10.3.1.4 but I get strange error messages > that > > I > > didn't get with derby 10.2.2.0. > > > > I use ibatis-2.3.0.677. > > > > Any ideas? > > > > Zsolt > > > > com.intland.codebeamer.persistence.util.PersistenceException: > > com.ibatis.common.jdbc.exception.NestedSQLException: > > --- The error occurred in > > com/intland/codebeamer/persistence/dao/sqlmap/UserDao.xml. > > --- The error occurred while applying a parameter map. > > --- Check the createUser-InlineParameterMap. > > --- Check the parameter mapping for the 'hostName' property. > > --- Cause: java.sql.SQLException: An attempt was made to get a data > value > > of > > type '<UNKNOWN>' from a data value of type 'VARCHAR'. > > at > > > com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate( > Ge > > neralStatement.java:91) > > at > > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert > > (SqlMapExecutorDe > > legate.java:447) > > at > > > com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.j > av > > a:82) > > at > > > com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.insert(SqlMapClientImpl.jav > a > > : > > 59) > > at > > > com.intland.codebeamer.persistence.util.SqlMapClientWrapper.insert(SqlMapC > li > > entWrapper.java:298) > > at > > > com.intland.codebeamer.persistence.dao.AbstractDao.create(AbstractDao.java > :8 > > 1) > > at > > > com.intland.codebeamer.persistence.dao.impl.UserDaoImpl.create(UserDaoImpl > .j > > ava:81) > > at > > > com.intland.codebeamer.persistence.test.WorkingSetItemDaoTests.prepareDumm > y( > > WorkingSetItemDaoTests.java :72) > > at > > > com.intland.codebeamer.persistence.test.WorkingSetItemDaoTests.testCrud(Wo > rk > > ingSetItemDaoTests.java:32) > > at > > sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > > at > > > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java: > 39 > > ) > > at > > > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorIm > pl > > .java:25) > > at java.lang.reflect.Method.invoke (Method.java:585) > > at > > junit.framework.TestCase.runTest(TestCase.java:154) > > at > > junit.framework.TestCase.runBare(TestCase.java:127) > > at > > > org.springframework.test.ConditionalTestCase.runBare(ConditionalTestCase.j > av > > a:69) > > at > > junit.framework.TestResult$1.protect(TestResult.java:106) > > at > > junit.framework.TestResult.runProtected(TestResult.java:124) > > at junit.framework.TestResult.run(TestResult.java:109) > > at junit.framework.TestCase.run(TestCase.java:118) > > at > > > org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit > 3T > > estReference.java:130) > > at > > org.eclipse.jdt.internal.junit.runner.TestExecution.run > > (TestExecution.java:3 > > 8) > > at > > > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTest > Ru > > nner.java:460) > > at > > > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTest > Ru > > nner.java:673) > > at > > > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunne > r. > > java:386) > > at > > > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunn > er > > .java:196) > > Caused by: > > com.ibatis.common.jdbc.exception.NestedSQLException: > > --- The error occurred in > > com/intland/codebeamer/persistence/dao/sqlmap/UserDao.xml. > > --- The error occurred while applying a parameter map. > > --- Check the createUser-InlineParameterMap. > > --- Check the parameter mapping for the 'hostName' property. > > --- Cause: java.sql.SQLException: An attempt was made to get a data > value > > of > > type '<UNKNOWN>' from a data value of type 'VARCHAR'. > > ... 26 more > > Caused by: java.sql.SQLException: An attempt was made to get a data > value > > of > > type '<UNKNOWN>' from a data value of type 'VARCHAR'. > > at > > org.apache.derby.client.am.SQLExceptionFactory.getSQLException > > (Unknown > > Source) > > at > > org.apache.derby.client.am.SqlException.getSQLException(Unknown > > Source) > > at > > org.apache.derby.client.am.PreparedStatement.setNull(Unknown > > Source) > > at sun.reflect.NativeMethodAccessorImpl.invoke0 > > (Native Method) > > at > > > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java: > 39 > > ) > > at > > > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorIm > pl > > .java:25) > > at java.lang.reflect.Method.invoke(Method.java:585) > > at > > > com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedSt > at > > ementLogProxy.java:70) > > at $Proxy1.setNull(Unknown Source) > > at > > > com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParameter( > Ba > > sicParameterMap.java:171) > > at > > > com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParameters > (B > > asicParameterMap.java:125) > > at > > > com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.j > av > > a:79) > > at > > > com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpda > te > > (GeneralStatement.java:200) > > at > > > com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate( > Ge > > neralStatement.java:78) > > ... 25 more > > Caused by: org.apache.derby.client.am.SqlException : An > > attempt was made to > > get a data value of type '<UNKNOWN>' from a data value of type > 'VARCHAR'. > > at > > > org.apache.derby.client.am.PreparedStatement$PossibleTypes.throw22005Excep > ti > > on(Unknown Source) > > ... 37 more > > > > > > Zsolt > > > >
