Solved

Dynamically generate batch files with CF?

Posted on 2004-10-04
11
515 Views
Last Modified: 2013-12-24
Hello,

I have the task of automating a previously manual process, one step of which is to load the contents of a .dat file into the Oracle DB using sqlldr. (This .dat file is coming from an SAP system that is behind the firewall, and there is no way that I can change that process)

The sqlldr statement looks like this:
load data
          infile filename_LOG_mmddyy.dat (this filename changes daily)
          append into table tablename
          (  field1     position (01:12) ,
             field2     position (13:112),
             field3     position (113:113) ,
             field4     position (114:123),
             field5     position (124:133),
             field6     position (134:197) ,
             field7     position (198:198),
             field8     position (199:201),
             field9     position (202:301) ,
             field10   position (302:351)
                      )

My first question is - can I just write a SQL statement to update the database by reading the .dat file dynamically? If so, how?

My second question is - If I can't programatically update the Oracle DB by using CF to read the .dat file, can I dynamically create a batch file on the fly to update the database, since the .dat file that the control file is reading will change on a daily basis, so I want to make the filename a variable that can be manipulated. If that is possible, can someone send an example of how to dynamically generate a batch file with CF?

If I am going about this all wrong, please let me know if I am missing a much easier way to do this.
0
Comment
Question by:liltyga
11 Comments
 
LVL 35

Expert Comment

by:mrichmon
ID: 12221983
If you know that the .dat file is in a folder where it is the only dat file then you can use CFFILE to look for a filename of *.bat, then read in the contents, then parse to your DB I would think....
0
 
LVL 9

Assisted Solution

by:digicidal
digicidal earned 400 total points
ID: 12222608
Yes, I do this all the time with CSV's, but it is equally simple with Flats - just read the contents into a variable and then pull out your pieces as the fields with LEFT() or MID() as your fields.  If you want to pre-validate (incase you don't have triggers) you could even use the dynamic query functions ( QueryNew(), QueryAddRow(), QuerySetCell(), etc..) to create a regular CFQuery variable which you could then clean or check for valid formatting prior to the insert into the SQL.  Many possibilities.  It's also possible to use CFQUERY directly on a text data source file provided that the formatting is compatible.  I haven't done this myself (haven't needed to), but I have seen many examples on other sites.
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 12222659
" It's also possible to use CFQUERY directly on a text data source file provided that the formatting is compatible."

Yes but I think you need the filename to do this which is where the CFFILE comes in.
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 12222660
Or at least CFDIRECTORY
0
 
LVL 9

Expert Comment

by:digicidal
ID: 12222934
You are quite correct, although perhaps I am mistaken, but I interpreted the  filename_LOG_mmddyy.dat  name to mean that the filename could also be determined programmatically with logic similar to:

<cfscript>
varThisLog = 'filename_LOG_' & NumberFormat(DatePart('m',Now()),'00');
varThisLog = varThisLog & NumberFormat(DatePart('d',Now()),'00');
varThisLog = varThisLog & DatePart('yyyy',Now());
</cfscript>

Yes, if the file name is not known you would have to use CFFILE and/or CFDIRECTORY to search for the most recent file of a particular type, etc.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 9

Assisted Solution

by:digicidal
digicidal earned 400 total points
ID: 12222944
Sorry... misleading snippet... correctly:

<cfscript>
varThisLog = 'filename_LOG_' & NumberFormat(DatePart('m',Now()),'00');
varThisLog = varThisLog & NumberFormat(DatePart('d',Now()),'00');
varThisLog = varThisLog & DatePart('yyyy',Now()) & '.dat';
</cfscript>

forgot the file extension... just academic, but in case someone actually used that for something. :)
0
 
LVL 9

Assisted Solution

by:digicidal
digicidal earned 400 total points
ID: 12222989
Perhaps only because I've worked with a few clients that had apps dumping logs with similar names I assume that the 'filename_' part is consistent with a particular log originator: ie that there is a SRV03FINANCIAL_LOG_01012004.dat file and a SRV05HRUPLOAD_LOG_01012004.dat file etc...

Is this correct,  liltyga?  Is there anything else you are required to do with the data prior to the append or are you simply looking to automate that process exactly?  I think mrichmon and myself have provided adequate information for your solution, but let us know if there is anything that's unclear or causing problems.
0
 
LVL 35

Assisted Solution

by:mrichmon
mrichmon earned 50 total points
ID: 12227476
Oh yes I overlooked taht part.
If there is a programatic way such as the date to determine the filename then that would mean you could just query the file directly if it is well formed structure (i.e. always has every field with a delimeter)

Example of well formed:

col1, col2, col3, col4
1, 2, 3, 4
5, , 6, 7

Example of not well formed

col1    col2   col3    col4
1          2         3       4
5                    6         7

(In the second example you don't know that a value is missing because tehre are no delimeters....
0
 
LVL 7

Assisted Solution

by:INSDivision6
INSDivision6 earned 50 total points
ID: 12229012
If you under Windows, the following tag supports and has examples of dynamic batch file creation:

http://www.cftagstore.com/tags/cfxexec.cfm

0
 
LVL 9

Accepted Solution

by:
digicidal earned 400 total points
ID: 12234826
mrichmon is correct in his examples... however I surmise from your question that the logs are flat files with fixed positions...

In this case there is no way to directly query the file itself to my knowledge... that is why I indicated that you could then use the cffile action="read" to first capture the data and then use string functions to walk the logs and create a useable query with the QueryNew(), etc... functions in CF.  If your log files are indeed delimited in some fashion then by all means query the file directly. :)

If not I would think that you can essentially look at the information returned by the file read as a ridiculously long list of CRLF delimited values.  Each element of which could be extracted into cells by something like:

myQuery = QueryNew(col1,col2... col10);
... Then loop over the list and do...
QueryAddRow(myQuery,1);
QuerySetCell(myQuery,'col1',Mid(varMyCurrentRow,1,12);
... End your loop action when all columns for the row are populated.

... Now you can access your myQuery variable as if you had queried your database for it.

Are we missing something in your question or have one of us or both answered it already and are just having our own discussion now?  I can't say I complain either way... mrichmon is good company. LOL!

Let us know - since 3/4 of it is done by now we can probably just flesh out the script and you can cut and paste - but I 'truncated' my response short of that incase you want to work on your own (potentially better) solution.  The only thing to note if you are not familiar with string functions is that unlike your query with position start and end identifiers you are providing a start position and a count (the size of the field value itself).
0
 

Author Comment

by:liltyga
ID: 12234984
Thanks so much for everyone's help - my email was down and I am just getting  a chance to check this post out. When I get back to work I will try out your detailed suggestions, and award the points accordingly. Thanks again!
0

Featured Post

Scale it in WD Gold

With up to ten times the workload capacity of desktop drives, WD Gold hard drives employ advanced technology to deliver among the best in reliability, capacity, power efficiency and performance.

Join & Write a Comment

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now