Is JDBCAdaptor generating the wrong SQL for fixed length character columns?
Is JDBCAdaptor generating the wrong SQL for fixed length character columns?
- Subject: Is JDBCAdaptor generating the wrong SQL for fixed length character columns?
- From: Lars Sonchocky-Helldorf <email@hidden>
- Date: Tue, 08 Nov 2011 20:56:56 +0100
Hi folks!
For reason not to be discussed here my coworker has created a PostgreSQL 9 database table like follows:
CREATE TABLE systemmessagecontent
(
id integer NOT NULL,
"language" character(2) NOT NULL,
message text NOT NULL,
subject character varying(255) NOT NULL,
systemmessageid integer NOT NULL,
CONSTRAINT systemmessagecontent_pkey PRIMARY KEY (id),
CONSTRAINT systemmessagecontent_systemmessage_fk FOREIGN KEY (systemmessageid)
REFERENCES systemmessage (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
The interesting point here is column "language" which is a two character fixed string.
In the corresponding EOModel the property for this has "char" as External Type and an External Width of 2 and no prototype (since there is nothing matching).
When running the application and trying to update that property/column the following SQL is generated:
Nov 08 18:28:34 BKAdmin[55555] DEBUG NSLog - === Begin Internal Transaction
Nov 08 18:28:34 BKAdmin[55555] DEBUG NSLog - evaluateExpression: <com.webobjects.jdbcadaptor.PostgresqlExpression: "UPDATE SystemMessageContent SET language = ?::char WHERE (id = ?::int4 AND systemMessageID = ?::int4)" withBindings: 1:"de"(language), 2:28(id), 3:48(systemMessageID)>
Nov 08 18:28:34 BKAdmin[55555] DEBUG NSLog - === Commit Internal Transaction
Remarkable here is the "?::char" Obviously this is a cast into a single character type of PostgreSQL.
see http://www.postgresql.org/docs/9.1/interactive/datatype-character.html table 8-5 for this.
Correct would have been to use character(2) or char(2) as my tests with PGAdmin revealed:
At first I fired the following SQL:
UPDATE SystemMessageContent SET language = 'de'::char WHERE (id = 28::int4 AND systemMessageID = 48::int4)
The result was the same like the one from the WOApp itself, after updating the database contained this: "d "
When I changed the query accordingly ('de'::char to 'de'::char(2)) I got what I expected:
UPDATE SystemMessageContent SET language = 'de'::char(2) WHERE (id = 28::int4 AND systemMessageID = 48::int4)
After this the database contained a "de".
Trying to use "char(2)" as External Type in my EOModel (instead of "char") I've got the following exception:
Nov 08 18:40:36 BKAdmin[55555] WARN NSLog - *** JDBCAdaptor : no type info found for char(2)
Nov 08 18:40:36 BKAdmin[55555] DEBUG NSLog - === Begin Internal Transaction
Nov 08 18:46:49 BKAdmin[55555] INFO er.transaction.adaptor.Exceptions - Database Exception occured: com.webobjects.eoaccess.EOGeneralAdaptorException: Unable to find type information for external type 'char(2)' in attribute 'language' of entity 'SystemMessageContent'. Check spelling and capitalization.
Now I think that maybe "char" was the right External Type to begin with. Only the JDBCAdaptor did not respect the external with of "2" and did not mangle "char" into "char(2)"
Is there something I can do about this?
Thanks and cheers,
Lars
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden