Re: [SOLVED]: Is JDBCAdaptor generating the wrong SQL for fixed length character columns?
Re: [SOLVED]: Is JDBCAdaptor generating the wrong SQL for fixed length character columns?
- Subject: Re: [SOLVED]: Is JDBCAdaptor generating the wrong SQL for fixed length character columns?
- From: Chuck Hill <email@hidden>
- Date: Thu, 10 Nov 2011 10:10:47 -0800
Now _that_ is a bug. :-)
On 2011-11-10, at 10:09 AM, Alexander Spohr wrote:
> Hi Chuck,
>
> you have to use useBundledJdbcInfo=true with PostgreSQL. Otherwise your PostgreSQLAdaptor will hold an open transaction into the database. That will prevent commits from getting closed and written (kicking the old values), therefore bloating your database and finally kill your performance.
>
> We learned the hard way.
>
> atze
>
>
> Am 09.11.2011 um 23:39 schrieb Chuck Hill:
>
>> Hi Lars,
>>
>> Can you check your connection URL? It should not be using that file, that is mostly for Entity Modeler to generate the schema in the absence of a database connection.
>>
>> See:
>>
>> /**
>> * <P>This method returns true if the connection URL for the
>> * database has a special flag on it which indicates to the
>> * system that the jdbcInfo which has been bundled into the
>> * plugin is acceptable to use in place of actually going to
>> * the database and getting it.
>> */
>> protected boolean shouldUseBundledJdbcInfo() {
>> boolean shouldUseBundledJdbcInfo = false;
>> String url = connectionURL();
>> if (url != null) {
>> shouldUseBundledJdbcInfo = url.toLowerCase().matches(".*(\\?|\\?.*&)" + PostgresqlPlugIn.QUERY_STRING_USE_BUNDLED_JDBC_INFO.toLowerCase() + "=(true|yes)(\\&|$)");
>> }
>> return shouldUseBundledJdbcInfo;
>> }
>>
>>
>> private static final String QUERY_STRING_USE_BUNDLED_JDBC_INFO = "useBundledJdbcInfo";
>>
>>
>>
>> Chuck
>>
>>
>> On 2011-11-09, at 11:41 AM, Lars Sonchocky-Helldorf wrote:
>>
>>>
>>> Am 08.11.2011 um 20:56 schrieb Lars Sonchocky-Helldorf:
>>>
>>>> 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?
>>>
>>> Adding the following to PostgresqlPlugIn.framework/Resources/JDBCInfo.plist:
>>>
>>> "character" = {
>>> "defaultJDBCType" = (
>>> "CHAR"
>>> );
>>> "createParams" = "1";
>>> "maxScale" = "0";
>>> "minScale" = "0";
>>> "isNullable" = "T";
>>> "isSearchable" = "T";
>>> "precision" = "10485760";
>>> };
>>>
>>> and setting "character" as External Type in the corresponding EOModel did help.
>>>
>>> The result then is:
>>>
>>> Nov 09 13:13:18 BKAdmin[55555] DEBUG NSLog - === Begin Internal Transaction
>>> Nov 09 13:13:18 BKAdmin[55555] DEBUG NSLog - evaluateExpression: <com.webobjects.jdbcadaptor.PostgresqlExpression: "UPDATE SystemMessageContent SET language = ?::character(2) WHERE (id = ?::int4 AND systemMessageID = ?::int4)" withBindings: 1:"de"(language), 2:28(id), 3:48(systemMessageID)>
>>> Nov 09 13:13:18 BKAdmin[55555] DEBUG NSLog - === Commit Internal Transaction
>>>
>>> Could somebody with commit access to the PostgresqlPlugIn.framework please add this code upstream?
>>>
>>>
>>>
>>> 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
>>
>> --
>> Chuck Hill Senior Consultant / VP Development
>>
>> Practical WebObjects - for developers who want to increase their overall knowledge of WebObjects or who are trying to solve specific problems.
>> http://www.global-village.net/products/practical_webobjects
>>
>>
>>
>>
>>
>>
>>
>> _______________________________________________
>> 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
>
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their overall knowledge of WebObjects or who are trying to solve specific problems.
http://www.global-village.net/products/practical_webobjects
_______________________________________________
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