[dba-VB] VB.Net - Hook an open program

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

Contribute your unused CPU cycles to a good cause:

-----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

Contribute your unused CPU cycles to a good cause:

dba-VB mailing list
dba-VB at databaseadvisors.com

dba-VB mailing list
dba-VB at databaseadvisors.com

More information about the dba-VB mailing list