Re: how to access final SQL from parameterized ADO

Tech-Archive recommends: Fix windows errors by optimizing your registry

dondraper wrote:
Here's why: I want to save the complete query so a user can name it
and then run it again at a later date...

So give the set of parameter values a name and re-use them at a later
time ...


In a web application, my users use different means (query by
example form or report builder interface) to construct a query against
a database. The query is run and the user views a report of the
results. The user then has the option to "save" this report as a named
report.


Ahhh! Ad hoc queries! Thus far, I've been able to convince my users that
they don't need those.

How is the rest of the statement built? Via concatenation? is the user
allowed to supply the names of the tables or columns? If so, there's a
security risk. Even if you are restricting the words to be used via
dropdowns or lists, a hacker could conceivably cause his own "data" to
be submitted, so hopefullly you are validating these inputs pretty well
on the server.

If I could access the query sent to SQL Server, I could easily store
this away in the database and allow the user to later retrieve it,
then pass it to the cmd.CommandText and have ADO run the query again.
Very easy and efficient plus I know the query was constructed
correctly and will accurately return the original results.

However, using paramaterized queries (which we all should for better
datatype checking, security, performance, etc) now complicates things
as I cannot save a simple query generated by a user.
The original SQL
with my embedded question mark placeholders is useless without the
original values and datatypes.

Actually, given that vbscript consists of nothing but variants, you
really don't need to build a parameters collection. Let's look at your
initial example:

SQLstmt = "select col1, col2 from table1 where col3 = ?"
set dc = Server.CreateObject("ADODB.Connection")
set cmd = Server.CreateObject("ADODB.Command")
dc.ConnectionTimeout = 30
dc.CursorLocation = Application("myCursorLocation")
dc.Open Application("myConnectionString")
SET cmd.ActiveConnection = dc
cmd.CommandType = adCmdText
cmd.CommandText = SQLstmt
arParms = Array(21)
set rs=cmd.Execute(,arParms,1)

However, there are cases (usually involving dates) where it is necessary
to convert the values to the correct datatype before putting them in the
parameter array.

And we wonder why so many developers
continue to use concatenated strings to build their SQL statements. I
would bet this is one of them.

Maybe. I think the overriding reason is that this is the only technique
shown in samples, tutorials, etc. Which is a crime in my book.


One final question...is it possible under ADO 2.8 to serialalize the
parameters collection so it could be stored and later retrieved? If
yes, would this retain the values added to those parameters? If yes,
any assistance in that direction would be appreciated.

Nothing builtin. You would have to write your own code to loop through
the collection and serialize it (perhaps to an xml document), as well as
the code to deserialize it.
Of course, you could create the delimited strings (or xml document) as
you were building the parameters collection.
And of course, since you would be writing the code, you can choose to
store the values as well as the other parameter properties.

Here is a link to my stored procedure code generator:
http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator.zip

Perhaps you can reverse-engineer it and get some ideas about creating a
serializer/deserializer.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.

Relevant Pages

  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • Re: Fields in Label dont give correct information
    ... You need to create a query based on tblRegEvent and tblRegPayment. ... -If your DCountis to count values in your report, ... half the time with very few errors (other than that label problem I had.) ... For example in the Room assignment on the report an idividual will be ...
    (microsoft.public.access.reports)
  • Re: Tips on domain aggregate replacements
    ... This already is a split db, but the users are 400 miles away from the server hosting the data:) They are all running the same copy of the FE locally on a single terminal server via TS/RDP sessions. ... It doesn't seem to have much impact on performance; the report takes just about as long to run if they are all logged in as it does when I am logged on testing it at night. ... I would do DSums from the controls on the report on the data returned by the query. ... The biggest offender is a certain report that needs to Sum a particular complex total for each of the next twelve months (the DSum in VBA was a sideline to this issue) and present these as items on each line of output. ...
    (microsoft.public.access.modulesdaovba)
  • Re: using a form with combo box to input criteria
    ... Candia Computer Consulting - Candia NH ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)
  • Re: using a form with combo box to input criteria
    ... Candia Computer Consulting - Candia NH ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)