How to push log4j logs to a database

[Ctodd1123]

Hi,

I want to push my log4j logs to a Microsoft SQL server DB (I have already seen Talend's built in log system at File>Edit project properties>Job Settings > Stats & Logs, sadly this does not do the entire job for us). I am setting up the Log4j config file in the Project settings, see below.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">

<log4j:configuration xmlns:log4j='http://jakarta.apache.org/log4j/' >

    <!--  CONSOLE appender  -->
    <appender name="CONSOLE" class="org.apache.log4j.ConsoleAppender">
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern" value="[%-5p]: %c - %x - %d - %n - %m" />
        </layout>
    </appender>

    <!-- DB Logger -->
    <appender name="DB" class="org.apache.log4j.jdbc.JDBCAppender">
        <param name="URL" value="jdbc:sqlserver://[server]:[port];databaseName=[DBname];user=[username];password=[pw]"/>
        <param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
	<param name="user" value="[username]"/>
        <param name="password" value="[pw]"/>
        <param name="sql" value="INSERT INTO [DBname].[tablename] VALUES('%x','%d','%c','%p', '%m')"/>
        <layout class="org.apache.log4j.PatternLayout">
        </layout>
    </appender>

    <!-- Spring Logger -->
    <logger name="log4j.logger.org.springframework">
        <level value="WARN" />
        <appender-ref ref="CONSOLE"/>
    </logger>

	<!-- Root Logger -->
    <root>
        <priority value ="DEBUG"/>
        <appender-ref ref="CONSOLE"/>
        <appender-ref ref="DB"/>
    </root>

</log4j:configuration>

Now when I run a simple job that is importing a delimitered file with the wrong path, log4j should catch the error and push it to the DB per the query in config file but instead I am getting this error in the command line. Any help would be great, I am using Talend fabric (latest version), local windows machine.

[statistics] connecting to socket on port 3937
[statistics] connected
[ERROR]: talendlogging.commonjavaexceptions_0_1.CommonJavaExceptions - - 2019-05-09 10:37:15,099 -
- tFileInputDelimited_1 - U:\bcto\Arachne\LoggingSystem\prototyping\stats_file.tx (The system cannot find the file specified)[statistics] disconnected
log4j:ERROR Failed to excute sql
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'U:'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:885)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:778)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServerStatement.java:703)
at org.apache.log4j.jdbc.JDBCAppender.execute(JDBCAppender.java:218)
at org.apache.log4j.jdbc.JDBCAppender.flushBuffer(JDBCAppender.java:289)
at org.apache.log4j.jdbc.JDBCAppender.append(JDBCAppender.java:186)
at org.apache.log4j.AppenderSkeleton.doAppend(AppenderSkeleton.java:251)
at org.apache.log4j.helpers.AppenderAttachableImpl.appendLoopOnAppenders(AppenderAttachableImpl.java:66)
at org.apache.log4j.Category.callAppenders(Category.java:206)
at org.apache.log4j.Category.forcedLog(Category.java:391)
at org.apache.log4j.Category.error(Category.java:305)
at talendlogging.commonjavaexceptions_0_1.CommonJavaExceptions.tFileInputDelimited_1Process(CommonJavaExceptions.java:914)
at talendlogging.commonjavaexceptions_0_1.CommonJavaExceptions.runJobInTOS(CommonJavaExceptions.java:3089)
at talendlogging.commonjavaexceptions_0_1.CommonJavaExceptions.main(CommonJavaExceptions.java:2902)
U:\bcto\Arachne\LoggingSystem\prototyping\stats_file.tx (The system cannot find the file specified)

[Anonymous]

Hello,

You can define the output level of log messages to override the global configuration at Job execution.

For more information, see TalendHelpCenter:Customizing log4j output level at runtime

Let us know if it is what you are looking for.

Best regards

Sabrina