MySQL Forums
Forum List  »  Microsoft SQL Server

Re: Calling stored procedures ... Dynamic SQL
Posted by: Perry Way
Date: February 11, 2006 01:27PM

Hi Roland,

Why "all over the place" ? Well.. okay.. it's like this. I have created a systematic approach to building modular web applications for a web server I've created. I am calling the entire interfacing mechanism from the web module to the database and back again from the database to the web module as formatted HTML "chuncks" as my "webform" system.

The webform system utilizes stored procedures entirely. Of course I built my webform system after having begun my development picking SQL Server <g>. But now I have a client for a very large DB application for a verticle market who is balking at the price of SQL Server, so now I have to find ways to possibly make this work in MySQL.

Given the main DB mechanism is entirely through stored procedures, I have ummm... offloaded a lot of logic at the data level to the stored procedure. So.. say, from my web application I have a HTML form with form fields for a search. The user can type in a search value or use a drop down selection or a checkbox, etc. And if they don't supply a value the webform system doesn't care, but it does transform empty form field values (or query field values depending on POST vs. GET) as NULLs. So the assignments take place from the webform system and inside the stored procedures I am building dynamic SQL statements on the basis of parameters being NULL or NOT NULL.

You may scratch your head as to why did I build a web server, let alone this webform system and so much else (this is 2 years in the making, btw) when I could say.. use IIS and something cheesy like ASP.net or something more professional like ISAPI, or say maybe even use Apache and php or Apache modules, like so many other people do, but I have very specific reasons why I went this far with my development. In short, I have a far superior product for building web applications. It is lightening fast, has a tiny footprint, is easy and fast to develop the web module itself, is VERY secure (no hacking capabilities as I can't say the same thing about IIS, or .net, or ASP or anything else Micro$oft for that matter), and here's the only drawback (seemingly a drawback only to a few). Data logic is kept within the Database. Hmmmm... seems to me that's what a RDBMS is there for, isn't it? :) Of course if I wanted to use a Query and do client side data manipulation manually, they I can do so but then the whole sweetness of the webform system would be missing in that equation, such as everything that controls the webform system is data driven! This includes input parameters, output parameters, display grids, form fields preparation (including drop down selections), output fields, order, appearance, etc. etc. So the bottom line is I have developed a system that allows for an application to morph over time so long as the total number of entities and the basis for how one deals with each entity is undisturbed, I can modify the application entirely by DATA. Add a field, subtract one, alter the appearance. etc. etc.

So, given this sort of structure, it would greatly benefit me to be able to construct dynamic SQL for most of my "search forms". Most other places don't need dynamic SQL, such as display forms for detailed records (or input forms for adding records). This includes grids of child records in master-detail relationships. They don't need to be dynamic.

Perry

p.s. I think I found a way to do what I need done using the PREPARED approach. I had made a syntax boo-boo and realized my error a few minutes ago.

Options: ReplyQuote


Subject
Written By
Posted
Re: Calling stored procedures ... Dynamic SQL
February 11, 2006 01:27PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.