Table Dml Stored Procedure Generation

[asptrader]

I would love to use Mysql and SQLyog rather than Oracle except…

I have used Oracle Designer to generate stored procedures that provide an api for each table for DML operations in an RDBMS. It works great.

Does SQLyog do the same kind of stored procedure generation for Mysql? If not now, how soon might this functionality be provided?

Thanks.

[Rohit]

Not currently! But terrific idea.

Would you be interested in giving us more examples/specs?

How many APIs per table? INSERT, UPDATE, DELETE?

All columns? Selected Columns?

[asptrader]

Rohit wrote on Sep 19 2006, 05:16 AM:

Not currently! But terrific idea.

Would you be interested in giving us more examples/specs?

How many APIs per table? INSERT, UPDATE, DELETE?

All columns? Selected Columns?

I'm happy to hear you think this is a good idea.

I am a very senior Oracle person. I have found that the default Stored Procedures and Triggers generated out of Oracle Designer for Oracle tables are extremely useful. The generated programs provide a standard code API for all DML statements for tables in a database.

I am in the process of working on a relatively large web based application that I don't want to write the standard and detailed DML stored procedures and triggers for. So, my choice of database product will be based on what product I can find that will do this for me.

A key benefit of providing this kind of capability is that your users would only need to correctly address the API for a given table without needing to manage SQL and RDBMS “internals”. As you know, the manual writing of this essential and required code consumes an ENORMOUS amount of time if it is done correctly.

Regarding your specific questions… The Stored Procedure for each table needs to manage DML for all the columns in the table. Oracle Designer generates a Stored Procedures and Triggers that ONLY allow changes through the Stored Procedure itself. Oracle Designer generates a single Oracle “Package” that contains “Package Procedures” for each of the DML operations. I'm not familiar enough with Mysql to know how best to do it in Mysql.

Last time I checked, there were a couple of products that provide examples of this essential DML Stored Procedure/Triggers generation capability. The two I remember were Oracle Designer and some other product that Steven Feuerstein put together (I can't recall what his product was called).

My recommendation would be to follow either of those two models/templates. You can't go wrong following either of them.

So….. when can I have it?

😀

[Author]

Posts