logging-log4j-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nick Williams <nicho...@nicholaswilliams.net>
Subject Re: Log4j2: Column casting in JDBCAppender
Date Thu, 22 Aug 2013 22:03:01 GMT
Yes, I mean like that. I want to avoid something like that.

N

On Aug 22, 2013, at 4:59 PM, Scott Deboy wrote:

> You mean like:
> http://logging.apache.org/log4j/companions/receivers/apidocs/org/apache/log4j/db/dialect/package-summary.html
> 
> Scott
> 
> On 8/22/13, Nick Williams <nicholas@nicholaswilliams.net> wrote:
>> IMO, this is a bug in the PGSQL JDBC driver and not something that Log4j can
>> easily (or should) address. For a detailed discussion, see the PGSQL thread
>> about casting text to PGSQL enums [1].
>> 
>> The problem lies in PGSQL's type handling. With PGSQL, MySQL, and any other
>> database type that supports enums, you can write a query like this:
>> 
>> INSERT INTO myTable (enumColumn) VALUES ('enumValue');
>> 
>> And the database engine implicitly casts the text to enum. If you try this
>> out on a PGSQL prompt, it works. However, unlike MySQL and other vendors,
>> PGSQL does NOT allow String parameters in prepared statements to be
>> auto-casted to enums. So the following is valid in all vendors that support
>> enums:
>> 
>> statement = connection.prepareStatement("INSERT INTO myTable (enumColumn)
>> VALUES ('enumValue')");
>> 
>> But the following, while valid with MySQL and everything else, breaks with
>> PGSQL's JDBC driver:
>> 
>> statement = connection.prepareStatement("INSERT INTO myTable (enumColumn)
>> VALUES (?)");
>> statement.setString(1, "enumValue");
>> 
>> IMO, this was a stupid decision on PGSQL's part, and from the thread it
>> sounds like others agree with me. It makes it extremely difficult to use
>> PGSQL enums with, for example, the Java Persistence API or O/RMs in general.
>> From the sounds of the thread, the guys over at PGSQL have to intention of
>> fixing this.
>> 
>> Now, as for your suggestion. As much as I'd like to accommodate your need
>> here, it's simply not feasible. Nearly every single database vendor has a
>> different way to cast data types. Some have a CAST function, others a CAST
>> keyword, and then PGSQL has that odd double-colon syntax (::). If we were to
>> add a casting feature, we would then have to have a sense of database
>> dialects like an O/RM and learn the syntax of each database, correctly
>> applying the syntax based on what type of database we're logging to. This
>> would be a heavyweight bulky addition to such a simple feature. My
>> recommendations, in descending order of how much effort it will require:
>> 
>> 1) Submit a patch to PGSQL's JDBC driver and lobby for its acceptance.
>> 2) Switch to a database vendor that's less [insert insulting word here]
>> about its enum casting--like any of the other ones. My favorite is MySQL.
>> 3) Stop using a database enum column.
>> 
>> :-/
>> 
>> Nick
>> 
>> [1]
>> http://www.postgresql.org/message-id/CAPPfruyta0A0xGhG4Zh785sS0_FZ8GczzcjZGXo2yfPhaDxuaA@mail.gmail.com
>> 
>> On Aug 22, 2013, at 4:12 PM, Peter Rifel wrote:
>> 
>>> Hello,
>>> 
>>> I'm wondering if it is at all possible to cast columns to a certain type
>>> (a custom enum) with the JDBCAppender.  I have an existing PostgreSQL
>>> database that I send all warnings and errors to and the log level is
>>> currently being stored as an enum.  When I try and log to the database I
>>> get the following message:
>>> 
>>> org.postgresql.util.PSQLException: ERROR: column "level" is of type
>>> log_level but expression is of type character varying  Hint: You will need
>>> to rewrite or cast the expression.
>>> 
>>> Looking through the JDBCDatabaseManager it doesn't appear that this is
>>> possible; including the cast in the pattern string results in it getting
>>> passed as the value itself rather than the prepared statement.  Does
>>> anyone have any other ideas on how to accomplish this?  I wouldn't mind
>>> making contributions to the source code to help implement this feature if
>>> it is desired.
>>> 
>>> Thanks,
>>> 
>>> Peter
>> 
>> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: log4j-user-unsubscribe@logging.apache.org
> For additional commands, e-mail: log4j-user-help@logging.apache.org
> 


---------------------------------------------------------------------
To unsubscribe, e-mail: log4j-user-unsubscribe@logging.apache.org
For additional commands, e-mail: log4j-user-help@logging.apache.org


Mime
View raw message