topic Re: Loading logfiles into a SQL Server DB via XML in Talend Studio

Loading logfiles into a SQL Server DB via XML

BillG-Quanex — Fri, 15 Nov 2024 22:13:45 GMT

I have a lot of logfiles that I want to load into a SQL Server database via a Talend job so that I can preserve the logs without keeping the files in the file system (we generate over 3k log files every day). I do not want to change the jobs to write to the database directly as I don't want the jobs to be dependent on the DB server being available.

I have created a Talend job that can read a log file and convert it to an XML document which I can then send to a SQL Server (2016) stored procedure. The stored procedure is executed, however the contents of the log file are NOT being stored in the database.

I have ruled out the following possible causes:

connectivity/permissions - I have logged in to SSMS with the account used by the Talend job and executed the procedure (minus the XML header since I have not found a way to have SSMS allow UTF-8)

The stored procedure is:

ALTER

procedure

[myschema].[myProc]

@logXML

xml,

@logFile

varchar

(max),

@result

varchar

(max)

OUT

begin

declare

@myAction

varchar

(max);

begin

try

begin

transaction

set

@myAction

'inserting log file '

@logFile

;

insert

into

[myDB].[mySchema].[myTable]

(

JobName,

LogName,

RunDate,

LogLineNum,

LogLine

)

(

select

logs.value(

'JobName[1]'

'varchar(500)'

)

JobName,

logs.value(

'LogName[1]'

'varchar(500)'

)

LogName,

logs.value(

'runDate[1]'

'varchar(20)'

)

runDate,

lines.value(

'Number[1]'

'integer'

)

LogLineNum,

lines.value(

'Content[1]'

'varchar(max)'

)

LogLine

from

@logXML

.nodes(

'/LogFileContents'

)

l1(logs),

@logXML

.nodes(

'/LogFileContents/LogLines/Line'

)

l2(lines)

);

commit

transaction;

set

@result

'SUCCESS'

;

end

try

begin

catch

if @

@TRANCOUNT

begin

rollback

set

@result

'Error '

@myAction

'. Error code: '

+ @

@ERROR

;

end

catch

end

The XML document generated by the Talend job looks like the following:

<?xml version="1.0" encoding="UTF-8"?> // I do

not

have control

this - I

get

this "free"

from

Talend

<JobName>myTask</JobName>

<LogName>myLogFile</LogName>

<runDate>YYYYMMDDHHmiss</runDate>

<Line>

</Number>

st Log Message</Content>

</Line>

<Line>

</Number>

nd Log Message</Content>

</Line>

...

<Line>

Last

</Number>

Last

Log Message</Content>

</Line>

</LogLines>

</LogFileContents>

In Talend I have tried passing the XML document to the tDBSP as a document (which results in a Talend Error). It is currently configured to pass the XML document as a String - I had to add the "sendStringParametersAsUnicode=false" to my connection string (additional parameters) to eliminate the "unable to switch encoding" error. When I check the result from the call to the stored proc I get "SUCCESS". I have even tried having the stored proc pass back a non-sense value for the result instead of "SUCCESS" (and the call to the stored proc returned the non-sense value).

In the stored proc I have tried changing the input parameter to a varchar(max) and then converting that to an XML variable via

@logXML

varchar

(max),

...

declare

@myXML

xml =

convert

(xml,

@logXML

)

...

from

@myXML

.nodes(...)

l1(logs)

@myXML

.nodes(...)

l2(lines)

which also did not load the document into the database.

However if I execute the stored procedure via SSMS using the XML Document (only difference is the missing <?xml...> document header):

<JobName>myTask</JobName>

<LogName>myLogFile</LogName>

<runDate>YYYYMMDDHHmiss</runDate>

<Line>

</Number>

st Log Message</Content>

</Line>

<Line>

</Number>

nd Log Message</Content>

</Line>

...

<Line>

Last

</Number>

Last

Log Message</Content>

</Line>

</LogLines>

</LogFileContents>

The entire contents loads as expected (The log file I am testing with has 40 lines and all 40 show up in the database, exactly as I expect).

I have also reviewed the following links - but they are in regards to processing XML files, not XML strings.

I feel like there's something not quite configured right in Talend but I'm not sure where that would be. Any input/guidance would be greatly appreciated.

Re: Loading logfiles into a SQL Server DB via XML

Anonymous — Mon, 09 Jan 2023 08:33:42 GMT

Hello

I'm not a SQL SERVER stored procedure expert, can you try to pass XML content as a string type? In the job, you can use a tWriteXMLField to write a XML string.

Regards

Shong

Re: Loading logfiles into a SQL Server DB via XML

BillG-Quanex — Mon, 09 Jan 2023 21:18:58 GMT

Hi Shong,

Thanks for the info. I have tried using the tWriteXMLField and one positive thing from that is that I was able to eliminate the XML header - the XML header initially caused me issues since Talend usually encodes via UTF-8 but MS SQL Server doesn't accept that XML encoding by default (there is an "Additional Parameter" value I found that allowed the procedure to accept UTF-8 encoding).

Unfortunately, whether I specify the input XML parameter as a string (and convert it to XML via convert(xml, @logXML)) or if I simply declare the input parameter as XML (ALTER procedure [myproc] @logXML as varchar(max)) the stored procedure is not loading the data into the database when called from Talend. I can take the string generated by tWriteXMLField and execute the stored procedure in SSMS (not via Talend) and the XML gets successfully processed into the DB.

Bill.

Re: Loading logfiles into a SQL Server DB via XML

BillG-Quanex — Thu, 19 Jan 2023 20:16:35 GMT

So it seems that I need to add a tDBCommit after calling the stored procedure - even though the stored procedure has transaction handling inside. It appears to have something to do with the fact that I'm using a tDBConnection for my tDBSP instead of configuring the connection in the tDBSP component.

Re: Loading logfiles into a SQL Server DB via XML

Anonymous — Fri, 20 Jan 2023 03:16:41 GMT

Does it work now if you use a tDBCommit? If you use the DB connection created by tDBConnection, you should use a tDBCommit to commit the changes.

Re: Loading logfiles into a SQL Server DB via XML

BillG-Quanex — Tue, 24 Jan 2023 18:46:31 GMT

Shong - yes it works now. I'm curious as to why I need a tDBCommit when the stored procedure had a commit in it. The point of having the stored procedure was, in part, to have it handle the transaction rather than having to manage the transaction (and rollback) in Talend. Is this a function of (1) SQL server, (2) Java (seems unlikely as I have worked with Java developers elsewhere who used Oracle PL/SQL stored procedures that handled the transactions) or a (3) design choice by Talend?