John W. Colby
jwcolby at colbyconsulting.com
Tue Aug 23 16:32:05 CDT 2005
OK< sorry, I just fired off that response. This is a system that pulls data out of a set of related tables and massages it into a text file. The end result is a fixed width report (at this moment) in a SERIOUSLY denormalized format (ThisFld1, ThisFld2 etc by the DOZENS). This report will be sent to a client Insurance company where it will be imported into a mainframe. The "jobs" are reports that have to be sent. The same report may have to be sent multiple places, attached to an email in one case, FTPed in another. It is in fact also being sent to our own people (MY client, the call center) in a CSV format. Related records from a DOZEN tables (or more) have to be pulled, some have to be denormalized (placed in a temp table) then everything linked back together. Once the data is in a "single record" format (SERIOUSLY DENORMALIZED) it has to be exported out to a fixed width format. Unfortunately the client keeps changing where the fields go in the record, what the format looks like etc. And yes, this is happening on a regular basis. The client (insurance company) is moving their entire company to a new software package, they are clueless as you might expect, and things move slowly. TWO years so far (and counting). They have even flopped back and forth between Fixed width and comma delimited (and back again). But... I design to allow me to just change the table. As a result, I have set it all up so that it is table driven. I have a table of what they call their fields, what position in the "big string" each field (start and stop bytes), the name of the field in MY application which maps to the field in THEIR application, the format string that makes it look like they want, whether the field MUST exist in order to even allow the export to occur etc. I read the format table out and in to a field def/supervisor class system. Another class causes the data denormalization to occur, handles some details like checking that offsets (dates) fall within specific windows, pulls the data out of my query and hands it off to the field supervisor which hands each data piece to the appropriate "field class" which formats the field per the instructions in the format table and hands back the piece to the supervisor which inserts it into the "big string" in the right place... OR into a comma delimited "CSV" format string, and hands the finished formatted "big string" off to the data logger which writes it out to a file on a disk with a specific name and a specific location on the hard disk. Once the entire file is written, the finished file is placed in a "job" queue to be sent out to wherever it is going. This process is an entire application, with (in Access) 11 classes and a handful of modules. There is absolutely nothing trivial about doing this. The upside is that as the client comes back and says "no, move this field to here, left justify in the field instead of right, and oh, by the way it should have 4 decimal digits after the decimal point instead of two", I can just go into my definition table and make the appropriate changes, and the new report pops out as they request. Furthermore the process is broken down into "black boxes" that create the export data, logs the finished export string, logs the fact that a given record was exported and what file on the hard disk it was placed in, transmits the file to the correct place, logs the fact that the specified file was transmitted. When I am finished I can run a query to see if a claim record was sent, what file it is in and where on the disk it resides, what day and time it was sent, what method and address was used to send it etc. If a record fails at any step, an email is sent to me and my "boss" at my client informing me that step X of the process failed. Doing that does NOT allow "setting it up in the BE and do not interfere". This is NOT an FE in the classic sense, it is a report generator / logger / sender application, which, just happens to be, in an FE (of its own). All of this is working at this point but it is written in VBA / Access FE container. I am looking at moving the entire thing to .Net, on my own dime, as an exercise in learning VB.Net. Now... For the piece about which I am asking, I am discussing the scheduler part, what report is sent to whom, using what transmission method, at what time of the day/night, including / excluding holidays / weekends / custom schedule etc. The client wants it, and the client is willing to pay for it, so the client gets it. I am just examining my options for moving JUST THIS PIECE (since the whole thing is modular anyway) into VB.Net. Does that make it a little clearer? ;-) John W. Colby www.ColbyConsulting.com Contribute your unused CPU cycles to a good cause: http://folding.stanford.edu/ -----Original Message----- From: dba-vb-bounces at databaseadvisors.com [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller Sent: Tuesday, August 23, 2005 4:49 PM To: dba-vb at databaseadvisors.com Subject: RE: [dba-VB] VB.Net - Hook an open program I think that you are going about this is in a seriously wrong way, JWC. IMO, you create the jobs and schedule them and keep the FE _way_ out of the picture. Unless I am misunderstanding you, you are putting the FE before the BE -- which IMO is the biggest no-no in the SQL business. To put it another way, everything you can blame on MS should be blamed on MS. Set it up in the BE and DO NOT INTERFERE. Not to say there are no reasons for services, but from what you have described thus far, this is NOT one of them. Schedule the job at a frequency you like, then leave your FEs do realize the results. You muddy the waters too much by asking the FEs to muddy the waters. You are the worm chasing its tail, IMO, in the direction you are going. AF`s Rule 1: everything the BE can do, the BE should do. Corollary: no code affecting scheduled sprocs, udfs, etc. should exist in the FE. -----Original Message----- From: dba-vb-bounces at databaseadvisors.com [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of John W. Colby Sent: August 22, 2005 11:06 PM To: dba-vb at databaseadvisors.com Subject: [dba-VB] VB.Net - Hook an open program I want to load an application that loads scheduled tasks into classes from a database. Once the tasks are loaded I want my service to call this application and ask it to run any tasks that the scheduler deems ready to run. Is it possible for one program to call another open (running) program? I would like the scheduler to be part of the service but until the system is debugged I want the scheduler to be a program with a user interface that I can watch. For this reason, the service would cause the scheduler to load, and then periodically "call" the scheduler and ask it to check its list of tasks and run any as appropriate. Is this possible? John W. Colby www.ColbyConsulting.com Contribute your unused CPU cycles to a good cause: http://folding.stanford.edu/ _______________________________________________ dba-VB mailing list dba-VB at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-vb http://www.databaseadvisors.com _______________________________________________ dba-VB mailing list dba-VB at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-vb http://www.databaseadvisors.com