I have changed my query and that is running fine in SQL Navigator tool. Still I am facing issues in adding Oracle database receiver in chainsaw.
Following are my implementation Details. XML file <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE log4j:configuration > <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/"; debug="true"> <appender name="A2" class="org.apache.log4j.ConsoleAppender"> <layout class="org.apache.log4j.SimpleLayout"/> </appender> <plugin name="CustomDBReceiver" class="org.apache.log4j.db.CustomSQLDBReceiver"> <connectionSource class="org.apache.log4j.db.DriverManagerConnectionSource"> <param name="password" value="LOG654"/> <param name="user" value="APP_LOG_USER"/> <param name="driverClass" value="oracle.jdbc.driver.OracleDriver"/> <param name="url" value="jdbc:oracle:thin:@10.53.12.54:1521:odsd"/> </connectionSource> <param name="refreshMillis" value="5000"/> <param name="sql" value='select logger as LOGGER, log_date as TIMESTAMP, lvl "LEVEL", thread_name "THREAD", ndc as NDC, mdc as MDC,message as MESSAGE, class_name "CLASS", method_name as METHOD, file_name "FILE", line_number as LINE, concat(concat('{{application,databaselogs,hostname,localhost,log4jid,',l og_id), '}}') as PROPERTIES, exception "EXCEPTION" from ods_audit.APP_LOG'/> <param name="IDField" value="log_id"/> <param name="application" value="App"/> <param name="hostname" value="Host"/> </plugin> <root> <level value="debug"/> </root> </log4j:configuration> Now I am getting following error java.sql.SQLException: Non supported SQL92 token at position: 223: at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179) at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1130) at oracle.jdbc.driver.OracleSql.handleToken(OracleSql.java:201) at oracle.jdbc.driver.OracleSql.handleODBC(OracleSql.java:121) at oracle.jdbc.driver.OracleSql.parse(OracleSql.java:69) Thanks Menka Rally -----Original Message----- From: Scott Deboy [mailto:[EMAIL PROTECTED] Sent: Friday, October 17, 2008 9:45 PM To: Log4J Users List Subject: RE: Chainsaw database connectivity error You should be able to run your SQL statement through a query tool..get that query returning results and the receiver should work. According to these links, in Oracle the concat function only allows concatenation of two strings, use the || operator instead.. http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions 026.htm#i77004 http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/operators 003.htm Scott Deboy Principal Engineer COMOTIV SYSTEMS 111 SW Columbia Street Ste. 950 Portland, OR 97201 Office: 503.224.7496 Direct Line: 503.821.6482 Cell: 503.997.1367 Fax: 503.222.0185 [EMAIL PROTECTED] www.comotivsystems.com -----Original Message----- From: Rally, Menka [mailto:[EMAIL PROTECTED] Sent: Friday, October 17, 2008 1:06 AM To: Log4J Users List Subject: RE: Chainsaw database connectivity error Thanks for your reply Brett. Still it is not working. I have tried following two options:- 1) <param name="sql" value='select logger as LOGGER, log_date as TIMESTAMP, lvl as LEVEL, thread_name as THREAD, ndc as NDC, mdc as MDC,message as MESSAGE, class_name as CLASS, method_name as METHOD, file_name as FILE, line_number as LINE, concat('{{application,databaselogs,hostname,localhost,log4jid,', log_id, '}}') as PROPERTIES, exception as EXCEPTION from APP_LOG'/> 2) <param name="sql" value='select logger as LOGGER, log_date as TIMESTAMP, lvl as LEVEL, thread_name as THREAD, ndc as NDC, mdc as MDC,message as MESSAGE, class_name as CLASS, method_name as METHOD, file_name as FILE, line_number as LINE, concat(''{{application,databaselogs,hostname,localhost,log4jid, '', log_id, ''}}'') as PROPERTIES, exception as EXCEPTION from APP_LOG'/> Please correct me wherever I am wrong. Thanks Menka -----Original Message----- From: Brett Randall [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 15, 2008 3:50 PM To: Log4J Users List Subject: Re: Chainsaw database connectivity error Looks like JDBC is seeing the curly braces as a JDBC escape sequence. I reckon your quotes should be single quotes ' rather than double-quotes " , as you are after a string literal. Best of luck, Brett On Wed, Oct 15, 2008 at 8:34 PM, Rally, Menka <[EMAIL PROTECTED]> wrote: > Hello > > > > I am facing problem in adding Oracle database receiver in chainsaw. > > > > Database details: - > > > > Here's the description of the APP_LOG table > > > > NAME Null? Type > > ------------------------------- --------- ----- > > LOG_ID NOT NULL NUMBER(18,0) > > LOGGER VARCHAR2(45) > > LOG_DATE NOT NULL DATE > > LVL VARCHAR2(45) > > THREAD_NAME VARCHAR2(45) > > MESSAGE VARCHAR2(1000) > > NDC VARCHAR2(45) > > MDC VARCHAR2(45) > > CLASS_NAME VARCHAR2(45) > > METHOD_NAME VARCHAR2(45) > > FILE_NAME VARCHAR2(45) > > LINE_NUMBER VARCHAR2(45) > > PROPERTIES VARCHAR2(1000) > > EXCEPTION VARCHAR2(1000) > > > > > > XML Details: - > > > > > > <?xml version="1.0" encoding="UTF-8" ?> > > <!DOCTYPE log4j:configuration > > > <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/"; > debug="true"> > > <appender name="A2" class="org.apache.log4j.ConsoleAppender"> > > <layout class="org.apache.log4j.SimpleLayout"/> > > </appender> > > <plugin name="CustomDBReceiver" > class="org.apache.log4j.db.CustomSQLDBReceiver"> > > <connectionSource > class="org.apache.log4j.db.DriverManagerConnectionSource"> > > <param name="password" value="LOG654"/> > > <param name="user" value="APP_LOG_USER"/> > > <param name="driverClass" > value="oracle.jdbc.driver.OracleDriver"/> > > <param name="url" > value="jdbc:oracle:thin:@10.53.12.54:1521:odsd"/> > > </connectionSource> > > <param name="refreshMillis" value="5000"/> > > <param name="sql" value='select logger as LOGGER, log_date as > TIMESTAMP, lvl as LEVEL, thread_name as THREAD, ndc as NDC, mdc as > MDC,message as MESSAGE, > > > > class_name as CLASS, method_name as METHOD, file_name as FILE, > line_number as LINE, > concat("{{application,databaselogs,hostname,localhost,log4jid,", log_id, > > > > > "}}") as PROPERTIES, exception as EXCEPTION from APP_LOG'/> > > <param name="IDField" value="log_id"/> > > <param name="application" value="App"/> > > <param name="hostname" value="Host"/ > > </plugin> > > <root> > > <level value="debug"/> > > </root> > > </log4j:configuration> > > > > > > > > Following is the error coming in chainsaw logs > > > > Level > > ERROR > > Logger > > org.apache.log4j.db.CustomSQLDBReceiver > > Time > > 2008-10-14 05:30:53,066 > > Thread > > Chainsaw-WorkerThread > > Message > > *************Problem receiving events > > NDC > > null > > Class > > ? > > Method > > > Line > > > File > > > Properties > > {{hostname,chainsaw}{log4jid,384}{application,log}} > > Throwable > > java.sql.SQLException: Non supported SQL92 token at position: 221: > > at > oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) > > at > oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179) > > at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1130) > > at oracle.jdbc.driver.OracleSql.handleToken(OracleSql.java:201) > > at oracle.jdbc.driver.OracleSql.handleODBC(OracleSql.java:121) > > at oracle.jdbc.driver.OracleSql.parse(OracleSql.java:69) > > > > > > > > Thanks > > Menka > > > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
