Re: How do I verify a valid sql statement programatically ??
From: C Newby (can_at_democracydata.com)
Date: 04/21/04
- Next message: C#User: "select problem!!!"
- Previous message: Adam Machanic: "Re: Grouping Problem"
- In reply to: Liz: "Re: How do I verify a valid sql statement programatically ??"
- Next in thread: Liz: "Re: How do I verify a valid sql statement programatically ??"
- Reply: Liz: "Re: How do I verify a valid sql statement programatically ??"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 21 Apr 2004 12:35:29 -0400
I totally hear what you're saying (at least i think i am). However I think
our circumstances are different. Essentially I am trying to fix a design
flaw in a legacy application. In the legacy app, we have a central data grid
control that was written using an amalgamation of an ADO client side
recordset, ASP, and javascript. The grid allows arbitrary column sorting and
contains a context based pageing mechanism which is non-arbitraily tied to
the sorting of the grid.
So for example, if the grid is showing a list of 1000 people records, the
columns might be something like: Last Name, First Name, and SSN. Let's also
suppose that the list is sorted by Last Name. Our pageing list might then
contain 10 items ( representing pages of 100 records ) with visual
representations like: "Adams - Easton", "Edwards - Larson", "Lawdale -
Peterson" ... etc.
If I then change the sorting of the list to SSN, the pages might look like:
"000-00-0000 - 111-11-1111" ... etc.
So far so good. But the problem with the orginal design was that in order to
fullfill those requirements, the designer choose to store the recordset in
the ASP session to minimize generating the query from the DB with different
sort orders. The orginal problem was that this data grid was added many
months into the development of the app, and the requirement was that *all*
parts of the system that generated record lists would use the same list
control. Many places in our system generate dynamic queries based on various
search interfaces, and these components had already been written and tested.
And really, there was no good excuse, that I can think of, for putting the
recordset into the session, aside from making the list control's features
easier to implement.
And so i come in! Problems, as one might expect, have arisin as the
application has tried to scale. I am in the process of retrofitting a new
list control that doesn't use the ASP session. The adapter that I'm writing
needs to modify queries the way I previously illustrated because I would
otherwise have to oversee the modification and testing of something on the
order of 100 different components that generate dynamic queries, which for
our current business requirements isn't practical.
That was a mouthful. Anyway, I've determined that my present course is the
best way ... however, I am more than open to suggestion.
Thanks again for your attention.
"Liz" <liz@tiredofspam.com> wrote in message
news:eGd2PW7JEHA.2556@TK2MSFTNGP11.phx.gbl...
>
> "C Newby" <can@democracydata.com> wrote in message
> news:#AwYT06JEHA.204@TK2MSFTNGP10.phx.gbl...
> > Liz, thanks for your help. However, both of these methods require a trip
> to
> > the DB. Also, in strictest terms, these two methods *parse* the query,
as
> > opposed to simply checking syntax.
> > For example, suppose I want to check the syntax of the following:
> >
> > SELECT someCol FROM someTable WHERE someOtherCol = @someVar
> >
> > Again, strictly speaking, the syntax of this statement is correct. That
is
> > to say, it is gramatically correct. However it will not parse (using the
> > methods you and the others pointed out) because I omitted the
declaration
> of
> > @someVar.
>
> ok, true ... but such a query would not be sent to the DB as is; anyway,
> yes I suppose SQLDMO would indeed take a server trip to check the query
>
> > I cite this example only because it specifically relates to my current
> > problem! I'm essentially writing an adapter for an older application
from
> > which I am receiving queries that take on any and every shape
imagineable.
> > Some of these queries can generate 100K+ record datasets that will
> > eventually be bound to a web control similiar to a pageable ASP.NET
> > datagrid. So, one of the adapter's features is to identify output
SELECT
> > and ORDER BY columns before the query is actually executed so that it
can
> > dynamically create extended sorting orders based on hard-coded
conditions.
> > For example, if the primary sort column is LastName, I need to determine
> if
> > the return set is secondary sorted on FirstName. If it isn't, I need to
> > check the SELECT clause for a FirstName field, and if it exists, insert
it
> > as the secondary sort column of the ORDER BY clause. This isn't so bad
for
> > most queries, but quickly becomes complicated when you consider the
> breadth
> > of possibilities for a T-SQL statement, possiblities which I have been
> > fortunate enough to verify the existence of throught my legacy app. Fun
> > stuff right? :)
>
> it's not clear what you're doing here but it's sounding like, in a
fashion,
> you're interposing some sort of "pre-optimizer" between client and DB ?
...
> if so, why ? The SQL optimizer generally works quite well ... if you need
> to constrain the queries, why not do it on the front end at the client;
> which also is more "honest" .. as a user, I know that what I submit is
being
> executed rather than altered and then executed ... I don't know .. I
always
> have trouble with open-ended client interfaces which tie off to back ends
> which are incapable of meeting the demand
>
> > Anyway...I actually think i figured out how to do this the old fashion
way
> > ( ie, parseing the query myself) without having to get too deep in the
> weeds
> > of the T-SQL syntax.
>
> > Thank you again for your comments.
>
> >
> >
> >
> >
> >
> > "Liz" <liz@tiredofspam.com> wrote in message
> > news:uxRnY7zJEHA.3380@TK2MSFTNGP09.phx.gbl...
> > >
> > > "C Newby" <can@democracydata.com> wrote in message
> > > news:OSEngMyJEHA.228@TK2MSFTNGP10.phx.gbl...
> > >
> > > > Is there an object somewhere that i can use to programatically
verify
> > the
> > > > syntactic correctness of a given T-SQL statement? As of now, I am
> > > submitting
> > > > the query and catch an exception which requires a trip to the DB.
This
> > > might
> > > > seem ok...after all, why would i bother if i wasn't going to run the
> > query
> > > > in the first place. But my case is different. So far as i can tell
> > anyway,
> > > > and having to hit the DB is not required for my business flow.
> > >
> > > Have a look at this:
> > > http://www.codeproject.com/database/sqlvalidator.asp
> > >
> > > Also, I should think you would find something in the SQLDMO objects; I
> > > believe they expose all the objects used in EM and you can "Check
> Syntax"
> > of
> > > SPs in EM ...
> > >
> > > Let us know how it works out ...
> > >
> > >
> > >
> > >
> >
> >
>
>
- Next message: C#User: "select problem!!!"
- Previous message: Adam Machanic: "Re: Grouping Problem"
- In reply to: Liz: "Re: How do I verify a valid sql statement programatically ??"
- Next in thread: Liz: "Re: How do I verify a valid sql statement programatically ??"
- Reply: Liz: "Re: How do I verify a valid sql statement programatically ??"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
