ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Zsolt Koppany" <zkoppanyl...@intland.com>
Subject RE: Not working with derby 10.3.1.4
Date Mon, 03 Sep 2007 07:47:42 GMT
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: larry.meadors@gmail.com [mailto:larry.meadors@gmail.com] 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 <zkoppanylist@intland.com> wrote:
> >
> >
> >
> >
> > VARCHAR(255)
> >
> >
> >
> >
> > Zsolt
> >
> >
> >  ________________________________
> >
> >
> > From: Brandon Goodin [mailto:brandon.goodin@gmail.com]
> >  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 < zkoppanylist@intland.com> 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
> >
> >


Mime
View raw message