DSXchange

Hey,
I havent used a whole lot of basic and hence I ask. One of my columns in the SQL server is a file path. e.g (\\ pathname\edxt\data\folder\abc.tiff) I want to check if this file path and document actually exists on the system before I transfer the data from my transformer.How can I check for that? If it does'nt exist I need to write that row to an error file.
Any suggestions would be helpful.

Thanx guys

Do it in job control before your run your job. Simply use a DOS batch script using the dir command and check for the results of the file. Use the DSExecute API (see your manual) and either call it using a custom BATCH job control, or use it in a DS user function/routine.

Create a Routine that attempts to open the file as if it were a record in a table, relying on the fact that DataStage BASIC can treat the folder as a table. For example:

Hey ,
I wrote a transform and it worked.So thanx for your prompt response.Thanks a lot Ray.I am going to try your response as well.


This is what I wrote in the Transform
FUNCTION KsDirExists(Arg1)

Ans = 10
openseq Arg1 to testfile Then Ans = 'opened'
Else Ans = 'not Opened'
closeseq testfile




RETURN(Ans)

You need to be careful if you are doing a lot of repetitive calls to a DS function doing OPENSEQ statements looking for the same file. DS has issues and will false-positive hit sometimes. The most solid solution is to use the OS's own tools for file existence (dir on NT and ls or test on UNIX).

Arrr, who needs performance?!!

(He's right, of course.)

The Basic Manual says not to use this as a transform but basically I need to check this for 60,000 rows. The file path is a column in my Sql server table. So if i write a routine and call it before my transformer stage executes I need to write the ones that do not exist to a sequential file and not tranfer them to my target database.

Probably the fastest approach is to do it in BASIC as I outlined earlier, with the opened directory held open by having its file variable declared to be in COMMON. The modified code is:

Ray,
I did not completely understand your code.
But I tried using it and compiling it and it gave me the following:

WARNING: Variable 'FINFO$IS.FILEVAR' never assigned a value.
Compilation Complete.
is what I wrote and it works for files that reside on the ascential server but not across the network.

Sorry about that. I've edited the code adding one line, the $INCLUDE declaration. This loads the constants used by the FileInfo() function, in particular FINFO$IS.FILEVAR.
It should be OK now.

When I used the modified code it worked in the morning.Now when I try to run it, it gives me a " Failed to compile test harness error".Do you have any clue as to what that means.
Also in the morning it wasnt reading files stored on an other server.I mean it used to open files on the Ascential server but not on any other though I had the access rights to that server.
In any case it now completely does not open anything.Thanks a lot ray for your help.

[quote="kcbland"]You need to be careful if you are doing a lot of repetitive calls to a DS function doing OPENSEQ statements looking for the same file. DS has issues and will false-positive hit sometimes. The most solid solution is to use the OS's own tools for file existence (dir on NT and ls or test on UNIX).[/quote]

Ray here is my code review.

Conceptually using the openpseq or openseq opcode to check for existance of a file is incorrect, honest. Consider what baggage and potential probems you are carrying with you.

1) You are going to create a readu lock, most people would not be aware of this. This has numerous side affects.

2) You are going to open the file for writing, why?, apart form the overhead, room for some very (actually mind boggling) obscure failures.

Checking for existence should only check the directoru header the file is contained in. Creating solid code involves not doing things that you do not need to do, unless you are very aware of the consequences.

If you must use BASIC use a select, readlist and find.

I do not have the time to give a case study of this here, but Ray I would be pleased to do so over a quiet drop of liffy water with you, next time we are in the same city.

Of course the proposed code will work for most people in most circustances most of the time. I do not expect people to be aware of these obscurities, but only doing what you have do will provide protection.


See ya soon mate.

Thanks, Johno.

I was aware of the OpenSeq issues - it weren't me, guv! I used OpenPath (not OpenSeq), and opened the directory into COMMON. Others magically transmogrified my OpenPath into OpenSeq. No-one seems to have noticed.

I suspect, indeed, that even that was unnecessary, since !GET.PATHNAME checks for existence would have to check that out.

Otherwise, I was under the impression that ReadV with a third argument of 0 had smarts built in so that it did only do the directory scan (or apply the hashing algorithm or do a BSCAN, as appropriate).

The pint or two of Liffey water sounds like a good plan, though. Will be in your neck of the woods the last two weeks of June. The question is, will you?

Regards,
Ray

openpath and openseq are undistinguishible uner the covers, notice I said openpseq and openseq.

End of June Ray, currently scheduled to be back in Tokyo 24th, been there for the last 3 weeks, we have a very large PX client going live, after 9 months of devellopment, so have to be there.

jwhyman wrote:openpath and openseq are undistinguishible uner the covers, notice I said openpseq and openseq.

Are you sure? OpenPath can open Type 1/19 files. What does it mean if OpenSeq opens one of these? I just tested it (OpenSeq with a Type 19 file); it took the Else clause with a Status() of 0, even though the Type 19 file I specified actually exists. This is not in accordance with what's in the manual!
INMAT() returned 0, and a STATUS statement took its ELSE clause. Something funny is happening in the state of Denmark!

OpenPath was quite happy with the same Type 19 file. STATUS() returns 19, INMAT() returns 1, and STATUS statement takes its THEN clause.

I can't accept that they're indistinguishable. And, in the public version of DataStage that I'm using, OpenPseq does not compile.