Use of DSGetLinkInfo() to catch SQL errors?

RodBarnes · Post by **RodBarnes** » Thu Nov 12, 2009 1:37 pm

I was exploring the use of DSGetLinkInfo() to obtain the DSJ.LINKSQLSTATE, DSJ.LINKDBMSCODE, or DSJ.LINKLASTERR values for a link that applies data to an MSOLEDB stage. I would like to capture this and then write it to a seq file for troubleshooting later. So, the link the value would be written to is different than the link that is being evaluated.

But I've not been very successful -- all I get back are empty values. Has anyone a method for using this (or something else) to catch what error ocurred on another link? Particularly for evaluting and capturing the results on an MSOLEDB link?

ray.wurlod · Post by **ray.wurlod** » Thu Nov 12, 2009 5:11 pm

Have you tried specifying the name of the active stage on the other end of the link as the stage name argument in DSGetLinkInfo()?

RodBarnes · Post by **RodBarnes** » Thu Nov 12, 2009 5:51 pm

Well, I may not be using it correctly.

According to the docs, the syntax is: DSGetJobInfo(JobHandle, StageName, LinkName, InfoType)
It states that the DSJ.ME constant may be used for the first three parameters to access the current job, current stage, and current link.

The job has a transformer stage named 'dupCheck' with a link 'insert' to the database stage 'Site_System' and a link 'write' to the sequential stage 'out_Site_System' where I want to log the output of the function.

Code: Select all

dupCheck (xfm) ---- insert ----> Site_System (msoledb)
dupCheck (xfm) ---- write ----> out_Site_System (seq)

I want the fuction to provide error information on the 'insert' link connecting the active stage 'dupCheck' with the passive stage 'Site_System'. I've been putting the function call in a stage variable in 'dupCheck' (the active stage). I also tried putting it in the derivation on the 'write' link passing the name of the stage as 'dupCheck'. The results were the same.

Here's the different ones I've tried.

Code: Select all

DSGetJobInfo(DSJ.ME, DSJ.ME, DSJ.ME, DSJ.LINKSQLSTATE)
DSGetJobInfo(DSJ.ME, DSJ.ME, 'insert', DSJ.LINKSQLSTATE)
DSGetJobInfo(DSJ.ME, 'dupCheck', 'insert', DSJ.LINKSQLSTATE)

DSGetJobInfo(DSJ.ME, 'dupCheck', 'insert', DSJ.LINKDBMSCODE)
DSGetJobInfo(DSJ.ME, DSJ.ME, DSJ.ME, DSJ.LINKDBMSCODE)
DSGetJobInfo(DSJ.ME, DSJ.ME, 'insert', DSJ.LINKDBMSCODE)

DSGetJobInfo(DSJ.ME, 'dupCheck', 'insert', DSJ.LINKLASTERR)
DSGetJobInfo(DSJ.ME, DSJ.ME, DSJ.ME, DSJ.LINKLASTERR)
DSGetJobInfo(DSJ.ME, DSJ.ME, 'insert', DSJ.LINKLASTERR)

But all I get in the output is blank. Maybe this doesn't work with the MSOLEDB stage? Or maybe I just don't understand how this is supposed to work.

chulett · Post by **chulett** » Thu Nov 12, 2009 5:57 pm

I've never tried but would be reluctant to rely on DSJ.ME for anything other than the job handle. So, in my mind, when you do either of the following:

Code: Select all

DSGetJobInfo(DSJ.ME, 'dupCheck', 'insert', DSJ.LINKSQLSTATE) 
DSGetJobInfo(DSJ.ME, 'dupCheck', 'insert', DSJ.LINKDBMSCODE)

Your call is syntactically correct. However, I think you'll find that you need to use this in a Reject link rather than the original insert link. That or you'll find that it just doesn't work with the MSOLEDB stage.

Having nothing Windows based here other than the clients, I can't do any kind of validation for this.

ray.wurlod · Post by **ray.wurlod** » Thu Nov 12, 2009 7:24 pm

DSJ.ME only works for stage name IF you are calling from within a stage (typically a Transformer stage, or a routine invoked from one). Otherwise you need the explicit stage name (which may have been obtained from DSGetJobInfo(hJob, DSJ.STAGELIST)).

Similarly, explicit link names are required rather than DSJ.ME if you are not calling from within a link within a Transformer stage. Link names can be had from DSGetStageInfo(hJob, StageName, DSJ.LINKNAMES).

RodBarnes · Post by **RodBarnes** » Wed Nov 18, 2009 12:19 pm

Using this in a transformer stage variable I was successful at getting the LinkLastError info on a link to an MSOLEDB stage :

Code: Select all

DSGetLinkInfo(DSJ.ME, DSJ.ME, 'insert', DSJ.LINKLASTERR)

I've not had any luck using the DSJ.LINKSQLSTATE or DSJ.LINKDBMSCODE information. Apparently not available from an MSOLEDB stage.

Since there doesn't seem to be much more to do here, I'm going to mark this as a workaround.

ray.wurlod · Post by **ray.wurlod** » Wed Nov 18, 2009 4:02 pm

SQL State is only relevant if you are using ODBC protocols (that is, there is an ODBC stage or Dynamic RDBMS stage on the other end of the link).

Not sure what's happening with DBMS code. This is 0 if no connection to the database server is made (for example, an ODBC driver will detect an error without even connecting to the database, and will set DBMSCODE to 0). Maybe OLEDB works differently. Dunno.