arthurblake / log4jdbc Goto Github PK

I'm working with a pre-1.6 oracle driver which doesn't have setNString.

To set a unicode value, I must get access to the raw
OraclePreparedStatement and call "setFormOfUse(n, FORM_NCHAR)" on it to
flip that column over to unicode.

When using log4jdbc, I see no way to gain access to the
"realPreparedStatement" (protected member) to accomplish this.

Please consider adding a get method to expose the raw prepared statement
for cases like this.

To be able to debug connections to several databases, it would be great if you 
could include the URL and username in the log message when a new connection is 
made.

Code inspections:
 In RdbmsSpecifics.java a static SimpleDateFormat is used.
  protected static final DateFormat dateFormat = 
    new SimpleDateFormat("MM/dd/yyyy HH:mm:ss.SSS");

The dateFormat object will be used by all threads and SimpleDateFormat is
not thread safe (see javadoc). 

What steps will reproduce the problem?
1. use log4jdbc4-1.2alpha2.jar and configure jdbc.connection to DEBUG level
in log4j(in jboss 5.1)
2. start jboss
3. there are no logs with jdbc.connection
4. copy code from latest source into my own jar and swap that jar from
jboss/server/all/lib with the log4jdbc one
5. run again and the jdbc.connection logs show up.

docs seems to incorrectly say since alpha1, jdbc.connection is supported
but it doesn't seem to work in alpha2 but works in the latest source code.
 IS there going to be another binary release?

A PreparedStatement like the following won't be processed properly in
"PreparedStatementSpy#dumpedSql()":
"SELECT count(*) FROM users WHERE lastname NOT IN ('?') AND firstname=?"

Expected Result:
"SELECT count(*) FROM users WHERE lastname NOT IN ('?') AND firstname=1"

Actual Result:
"SELECT count(*) FROM users WHERE lastname NOT IN ('1') AND firstname=?"

Note: 
the question mark is inside a string literal and therefore it must not be
taken as an argument for the query.

Note:  This bug was originall entered by an anonymous user when the project 
was hosted on sourceforge.

Hi,

I would like to know if log4jdbc will work with c3p0 (connection pooling 
which i configure as my Data source in a spring hibernate based project)

What steps will reproduce the problem?
1. use postgis-jdbc 1.1.6 or any other gis-enabed jdbc ( in my case in
combination with hibernatespatial)
2. issue any query contianing geometry (point in polygon)
3. the resulting sql string logged is invalid, although the query works.

What is the expected output? What do you see instead?
wrong output: 
select count(*) as y0_ from public.b_hotel this_ where (this_.coordinate &&
SRID=4326;POLYGON((65.1 10.75,64.79256448645168
7.628554847741948,63.882072520180586 4.6270650821585635,62.40351379684073
1.8608762716863652,60.41370849898476 -0.5637084989847594,57.989123728313636
-2.5535137968407238,55.22293491784144 -4.032072520180588,52.221445152258056
-4.942564486451687,49.1 -5.25,45.97855484774195
-4.942564486451687,42.97706508215857 -4.032072520180588,40.210876271686374
-2.5535137968407255,37.78629150101524
-0.5637084989847612,35.796486203159276 1.8608762716863652,34.31792747981941
4.627065082158569,33.407435513548315 7.628554847741956,33.1
10.750000000000012,33.40743551354832 13.871445152258067,34.31792747981942
16.872934917841455,35.79648620315929 19.639123728313656,37.786291501015256
22.06370849898478,40.21087627168639 24.05351379684074,42.977065082158596
25.5320725201806,45.97855484774198 26.442564486451694,49.100000000000044
26.75,52.2214451522581 26.44256448645168,55.22293491784148
25.53207252018057,57.98912372831368 24.053513796840697,60.4137084989848
22.063708498984724,62.403513796840755 19.63912372831359,63.882072520180614
16.87293491784138,64.7925644864517 13.87144515225799,65.1 10.75))  AND  
within(this_.coordinate, SRID=4326;POLYGON((65.1 10.75,64.79256448645168
7.628554847741948,63.882072520180586 4.6270650821585635,62.40351379684073
1.8608762716863652,60.41370849898476 -0.5637084989847594,57.989123728313636
-2.5535137968407238,55.22293491784144 -4.032072520180588,52.221445152258056
-4.942564486451687,49.1 -5.25,45.97855484774195
-4.942564486451687,42.97706508215857 -4.032072520180588,40.210876271686374
-2.5535137968407255,37.78629150101524
-0.5637084989847612,35.796486203159276 1.8608762716863652,34.31792747981941
4.627065082158569,33.407435513548315 7.628554847741956,33.1
10.750000000000012,33.40743551354832 13.871445152258067,34.31792747981942
16.872934917841455,35.79648620315929 19.639123728313656,37.786291501015256
22.06370849898478,40.21087627168639 24.05351379684074,42.977065082158596
25.5320725201806,45.97855484774198 26.442564486451694,49.100000000000044
26.75,52.2214451522581 26.44256448645168,55.22293491784148
25.53207252018057,57.98912372831368 24.053513796840697,60.4137084989848
22.063708498984724,62.403513796840755 19.63912372831359,63.882072520180614
16.87293491784138,64.7925644864517 13.87144515225799,65.1 10.75)))) limit 1

expected: a string, that i can copy-paste into postgresql that will work
like any other string produced by log4jdbc
this may for example work:

select count(*) as y0_ from public.b_hotel this_ where
(within(this_.coordinate, setsrid(GeometryFromText('POLYGON((65.1
10.75,64.79256448645168 7.628554847741948,63.882072520180586
4.6270650821585635,62.40351379684073 1.8608762716863652,60.41370849898476
-0.5637084989847594,57.989123728313636
-2.5535137968407238,55.22293491784144 -4.032072520180588,52.221445152258056
-4.942564486451687,49.1 -5.25,45.97855484774195
-4.942564486451687,42.97706508215857 -4.032072520180588,40.210876271686374
-2.5535137968407255,37.78629150101524
-0.5637084989847612,35.796486203159276 1.8608762716863652,34.31792747981941
4.627065082158569,33.407435513548315 7.628554847741956,33.1
10.750000000000012,33.40743551354832 13.871445152258067,34.31792747981942
16.872934917841455,35.79648620315929 19.639123728313656,37.786291501015256
22.06370849898478,40.21087627168639 24.05351379684074,42.977065082158596
25.5320725201806,45.97855484774198 26.442564486451694,49.100000000000044
26.75,52.2214451522581 26.44256448645168,55.22293491784148
25.53207252018057,57.98912372831368 24.053513796840697,60.4137084989848
22.063708498984724,62.403513796840755 19.63912372831359,63.882072520180614
16.87293491784138,64.7925644864517 13.87144515225799,65.1 10.75))'),4326)))
limit 1

What version of the product are you using? On what operating system?
1.2alpha1, postgis-jdbc 1.1.6, slfj 1.5.3, log4j 1.2.14, jdbc: postgresql
8.1-407.jdbc3.jar

If the option 'log4jdbc.trim.sql.extrablanklines' is true, then the output sql 
will be end with a char '\n'. I think it's better to remove it. :) What do you 
think? Thanks.

What steps will reproduce the problem?
1. Insert or update SQL done by preparedstatement and the String values has 
single quote. The generAted SQL was not with escaped that single quote. 
Therefore, cannot execute directly on the SQL tool
2.
3.

What is the expected output? What do you see instead?
escaped with that single qoute

What version of the product are you using? On what operating system?
1.2 beta 2

Please provide any additional information below.

The documentation states you should set a logger to FATAL to completely disable 
it, and disabling all 5 loggers will disable log4jdbc altogether (which would 
be a nice feature, by the way). However, Logback does not support the FATAL 
level.

What steps will reproduce the problem?
1. Use Logback as the logging backend (which is a natural choice if you use 
slf4j as your logging frontend).
2. Try to set a logger to FATAL – won’t work, default level used.

What steps will reproduce the problem?

Use this log4jdbc.properties

log4jdbc.dump.sql.addsemicolon=true
log4jdbc.dump.sql.select=true
log4jdbc.dump.sql.insert=true
log4jdbc.dump.sql.update=false
log4jdbc.dump.sql.delete=true
log4jdbc.dump.sql.create=true

What is the expected output?
log for all transactions except the update ones

What do you see instead?
Nothing

What version of the product are you using? On what operating system?
1.2beta2
Windows 7

Please provide any additional information below.

It would be nice to have an option in DriverSpy to strip new lines and replace 
them by " ", something like
sql.replaceAll("\\s+", " ").

What is the expected output? What do you see instead?
Something like:
select *
from someTable
where column = 'something'

would become:
select * from someTable where column = 'something'

What steps will reproduce the problem?
1. Create a JPQL query with a parameter 
         .setParameter("mydate", date, TemporalType.DATE)
Using "between" JPQL operator between two dates mapped as java.sql.date (no 
time, date only)
2. Execute the query

What is the expected output? What do you see instead?

select * as col_0_0_ from A_COL_DATE cutoff0_ where to_date('06/30/2011 
14:56:38', 'mm/dd/yyyy hh24:mi:ss') between cutoff0_.DATE_START and 
cutoff0_.DATE_END ) where rownum <= 2 {executed in 7 msec}

Although the query is really executed ignoring the time part, using only the 
date.

What version of the product are you using? On what operating system?


Please provide any additional information below.

What steps will reproduce the problem?
not exactly sure.

in 1.2 alpha1(we are moving to alpha2 which has the batch.clear which "may"
fix this).

I am going to ease into the major issue which is at the bottom and causing
database commit failures in our production environment but please read
through it.

Basically, in my logs, I keep seeing this log 

2010-04-20 17:34:51,760 INFO  [jdbc.sqlonly] (http-0.0.0.0-8080-3)
[player][session:QsWA__][OP1521] batching 9 statements: 1: insert into
VOICELOG.Tpvcalldata (displayrule, displaytype, label,

I can't tell where this comes from.  In the source code it looks like every
batching should look like this log does...

2010-04-20 17:34:52,050 ERROR [jdbc.sqlonly] (http-0.0.0.0-8080-3)
[player][session:QsWA__][OP1521] 1619. PreparedStatement.executeBatch()
batching 9 statements:

I searched the past month of logs in production.  This second log occurs
only twice and when it does, it results in a failure as it is preceded by
the first log which has already committed data so I think log4jdbc may be
causing a double commit(inserting same values twice).  What I don't get
when looking at the source code is I see in StatementSpy.executeBatch where
the second log is logged.  I don't ever see how the first log is possible.

We have had this production issue twice in the past month.  We are rolling
out 1.2alpha2 this weekend(If this issue goes away, I will update this
ticket though i would like to know where the first log comes from?? in the
source code)

Currently sqltiming uses System.currentTimeMillis() to calculate the timing 
statistics. On windows this typically results in 0, 15 or more, where 0 can be 
anything between 0 and 15 ms. 

Is it possible to add an option to measure in nanoSeconds, using 
System.nanoSeconds() to have better timing statistics?

The methods void setBlob(int i, Blob x) and void setClob(int i, Clob x)
dereference parameter x without checking whether it is null. Attached patch
fixes this.

1.build a web project of  spring+ hibernate + struts+ log4jdbc+mysql
2.create a test table in mysql ,in web project write some CRUT java code,run 
the project ,and add or modify this value a'a" to a varchar field,will show  
the Exception java.util.NoSuchElementException;java.util.StringTokenizer
3.at the 146 line of net.sf.log4jdbc.BasicSqlFormatter.java ,this code : while 
( !"\"".equals( t ) );  throws that exception,

while i have used this code "while ( !"\"".equals( t ) && 
tokens.hasMoreTokens()  );" to replace it,the project is all right.

What steps will reproduce the problem?
1. set log4jdbc.dump.sql.maxlinelength=20
2. enable logging with sql statements

What is the expected output? What do you see instead?
The logged sql statement should break after the next space after 20
characters, but still break after 90 characters.

What version of the product are you using? On what operating system?
current repository revision 63/1.2 alpha2

Patch to fix this is attached.

What steps will reproduce the problem?
1. Set DriverSpy.DumpSqlSelect to true
2. Have a query like that: "/* some comments */ select * from There;
3. See that the query is not considered a select.

What is the expected output? What do you see instead?
Before testing if the query is an select (or any other type of query), comment 
blocks should be removed

What version of the product are you using? On what operating system?
1.2

package net.sf.log4jdbc;

import java.sql.Connection;

import org.aopalliance.intercept.MethodInterceptor;
import org.aopalliance.intercept.MethodInvocation;

public class DataSourceSpyInterceptor implements MethodInterceptor {

    private RdbmsSpecifics rdbmsSpecifics = null;

    private RdbmsSpecifics getRdbmsSpecifics(Connection conn) {
        if(rdbmsSpecifics == null) {
            rdbmsSpecifics = DriverSpy.getRdbmsSpecifics(conn);
        }
        return rdbmsSpecifics;
    }

    public Object invoke(MethodInvocation invocation) throws Throwable {
        Object result = invocation.proceed();
        if(SpyLogFactory.getSpyLogDelegator().isJdbcLoggingEnabled()) {
            if(result instanceof Connection) {
                Connection conn = (Connection)result;
                return new ConnectionSpy(conn,getRdbmsSpecifics(conn));
            }
        }
        return result;
    }

}







spring config:

{{{
<bean id="log4jdbcInterceptor" class="net.sf.log4jdbc.DataSourceSpyInterceptor" 
/>
   <bean id="dataSourceLog4jdbcAutoProxyCreator" class="org.springframework.aop.framework.autoproxy.BeanNameAutoProxyCreator">
      <property name="interceptorNames">
          <list>
             <value>log4jdbcInterceptor</value>        
          </list>
      </property>
      <property name="beanNames">
          <list>
             <value>dataSource</value>
          </list>
      </property>
   </bean>
}}}

After i plugged log4jdbc into my connections, i started to see these messages 
in the error logs:

"2012-02-01 09:51:00 - ERROR ajp-8009-9 jdbc.sqltiming - 10. 
Connection.setAutoCommit(false)
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet 
successfully received from the server was 55,923,403 milliseconds ago.  The 
last packet sent successfully to the server was 55,923,415 milliseconds ago. is 
lonn
    ger than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using thee
     Connector/J connection property 'autoReconnect=true' to avoid this problem."

It basically repeats these messages in different classes: jdbc.sqlonly, 
jdbc.sqltiming and jdbc.audit.

I already have autoReconnect set to true. I suspect the problem is that 
log4jdbc is not respecting testOnBorrow=”true” validationQuery=”select 
1″

1/ Why did this error stop appearing after i enabled log4jdbc
2/ How can i avoid printing duplicate error messages

--Ricardo

The current code on SVN is buggy, as the prefix changed from jdbc:log to
jdbc:log4, but the calls to substring didn't. I'm submitting a quick and
dirty patch that simply replace the url.substring(9) calls to
url.substring(10), but ideally such logic should be refactored in a helper
method (which in turn calculates the size using a constant with the prefix).

Also, it might be necessary to update the project's documentation in order
to reflect the new prefix.

example:

source sql:
select * from userinfo u inner join role r on u.id = r.id inner join blog b on 
u.id = b.id where a=123 and diy=234

formated sql:
select
        * 
    from
        userinfo u 
    inner join
        role r     
            on u.id = r.id 
    inner join
        blog b 
            on u.id = b.id 
    where
        a=123 
        and diy=234



  /**
   * Slf4jSpyLogDelegator.processSql()
   */
  private String processSql(String sql)
  {
    // ... other codes
    if(formatSql) {
        return new BasicSqlFormatter().format(output.toString());
    }else {
        return output.toString();
    }
  }


attach file:BasicSqlFormatter.java is copy from hibernate

What steps will reproduce the problem?
1. Create a simple select to multiples tables with JOIN and columns alias. 
Example: SELECT p.id, p.name, d.name AS department FROM person p LEFT JOIN 
department d USING (id_department)
2. When try to retrieve rs.getString("department") the program thorw error.

What is the expected output? What do you see instead?
I expect retrieve the coulmn data, but throw error

What version of the product are you using?
My used versio is: log4jdbc-remix 0.2.4, based on log4jdbc 1.2 beta 2 jar for 
JDBC 4 (JDK 1.6 and 1.7)


Iam using spring, when remove the jdbc-remix datasource Spied the program work, 
but the error is present with jdbc-remix datasource.

Detail of error stack here:

java.lang.RuntimeException: ResultSet.getXXX(colName): could not look up name
    at net.sf.log4jdbc.DefaultResultSetCollector.setColIndexFromGetXXXMethodParams(DefaultResultSetCollector.java:163)
    at net.sf.log4jdbc.DefaultResultSetCollector.methodReturned(DefaultResultSetCollector.java:107)
    at net.sf.log4jdbc.ResultSetSpy.reportAllReturns(ResultSetSpy.java:89)
    at net.sf.log4jdbc.ResultSetSpy.reportReturn(ResultSetSpy.java:261)
    at net.sf.log4jdbc.ResultSetSpy.getString(ResultSetSpy.java:2553)
    at ve.com.pdvsa.intevep.edgeview.dao.imp.LevantamientoDAOImp$LevantamientoMapper.mapRow(LevantamientoDAOImp.java:256)
    at ve.com.pdvsa.intevep.edgeview.dao.imp.LevantamientoDAOImp$LevantamientoMapper.mapRow(LevantamientoDAOImp.java:1)
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:92)
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:1)
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:648)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:636)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:665)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:673)
    at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:728)
.............
........

Enhancement: Add the ability to supply a custom SpyLogDelegator (to take
more control over things actually get logged.)

See attached submission which is working fine for me needs.

See this thread:
http://groups.google.com/group/log4jdbc/browse_thread/thread/21e97eda7a48b033

It would be nice if CallableStatement parameters set by name instead of
index would be logged as well. But is this even possible? For example, this
is the SQL I use to call procedures, but I use the by name setters.

For example: { ?=call myfunc(?,?) }

The parameters could be logged on a separate line perhaps?

Deploy logjdbc.jar and slf4j jars in my Tomcat /lib replace "jdbc" with 
"jdbc:log4jdbc" in my server.xml

 url="jdbc:log4jdbc:oracle:thin:@...



org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of 
class 'oracle.jdbc.driver.OracleDriver' for connect URL 'jdbc:log4jdbc:oracle:

I am using log4jdbc3-1.2alpha2.jar, connected to an Oracle database.  When a 
date or timestamp is bound to a PreparedStatement, the value appears in the 
log as 2009-06-25 13:54:12.623, with no quotes.  To better support using the 
string outside of the log, please wrap it in single quotes: '2009-06-25 
13:54:12.623'.  (BTW, SqlServerRdbmsSpecifics already displays 
dates/timestamps in this format.)

See the attached code for the fix.

BTW, great tool!

For automatic loading of JDBC drivers, it required for the file 
'\META-INF\services\java.sql.Driver' to be in the jar file with the classname 
of the driver.

This file is not present.

Add file: \META-INF\services\java.sql.Driver
content: net.sf.log4jdbc.DriverSpy

at 2008 i used this tool - it help me a lot. thanks :)

but it gathers too much of SQLs when Hibernate ame makes it's work.

I find out that this is because of non clearing currentBatch in 
StatementSpy.java

my fix was:
currentBatch = new ArrayList();

right now i verified that in your code written:
currentBatch.clear();

possible it make same thing and no fix are needed now.

It will result in easy integration with maven projects and faster adoption

Consinder using Maven Ant tasks for the implementation:
http://maven.apache.org/ant-tasks/index.html. It gives to ant tasks for
various maven activities. More specifically,  the 'artifact:deploy' ant
task allows the deployment of an ant generated jar file to a maven repository.

This is perhaps a cosmetic issue.

In my unit tests (call them integration tests if you want) I will sometimes 
violate database constraints and check whether the expected error is raised. 
Now with log4jdbc this leads to a full stacktrace from the jdbc.audit, 
jdbc.sqlonly and jdbc.sqltiming.

I my case, that’s 3 stacktraces. See issue #49 for why I don’t use FATAL to 
cut this down to only 1. But even a single stacktrace is not helpful in this 
case.

Hi, the date format is wrong for MySQL, so it's not possible to cut and paste 
the SQL into a database tool.

I've written a patch for it (attached).

What steps will reproduce the problem?
1. Don't register the driver with Class.forName("net.sf.log4jdbc.DriverSpy");
2. Get a connection with jdbc:log4jdbc:...

What is the expected output? What do you see instead?
Expected: DriverManager should detect the driver itself.
Actual: No suitable driver found for jdbc:log4jdbc:...

What version of the product are you using? On what operating system?
WindowsXP, log4jdbc4-1.2beta1.jar

Please add a META-INF\services\java.sql.Driver file with the content
"net.sf.log4jdbc.DriverSpy" in the log4jdbc4-*.jar. This will automatically
register the driver (at least with flat classloaders, others I haven't tested).

We LOVE to copy and paste the sql right from the log4jdbc logs.  It would
be awesome if we could swap in a certain logger that added to_date('the
date', 'the format') so any SQL with Dates we could cut and paste into an
sql editor and run.  I frequently just hand off the sql to dbas and they
shoot back I don't have the to_data and I have to explain that is just a
log...I actually passed in a Date object to oracle's adapter.....it would
be awesome if we had a logger for this so it was all cut and paste and run.

What steps will reproduce the problem?
1. use oracle and ibatis, log4jdbc 
2. make sqlmap for ibatis
3. sql use oracle string concatenation '||'

What is the expected output? What do you see instead?
run ok
I see error (ibtais throw exception with error code 17004)

What version of the product are you using? On what operating system?
my system windows7 x64 sp1, ibatis, oracle 11gr2, jdk 1.6.0_u31,
eclipse hellios r2, tomcat 6

Please provide any additional information below.

where somefield = #field1#||#field2#||#field3#

run ok without log4jdbc.
but error throw with log4jdbc  

I'm using log4jdbc 1.2 beta 2.

When I was doing performance profiling for unrelated reasons, I noticed that 
log4jdbc was using a little over 1% of my CPU time, which surprised me since I 
had almost all the log4jdbc loggers turned off and nothing was actually being 
logged. 

It looks like in the Slf4jSpyLogDelegator.sqlOccurred() method, it's calling 
processSql() before it checks if the logger is enabled.  It would be more 
efficient to check if the loggers are enabled first to avoid unnecessary 
tokenization of the SQL statement.

What steps will reproduce the problem?
1. Create PreparedStatement
2. add batch
3. execute
4. add another batch
5. execute

What is the expected output? What do you see instead?
I expected to see execute 3 and execute 4 seperatly but I get 3+4 in execute 4

What steps will reproduce the problem?
1. using the following log4j configuration:
! Log all JDBC calls except for ResultSet calls
log4j.logger.jdbc.audit=FATAL,jdbc
log4j.additivity.jdbc.audit=false

! Log only JDBC calls to ResultSet objects
log4j.logger.jdbc.resultset=FATAL,jdbc
log4j.additivity.jdbc.resultset=false

! Log only the SQL that is executed.
log4j.logger.jdbc.sqlonly=ERROR,sql
log4j.additivity.jdbc.sqlonly=false

! Log timing information about the SQL that is executed.
log4j.logger.jdbc.sqltiming=WARN,sqltiming
log4j.additivity.jdbc.sqltiming=false

! Log connection open/close events and connection number dump
log4j.logger.jdbc.connection=FATAL,connection
log4j.additivity.jdbc.connection=false

! the appender used for the JDBC API layer call logging above, sql only
log4j.appender.sql=org.apache.log4j.FileAppender
log4j.appender.sql.File=./logs/sql.log
log4j.appender.sql.Append=false
log4j.appender.sql.layout=org.apache.log4j.PatternLayout
log4j.appender.sql.layout.ConversionPattern=-----> %d{yyyy-MM-dd 
HH:mm:ss.SSS} %m%n%n

! the appender used for the JDBC API layer call logging above, sql timing
log4j.appender.sqltiming=org.apache.log4j.FileAppender
log4j.appender.sqltiming.File=./logs/sqltiming.log
log4j.appender.sqltiming.Append=true
log4j.appender.sqltiming.layout=org.apache.log4j.PatternLayout
log4j.appender.sqltiming.layout.ConversionPattern=-----> %d{yyyy-MM-dd 
HH:mm:ss.SSS} %m%n%n

! the appender used for the JDBC API layer call logging above
log4j.appender.jdbc=org.apache.log4j.FileAppender
log4j.appender.jdbc.File=./logs/jdbc.log
log4j.appender.jdbc.Append=false
log4j.appender.jdbc.layout=org.apache.log4j.PatternLayout
log4j.appender.jdbc.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss.SSS} 
%m%n

! the appender used for the JDBC Connection open and close events
log4j.appender.connection=org.apache.log4j.FileAppender
log4j.appender.connection.File=./logs/connection.log
log4j.appender.connection.Append=false
log4j.appender.connection.layout=org.apache.log4j.PatternLayout
log4j.appender.connection.layout.ConversionPattern=%d{yyyy-MM-dd 
HH:mm:ss.SSS} %m%n

2. added -Dlog4jdbc.sqltiming.warn.threshold=1 -
Dlog4jdbc.sqltiming.error.threshold=2 options to JAVA_OPTS of my Tomcat 
instance.


What is the expected output? What do you see instead?
Expected to see some slow SQL in sqltiming.log, but the log is empty


What version of the product are you using? On what operating system?
I'm using log4jdbc4-1.2beta1 on OpenSuse 10.3

What steps will reproduce the problem?
Log any insert statement with Dates.

What is the expected output? What do you see instead?
it looks like this:
insert into table (columns,...) values (1, 'string', 2222-02-22 22:22:22.0,
2009-06-10 15:02:05.0, 2009-06-10 15:02:05.0, 1) 
and there are two problems to put this into informix sql client
a) dates are not in the apostrophes ''
b) dates have additional characters .0 which are not parsed correctly by
informix

What version of the product are you using? On what operating system?
log4jdbc3-1.2alpha2.jar

Please provide any additional information below.
Maybe adding a DateTimeFormat and 'apostrophe required' options to
configuration would be a solution for that?

Support for XADataSource

What steps will reproduce the problem?
1. Using Oracle 10gR2 and it's own JDBC driver, on Tomcat 5.5/Linux.
2. Use the following in your resource file:

<Resource name="jdbc/myConnection" auth="Container"
    type="oracle.jdbc.pool.OracleDataSource"
    driverClassName="net.sf.log4jdbc.DriverSpy"
    factory="oracle.jdbc.pool.OracleDataSourceFactory"

    url="jdbc:log4jdbc:oracle:thin:@localhost:1521:MYSID"
    user="MyUSer"
    password="MyPassword"
/>

I'm expecting it to work as is, however I get the following exception:

Caused by: java.sql.SQLException: Invalid Oracle URL specified
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
        at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:301)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:221)
        at oracle.jdbc.pool.OracleConnectionPoolDataSource.getPhysicalConnection(OracleConnectionPoolDataSource.java:157)
        at oracle.jdbc.pool.OracleConnectionPoolDataSource.getPooledConnection(OracleConnectionPoolDataSource.java:94)
        at oracle.jdbc.pool.OracleImplicitConnectionCache.makeCacheConnection(OracleImplicitConnectionCache.java:1529)
        at oracle.jdbc.pool.OracleImplicitConnectionCache.getCacheConnection(OracleImplicitConnectionCache.java:464)
        at oracle.jdbc.pool.OracleImplicitConnectionCache.getConnection(OracleImplicitConnectionCache.java:333)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:404)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:189)
...

I'm using log4jdbc4-1.2beta2.jar and slf4j-api-1.5.11.jar.

At first sight it looks like "jdbc:log4" is not removed from the URL before 
providing it to the driver class oracle.jdbc.OracleDriver.

What steps will reproduce the problem?
1. Resultset.getStatement() method call

What is the expected output? What do you see instead?

In connection.log reports new connection creation when ResultSet.getStatement() 
is called.
According to 
http://download.oracle.com/javase/6/docs/api/java/sql/ResultSet.html#getStatemen
t%28%29 null or the Statement which produced the ResultSet is returned. I 
wouldn't expect a new Connection creation here:

Current code:
     Statement s = realResultSet.getStatement();
      if (s == null)
      {
        return (Statement) reportReturn(methodCall, s);
      }
      else
      {
        //todo: what's going on here?
        return (Statement) reportReturn(methodCall, new StatementSpy(new ConnectionSpy(s.getConnection()), s));
      }

What about this suggested code?:

  Statement s = realResultSet.getStatement();
  return (Statement) reportReturn(methodCall, s);

What version of the product are you using? On what operating system?
log4jdbc3-1.2alpha2.jar 

It would be nice if log4jdbc supported the javax.sql.DataSource api.

Currently the sqltiming logger has properties that set thresholds for logging 
slow SQL statements at the WARN and ERROR levels. It also logs at the ERROR 
level any SQL that causes an exception, along with the stack trace.

It would be nice to have a way not to log the exceptions and corresponding SQL 
to allow generating just a log of slow SQL to be used for analyzing performance.

It could be another property that controls the behavior of the sqltiming 
logger.  The sqltiming logger and the sqlonly logger could always log to 
different files if someone wanted to separately track slow SQL without loosing 
the ability to track down exceptions.

It would be nice to have the SQL Output formatted for better readability.
In Hibernate is e.g. a special parameter : formatted_sql

The output in Logger:
Hibernate: 
    select
        emailsv0_.bean_id as bean1_147_,
        emailsv0_.bean_module as bean2_147_,
        emailsv0_.email_address as email3_147_,
        emailsv0_.email_address_caps as email4_147_,
        emailsv0_.id as id147_ 
    from
        sugarcrm.emails_v emailsv0_ 
    where
        emailsv0_.email_address_caps='SDASDSADFDSFSDSD' 
        and emailsv0_.bean_module='Contacts'

versus

JDBC Logger: 
 com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
3. select emailsv0_.bean_id as bean1_147_, emailsv0_.bean_module as bean2_147_, 
emailsv0_.email_address 
as email3_147_, emailsv0_.email_address_caps as email4_147_, emailsv0_.id as 
id147_ from sugarcrm.emails_v 
emailsv0_ where emailsv0_.email_address_caps='SDASDSADFDSFSDSD' and 
emailsv0_.bean_module='Contacts' 

I think it'd be useful if ConnectionSpy were able to detect potential
connection leaks, as determined by some function of how long the connection
has been opened.

What steps will reproduce the problem?
1.set log4jdbc.dump.sql.select=false
2.do some inserts with jdbc batch insert feature
3. no inserts on log.

What is the expected output? What do you see instead?
Expected:
jdbc.sqlonly [] - zzz. PreparedStatement.executeBatch() batching xx statements:

See nothing.

What version of the product are you using? On what operating system?
log4jdbc4-1.2beta2 W7 64bits

Please provide any additional information below.
Great tool!

JDBC uses ISO-style escape for date (e.g. MM/dd/YYYY) whereas MySQL uses ( 
YYYY-mm-DD) ... This means that while the resulting log file will have dialect 
specific SQL for many operations, the date formats will be wrong.

I would suggest 2 feature enhancement to address this issue

1) Allowing the user to specify a SimpleDateFormat string such that the SQL 
could be logged.

2) Allowing the user to specify client-side and server-side time zone 
parameters.

add "log4jdbc.defaultRdbmsSpecifics" to file "log4jdbc.properties" or system 
properties for define custom RdbmsSpecifics
eg:
log4jdbc.defaultRdbmsSpecifics=net.sf.log4jdbc.Db2RdbmsSpecifics