Here are a few suggestions: - the field assigned as the IDField needs to be a not null, unique, preferably an auto-incrementing int (you could probably use the ID column) - i'm not sure what msisdn is, but it should only show up once in the sql param - you need to verify that you're successfully connecting to the database (if you have a line in the chainsaw-log tab with '*************Problem receiving events', you're not connecting to the database - the exception should give details - the 'application' and 'hostname' params need to be set up in the sql, not as actual params on the receiver (the javadoc example shows how you can use the mysql CONCAT function to build the string that will allow you specify multiple properties, I'm not sure what the oracle equivalent string concatenation function is)
Here's the sql I'd try. I'm hard-coding the two properties and using MSISDN as the LOGGER, and ID as the log4jid (and ID) field - you'd need to change the concat function to work with oracle select msisdn as LOGGER, INCOMING_HTTP_REQ as TIMESTAMP, "DEBUG" as LEVEL, "" as THREAD, URL as MESSAGE, "" as NDC, "" as MDC, "" as CLASS, "" as METHOD, "" as FILE, "" as LINE, concat("{{application,myapp,hostname,myhost, log4jid,",ID,"}}") as PROPERTIES, "" as EXCEPTION from cc_store in the receiver config, change IDField to reference the ID column (unless it's really the msisdn column you're using as the unique id), and get rid of the 'application' and 'hostname' params (they're defined in the sql concat statement above) -----Original Message----- From: Ghielli Patrick [mailto:[EMAIL PROTECTED] Sent: Tue 3/22/2005 8:33 AM To: 'Log4J Users List' Cc: Subject: RE: Chainsaw with CustomSQLDBReceiver fo oracle (Same with inclusions) Hi, I have included the table schema and the DB receiver config file. I kept the remarks below in mind, since they were already mentionned in an earlier posting. But unfortunately, still no success. The 'application' and 'hostname' paraeters seem to have no effect at all. Regards, Patrick. Table schema: Name Datatype Size Scale Nulls? Default Value MSISDN VARCHAR2 24 0 No ID VARCHAR2 15 0 No URL VARCHAR2 256 0 No WSP_STATUS_CODE NUMBER 4 0 Yes HTTP_STATUS_CODE NUMBER 4 0 No SESSION_MANAGEMENT_STATUS NUMBER 1 0 Yes ACCESS_GROUP VARCHAR2 24 0 No USER_GROUP VARCHAR2 24 0 No USER_AGENT VARCHAR2 256 0 No OUTGOING_HTTP_REQ NUMBER 19 0 No INCOMING_HTTP_RESP NUMBER 19 0 Yes OUTGOING_HTTP_RESP NUMBER 19 0 Yes INCOMING_HTTP_REQ DATE 7 0 Yes DB Receiver config: <?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="pwd"/> <param name="user" value="user"/> <param name="driverClass" value="oracle.jdbc.driver.OracleDriver"/> <param name="url" value="jdbc:oracle:thin:@101.101.101.101:1521:msp"/> </connectionSource> <param name="refreshMillis" value="5000"/> <param name="sql" value='select msisdn as LOGGER, INCOMING_HTTP_REQ as TIMESTAMP, msisdn, msisdn as THREAD, msisdn as MESSAGE , msisdn as NDC, msisdn as MDC, msisdn as CLASS, msisdn as METHOD, msisdn, msisdn as LINE, msisdn as PROPERTIES, msisdn as EXCEPTION FROM cc_store'/> <param name="IDField" value="MSISDN"/> <param name="application" value="App"/> <param name="hostname" value="Host"/> </plugin> <root> <level value="debug"/> </root> </log4j:configuration> -----Original Message----- From: Scott Deboy [mailto:[EMAIL PROTECTED] Sent: vrijdag 18 maart 2005 6:22 To: Log4J Users List Subject: RE: Chainsaw with CustomSQLDBReceiver fo oracle If you can post your table schema and what you have for an 'sql' parameter and we can figure it out. Scott There are a number of limitations described in the JavaDoc: http://cvs.apache.org/viewcvs.cgi/logging-log4j/src/java/org/apache/log4j/db /CustomSQLDBReceiver.java?rev=1.3&view=markup - You must have an identity column and specify that field as the IDField param. - All of the event fields must be defined in the SQL statement, even if you don't have columns for them in your database - Unless you specify 'application' and 'hostname' params, the events may show up on the chainsaw-log tab (but more likely) an empty tab. There is an example receiver configuration on Chainsaw's Welcome tab that shows an example CustomSQLDBReceiver config. When running, look on the chainsaw-log tab for messages containing 'lastID' - this will tell you if your sql is executing, because lastID is the highest value of the identity field that the receiver was able to retrieve from the database. -----Original Message----- From: Ghielli Patrick [mailto:[EMAIL PROTECTED] Sent: Wed 3/16/2005 8:14 AM To: 'log4j-user@logging.apache.org' Cc: Subject: Chainsaw with CustomSQLDBReceiver fo oracle Hi, I am trying to configure a CustomSQLDBReceiver for the Oracle DB I am using. After solving some problems mentionned in other postings on this mailing list, I now try set up a working sql statement. The statement I have now results in an error. The event in the chainsaw log describe it as an invalid column name. However, executing this statement in sqlplus returns the result as I would have expected. I should add that the chainsaw log output says that it is not able to select a dialect. Perhaps that has something to do with it. But I don't know how to make it choose the correct one. Thanks, Patrick Ghielli --------------------------------------------------------------------- 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]