MySQL Forums
Forum List  »  Microsoft SQL Server

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

Roland Bouman wrote:
> Perry, thanks for your elaborate reply!
>
> Perry Way wrote:
> > 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
>
> You wrote your own http server?

Yes. It is called Hermes.


>
> > 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.
>
> This sounds lot like oracle's mod_plsql. This is
> an apache http server plugin that gets to handle
> certain http requests. It does so by interpreting
> the request uri as a stored procedure call. Inside
> the procedure you can use a special procedure call
> that results in text in the http response body
> (which is then returned to the http server and
> then to the client)

My system (Hermes Web Modules or .hwm) goes a bit further though insofar as how it is interfaced. The Webform system can also provide the stored procedures' input parameters from Hermes Sessions which are persistent from hit to hit and basically interfaced to the web client by way of a NONCE value in a cookie. Also there are other ways to interface the params, such as a portion of a path. Example:

'/customer/view/12345' in this path, '12345' could be sent as an input parameter to view customer id 12345, through a call to GetPathPortion(pathIndex). In the Webform data in the db, the index of the parameter can be specified, or you could rely on automation through a '*' value in the path by storing the path as '/customer/view/*'. This allows for a lot of flexibility.

>
> > 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.
>
> you could check out postgres (www.postgresql.org).
> I know they do support dynamic sql
> (http://www.postgresql.org/docs/8.0/static/ecpg-dy
> namic.html), but they indicate that you cannot use
> it to transfer a resultset directly.

Well, I found through experimentation MySQL can do what I need to do, but I'm not sure what you mean by 'transfer'. What I want is for the execution of the stored procedure to return a dataset to the client.


> What you can do as a workaround, is to have your
> dynamic sql fill up a temporary table. Then just
> use the temporary table as you would use any
> table.

Ahh yes, well I have those needs too. I see MySQL has this capability but I've not yet experimented with this to know if there are any problems associated to global temporary tables versus simple temporary tables as well as global/temporary applies to cursors. These two issues pop up in SQL Server, but there are ways to do everything there.



> May I ask, can you post a snippet of the typical
> way you capture and then use the resultset after
> using dynamic sql in ms sql server? again, just
> curious

Sure. I can do that. But probably best (since this is MySQL forum) if I do this via email privately. There is actually quite a bit of flexibility with dynamic SQL in SQL Server. You can even use dynamic SQL for making temporary tables and cursors. Way cool stuff.


>
> > 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
>
> Sounds like a plan
>
> > 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
>
> I'm not. Im scratchin my head as to why you need
> dynamic sql to achieve this. The make up of the
> tables and the queries you build on them is known
> before you are acrtually running the app, right?

Yes, but the WHERE clause and the ORDER BY clause are both dynamic and based upon user input, or in the case of a grid, by clicking on a column header to toggle sorting. Perhaps you'd like to see a demonstration online on one of my applications, which if you give me your email address I will play followup on that.


> So, I would just use the conceptual data model (of
> which i am sure yoiu are maintaining the metadata
> somewhere in your design framework) to generate
> all the procedures I need. Or is there some
> catch?

Not sure where you were going with that statement. If you're meaning do I know the amount of fields or their names or whatnot, I'm not getting that nitty gritty with the Webform system. I already "know" that. The understanding is the database developer knows that which he already wants to retrieve. If anything should change such as the number of fields, then a simple modification of the SELECT and FROM portions of the query would be altered in the appropriate stored procedure(s).


>
> > 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
>
> No, I think I understand how this would wokr out.
> Sounds interesting
>
> > 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
>
> Well, theres always a lot of logic invlolved in
> apps that have to do with the business process.
> Which is not data-oriented per se. So, in my
> opinion, that could equally well reside in another
> layer, as long as there remains a tight
> correspondence with the data model it acts upon.
> On the other hand, I have built apps that had
> nearly all of the business logic encoded in
> database stored procs.

Okay. Umm.. I can do both, actually. It's a matter of choice to the programmer, but in most cases the business logic as it pertains to actual data is (by me so far) contained entirely in the stored procedures. Other kinds of logic is application level, such as ummm... say.. user rights to perform certain activities. Controlling access to a web path has been done at the application level, using application procedures/functions that identify if action can be performed by a user right level, however even here, the users, passwords and their rights are stored in the database.


> (BTW, one of the reasons why people use a separate
> layer for business logic is to be able to abstract
> from the underlying rdbms...something you indeed
> seem to have a problem with right now)

I can't imagine yet, a situation where I would need a layer between the web module and the Webform/Database layer. Anything I need to abstract like your alluding here can be done in the web module itself. I can place a query object or a stored procedure object directly in the web module itself and call it manually (which I am doing in a Calendar of Events project where I build an HTML Calendar output). The connection object from the Webform is auto-magically assigned in those cases.


> > 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,
>
> I have done this sort of thing too. I still dont
> see why it is absoluely necessary to use dynamic
> sql for that.

Yeah, I started out with my Webform system by using it internally in ISAPI .dll's. My Webform system sort of predates Hermes, though I have vastly improved and added to it since making it native to Hermes. It is actually a fairly sweet solution. Wish I could show you in person how simple it is to write an application this way. Most of my "action paths" (the portion of the full URI path generally following the module name) require about 10 or 20 lines of code, and generally they are calls to "drive" the Webform system, such as.... ExecProc(procName) or CreateGrid(procName) or GetOptionsList(procName). I have experimented off and on last year with making a fully automated Webform system but there are complexities associated to sequence of events that really require one to "drive" it, and I have abandoned that idea for the most part. There is very little application code required, as it stands right now.


> > 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.
>
>
> So, youve got a solution for transferring the
> dataset then? COol, please let me know. And if
> youve got a link with some info on your webserver,
> please post it. Im genuinly interested.

'transfer' ? Not sure again what you mean. I have a way to return the dataset to the client though. On the bottom of this post is a MySQL example:

Regarding Hermes, okay, umm... I'd be rather embarresed if half the world showed up to look at my website the way it stands today because I am a one-man-show and not much time to make my own website (hahaha). I have some clients, and their needs go first right now, but in a few months I have plans to market Hermes, but for specific useages. There are other things to Hermes which I haven't listed here, which makes it quite a powerful tool and I don't want it to be used by just anyone. I'm not really planning on doing a general public release because I don't want Hermes to be used for certain things (like hate speach, sex sites, anything to do with politics, among other things) so I'm exploring how to protect Hermes' useage by way of licensing verbiage and some "big-brother" technology, but that's further down the line. If you list your email address, I'll write to you offline and give you a link to a white report.


>
> kind regards roland

Thanks, its been a pleasure reading your postings in this forum. You are very helpful and most kind.

Perry



MySQL example for dynamic SQL stored procedure:

The following example will build a SQL statement with a dynamic WHERE clause. The WHERE clause construct needs to reflect input parameters being passed both when they have a value and when they are NULL. When NULL, the WHERE clause needs to not include that parameter, so it is necessary to do things dynamically.

First, the table definition for this example:

CREATE TABLE `testDynamic` (
`SystemID` int(10) unsigned NOT NULL auto_increment,
`SomeName` varchar(120) NOT NULL,
PRIMARY KEY (`SystemID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Put some records in table if you like to test this... And now the stored procedure with dynamic SQL:


DELIMITER $$;

DROP PROCEDURE IF EXISTS `TestDynamicSQL`$$

CREATE PROCEDURE `TestDynamicSQL` (
_FromID INT,
_ToID INT)
BEGIN

DECLARE _statement VARCHAR(500);
SET _statement = 'SELECT * FROM testDynamic ';

IF (_FromID IS NOT NULL) OR (_ToID IS NOT NULL) THEN
SET _statement = CONCAT(_statement, 'WHERE ');
IF (_FromID IS NOT NULL) THEN
SET _statement = CONCAT(_statement, '(SystemID >= ', _FromID, ') ');
END IF;
IF (_ToID IS NOT NULL) THEN
IF (_FromID IS NOT NULL) THEN
SET _statement = CONCAT(_statement, 'AND ');
END IF;
SET _statement = CONCAT(_statement, '(SystemID <= ', _ToID, ') ');
END IF;
END IF;

/* un-comment the line below if you want to also view the
SQL statement that was just constructed */
#SELECT _statement;

SET @statement = _statement;
PREPARE dynquery FROM @statement;
EXECUTE dynquery;
DEALLOCATE PREPARE dynquery;



END$$

DELIMITER ;$$


Now, executing the procedure gives you options. You can pass NULL to parameters or pass values. If you pass NULLs, then you get all records. If you pass values then you get a subset of values.

Examples of valid useage:

call TestDynamicSQL(5, 20);

call TestDynamicSQL(22, NULL);

call TestDynamicSQL(NULL, 10);

call TestDynamicSQL(NULL, NULL);

Example of INVALID useage:

call TestDynamicSQL(20, 5); #invalid because value range overlaps the wrong way, but does not cause execution error, just no rows returned.

Options: ReplyQuote


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


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.