Best way to call SQL queries

[Laphan]

Hi All

I know this might sound strange, but I'm doing an ecom site and I'm actually
going to use an Access Db in the development stages, because I think its
quick and easy to add/remove table info in this DB and then scale it up to a
MySQL db when I've virtually finished.

As MySQL doesn't have stored procs (yet) and because of the above, I want to
develop a method where I have all of my queries to hand, but not stored in
the db. I know it sounds strange, but I want to keep them out of the
ASP/HTML code so that I can manipulate them from say 1 include file in case
of code changes, etc.

Only problem that I can see with this is that as I include more and more
queries in the include file calling this include file on each ASP page might
mean that an ASP page is reading in a lot of unnecessary and unused text
because it might say only use 1 query.

I can also foresee that a problem may occur when I need to parse values to a
query.

What I'm trying to say is do I create these queries in procs, functions or
is this a stupid idea that I have?

Also should I always bring recordsets in through the .getrows for speed? A
lot of my queries will be bringing in lists of data and this .getrows
command seems to be a common one for this.

Thanks

Laphan

[Ken Schaefer]

Hi,

It may be worth creating various classes, and then putting the relevant SQL
queries for that object into the class. Put each class into a separate
include file, and just include the ones you need. Then you can call methods
on the class, and those methods will involve executing the relevant SQL
queries.

..GetRows() is a fantastic idea. It means you can:
a) open a connection
b) get the data
c) close the connection
all in one small piece of code. You can then use the data anywhere on the
page, without having to keep your connection open. It also means you can
reuse the data multiple times without having to use an expensive ADO cursor.

Cheers
Ken


"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:%23X7p0HYJEHA.3216@tk2msftngp13.phx.gbl...
: Hi All
:
: I know this might sound strange, but I'm doing an ecom site and I'm
actually
: going to use an Access Db in the development stages, because I think its
: quick and easy to add/remove table info in this DB and then scale it up to
a
: MySQL db when I've virtually finished.
:
: As MySQL doesn't have stored procs (yet) and because of the above, I want
to
: develop a method where I have all of my queries to hand, but not stored in
: the db. I know it sounds strange, but I want to keep them out of the
: ASP/HTML code so that I can manipulate them from say 1 include file in
case
: of code changes, etc.
:
: Only problem that I can see with this is that as I include more and more
: queries in the include file calling this include file on each ASP page
might
: mean that an ASP page is reading in a lot of unnecessary and unused text
: because it might say only use 1 query.
:
: I can also foresee that a problem may occur when I need to parse values to
a
: query.
:
: What I'm trying to say is do I create these queries in procs, functions or
: is this a stupid idea that I have?
:
: Also should I always bring recordsets in through the .getrows for speed?
A
: lot of my queries will be bringing in lists of data and this .getrows
: command seems to be a common one for this.
:
: Thanks
:
: Laphan
:
:

[Jeff Cochran]

On Sun, 18 Apr 2004 21:31:15 +0100, "Laphan" <news@DoNotEmailMe.co.uk>
wrote:

>I know this might sound strange, but I'm doing an ecom site and I'm actually
>going to use an Access Db in the development stages, because I think its
>quick and easy to add/remove table info in this DB and then scale it up to a
>MySQL db when I've virtually finished.

That's not strange, I know a number of developers who do this and
deploy on SQL Server.

>As MySQL doesn't have stored procs (yet)

Might want to look at:

[url]http://dev.mysql.com/doc/mysql/en/Stored_Procedures.html[/url]

>and because of the above, I want to
>develop a method where I have all of my queries to hand, but not stored in
>the db. I know it sounds strange, but I want to keep them out of the
>ASP/HTML code so that I can manipulate them from say 1 include file in case
>of code changes, etc.
>
>Only problem that I can see with this is that as I include more and more
>queries in the include file calling this include file on each ASP page might
>mean that an ASP page is reading in a lot of unnecessary and unused text
>because it might say only use 1 query.

Yep.

>I can also foresee that a problem may occur when I need to parse values to a
>query.
>
>What I'm trying to say is do I create these queries in procs, functions or
>is this a stupid idea that I have?

Not that stupid, but cumbersome.

>Also should I always bring recordsets in through the .getrows for speed? A
>lot of my queries will be bringing in lists of data and this .getrows
>command seems to be a common one for this.

GetRows is valuable, but has its place. You need to understand why
you wnat to use it, and then you'll understand when you shouldn't. I
wouldn't say a blanket "always" on anything, and if you code a Proc
or Function with GetRows and don't need to use it, you may be losing
speed instead of gaining it.

Jeff

[Ken Schaefer]

"Jeff Cochran" <jcochran.nospam@naplesgov.com> wrote in message
news:4083dc57.6464555@msnews.microsoft.com...
: On Sun, 18 Apr 2004 21:31:15 +0100, "Laphan" <news@DoNotEmailMe.co.uk>
: wrote:
:
: GetRows is valuable, but has its place. You need to understand why
: you wnat to use it, and then you'll understand when you shouldn't. I
: wouldn't say a blanket "always" on anything, and if you code a Proc
: or Function with GetRows and don't need to use it, you may be losing
: speed instead of gaining it.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

And when would you use a (non disconnected) recordset instead of GetRows?

Cheers
Ken

[Astra]

Hi Ken/Jeff

Thanks for the feedback.

I'd like to get the queries into their own include file for maintenace
purposes and was going to add each query string to it's own constant var,
but pass params would seem to make this impractical.

I'd like to hear more about the Class aspect, but if you were to read the
below example would you make it a function or a sub proc?

Sub (or function) CmdGetList(<the params>)

strSQL = "select * from products"
strSQL & strSQL = "where " & <theparamfield>

At this point I could have a command object to execute the code (which I
think would really make this a sub proc) or I could return the string back
to execute the code from where it came from (see below and I think this
would make it a function)

Proc work??

oCmd.CommandText = strSQL
oCmd.Execute

function work??

Return CmdGetList

End Sub/Function


Proc work??

call cmdGetList('Spade')

function work??

oCmd.CommandText = CmdGetList('Spade')
oCmd.Execute

Have I set the principle code out correctly?

Which is really the best option both pratically and efficieny-wise?

Many thanks

Rgds

Laphan


"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:Oagst1hJEHA.2556@TK2MSFTNGP11.phx.gbl...

"Jeff Cochran" <jcochran.nospam@naplesgov.com> wrote in message
news:4083dc57.6464555@msnews.microsoft.com...
: On Sun, 18 Apr 2004 21:31:15 +0100, "Laphan" <news@DoNotEmailMe.co.uk>
: wrote:
:
: GetRows is valuable, but has its place. You need to understand why
: you wnat to use it, and then you'll understand when you shouldn't. I
: wouldn't say a blanket "always" on anything, and if you code a Proc
: or Function with GetRows and don't need to use it, you may be losing
: speed instead of gaining it.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

And when would you use a (non disconnected) recordset instead of GetRows?

Cheers
Ken

[Ken Schaefer]

You can look up classes in the VBScript docs.

The real lifting would be in your routines. You could have a generic routine
that accepts:
a) connection string
b) command text (sproc name)
c) a dictionary object, or array, of parameter values (or even an array of
parameter objects, or something)

the routine calls another to return a connection based on the conn string.
You instantiate a command object
You interate through your dictionary object or array appending parameters
You return a resultset.
You call getrows
You return the resulting array to your app
You clean up your objects.

By wrapping this into classes you get nicer code (IMHO)

Set objDAL = Server.CreateObject("myCompany.DAL")
myResults = objDAL.ReturnResults(Application("ConnString"), "usp_mySproc",
objParams)


Cheers
Ken



"Astra" <info@NoEmail.com> wrote in message
news:%23c$Y1jtJEHA.2692@tk2msftngp13.phx.gbl...
: Hi Ken/Jeff
:
: Thanks for the feedback.
:
: I'd like to get the queries into their own include file for maintenace
: purposes and was going to add each query string to it's own constant var,
: but pass params would seem to make this impractical.
:
: I'd like to hear more about the Class aspect, but if you were to read the
: below example would you make it a function or a sub proc?
:
: Sub (or function) CmdGetList(<the params>)
:
: strSQL = "select * from products"
: strSQL & strSQL = "where " & <theparamfield>
:
: At this point I could have a command object to execute the code (which I
: think would really make this a sub proc) or I could return the string back
: to execute the code from where it came from (see below and I think this
: would make it a function)
:
: Proc work??
:
: oCmd.CommandText = strSQL
: oCmd.Execute
:
: function work??
:
: Return CmdGetList
:
: End Sub/Function
:
:
: Proc work??
:
: call cmdGetList('Spade')
:
: function work??
:
: oCmd.CommandText = CmdGetList('Spade')
: oCmd.Execute
:
: Have I set the principle code out correctly?
:
: Which is really the best option both pratically and efficieny-wise?
:
: Many thanks
:
: Rgds
:
: Laphan
:
:
: "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
: news:Oagst1hJEHA.2556@TK2MSFTNGP11.phx.gbl...
:
: "Jeff Cochran" <jcochran.nospam@naplesgov.com> wrote in message
: news:4083dc57.6464555@msnews.microsoft.com...
: : On Sun, 18 Apr 2004 21:31:15 +0100, "Laphan" <news@DoNotEmailMe.co.uk>
: : wrote:
: :
: : GetRows is valuable, but has its place. You need to understand why
: : you wnat to use it, and then you'll understand when you shouldn't. I
: : wouldn't say a blanket "always" on anything, and if you code a Proc
: : or Function with GetRows and don't need to use it, you may be losing
: : speed instead of gaining it.
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:
: And when would you use a (non disconnected) recordset instead of GetRows?
:
: Cheers
: Ken
:
:
:

[Laphan]

Thanks Ken


Ken Schaefer <kenREMOVE@THISadOpenStatic.com> wrote in message
news:Oj78bstJEHA.3980@TK2MSFTNGP10.phx.gbl...
You can look up classes in the VBScript docs.

The real lifting would be in your routines. You could have a generic routine
that accepts:
a) connection string
b) command text (sproc name)
c) a dictionary object, or array, of parameter values (or even an array of
parameter objects, or something)

the routine calls another to return a connection based on the conn string.
You instantiate a command object
You interate through your dictionary object or array appending parameters
You return a resultset.
You call getrows
You return the resulting array to your app
You clean up your objects.

By wrapping this into classes you get nicer code (IMHO)

Set objDAL = Server.CreateObject("myCompany.DAL")
myResults = objDAL.ReturnResults(Application("ConnString"), "usp_mySproc",
objParams)


Cheers
Ken



"Astra" <info@NoEmail.com> wrote in message
news:%23c$Y1jtJEHA.2692@tk2msftngp13.phx.gbl...
: Hi Ken/Jeff
:
: Thanks for the feedback.
:
: I'd like to get the queries into their own include file for maintenace
: purposes and was going to add each query string to it's own constant var,
: but pass params would seem to make this impractical.
:
: I'd like to hear more about the Class aspect, but if you were to read the
: below example would you make it a function or a sub proc?
:
: Sub (or function) CmdGetList(<the params>)
:
: strSQL = "select * from products"
: strSQL & strSQL = "where " & <theparamfield>
:
: At this point I could have a command object to execute the code (which I
: think would really make this a sub proc) or I could return the string back
: to execute the code from where it came from (see below and I think this
: would make it a function)
:
: Proc work??
:
: oCmd.CommandText = strSQL
: oCmd.Execute
:
: function work??
:
: Return CmdGetList
:
: End Sub/Function
:
:
: Proc work??
:
: call cmdGetList('Spade')
:
: function work??
:
: oCmd.CommandText = CmdGetList('Spade')
: oCmd.Execute
:
: Have I set the principle code out correctly?
:
: Which is really the best option both pratically and efficieny-wise?
:
: Many thanks
:
: Rgds
:
: Laphan
:
:
: "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
: news:Oagst1hJEHA.2556@TK2MSFTNGP11.phx.gbl...
:
: "Jeff Cochran" <jcochran.nospam@naplesgov.com> wrote in message
: news:4083dc57.6464555@msnews.microsoft.com...
: : On Sun, 18 Apr 2004 21:31:15 +0100, "Laphan" <news@DoNotEmailMe.co.uk>
: : wrote:
: :
: : GetRows is valuable, but has its place. You need to understand why
: : you wnat to use it, and then you'll understand when you shouldn't. I
: : wouldn't say a blanket "always" on anything, and if you code a Proc
: : or Function with GetRows and don't need to use it, you may be losing
: : speed instead of gaining it.
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:
: And when would you use a (non disconnected) recordset instead of GetRows?
:
: Cheers
: Ken
:
:
: