Ok, silly me. I was using the sid, not the service name. However, now I'm getting a table or view does not exist error. I displayed the xml file through IE and cut and paste the sql toad. Executed fine. Guess next step, I will create a little test script that uses jdbc to execute the query and see if that works.
Here is my lastest config file. <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE log4j:configuration > <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/";> <plugin name="MyDBReceiver" class="org.apache.log4j.db.DBReceiver"> <connectionSource class="org.apache.log4j.db.DriverManagerConnectionSource"> <param name="driverClass" value="oracle.jdbc.driver.OracleDriver"/> <param name="password" value="blah"/> <param name="user" value="I026526"/> <param name="url" value="jdbc:oracle:thin:@blah:1521:brewu"/> </connectionSource> <param name="IDField" value="log_id"/> <param name="refreshMillis" value="3000"/> <param name="sql" value="SELECT line as LINE, application as APPLICATION, log_level as "LEVEL", message as MESSAGE, hostname as HOSTNAME, src_file as "FILE", stack as THREAD, log_time as TIMESTAMP, '' AS NDC, '' AS MDC, '' AS CLASS, '' AS METHOD, CONCAT('{{USERID,'||USER_ID||',log4jid,'||log_id||'}}','') AS PROPERTIES, '' AS EXCEPTION, '' AS LOGGER from bu_log"/> </plugin> <root> <level value="debug"/> </root> </log4j:configuration> "Scott Deboy" <[EMAIL PROTECTED]> 09/08/2006 11:05 AM Please respond to "Log4J Users List" To: "Log4J Users List" <[email protected]> cc: Subject: RE: chainsaw with DBReceiver I didn't notice that you've specified the wrong receiver class. There is a DBReceiver, which supports a pre-defined schema (to be used with the log4j 1.3 DBAppender). You're using CustomSQLDBReceiver, so modify the receiver class to org.apache.log4j.db.CustomSQLDBReceiver. I've made that change to the XML below. Once you get the concat syntax fixed (results are returned in toad) and you have the properties format displaying correctly {{key1,value1,key2,value2}}, you should be fine. As for reserved keywords, you should be fine. Again, if the results come back in toad, Chainsaw can process them. You may need to wrap alias names in single quotes or brackets, if you can't get the SQL to return with the column aliases that are required by Chainsaw. Are you using the Web Start version of Chainsaw? You need to add your jdbc drivers and the log4j-1.3-db jar to the user.dir/.chainsaw/plugins directory, and you need to go to the application-wide preferences and check the 'ok to remove security manager box'. You can find more info at the Chainsaw page: http://logging.apache.org/log4j/docs/chainsaw.html - click on the 'important distribution notes' link. The application-wide preferences screen is also where you specify the URL to your config file, so that it loads on startup (provide the URL in the 'automatic configuration URL' box). When I load your config file, I see refreshMillis set to 3000. Connectionsource will be blank - it's not a simple name/value pair like 'user' or 'url'. What do you see for refreshMillis? Here's a version with the receiver class corrected, but still needs to have valid SQL provided. <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE log4j:configuration > <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/";> <plugin name="MyDBReceiver" class="org.apache.log4j.db.CustomSQLDBReceiver"> <connectionSource class="org.apache.log4j.db.DriverManagerConnectionSource"> <param name="driverClass" value="oracle.jdbc.driver.OracleDriver"/> <param name="password" value="blah"/> <param name="user" value="blah"/> <param name="url" value="jdbc:oracle:thin:@blah:1521:brewutcp"/> </connectionSource> <param name="IDField" value="log_id"/> <param name="refreshMillis" value="3000"/> <param name="sql" value="SELECT line as LINE, application as APPLICATION, log_level as LEVEL, message as MESSAGE, hostname as HOSTNAME, src_file as FILE, stack as THREAD, log_time as TIMESTAMP, '' AS NDC, '' AS MDC, '' AS CLASS, '' AS METHOD, CONCAT('{{USERID'||USER_ID||',log4jid,'||log_id||'}}') AS PROPERTIES, '' AS EXCEPTION, '' AS LOGGER from bu_log"/> </plugin> <root> <level value="debug"/> </root> </log4j:configuration> Scott -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Fri 9/8/2006 7:21 AM To: Log4J Users List Subject: RE: chainsaw with DBReceiver Scott, Thanks so much for taking time to help me. I really appreciate it. I made the modifications you suggested, but still no luck. Forgive me for asked what seems like a silly question; however .... level and file are key words in oracle, and putting the below sql in toad and executing it results in errors. In addition, it doesn't like the syntax for the concat. When I launch chainsaw, I tell it I want to select my own config file and point it to the file where the information below is stored. The name of the receiver then shows up in chainsaw, but nothing happens and I get no errors. When I look at the properties through chainsaw, the refreshMilis is not the same as what is shown in the information below and the connectionSource is blank. Any thoughts? Also, is there a way in chainsaw to have it default to a specific config file on start up? Below is my modified config file. <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE log4j:configuration > <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/";> <plugin name="MyDBReceiver" class="org.apache.log4j.db.DBReceiver"> <connectionSource class="org.apache.log4j.db.DriverManagerConnectionSource"> <param name="driverClass" value="oracle.jdbc.driver.OracleDriver"/> <param name="password" value="blah"/> <param name="user" value="blah"/> <param name="url" value="jdbc:oracle:thin:@blah:1521:brewutcp"/> </connectionSource> <param name="IDField" value="log_id"/> <param name="refreshMillis" value="3000"/> <param name="sql" value="SELECT line as LINE, application as APPLICATION, log_level as LEVEL, message as MESSAGE, hostname as HOSTNAME, src_file as FILE, stack as THREAD, log_time as TIMESTAMP, '' AS NDC, '' AS MDC, '' AS CLASS, '' AS METHOD, CONCAT('{{USERID'||USER_ID||',log4jid,'||log_id||'}}') AS PROPERTIES, '' AS EXCEPTION, '' AS LOGGER from bu_log"/> </plugin> <root> <level value="debug"/> </root> </log4j:configuration> "Scott Deboy" <[EMAIL PROTECTED]> 09/08/2006 12:52 AM Please respond to "Log4J Users List" To: "Log4J Users List" <[email protected]> cc: Subject: RE: chainsaw with DBReceiver I think this xml (or this with a few tweaks) may work for you..Note, I don't know if you have a column that would make sense as the 'logger' column, so in this example I'm using the string 'logger'. I've added some explanation below the xml. <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE log4j:configuration > <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/";> <plugin name="MyDBReceiver" class="org.apache.log4j.db.DBReceiver"> <connectionSource class="org.apache.log4j.db.DriverManagerConnectionSource"> <param name="driverClass" value="oracle.jdbc.driver.OracleDriver"/> <param name="password" value="blah"/> <param name="user" value="blah"/> <param name="url" value="jdbc:oracle:thin:@blah:1521:blah"/> </connectionSource> <param name="IDField" value="log_id"/> <param name="refreshMillis" value="3000"/> <param name="sql" value="SELECT 'logger' as LOGGER, log_time as TIMESTAMP, log_level as LEVEL, stack as THREAD, message as MESSAGE, '' as NDC, '' as MDC, '' as CLASS, '' as METHOD, src_file as FILE, line as LINE, CONCAT('{{USERID'||USER_ID||',log4jid,'||log_id||'}}') AS PROPERTIES, '' AS EXCEPTION from bu_log"/> </plugin> <root> <level value="debug"/> </root> </log4j:configuration> All columns are required - you're missing the LOGGER column. Also, your 'properties' column is missing the double-braces at each end as well as the comma separation between each name and value. Also, leave out the order by - it breaks the ability to use IDField which is needed to retrieve new events every x milliseconds (the receiver is blindly adding a where clause in order to avoid retrieving the same events twice). You also need an IDField param in the receiver config if you want the query to be ran more than once. Does your table use log_id as the (int) unique identifier? If so, use log_id as your IDField param in the xml and leave it out of the SQL. Your resulting 'properties' column should look like this - note the braces and commas: {{name1,value1,name2,value2}} Run your SQL in a query analyzer and make sure you have each required column, and that the properties column looks correct. It takes a bit of work to get set up, but it works! Good luck! Scott -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thu 9/7/2006 2:26 PM To: [email protected] Subject: chainsaw with DBReceiver Hi, I am trying to using chainsaw to monitor logs that we insert into our database (oracle db). I created the file below and launched chainsaw. I manually pointed chainsaw to this config file. The receiver shows up; however, nothing happens. I get no errors and I get no tab for the receiver. I did notice that the refreshMillis is different in the properties in the chainsaw gui than in the file below. In addition, the connectionSource is blank. Can you give me an idea of what I am doing wrong? In addition, the sql below has issues in oracle because level is a key word in oracle. Does DBReceiver require the field be aliased as LEVEL? If so, how would I get around the key word issue. Also, is there a way that upon start up, this receiver would get loaded automatically? Thanks. <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE log4j:configuration > <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/";> <plugin name="MyDBReceiver" class="org.apache.log4j.db.DBReceiver"> <connectionSource class="org.apache.log4j.db.DriverManagerConnectionSource"> <param name="driverClass" value="oracle.jdbc.driver.OracleDriver"/> <param name="password" value="blah"/> <param name="user" value="blah"/> <param name="url" value="jdbc:oracle:thin:@blah:1521:blah"/> </connectionSource> <param name="refreshMillis" value="3000"/> <param name="sql" value="SELECT log_id as ID, line as LINE, application as APPLICATION, log_level as LEVEL, message as MESSAGE, hostname as HOSTNAME, src_file as FILE, stack as THREAD, log_time as TIMESTAMP, '' AS NDC, '' AS MDC, '' AS CLASS, '' AS METHOD, CONCAT(USER_ID||' '||CATEGORY||' '||log4jid) AS PROPERTIES, '' AS EXCEPTION from bu_log order by TIMESTAMP desc, APPLICATION"/> </plugin> <root> <level value="debug"/> </root> </log4j:configuration> --------------------------------------------------------------------- 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]
