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: '[email protected]'
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]
