Hibernate version: 2.1.5

I have read numerous entries in FAQ and I have bought the ebook Hibernate in Action.

Is there a way of logging just the SQL commands (with the bound types) but not the columns returned.

For instance, I would like to log:

18:05:49,534 DEBUG SQL:226 - select message0_.MESSAGE_ID as MESSAGE_ID, message0_.MESSAGE_TEXT as MESSAGE_2_, message0_.NEXT_MESSAGE_ID as NEXT_MES3_ from MESSAGES message0_

Or if there was w where clause include that with the binding, but I do not want to log the entire set of rows returned.

In other words (I know I am fussy) I want more than log4j.logger.net.sf.hibernate.SQL=info but less than log4j.logger.net.sf.hibernate.SQL=debug

This is more obvious where I need to see the data (or at least the commands) when updating or inserting but not wanting the entire set of rows when doing a select.

The only thing I can think of doing is to invoke my own (non Hibernate) log messages.

Any other suggestions ????

Thanks for your help,

Mike

In the 2.1.5 code, I do not see anything that will allow you to log the bound parameters. I know that in the 3.x versions this can be done.

If you really want to see the bind parameters you could submit a patch that modifies the methods bindNamedParameters and bindPositionalParamters that are sprinkled across a few classes to do the logging that you need.

Use P6spy.

Or look at the provided log4j.properties, the line that says "Log JDBC bind parameters".

Thanks for your help guys .............

I installed P6Spy.

Unfortunately, I ran into a problem, as I am running some legacy code, accessing another non-hibernate database, and this loads the JDBC drivers ....... P6Spy then complains.

Setting the deregisterdrivers=true did not alleviate the problem.

I decided to remove the functionality that required the access to the database via existing JDBC drivers and successfully got the P6Spy to log the sql queries ...........

Unfortunately, P6Spy still returns all of the resultset from a select statement, despite me trying to exclude the resultset:

The spy.log file has the configuration as follows :

1092118793072|-1||debug||com.p6spy.engine.common.P6SpyOptions reloading properties
1092118793088|-1||info||Using properties file: D:\Qmdb\WEB-INF\classes\spy.properties
1092118793088|-1||info||No value in environment for: getStackTrace, using: false
1092118793088|-1||info||No value in environment for: getFilter, using: false
1092118793088|-1||info||No value in environment for: getAppender, using: com.p6spy.engine.logging.appender.Log4jLogger
1092118793088|-1||info||No value in environment for: getInclude, using:
1092118793088|-1||info||No value in environment for: getAppend, using: true
1092118793088|-1||info||No value in environment for: getDeregisterDrivers, using: false
1092118793088|-1||info||No value in environment for: getUsePrefix, using: false
1092118793088|-1||info||No value in environment for: getExecutionThreshold, using: 0
1092118793088|-1||info||No value in environment for: getAutoflush, using: true
1092118793088|-1||info||No value in environment for: getExclude, using:
1092118793088|-1||info||No value in environment for: getExcludecategories, using: info,debug,batch,result
1092118793088|-1||info||No value in environment for: getIncludecategories, using:
1092118793088|-1||info||No value in environment for: getLogfile, using: spy.log
1092118793088|-1||info||No value in environment for: getRealdriver, using: ca.edbc.jdbc.EdbcDriver
1092118793088|-1||info||No value in environment for: getRealdriver2, using:
1092118793088|-1||info||No value in environment for: getRealdriver3, using:
1092118793088|-1||info||No value in environment for: getSpydriver, using: com.p6spy.engine.spy.P6SpyDriver
1092118793088|-1||info||No value in environment for: getDateformat, using:
1092118793088|-1||info||No value in environment for: getDateformatter, using: null
1092118793088|-1||info||No value in environment for: getStringmatcher, using: com.p6spy.engine.common.SubstringMatcher
1092118793088|-1||info||No value in environment for: getStringMatcherEngine, using: com.p6spy.engine.common.SubstringMatcher@14b4b3a
1092118793088|-1||info||No value in environment for: getStackTraceClass, using:
1092118793088|-1||info||No value in environment for: getSQLExpression, using: null
1092118793088|-1||info||No value in environment for: getReloadProperties, using: false
1092118793088|-1||info||No value in environment for: getReloadPropertiesInterval, using: 60
1092118793088|-1||info||No value in environment for: getJNDIContextFactory, using: null
1092118793088|-1||info||No value in environment for: getJNDIContextProviderURL, using: null
1092118793088|-1||info||No value in environment for: getJNDIContextCustom, using: null
1092118793088|-1||info||No value in environment for: getRealDataSource, using: null
1092118793088|-1||info||No value in environment for: getRealDataSourceClass, using: null
1092118793088|-1||info||No value in environment for: getRealDataSourceProperties, using: null


So using P6Spy, I am no better off than when I was logging the output from hibernate directly (other trhan I have got the bound query data) .....

This leads me back to the same question .........

Is there away, preferrably using Hibernate configuration and not P6Spy (due to the problem with having legacy JDBC code) that I can log the sql queries (preferrably with the bound parameters), but not log the result set ..........

I have tried setting :

### log JDBC bind parameters ###
log4j.logger.net.sf.hibernate.type=info

### Log the SQL
log4j.logger.net.sf.hibernate.SQL=debug


But I still always get the result set ..........

Alternatively, and I know this is not the correct forum, is there some settings I can use in P6Spy to acheieve what I want ...... or soem other tool ........

Thanks for your help,

Mike.

Post your configuration for P6Spy (spy.properties as well as any p6spy within hibernate config)

Matt,

The P6Spy configuration file is :


module.log=com.p6spy.engine.logging.P6LogFactory

realdriver=ca.edbc.jdbc.EdbcDriver
realdriver2=
realdriver3=

executionthreshold=

outagedetection=false
outagedetectioninterval=

# filter what is logged
filter=false

# comma separated list of tables to include when filtering
include =

# comma separated list of tables to exclude when filtering
exclude =

# sql expression to evaluate if using regex filtering
sqlexpression =


# turn on tracing
trace = true
autoflush = true

# sets the date format using Java's SimpleDateFormat routine
dateformat=

#list of categories to explicitly include
includecategories=

#list of categories to exclude: error, info, batch, debug, statement,
#commit, rollback and result are valid values
excludecategories=info,debug,batch,result


#allows you to use a regex engine or your own matching engine to determine
#which statements to log
#
#stringmatcher=com.p6spy.engine.common.GnuRegexMatcher
#stringmatcher=com.p6spy.engine.common.JakartaRegexMatcher
stringmatcher=

# prints a stack trace for every statement logged
stacktrace=false
# if stacktrace=true, specifies the stack trace to print
stacktraceclass=

# determines if property file should be reloaded
reloadproperties=false
# determines how often should be reloaded in seconds
reloadpropertiesinterval=60

#if=true then url must be prefixed with p6spy:
useprefix=false

#specifies the appender to use for logging
appender=com.p6spy.engine.logging.appender.Log4jLogger
#appender=com.p6spy.engine.logging.appender.StdoutLogger
#appender=com.p6spy.engine.logging.appender.FileLogger

# name of logfile to use, note Windows users should make sure to use forward slashes in their pathname (e:/test/spy.log) (used for file logger only)
logfile = spy.log

# append to the p6spy log file. if this is set to false the
# log file is truncated every time. (file logger only)
append=true

#The following are for log4j logging only
log4j.appender.STDOUT=org.apache.log4j.ConsoleAppender
log4j.appender.STDOUT.layout=org.apache.log4j.PatternLayout
log4j.appender.STDOUT.layout.ConversionPattern=p6spy - %m%n

#log4j.appender.CHAINSAW_CLIENT=org.apache.log4j.net.SocketAppender
#log4j.appender.CHAINSAW_CLIENT.RemoteHost=localhost
#log4j.appender.CHAINSAW_CLIENT.Port=4445
#log4j.appender.CHAINSAW_CLIENT.LocationInfo=true

log4j.appender.SPY_FILE=org.apache.log4j.FileAppender
#log4j.appender.SPY_FILE.File=qmdb.p6spy.log
log4j.appender.SPY_FILE.File=qmdb.p6spy.log.file
log4j.appender.SPY_FILE.layout=org.apache.log4j.PatternLayout
log4j.appender.SPY_FILE.layout.ConversionPattern=%d{ISO8601} (%t) - %m%n
log4j.appender.SPY_FILE.Append=true
# log4j.appender.SPY_FILE.MaxFileSize=51200KB
# log4j.appender.SPY_FILE.MaxBackupIndex=10

log4j.appender.SQLPROFILER_CLIENT=org.apache.log4j.net.SocketAppender
log4j.appender.SQLPROFILER_CLIENT.RemoteHost=localhost
log4j.appender.SQLPROFILER_CLIENT.Port=4445
log4j.appender.SQLPROFILER_CLIENT.LocationInfo=true

log4j.logger.p6spy=INFO,SPY_FILE


#################################################################
# DataSource replacement #
# #
# Replace the real DataSource class in your application server #
# configuration with the name com.p6spy.engine.spy.P6DataSource,#
# then add the JNDI name and class name of the real #
# DataSource here #
# #
# Values set in this item cannot be reloaded using the #
# reloadproperties variable. Once it is loaded, it remains #
# in memory until the application is restarted. #
# #
#################################################################
#realdatasource=/RealMySqlDS
#realdatasourceclass=com.mysql.jdbc.jdbc2.optional.MysqlDataSource

#################################################################
# DataSource properties #
# #
# If you are using the DataSource support to intercept calls #
# to a DataSource that requires properties for proper setup, #
# define those properties here. Use name value pairs, separate #
# the name and value with a semicolon, and separate the #
# pairs with commas. #
# #
# The example shown here is for mysql #
# #
#################################################################
#realdatasourceproperties=port;3306,serverName;ibmhost,databaseName;mydb


#################################################################
# JNDI DataSource lookup #
# #
# If you are using the DataSource support outside of an app #
# server, you will probably need to define the JNDI Context #
# environment. #
# #
# If the P6Spy code will be executing inside an app server then #
# do not use these properties, and the DataSource lookup will #
# use the naming context defined by the app server. #
# #
# The two standard elements of the naming environment are #
# jndicontextfactory and jndicontextproviderurl. If you need #
# additional elements, use the jndicontextcustom property. #
# You can define multiple properties in jndicontextcustom, #
# in name value pairs. Separate the name and value with a #
# semicolon, and separate the pairs with commas. #
# #
# The example shown here is for a standalone program running on #
# a machine that is also running JBoss, so the JDNI context #
# is configured for JBoss (3.0.4). #
# #
#################################################################
#jndicontextfactory=org.jnp.interfaces.NamingContextFactory
#jndicontextproviderurl=localhost:1099
#jndicontextcustom=java.naming.factory.url.pkgs;org.jboss.nameing:org.jnp.interfaces

#jndicontextfactory=com.ibm.websphere.naming.WsnInitialContextFactory
#jndicontextproviderurl=iiop://localhost:900



The hibernate configuration file is :

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration
PUBLIC "-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-2.0.dtd">

<hibernate-configuration>

<session-factory>
<property name="hibernate.connection.driver_class">com.p6spy.engine.spy.P6SpyDriver</property>
<property name="hibernate.connection.url">jdbc:edbc://XXXXX:123/testdb</property>
<property name="hibernate.connection.username">YYYY</property>
<property name="hibernate.connection.password">ZZZZ</property>
<property name="hibernate.dialect">net.sf.hibernate.dialect.IngresDialect</property>
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">20</property>
<property name="hibernate.c3p0.timeout">300</property>
<property name="hibernate.c3p0.max_statements">50</property>
<property name="hibernate.c3p0.idle_test_period">3000</property>

<property name="hibernate.show_sql">true</property>

<!-- Mapping files -->
<mapping resource="hello/Messages.hbm.xml"/>
</session-factory>

</hibernate-configuration>



Thanks for your help,

Mike