Re: sql/plsql query question

Daniel Morgan <damorgan_at_x.washington.edu> wrote in news:1068156040.546706_at_yasure:

> It is better because it is designed to do this. Your assumption about
> what may or may
> not be run and under what conditions may be flawed. NDS solves the
> problem.
>
> Why the apparent resistance to the suggestion everyone has made?
>

First, "everyone" is only two people in this case:)

Mostly, it is because I try to avoid dynamic SQL at all costs.

My main reason being that with DSQL you end up coding schema object names (tables, columns, functions etc..) as strings, rather than as symbols that are visible and can be checked at compile time.

I'm sure this isn't news to anybody around here. Maybe you've all learned to live with it, I haven't yet.

In fact, in previous systems that I've built, I've gone to some lengths to provide compile-time symbolic names for these sorts of things, and use them when I do resort to dynamic SQL.
For example, my C++/API (OCI based) looks something like this:

       TableClass table;
       Placeholder<string> ph("abc");
       stm.Columns << table_ref.col1 << table_ref.col2;
       stm.From << table_ref;
       stm.Where << table_ref.col1.Equals(ph);
       while (stm.Fetch())
           cout << table_ref.col1.ColumnName << "=" << table_ref.col1();

  Where TableClass is a *generated* class that contains members for   each column in the table; Placeholder is a generic placeholder   class that knows how to be bound to the statement; And stm is a   Statement class that generates/prepares/executes/binds/declares   everything when required.

  The point is - although my API library does indeed compose dynamic   SQL for this statement, there are only compile time symbols used to   actually build the statement. If a column name changes, code that   uses that column will fail at compile time - not at run time.

  I mention this only by way of illustrating just how far I will   go to solve what I consider to be a serious serious problem with   using dynamic SQL in a serious application.

Also, isn't there at least some performance hit in composing/compiling/preparing the NDS statement each time? I wouldn't let that be the deciding factor - it is a secondary consideration only.

Thanks   Received on Thu Nov 06 2003 - 16:47:46 CST