Re: Calling stored procedures ... Dynamic SQL

[Roland Bouman]

Hi Perry,

I'll just to the reply here, because it's easier now i've got your message text in place.

If you want to reply on the forum, fine. You can also e-mail me at

[e-mail]

(TIA)

Perry Way wrote:
> 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.

mm, never heard of NONCE before.

> 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

Ok, so the GetPathProtion method/function is called by the WebForm, an the call is handeld by the webserver right? So, webserver forms some kind of runtime environment for the webforms? You sorta build your owns 'scripting language' in wich you program the webforms and those run inside the webserver?

> 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.

Ah. Well, you see, orphan SELECTs inside MySQL Procedures are returned to the calling client environment. This even goes for procedures that are called from inside procedures. So, if I have a procA that calls procB, and procB issues a SELECT, the resultset is returned to the client environment that calls procA. procA itself cannot manipulate or otherwise process the resultset.

Most of my experience is from the Oracle database. In Oracle, there's no such thing as a dangling SELECT inside procedures. You've got SELECT INTO for single row select expressions and you've got some variant of a CURSOR. Also, EXECUTE applied to a SELECT statement yields a cursor.

In MSSQL 2000 you've got the ability to use a dynamic query string as a table in the from list using a ROWSET function (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_68hf.asp). You can even use a user-defined function in a from list (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_68hf.asp). I never used this, but I thought you were looking for this type of functionality

In both the Oracle and the MS SQL case, there resultset (or a cursor linked to it) can be received and handled fully within the Stored procedure context - thats what I mean when saying it is 'transferred' or 'captured'.

> 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.

In MySQL, a temporary table is bound to the session. It is automatically dropped when the session ends. (yep, structure and data). Sessions can only access temporary tables created in the same session - not those of other sessions (see: http://dev.mysql.com/doc/refman/5.0/en/create-table.html, and http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html). So this is alot like a normal temporary table in sql server.

If you need to have other session see the data in your 'temp' table, you can create a normal table using the ENGINE=MEMORY option. This will create a normal table, except that all it's data is stored in memory. In this case, the structure will be persistent, but the data is not: It will be discarded when the server process stops. So, there's nothin like a global temporary table in MySQL


> this is MySQL forum) if I do this via email
> privately. There is actually quite a bit of

on the top of this reply


> 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

Yes, I see now. Actually, I think I jumped the gun: I use server-side dynamic sql in my apps too for exactly those purposes

> 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).

Ok, I think I understand now.

>
> 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

I'm assuming there's always the 'hard' authorization on the database level too?

>
> 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.

That's because you extended you webserver. In a traditional case, using a separate webserver, a scripting language or dynamic page language and a database server, you could try and handle almost all businesss logic using database procedures. However, you can ask yourself what tasks have to do with the business process and what tasks have to do with the data involved in the business process.

When the app is designed well, there will ususally be a quite clear separation between the components that perform data functionality ('storeCustomerData()' ) and the components that execute business functions ('shipOrder()'). (just 3-tier design - I am not touching on the front end presentation layer here). The business functions sit on top of, and are supported by the data functions. Of course, the business function could be implemented as stored procedures, physically linking the to the database. However, for reasons of portability and scaleability it could prove to be a good idea to run the business functions in a alltogether separate layer. Portability is served here because if you want to switch databases, you wont have to worry about the business logic implementation (provided teh interface between business functions and data fnctions remains the same). Scaleability is server here, because data functions and business functions could have very different resource usage on the server. The separatation allows you to tune the performance, hardware etc. for these layers separately .
>
> 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.

It all sounds very exciting, very interested in seeing it

(Thanks for the snippet - it totally reflects your earlier point regarding the dynamic WHERE)