I have a live interface populating mysql and MSDN databases. I would
like to have the data in either database have a trigger fire an event
that can send the new data to several different files in the root
directory.

two requets.   1-  An event
               2-  A trigger to fire the event



Thank you

---

Request for Question Clarification by mathtalk-ga on 08 Aug 2005 12:03 PDT

Hi, Amy:

It's a bit hard to know what you are looking for.  The word "trigger"
means one thing in connection with databases, namely that changes to a
table (inserts, updates, deletes) cause a specific bit of code (like a
stored procedure) to run.  This may not be directly related to what
you have in mind.

Another topic is what are called "extended" stored procedures, which
describes (at least in Microsoft SQL Server and similar engines) a
piece of code that actually runs outside the database environment, but
is called like a stored procedure.  An extended stored procedure might
be useful in writing data to files in the root directory (or
elsewhere).

So you might be interested in knowing more about these topics as they
related to mySQL and MSDE(?) databases.  [The latter is what Microsoft
calls their freely redistributable database engine.]  But your
Question suggests you are looking into a specific application rather
than seeking general information.


regards, mathtalk-ga

---

Clarification of Question by amy123456-ga on 08 Aug 2005 15:56 PDT

Thank you for your comments. You are right, i am having an live
interface populate  a database ( Still deciding which database to use
(mysql or MSDN ) but the problem that i would like to solve is being
able to redistribute the data in the database to another file and or
database. The only way that i see
this working is by using triggers in mysql 5.0. Anytime the interface sends 
a ADT transaction to the database i would like to have that
information feed to another file and or database. The interface can
only feed the database ----from the databse i need to send that info
to another file and or database.

Thank you

---

Request for Question Clarification by mathtalk-ga on 08 Aug 2005 20:07 PDT

Okay, let's grant that you cannot change anything about how data is
fed into the MySQL database.

MySQL triggers are brand new, bleeding edge some would say, in version
5.  Is that the version of MySQL you are trying to interface with?

Setting up a feed from one database to another is pretty standard
stuff, but I'm not convinced that triggers as implemented in MySQL 5.0
are much of a contribution to the arsenal of techniques.

First think about push versus pull in the design.  You seem to be set
on a "push" design:  when a record arrives in the MySQL database,
something "automatically" gets written to a file.

This may seem like the natural approach.  After all, you have an
application interface that is "pushing" data into the database.  Why
not set up a "domino" effect that would then "push" the same data
onward down the pipeline?

But database engines are designed around a goal of data integrity and
support for relatively basic standards (like the SQL statements), with
performance on these modest ambitions the crowning glory.  Reliably
executing elaborate side-effects is not on the cards for an open
source effort like MySQL at this point in its development.

A better approach, in my opinion, is setting up an external
application that tracks the accumulation of records in the MySQL
database through periodic queries and forwards the discovery of novel
records into a file (or into another database directly).

For us to give you more focused and useful assistance, you need to
spell out what tables and what database connections are involved. 
Then I'd hope we can sketch out some "plumbing" to bridge your two
data stores.

regards, mathtalk-ga

---

Clarification of Question by amy123456-ga on 09 Aug 2005 03:44 PDT

Good morning MathTalk, i am glad it is you trying to help-- thank you.

What would you suggest?

being that i only have one feed from the interface to a database- and
i need that data for other process in other files or databases.

Thank you very much for your help

---

Request for Question Clarification by mathtalk-ga on 09 Aug 2005 05:49 PDT

The basic suggestion is to set up a periodic reporting task that
extracts "new" data from (say) the MySQL database and adds it to a
file or to another database, such an MSDE database.

So, to recap my requests for clarification:

  1) What version of MySQL are you using?

  2) What is the definition of the table (or tables) in MySQL that 
     are capturing the information that needs "forwarding"?

regards, mathtalk-ga

---

Clarification of Question by amy123456-ga on 09 Aug 2005 09:49 PDT

MYSQL - ver 5.x

The folowing are the data elements in the database.

PatientIdentifier
PatientAccount
PtFirstName
PtLastname
DateOfBirth
Street
State
Zip
Gender
DateOfService
Loc
Room
Bed
Attending
Attending1
AdmitDateTime
ChiefC
Age


Thank you

---

Request for Question Clarification by mathtalk-ga on 09 Aug 2005 11:56 PDT

Two more questions (ah, but you are used to it by now, I hope!):

  1) Are all these fields in the same table?

  2) Are you allowed to add another column to the table?

regards, mathtalk-ga

---

Clarification of Question by amy123456-ga on 09 Aug 2005 14:45 PDT

yes to both questions.

Thank you

---

Request for Question Clarification by mathtalk-ga on 10 Aug 2005 04:22 PDT

Hi, Amy:

Thanks for the prompt Clarification.  We are getting close!

  1) What field or combination of fields in the table you describe
     above uniquely identifies a single row in the table, ie. what
     the primary key on the table (or, perhaps, what forms a unique
     index)?

  2) What "lifecycle" for these records needs to be tracked
     in a remote database or datafile?  That is, do you need
     to track/forward new rows?  Changes to existing rows?
     How about cases where existing rows are removed entirely?
     Of course it is possible that you want to track all such
     changes to the table, but it is better to be specific.

regards, mathtalk-ga

---

Clarification of Question by amy123456-ga on 10 Aug 2005 09:25 PDT

Good morning.

1- Key   --PatientAccount

2-track all changes to the table.

Thank you

---

Request for Question Clarification by mathtalk-ga on 10 Aug 2005 10:59 PDT

Just to be explicit, is it possible for rows to be deleted from this
table through the existing "live" interface that you have now?

regards, mathtalk-ga

---

Clarification of Question by amy123456-ga on 10 Aug 2005 16:03 PDT

If a patient comes in to the hospital, and he has already been here
before, the interface will do a merge, but no records are deleted.

Thank you

---

Request for Question Clarification by mathtalk-ga on 11 Aug 2005 05:43 PDT

Hi, Amy:

Here's the outline of my recommendation.

  (1) Add a timestamp column to the "source" MySQL table.

  (2) Write a program whose task it is to periodically
      (say once a minute) query for changed records in
      the "source" table.

  (3) This task includes writing the changed records to
      a text file and/or an MSDE database.

The relation between (1) and (2) is that when no value is supplied for
a timestamp column in a MySQL insert or update, the field gets a value
that is the currect time, as set by the database itself.  Thus we can
extract only the most recent changes in (2) rather than working over
the entire table to locate changes.

Since records, as keyed by PatientAccount, are inserted or updated,
but never deleted, the information forwarding task will only need to
manage new or changed rows.

Thoughts?

regards, mathtalk-ga

---

Clarification of Question by amy123456-ga on 11 Aug 2005 13:40 PDT

excellent. How long will it take to get it all together.

Thank you

---

Request for Question Clarification by mathtalk-ga on 12 Aug 2005 05:42 PDT

Hi, Amy:

It will expedite things to have the "DDL" (Data Definition Language)
for the source table in MySQL.  The names of the fields/columns are
given above, but I'd be guessing as to their datatypes, eg. varchar(3)
or integer, etc.

Also if you could clarify the role of the MSDE(?) database in this
project it would be helpful.  I'm aware that you want the data "sent"
someplace (possibly this MSDE database?), but it's not yet clear where
or in what form it should be kept.

regards, mathtalk-ga

---

Request for Question Clarification by mathtalk-ga on 16 Aug 2005 18:21 PDT

Hi, Amy:

I'm not sure if my last request was unclear or if it escaped your
attention, or perhaps it's taking some time to get the information.

The definition of the table in MySQL whose column names you provided
(but not the name of the table itself) will contain specific datatypes
for each field.  While I could certainly make some assumptions, e.g.
that date of birth is a ten character field, it would leave some work
for you later to fix up any incorrect guesses.

It would also be expeditious to know where the data is to be
forwarded.  Supposing the purpose is to maintain a suitably
synchronized set of records in the MSDE/SQL Server database, I
probably use the same datatypes (or their closest Microsoft
counterparts) to define the "target" table and some SQL code to make
life easier when doing inserts/updates to the table.

regards, mathtalk-ga

---

Clarification of Question by amy123456-ga on 17 Aug 2005 03:17 PDT

Good morning Mathtalk.

I will have that for you today.

Thank you very much

---

Clarification of Question by amy123456-ga on 17 Aug 2005 08:51 PDT

Good morning Math talk.

MYSQL  -  database name  -  adt
          table name     -  patient

Field                   Type            Null          Key       Default
---------------         ------          -------       ------    --------

PatientIdentifier	Varchar(255) 	yes			null
PatientAccount		Varchar(255)		      PRI		
PtFirstName		Varchar(255)	yes			null	
PtLastname		Varchar(255)	yes			null		
DateOfBirth		datetime	yes			null
Street			Varchar(255)	yes			null
State			Varchar(255)	yes			null
Zip			Varchar(255)	yes			null
Gender			Varchar(255)	yes			null
DateOfService		datetime	yes			null
Loc			Varchar(255)	yes			null		
Room			Varchar(255)	yes			null
Bed			Varchar(255)	yes			null
Attending		Varchar(255)	yes			null
AdmitDateTime		datetime	yes			null
ChiefC			Varchar(255)	yes			null
			Varchar(255)	yes			null



go to:      

MSDE     same info as mysql

and 

file    c:\adtliveccmc

Thank you very much

---

Request for Question Clarification by mathtalk-ga on 17 Aug 2005 13:45 PDT

Thanks, Amy.  Comparing these field definitions with the earlier ones,
I notice that the last four column names were:

Attending1
AdmitDateTime
ChiefC
Age

So, I'm going to assume that the last field here has the name "Age",
even though it is declared in a somewhat unlikely manner as
varchar(255).

The notion of keeping the table in MSDE "synchronized" with the
records in the MySQL table is clear.  However it is less clear what is
expected regarding the text file format of "c:\adtliveccmc".  Is this
simply a logfile to which the new records are appended as they are
received?  Or did you have in mind to reinvent the mechanics of a
database table within the confines of this text file, ie. unique key,
indexing, SELECT/INSERT/UPDATE ?

regards, mathtalk-ga

---

Clarification of Question by amy123456-ga on 17 Aug 2005 17:21 PDT

Hi.

The text flie is simply a logfile to which the new records are
appended as they are received.

Question.  If in the future i need to add new data elements to the
database because the interface is sending more data, will it be easy
to add or modify
the process?

Thank you

---

Clarification of Question by amy123456-ga on 24 Aug 2005 04:25 PDT

Good morning math talk. Do you have a time line for this project-  Please


Thank you very much

---

Clarification of Question by amy123456-ga on 29 Aug 2005 03:47 PDT

Good morning Math Talk-- Did you forget me?--- I need your help- Please

I'm reluctant to link to content that requires registration, even free
registration as in this case, but this article does seem quite to the
point:

[Worst Practice - Triggering External Events]
(by Steve Jones, 02/23/2004)
http://www.sqlservercentral.com/columnists/sjones/worstpracticetriggeringexternalevents.asp

"This article continues the series and looks at an item I see
mentioned in our discussion forum quite often, triggering external
events. From a trigger."


regards, mathtalk-ga

It goes against the grain in the database world to have the same data
in more than one place.

Couldn't your second application make use of the data where it is?
Then you have none of the risks of misaligned data that can come from
duplicate tables.

I think I would narrow the principle stated by rassler2-ga to this:
"owning" the same data in more than one place is a bad thing.

It often happens, of course, that data needs to be copied from one
place to another in application processing.  If it becomes unclear
which copy is the "master", then that is bad because it tends to
undermine data consistency.

regards, mathtalk-ga

Hi, Amy123456-ga:

I know you must be anxious to begin making progress on this.  I'm
doing a kind of brain dump below of my design suggestions for your
project.  It's not finished but it should help you to have this much
to read:

The general topic of your Question is "database replication".  Often a
vendor such as Microsoft will provide a mechanism for maintaining a 
replicated copy of all or part of a database in a "remote" place.  For
example:

[MS SQL Server 2000: Replication Overview]
http://www.microsoft.com/sql/evaluation/features/replication.mspx

[How to use replication with SQL Server 2000 Desktop Engine (MSDE 2000)]
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q324992

[MySQL Reference Manual/Chapter 6: Replication in MySQL]
http://dev.mysql.com/doc/mysql/en/replication.html

There are many variations on this theme, as the above articles suggest.

Of course you have a specific application in mind, so I'll not dwell on
the general topic.

For various reasons, esp. a limited amount of replication (1 table) and
the database heterogenieity (different DBMS engines), what I propose is
an ad hoc implementation.

Here again is the high level outline:

1. Add a timestamp column to the existing table in MySQL, and create a
   similar table in the MSDE database.

2. Write a .Net application which forwards changes (inserts & updates)
   from the MySQL database to the MSDE database and logs the activity
   to a text file.
   
   
Step 1.
=======

The first step is relatively simple, but you'll probably want to set up
test installations of MySQL and MSDE on your own development machine,
esp. for the sake of testing your work on the second step.  Frequently
much database development is done with test platforms, to avoid impacts
on production systems.

I've already mentioned once before that the choice of varchar(255) as
a data type for the Age field seems pretty dubious, as do a number of
the other data types given in your DDL for the source table.  But the
design decisions made for that table are presumably not in the scope
of this project, and I'll refrain from further remarks of that nature.

Connect to the MySQL database in question, and assuming that a backup
copy of the production database has been loaded to the test platform,
you could add the new timestamp column with a command like this:

mysql> ALTER table patient ADD Changed TIMESTAMP;

Alternatively if the change is first made in production, you could then
load a backup of the database to your test platform and pick up the new
column that way.  Of course this change will need to be evaluated with
whomever is responsible for the processes that update and use the table
as it exists now.  Commands that INSERT to or SELECT from the table by
specific fields will continue to work, but SELECT's that don't detail a
list of specific field names will begin to return the extra column (and
this could cause problems).

With older versions of MySQLManager one needed to quit and restart to
see changes made with the ALTER command.  I suspect this has been fixed
since then, but it's something to keep an eye out for.

Here's a command to make the "shadow" table in MSDE, e.g. using Query
Analyzer to connect to the database.

CREATE table patient
(
  PatientIdentifier       varchar(255)    Null,
  PatientAccount          varchar(255)    PRIMARY KEY CLUSTERED,
  PtFirstName             varchar(255)    Null,
  PtLastname              varchar(255)    Null,
  DateOfBirth             datetime        Null,
  Street                  varchar(255)    Null,
  State                   varchar(255)    Null,
  Zip                     varchar(255)    Null,
  Gender                  varchar(255)    Null,
  DateOfService           datetime        Null,
  Loc                     varchar(255)    Null,
  Room                    varchar(255)    Null,
  Bed                     varchar(255)    Null,
  Attending               varchar(255)    Null,
  AdmitDateTime           datetime        Null,
  ChiefC                  varchar(255)    Null,
  Age                     varchar(255)    Null,
  Changed                 datetime        Null
)

Note that MS SQL Server doesn't expect a command termination character
such as the semicolon used in MySQL.

The one critical difference in table schemas is that the column Changed
is data type "timestamp" in MySQL but "datatime" in MSDE.

I've required above the new column Changed to allow Null in both the
source and target databases.  This might be useful if you decided to
move ALL records from the source database table to the target table in
an initialization step.  We have no choice about allowing Nulls in the
source database table since at the time we add the column, all values
for that field will start out as Null (for pre-existing rows).  As new
rows are inserted or old rows updated, the value of the column Changed
will be automatically set to the current time as known to the database
server, unless INSERT or UPDATE statements actually specify a value
other than Null.

However if you don't plan to move all the records over (I don't know
how many of them there are, and possibly you do not care to copy any
that are "older") and you could just manage with moving rows that are
changed after the Changed column is added, then the field Changed on
the MSDE side will always have that field populated, and in that case
we could make it a nonnull value there.

If it _is_ desired to make an initial conversion of the entire patient
table, a good resource for data migration from MySQL to SQL Server 2000
(and hence the current version of MSDE) is this Microsoft white paper:

[Migrating MySQL to Microsoft SQL Server 2000]
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx

"The most direct option to migrate data from MySQL to Microsoft SQL
Server is to install the myODBC support and create a DTS package to
import and create database from MySQL to Microsoft SQL Server."

Step 2.
=======

I'm going to assume you'll want the replication application, which I'll
refer to as "RepApp" hereafter, written in C# since other code that you
maintain is already in that language.  However the details are easily
adapted to a program written say in VB.Net, if that is preferred.

We will naturally adopt the database access technology of ADO.Net to
communicate with both databases, and we will store a certain amount of
configuration information needed by RepApp in a small XML file, like we
used before in the OrderFulfillment application.

For this purpose you will need ADO.Net "drivers" of some kind for both
the MySQL database and the MSDE platform.

You shouldn't have to do much for the MSDE platform, as the required
components for ADO.Net were probably included with Visual Studio setup
and/or with the development/client tools for MSDE.  Under Project >
Add Reference..., look on the .Net tab for the "adodb" component, and
add this to your C# project.

On the MySQL side of things you should download one or both of the 
standard database "connectors" for ODBC and .Net here:

[MySQL Connectors Downloads]
http://dev.mysql.com/downloads/connector/

The ODBC connector would in particular be useful to migrate data from
the MySQL table to the MSDE table (in an initialization phase) since
it is what Microsoft's DTS (Data Transformation Services) relies on to
extract rows from MySQL prior to inserting them into SQL Server (MSDE
in this instance).

The ODBC connector can be used from ADO.Net through a "wrapper" that
uses the underlying COM functionality in a lowest common denominator
approach to database connectivity.  However I'll assume that the .Net
connector is installed and usable by RepApp.

The ADO.Net object (or class) model is probably new to you, so we'll
take a quick overview of that in a minute.  We should discuss database
authentication, focusing on the thorny issue of how database passwords
can be maintained securely for an ad hoc application like RepApp.

Until next time...

regards, mathtalk-ga

hi math talk.  You are still working on this project, Right?

Please help me with this.

Thank you

Hi, Amy:

Any questions about the steps outlined so far?  E.g. any problems
obtaining the MySQL ADO.Net "managed provider"?

I'm interested to know how many records and how far back in time you plan to go.

Of course, since there was no "timestamp" on the Patient table
previously, this is perhaps an all-or-nothing kind of issue.  However
I'm wondering what the volume of records involved is.

For a very high volume of records, I'd be more concerned about
optimizing the throughput of "RepApp".  Otherwise I'd focus on keeping
the design as simple as possible to minimize maintenance confusion.


regards, mathtalk-ga

1-Yes i did obtain the MySQL ADO.Net.
2-as far as going back! we are not. We are goping to start fresh.
3-It will be a very large vol. ( all admissions to the hospital)

Thank you once again for sticking with the project.

After conferring with a friend who does a good bit more work with
ADO.Net than I do, we came up with this approach for RepApp's
processing cycle:

 (1) Create a Connection object to the MySQL database, using database
name, user id, and password stored in (say) the RefAppConfig class
instance.

 (2) Create a Command object based on that which selects all rows from
the Patient table with timestamp Changed >= @lastChanged, a value of
type datetime which is maintained in the RefAppConfig class also,
representing the last known time of synchronization.

 (3) Create a DataReader object from the Command object to loop
through the results of the select statement.

 (4) Create Connection and Command objects for the MSDE database which
parameterize a stored procedure that will either UPDATE or INSERT a
single row for the "target" copy of the Patient table.

 (5) Loop through the results provided by the DataReader object,
sending the field values as parameters one at a time to the target
MSDE database table.  Keep track of the maximum timestamp Changed
found in this set of results, and after all rows have been processed,
revise that @lastChanged value in the RepAppConfig class.

 (6) Persist the RepAppConfig class instance and sleep for some
interval of time (also set in the configuration) before repeating the
cycle.


regards, mathtalk-ga

Sounds deep, but if it works that is all that matters.

Thank you once again mathtalk

Hi Math Talk.  How is it going?

Good morning Math Talk.

I am a little under the gun to finish this projects--


Please help-  Thank you very much

Okay, I hear you!  I'll give it some cycles this week.

-- mathtalk-ga

Thank you

Hi, Amy:

As I recall, you state above the version of MySQL you are using is
"ver 5.x".  I'm only aware of beta versions of MySQL 5.0.  Are you
certain that this is your installed database software for hospital
patient records?  I am proceeding on the assumption that it is.

regards, mathtalk-ga

Yes, Because it has triggers. Maybe in the future we might want to use triggres.


PS-If you think we should not please advise.

Thank you very much once again

Hi, Amy:

I understood the installation and use of the (existing) MySQL database
as a given for this project, not a element of design.  Administrative
responsibility for backups and other maintenance, together with policy
compliance around the patient database (HIPAA), while presumably not
on your shoulders as a developer, require you to be aware of those
issues and the implications for this project.

I referred to the current MySQL 5.0 as "beta", but it actually seems
to have become a "release candidate" in the past week:

[MySQL 5.0 Release Candidate Available]
http://www.mysql.com/news-and-events/news/article_959.html



regards, mathtalk-ga

Good Morning Math Talk.   please don't give up!
We need your help

Thank you

Hang in there, Amy.  I've set up the MySQL and SQL Server databases at
my end, and I'm working on developing the ADO.Net code as outlined
above.

Your patience is appreciated!

-- mathtalk-ga

Hi Math Talk. Are you still with me?

Thank you