[Hibernate-JIRA] Created: (HHH-2220) session.createSQLQuery(sql) translates database type CHAR(n) to Java type char instead of String
7:03 p.m.
session.createSQLQuery(sql) translates database type CHAR(n) to Java type char instead of
String
------------------------------------------------------------------------------------------------
Key: HHH-2220
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2220
Project: Hibernate3
Type: Bug
Components: query-sql
Versions: 3.2.0.ga
Reporter: Regis Pires Magalhaes
createSQLQuery() method translates database type CHAR(n) to Java type char instead of
String when using setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).
That happens when I do not use addScalar(). And that is the only problem that I have found
when not filling return types in advance.
A workaround I have made is to concatenate the projected field with an empty string
(''). See example below:
...
query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
String sqlQuery = "select s.name state from state s where s.name='PI'
";
query = session.createSQLQuery(sqlQuery);
...
result: [{STATE=P}]
name field is CHAR(2) in database definition (PostgreSQL, HSQLDB and Oracle were tested).
Note that it works when I concatenate the field used in projection with an empty string:
...
String sqlQuery = "select s.name || '' state from state s where
s.name='PI' ";
...
result: [{STATE=PI}]
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
1:26 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2220) session.createSQLQuery(sql) translates database type CHAR(n) to Java type char instead of String
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2220?page=c...
]
Hank Kuivila commented on HHH-2220:
-----------------------------------
I've had the same issue. Here's a test scenario:
The java code used is:
//PersistenceUtil is just a global that acquires/release/holds sessions
Session s = PersistenceUtil.currentSession();
s.createSQLQuery("create table #t (a char(10), b
varchar(10))").executeUpdate();
s.createSQLQuery("insert #t values ('foo',
'bar')").executeUpdate();
s.createSQLQuery("insert #t values ('mint',
'twinky')").executeUpdate();
for (Object o : s.createSQLQuery("select * from #t").list()){
Object[] oa = (Object[]) o;
System.out.println(oa[0] + ", " + oa[1]);
}
I'm using Hibernate 3.2.2, here's all the relevant log data with show sql:
- Hibernate 3.2.2
- hibernate.properties not found
- Bytecode provider name : cglib
- using JDK 1.4 java.sql.Timestamp handling
- configuring from resource: /hibernate.cfg.xml
- Configuration resource: /hibernate.cfg.xml
...........
- RDBMS: Microsoft SQL Server, version: Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
>>>>>>>>> we create a table:
Hibernate: create table #t (a char(10), b varchar(10))
................
>>>>>>>>>>> we put some values into
the table:
Hibernate: insert #t values ('foo', 'bar')
Hibernate: insert #t values ('mint', 'twinky')
>>>>>>>>>>> we retrieve values and
print them
Hibernate: select * from #t
>>>>>>>>>>> and lose the oo of foo
and the int of mint
f, bar
m, twinky
session.createSQLQuery(sql) translates database type CHAR(n) to Java
type char instead of String
------------------------------------------------------------------------------------------------
Key: HHH-2220
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2220
Project: Hibernate3
Issue Type: Bug
Components: query-sql
Affects Versions: 3.2.0.ga
Reporter: Regis Pires Magalhaes
createSQLQuery() method translates database type CHAR(n) to Java type char instead of
String when using setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).
That happens when I do not use addScalar(). And that is the only problem that I have
found when not filling return types in advance.
A workaround I have made is to concatenate the projected field with an empty string
(''). See example below:
...
query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
String sqlQuery = "select s.name state from state s where s.name='PI'
";
query = session.createSQLQuery(sqlQuery);
...
result: [{STATE=P}]
name field is CHAR(2) in database definition (PostgreSQL, HSQLDB and Oracle were
tested).
Note that it works when I concatenate the field used in projection with an empty string:
...
String sqlQuery = "select s.name || '' state from state s where
s.name='PI' ";
...
result: [{STATE=PI}]
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
6:09 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2220) session.createSQLQuery(sql) translates database type CHAR(n) to Java type char instead of String
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2220?page=c...
]
Allen Yu commented on HHH-2220:
-------------------------------
The problem is also confirmed on MYSQL
INFO: RDBMS: MySQL, version: 5.0.38-Ubuntu_0ubuntu1-log
INFO: JDBC driver: MySQL-AB JDBC Driver, version: mysql-connector-java-5.0.6 ( $Date:
2007-03-09 22:13:57 +0100 (Fri, 09 Mar 2007) $, $Revision: 6341 $ )
INFO: Using dialect: org.hibernate.dialect.MySQLDialect
Other settings:
INFO: Hibernate Annotations 3.3.0.GA
INFO: Hibernate 3.2.4.sp1
INFO: using JDK 1.4 java.sql.Timestamp handling
INFO: Hibernate EntityManager 3.3.1.GA
Temporary solution:
Problem can be solved by forcing Hibernate.STRING type on the column.
CREATE TABLE `IN_CATEGORY` (
`CATEGORY_ID` int(11) NOT NULL auto_increment,
`PREFIX` char(2) NOT NULL,
`EXTENSION` char(3) NOT NULL,
`PARENT_ID` int(11) default NULL,
PRIMARY KEY (`CATEGORY_ID`),
UNIQUE KEY `CATEGORY` (`PREFIX`,`EXTENSION`),
KEY `FK_PARENT_ID` (`PARENT_ID`),
CONSTRAINT `FK_PARENT_ID` FOREIGN KEY (`PARENT_ID`) REFERENCES `IN_CATEGORY`
(`CATEGORY_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Ex.
SQLQuery query = session.createSQLQuery("SELECT PREFIX, EXTENSION FROM
IN_CATEGORY");
query.addScalar("PREFIX", Hibernate.STRING);
query.addScalar("EXTENSION", Hibernate.STRING);
session.createSQLQuery(sql) translates database type CHAR(n) to Java
type char instead of String
------------------------------------------------------------------------------------------------
Key: HHH-2220
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2220
Project: Hibernate3
Issue Type: Bug
Components: query-sql
Affects Versions: 3.2.0.ga
Reporter: Regis Pires Magalhaes
createSQLQuery() method translates database type CHAR(n) to Java type char instead of
String when using setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).
That happens when I do not use addScalar(). And that is the only problem that I have
found when not filling return types in advance.
A workaround I have made is to concatenate the projected field with an empty string
(''). See example below:
...
query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
String sqlQuery = "select s.name state from state s where s.name='PI'
";
query = session.createSQLQuery(sqlQuery);
...
result: [{STATE=P}]
name field is CHAR(2) in database definition (PostgreSQL, HSQLDB and Oracle were
tested).
Note that it works when I concatenate the field used in projection with an empty string:
...
String sqlQuery = "select s.name || '' state from state s where
s.name='PI' ";
...
result: [{STATE=PI}]
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
10:29 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2220) session.createSQLQuery(sql) translates database type CHAR(n) to Java type char instead of String
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2220?page=c...
]
Anthony Patricio commented on HHH-2220:
---------------------------------------
you can set the expected behavior by extending the dialect you're using and
registering the hibernate type :
registerHibernateType(Types.CHAR, 2, Hibernate.STRING.getName());
or
registerHibernateType(Types.CHAR, 2, Hibernate.CHARACTER_ARRAY.getName());
session.createSQLQuery(sql) translates database type CHAR(n) to Java
type char instead of String
------------------------------------------------------------------------------------------------
Key: HHH-2220
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2220
Project: Hibernate3
Issue Type: Bug
Components: query-sql
Affects Versions: 3.2.0.ga
Reporter: Regis Pires Magalhaes
createSQLQuery() method translates database type CHAR(n) to Java type char instead of
String when using setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).
That happens when I do not use addScalar(). And that is the only problem that I have
found when not filling return types in advance.
A workaround I have made is to concatenate the projected field with an empty string
(''). See example below:
...
query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
String sqlQuery = "select s.name state from state s where s.name='PI'
";
query = session.createSQLQuery(sqlQuery);
...
result: [{STATE=P}]
name field is CHAR(2) in database definition (PostgreSQL, HSQLDB and Oracle were
tested).
Note that it works when I concatenate the field used in projection with an empty string:
...
String sqlQuery = "select s.name || '' state from state s where
s.name='PI' ";
...
result: [{STATE=PI}]
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
5:15 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2220) session.createSQLQuery(sql) translates database type CHAR(n) to Java type char instead of String
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2220?page=c...
]
Max Rydahl Andersen commented on HHH-2220:
------------------------------------------
Currently Hibernate supports a kind of "automagic" mapping from SQL types to
Hibernate/Java types - because of the many ambiguities in doing such mapping it will
sometime not match what you actually want.
That is why we *always* recommend to use explicit addScalar OR if you don't want that
all over your code use the subclassing of Dialect to dictate which of the multiple
possible mappings do you want.
The issue with CHAR(n) is the most problematic one, but it is not easy to fix - we would
need a registerType(type, from, to, typename) to map a range instead of a specific
length...but even then you could bump into mapping ambiguities (E.g. sometime you want an
array other times string etc.) Hence using .addScalar is recommended for *any* native sql
querying - depending on automatic discovery will always be risky and should only be used
to a minimum.
session.createSQLQuery(sql) translates database type CHAR(n) to Java
type char instead of String
------------------------------------------------------------------------------------------------
Key: HHH-2220
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2220
Project: Hibernate3
Issue Type: Bug
Components: query-sql
Affects Versions: 3.2.0.ga
Reporter: Regis Pires Magalhaes
createSQLQuery() method translates database type CHAR(n) to Java type char instead of
String when using setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).
That happens when I do not use addScalar(). And that is the only problem that I have
found when not filling return types in advance.
A workaround I have made is to concatenate the projected field with an empty string
(''). See example below:
...
query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
String sqlQuery = "select s.name state from state s where s.name='PI'
";
query = session.createSQLQuery(sqlQuery);
...
result: [{STATE=P}]
name field is CHAR(2) in database definition (PostgreSQL, HSQLDB and Oracle were
tested).
Note that it works when I concatenate the field used in projection with an empty string:
...
String sqlQuery = "select s.name || '' state from state s where
s.name='PI' ";
...
result: [{STATE=PI}]
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
5:33 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2220) session.createSQLQuery(sql) translates database type CHAR(n) to Java type char instead of String
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2220?page=c...
]
Thad West commented on HHH-2220:
--------------------------------
Two 1/2 years later and this is still the solution? Bummer....
session.createSQLQuery(sql) translates database type CHAR(n) to Java
type char instead of String
------------------------------------------------------------------------------------------------
Key: HHH-2220
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2220
Project: Hibernate Core
Issue Type: Bug
Components: query-sql
Affects Versions: 3.2.0.ga
Reporter: Regis Pires Magalhaes
createSQLQuery() method translates database type CHAR(n) to Java type char instead of
String when using setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).
That happens when I do not use addScalar(). And that is the only problem that I have
found when not filling return types in advance.
A workaround I have made is to concatenate the projected field with an empty string
(''). See example below:
...
query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
String sqlQuery = "select s.name state from state s where s.name='PI'
";
query = session.createSQLQuery(sqlQuery);
...
result: [{STATE=P}]
name field is CHAR(2) in database definition (PostgreSQL, HSQLDB and Oracle were
tested).
Note that it works when I concatenate the field used in projection with an empty string:
...
String sqlQuery = "select s.name || '' state from state s where
s.name='PI' ";
...
result: [{STATE=PI}]
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
4:50 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2220) session.createSQLQuery(sql) translates database type CHAR(n) to Java type char instead of String
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2220?page=c...
]
radhakrishna commented on HHH-2220:
-----------------------------------
Same in DB2, if I read a CHARACTER(33) column using session.createSqlQuery("select
character_column from table ") returns me a char ;)
session.createSQLQuery(sql) translates database type CHAR(n) to Java
type char instead of String
------------------------------------------------------------------------------------------------
Key: HHH-2220
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2220
Project: Hibernate Core
Issue Type: Bug
Components: query-sql
Affects Versions: 3.2.0.ga
Reporter: Regis Pires Magalhaes
createSQLQuery() method translates database type CHAR(n) to Java type char instead of
String when using setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).
That happens when I do not use addScalar(). And that is the only problem that I have
found when not filling return types in advance.
A workaround I have made is to concatenate the projected field with an empty string
(''). See example below:
...
query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
String sqlQuery = "select s.name state from state s where s.name='PI'
";
query = session.createSQLQuery(sqlQuery);
...
result: [{STATE=P}]
name field is CHAR(2) in database definition (PostgreSQL, HSQLDB and Oracle were
tested).
Note that it works when I concatenate the field used in projection with an empty string:
...
String sqlQuery = "select s.name || '' state from state s where
s.name='PI' ";
...
result: [{STATE=PI}]
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
12:12 p.m.
New subject: [Hibernate-JIRA] Resolved: (HHH-2220) session.createSQLQuery(sql) translates database type CHAR(n) to Java type char instead of String
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2220?page=c...
]
Strong Liu resolved HHH-2220.
-----------------------------
Resolution: Duplicate
Assignee: Strong Liu
duplicated by HHH-2304
session.createSQLQuery(sql) translates database type CHAR(n) to Java
type char instead of String
------------------------------------------------------------------------------------------------
Key: HHH-2220
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2220
Project: Hibernate Core
Issue Type: Bug
Components: query-sql
Affects Versions: 3.2.0.ga
Reporter: Regis Pires Magalhaes
Assignee: Strong Liu
createSQLQuery() method translates database type CHAR(n) to Java type char instead of
String when using setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).
That happens when I do not use addScalar(). And that is the only problem that I have
found when not filling return types in advance.
A workaround I have made is to concatenate the projected field with an empty string
(''). See example below:
...
query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
String sqlQuery = "select s.name state from state s where s.name='PI'
";
query = session.createSQLQuery(sqlQuery);
...
result: [{STATE=P}]
name field is CHAR(2) in database definition (PostgreSQL, HSQLDB and Oracle were
tested).
Note that it works when I concatenate the field used in projection with an empty string:
...
String sqlQuery = "select s.name || '' state from state s where
s.name='PI' ";
...
result: [{STATE=PI}]
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
4572
days inactive
6373
days old
hibernate-issues@lists.jboss.org
7 comments
8 participants
participants (8)
-
Allen Yu (JIRA) -
Anthony Patricio (JIRA)
-
Hank Kuivila (JIRA)
-
Max Rydahl Andersen (JIRA)
-
radhakrishna (JIRA)
-
Regis Pires Magalhaes (JIRA)
-
Strong Liu (JIRA)
-
Thad West (JIRA)