Skip to main content

Steps to log SQL queries in Java/Tomcat app when using SQL Server using log4JDBC & Spylog

SQL server does not support logging queries, but this feature is available in MySQL and PostGREs

We have to follow the steps below to enable logging in SQL Server based tomcat/java application.

1. Download the necessary jar files. (choose whichever version is latest)

      1. log4jdbc4-1.2.jar
      2. log4j-over-slf4j-1.7.7.jar
      3. log4j-1.2.16.jar

2. Place them in the lib folder and make sure they are added in class path.

3. Place log4j.properties in the lib folder as well and include it in class path.

4. configure the location where the log file must be written with respect to the deployed folder.

log4j.appender.Log4JDBC.File=../logs/sql.log

5. Modify the database_params file or any other database config file and make changes to the jdbc url and driver class name as follows. (sample attached)




drivername=net.sf.log4jdbc.DriverSpy

url=jdbc:log4jdbc:jtds:sqlserver://hostname:1433;databasename=dbname;useLOBs=false;

6. Restart the application for the changes to take effect. all the queries which pass through the jdbc driver will get printed in the sql.log as mentioned in the log4j.properties

Comments

Post a Comment

Popular posts from this blog

Recently executed queries in SQL Server

To find out what all queries were executed recently in a sql server database, use the following queries Specific database: SELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.* FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.dbid = DB_ID(' msdb ') ORDER BY deqs.last_execution_time DESC   All Databases: SELECT deqs.last_execution_time AS [Time], dest.text AS [Query] FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest ORDER BY deqs.last_execution_time DESC  
Post a Comment
Read more

Mundasupatti - a fun movie

I saw a movie the other day called Mundasupatti, I’m sure you’ve heard of it if you’re in Tamil Nadu. It was funny, they took a simple superstition which is around and made a fun movie out of it. It was a period film, exploring the 70’s of rural India. I was instantly reminded of swades, a great hindi film which again explores the development gap between cities and villages, it goes a bit further to compare development as seen by an Indian NASA engineer and his old house-hold nanny’s village.  While Swades was a serious film about self empowerment and braking society’s rules about casteism and encouraging education, Mundasupatti is just a funny movie about how stupid, people are.  The movie revolves around a village after which the film is named, the people in the village believe that taking a photograph causes people to get sick and die. The movie did a faithful representation of the rural India, with its proud people and crazy traditions which make no sense. People...
Post a Comment
Read more

Apache Nutch-Solr Integration

Apache Nutch-Solr Integration   As of this writing, I am using Solr 4.8.0 and Nutch 1.8.0 binaries for the integration. Will catch up to later versions as and when my project requires. We can cover installation and operation of Solr and Nutch separately and then talk about the integration. the version of Nutch that I am using is very closely built with Solr and the integration is very simple. For simplicity sake, Ill stick to Linux environment for both as Nutch does not operate in windows natively. Getting Solr to work 1. All you need to for Solr to work are the binaries. you can get them from their  official page  (version 4.8.0) 2. Extract the  solr-4.8.0.zip  in some location. for this tutorial, lets assume that its in  /home/test/Research/solr 4.8.0/ 3. Open terminal, navigate to  /home/test/Research/solr-4.8.0/example/  and execute the following command to start solr server java -jar start.jar  4...
1 comment
Read more