MySQL Forums
Forum List  »  Microsoft SQL Server

Re: Calling stored procedures ... Dynamic SQL
Posted by: Roland Bouman
Date: February 11, 2006 02:47PM

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?

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

> 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-dynamic.html), but they indicate that you cannot use it to transfer a resultset directly.

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.

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

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

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

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

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

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

kind regards roland

Options: ReplyQuote


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


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.