Hi,

I wonder is there any way to call an application which resides on the database server computer from stored procedure or trigger? Is the language of stored procedures and triggers only restricted by SQL statements and some arithmetic operations?

Thank you,

Jacob Nikom

It is interesting that Oracle and MS SQL Server already have this functionality. In Oracle it is Java stored procedure, in MS SQL Server it is called "extended stored procedure" where you can call C++ routine out of your stored procedure. It is difficult overestimate the convenience of being able to do some processing triggerted by the record insertion event.

In this case the database could be used not only for "pulling" data out, but also being able to "push"
data to some component.

Hi Jacob.

There currently isn't a build-in mechanism. There is talk about providing ability to use other languages when writing routines but nothing in progress (to my knowledge).

I think your best bet is to look into writing a User Defined Function (UDF). With a UDF, MySQL allows you to register an external program and then make calls to it like you would a native MySQL function. The UDF will be able to make calls to the system for whatever you need.

I'll be there on Tuesday if you want to get into more specifics of how to do this.

Mike

This question comes up frequently. See my blog about the issues with doing this:
http://karwin.blogspo...

Bill Karwin
(using Sheeri's guest account)

UDF looks like a reasonable solution to me similar to what Oracle and SQL
Server have. The only problem with it that it is not standard. People have
to implement it from scratch over and over with different details and lack
of documentation. May be some people already implemented it? Does it mean
that MySQL simply did not include their solution in the code base?

Without this functionality the relation between database and application lacks
a reciprocity. The database is notified when the application wants to change
the data. However, when the data in the database changed, it is difficult to
notify the application about this event.

I think that nonequivalence comes from the past when the data sharing was a
rare situation. The data changes were always initiated by application and it
always knew about those changes. Now the situation is different. When the data
are shared between multiple remote applications you have to notify other
interested parties about those changes.

Currently databases are mostly used as "pull" components. If they had standard
"push" functionality they could compete with messaging systems with the advantages
of automatic data persistence and powerful query language.

Bill Karwin criticized the usage of triggers for application notification. He said
that database could send the notification even when the tansaction failed. But
it is OK; it is "push", not "pull" paradigm, so the rules are different.

I think, the database should ALWAYS notify the application BEFORE the data
changes. It is the responsibility of application to verify that the change actually took place.
Applications know the data, so they could do it. It is still better than to poll the table
over and over.

I think that nonequivalence comes from the past when the data sharing was a
rare situation. The data changes were always initiated by application and it
always knew about those changes. Now the situation is different. When the data
are shared between multiple remote applications you have to notify other
interested parties about those changes.

Currently databases are mostly used as "pull" components. If they had standard
"push" functionality they could compete with messaging systems with the advantages
of automatic data persistence and powerful query language.

Well, and that's the problem -- the paradigm *has* changed. MySQL is fast and reliable because it does NOT put things like messaging into their database, which Oracle and SQL Server do. A database is not a messaging system, it's a database.

What effect would notification that there have been changes have on MVCC? I do wish there was a "pull" way to check if the data has changed.

The paradigm change of the application managing the schema causes this. I do not believe messaging is the correct way to handle this problem.

Consider the parallel to source code version control. Much like MVCC, you check out code, change it, and commit the code. Unlike many source code version control systems, though, MVCC ("data version control") does not have the equivalent of an "update" command, except for doing another pull from the database. It would be great if there was an easy way to do a "diff" of what's in the database versus what the application is changing, but that seems like it would be a programmatic thing (function or method), not a database thing.

And consider the database overhead and bandwidth....instead of just running queries, MySQL would have to somehow keep track of which thread has what data, and then notify every single thread that has that data, that it's changed. The applications will have to be written to keep threads open longer, which will consume lots of resources. That's lots more overhead for the database, and much more bandwidth, because there may be instances of the application that are using data that they do not care if it changed....so the messaging system would be wasting bandwidth, sending messages to instances that do not care. Although that could be mitigated by the application keeping a thread open when it cares about whether or not the data has changed.

Then again, I'm not fond of managed schema in the application...or at least, when the developers write that code. Seems to me it should be the DBA writing that code. It's *very* useful for data consistency and integrity, which is a function of the DBA, not a developer.

Jacob,

Just stumbled unto this over on the MySQL Forge. It's a UDF that provides a messaging API for MySQL. I have no idea the extent of messaging you need to do, but it seems like a good place to start.

http://messagequeue.l...

It's open source.

Mike

Or maybe it's not messaging you need at all, I just got that into my head reading Sheeri's post.

It is really nice that we have such a great discussion in our message board.
It sounds like what should and what should not be in a database is interesting
problem for many of us. Therefore I decided to add more thoughts on this matter.

First, I agree with Sheeri that a database should not implement every possible
functionality. This "feature creep" is one of the main reasons for a poor quality
of many software products, not only databases.

However, in my reply I did not asked for any new features. UDFs and triggers
already exist in MySQL. It looks like they provide enough functionality to run
an arbitrary external procedure synchronously with the data changes. In addition
I asked how to do it in more standardized way to avoid "reinventing the wheel".
The easiest way could be to run some predefined shell script from a predefined
directory. The script decouples the database code from the external routine.

I did not ask specifically for sending a message from the DBMS. I simply used
this task as an example of useful functionality that databases could implemented
more efficiently than specialized messaging systems (MS). One of the important
MS properties is its ability not only to send messages, but also to keep track
of them and analyze their history. By adding message sending mechanism to data
management a database could be an excellent MS.

Second, Sheeri asked more generic question - what functionality should be in
a database? To answer this one you have to answer the more fundamental one -
what is a database? Sheeri said that "a database is not a messaging system, it is
a database". For me this answer was not helpful enough to formulate the criteria.

I think the definition of a database is very simple - it is DBMS, namely
system for managing data. It means that if some functionality is really
helpful for data management there is nothing wrong to put it in a database.

Why database does not implement a feature which tells the application
that the data created by this application have changed? I think it is
very relevant to data management. All banks send you the data about your
accounts. When you buy something from Amazon they send you emails telling
that you have an order and the order have been processed, shipped, etc.
When you use Netflix they send you email that you CD have been send or
received. All of these messages essentially tell you that the state of the
database has changed.

I really like the Matthew Feinberg's message in Sheeri's archive (see
http://sheeri.com/arc... )

He says: "In the era of Web 2.0, web publishing is no longer the paradigm.
The new web is about collaboration and event driven applications where the
events may originate either within the client (e.g. browser) or on the server.
We both agree server originating events may propagate events to the client
either by pushing notifications or in response to client polling."

He presented it from application view point which emphasizes usage. If you
look at it more generically, namely from symmetry point you can ask a question:
"If the application is allowed to bother the database with data changes, why the
database is not allowed to bother the application about data changes?" I think it is
fair question.

Sheeri also touched very interesting question about database schema that is
managed by application. However, my message is already long enough.

I have a similar issue, I would like to call a Java broadcast app located on a remote server whenever there is a successful transcation update in the DB.

Prashanth