db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tim Halloran (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-342) ERROR 42818: Comparisons between 'LONG VARCHAR' and 'CHAR' are not supported
Date Wed, 20 Jun 2007 18:54:26 GMT

    [ https://issues.apache.org/jira/browse/DERBY-342?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12506630

Tim Halloran commented on DERBY-342:

This is a serious problem when derby is used with the Java Persistence API as it is non-obvious
how to create a query that looks up field mapped to long varchar columns in the derby database.

	public static Run find(final String run, final Date started,
			final EntityManager em) {
		assert run != null;
		assert started != null;
		assert em != null;
		try {
			Query q = em.createQuery("SELECT r FROM Run r WHERE r.run=?1 AND r.started=?2");
			q.setParameter(1, run);
			q.setParameter(2, started, TemporalType.TIMESTAMP);
			return (Run) q.getSingleResult();
		} catch (NoResultException e) {
			return null;
		} catch (NonUniqueResultException e) {
			FLog.logError("Lookup of " + run + " started at " + started
					+ " found more than one row in the database", e);
			return null;

Where "run" is a LONG VARCHAR NON NULL in Derby.  (aside: I can't describe the table in "ij"
due to bug DERBY-2785)  Running the code results:

[TopLink Warning]: 2007.06.20 02:21:42.757--UnitOfWork(13752725)--Exception [TOPLINK-4002]
(Oracle TopLink Essentials - 2.0 (Build b49-beta3 (05/31/2007))): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Comparisons between 'LONG VARCHAR' and 'VARCHAR'
are not supported.
Error Code: 30000
)))) AND (STARTED = ?))
	bind => [org.gjt.sp.jedit.jEdit, 2007-06-19 16:49:37.84]
Query: ReportQuery(com.surelogic.flashlight.entities.Run)

This is using "toplink" for the JPA implementation (part of Glassfish).

The problem is that JPA QL provides no mechanism to "type-cast" a query parameter to what
the database expects.

We (myself and Nathan Boy) did discover the following two workarounds:

1) Use "like" rather than "=", i.e., "SELECT r FROM Run r WHERE r.run like ?1 AND r.started=?2"
This seems to work but % and _ need to be escaped.

2) Trim both sides of the equality, i.e., "SELECT r FROM Run r WHERE TRIM(r.run)=TRIM(?1)
AND r.started=?2"
I don't know why this works.

Regards, and let me know if further detail is needed.  The more JPA QL is used with Derby
the more this will  come up, I suspect.

> ERROR 42818: Comparisons between 'LONG VARCHAR' and 'CHAR' are not supported
> ----------------------------------------------------------------------------
>                 Key: DERBY-342
>                 URL: https://issues.apache.org/jira/browse/DERBY-342
>             Project: Derby
>          Issue Type: Improvement
>          Components: JDBC, Tools
>    Affects Versions:
>         Environment: Windows XP Professional, Service Pack 2; Dell Latitude D600; 1.5GB
>            Reporter: Thomas J. Taylor
> Trying to run the same SQL query through JDBC-Derby and IJ results in the same error:
> ERROR 42818: Comparisons between 'LONG VARCHAR' and 'CHAR' are not supported.
> The same Java code/query works with MySQL 4.1 (Connector/J) and MS Access 2002 (JDBC-ODBC)
> Table Definition:
>   CREATE TABLE itemdata (valuetext LONG VARCHAR);
> SQL Statement
>   ij>SELECT * FROM itemdata WHERE valuetext = 'asdf';
>   ERROR 42818: Comparisons between 'LONG VARCHAR' and 'CHAR' are not supported.
> I have tried to use the 'cast' function to perform a LONG VARCHAR comparison:
>   ij> select * from itemdata where valuetext = cast('asdf' as long varchar);
>   ERROR 42818: Comparisons between 'LONG VARCHAR' and 'LONG VARCHAR' are not supported.
> The only way I've been able to get ANY result is the following:
>   ij> select * from itemdata where cast(valuetext as char) = 'asdf';
>   --------------------------------------------------------------------------------------------------------------------------------
>   ERROR 22001: A truncation error was encountered trying to shrink CHAR 'asdf' to length
> If I try to cast to VARCHAR, I get a syntax error:
>   ij> select * from itemdata where cast(valuetext as varchar) = 'asdf';
>   ERROR 42X01: Syntax error: Encountered ")" at line 1, column 63.
> I also have this problem if I change itemdata.valuetext to CLOB.

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message