Stored procs and command objects

[Ray at]

Person asks:

I've been coding up a storm for a project the last couple weeks and
have been looking for unrelated information - when I discovered that
the folks at [url]www.aspfaq.com[/url] recommend against using the command object
to do stored procedures. No 'meat and potatoes' explanation is given
other than "it's another object you have to deal with" and "it can be
done more simply using rs.execute"

I try pretty hard to code according to the recommendations of those
people I respect, like Adrian, but this just seems 'out there.'

The SP's I'm dealing with take as many as 20 parameters and I just
don't see how using cn.execute would be easier, or eliminating that
single object could reduce the code complexity. Am I just missing
something or is this just a bad example of where it doesn't really
apply to my use.

[member of active-server-pages group at Yahoo groups]


My response:
I've always taken it to be suggested simply for the sake of not creating
unnecessary overhead.


Is there more to it than that though?

Thanks,

Ray at home

[Ken Schaefer]

Personally I think you should use Command objects. I believe it makes for
more robust, and more readable code.

On the other hand, Aaron disagrees...

I also think you should use named ADO constants, rather than "magic numbers"

Aaron disagrees with this too.

Cheers
Ken

"Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
news:OXureWqkDHA.1728@TK2MSFTNGP11.phx.gbl...
: Person asks:
:
: I've been coding up a storm for a project the last couple weeks and
: have been looking for unrelated information - when I discovered that
: the folks at [url]www.aspfaq.com[/url] recommend against using the command object
: to do stored procedures. No 'meat and potatoes' explanation is given
: other than "it's another object you have to deal with" and "it can be
: done more simply using rs.execute"
:
: I try pretty hard to code according to the recommendations of those
: people I respect, like Adrian, but this just seems 'out there.'
:
: The SP's I'm dealing with take as many as 20 parameters and I just
: don't see how using cn.execute would be easier, or eliminating that
: single object could reduce the code complexity. Am I just missing
: something or is this just a bad example of where it doesn't really
: apply to my use.
:
: [member of active-server-pages group at Yahoo groups]
:
:
: My response:
: I've always taken it to be suggested simply for the sake of not creating
: unnecessary overhead.
:
:
: Is there more to it than that though?
:
: Thanks,
:
: Ray at home
:
:

[Ray at]

"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:eeW5WDtkDHA.2272@tk2msftngp13.phx.gbl...

> Personally I think you should use Command objects. I believe it makes for
> more robust, and more readable code.
>
> On the other hand, Aaron disagrees...

That is funny.

I go both ways (like, on this matter, not in any other way). There are
times that I will sacrifice efficiency for readability, and sometimes I
won't. I tend to only code explicitly if I am still unfamiliar with the
technology. Once I get familiar with it, I sacrifice readability. Then I
don't use it for a year, go back to change something, and have no idea what
the hell my code is doing. ;]

>
> I also think you should use named ADO constants, rather than "magic

numbers"

>
> Aaron disagrees with this too.

I'll have to go with Aaron on this one. IMO, it's best to memorize all the
constants, just like you should have the ascii chart memorized.

Ray at home

[Ken Schaefer]

"Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
news:e4KSjHtkDHA.3316@tk2msftngp13.phx.gbl...
:
: > I also think you should use named ADO constants, rather than "magic
: numbers"
: >
: > Aaron disagrees with this too.
:
: I'll have to go with Aaron on this one. IMO, it's best to memorize
: all the constants, just like you should have the ascii chart
: memorized.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

That's nonsense. (both the bit about memorising constants, and the bit about
knowing the ASCII chart).

I'm supposed to have memorised every single constant value for every single
piece of code that I write -or- that I review? What about projects where I'm
pulled in to do maintenance work? (I had to do some code for a project
produces reports in Microsoft Word - do you know how many constants there
are in Word VBA?). Imagine if the original developer had decided to use
"magic numbers" for everything. I'd be spending half my time looking in MSDN
trying to find out what they meant.

ANY programming course that you do tells you not to use "magic numbers"
(that's why they are called "magic numbers"). And that rule's been around
for a lot longer than ADO.

Things that should be memorised are things that you need to use often (for
example, I have lots of details of the TCP/IP protocol memorised, as well as
the ISO OSI model etc - doesn't mean everyone should. Nor should everyone
have to memorise the ACSII chart) . Furthermore, that doesn't mean that you
should use the numbers in code.

Cheers
Ken

[Ray at]

"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:%23bUF6OtkDHA.1800@TK2MSFTNGP10.phx.gbl...

>
> "Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
> news:e4KSjHtkDHA.3316@tk2msftngp13.phx.gbl...
> :
> : > I also think you should use named ADO constants, rather than "magic
> : numbers"
> : >
> : > Aaron disagrees with this too.
> :
> : I'll have to go with Aaron on this one. IMO, it's best to memorize
> : all the constants, just like you should have the ascii chart
> : memorized.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> That's nonsense. (both the bit about memorising constants, and the bit

about

> knowing the ASCII chart).
>

When I got my first computer and a copy of VB, I read "Visual Basic 5 for
Dummies." I think in the first chapter, it said something about memorizing
the ASCII chart. I thought it was nonsense too, but the book was right; I
was wrong. I'm still working on memorizing it though. ;] But, I know the
ones I use often like ®, °, ß, ×, etc.

> I'm supposed to have memorised every single constant value for every

single

> piece of code that I write -or- that I review?

Yes. Okay, kidding. But, for the things that you work with often, like
ForReading, ForWriting, etc.

> What about projects where I'm
> pulled in to do maintenance work? (I had to do some code for a project
> produces reports in Microsoft Word - do you know how many constants there
> are in Word VBA?).

But if you were a Word programmer, you'd know many of them. I think I went
wrong when I said memorize "all" the constants. Let me take that back and
say that you should memorize the ones you use often.

Imagine if the original developer had decided to use

> "magic numbers" for everything. I'd be spending half my time looking in

MSDN

> trying to find out what they meant.

That's true, but if you have reference materials, it only takes a second to
look at what values are expected, and what they mean.

>
> ANY programming course that you do tells you not to use "magic numbers"
> (that's why they are called "magic numbers"). And that rule's been around
> for a lot longer than ADO.

Rules. Pffft.

>
> Things that should be memorised are things that you need to use often (for
> example, I have lots of details of the TCP/IP protocol memorised, as well

as

> the ISO OSI model etc - doesn't mean everyone should.

Unless they have the need.

> Nor should everyone
> have to memorise the ACSII chart) . Furthermore, that doesn't mean that

you

> should use the numbers in code.

~I~ should. But one should use whatever he prefers. I prefer numbers.

Ray at home

[Bob Barrows]

Ray at <%=sLocation%> wrote:

> Person asks:
>
> I've been coding up a storm for a project the last couple weeks and
> have been looking for unrelated information - when I discovered that
> the folks at [url]www.aspfaq.com[/url] recommend against using the command object
> to do stored procedures. No 'meat and potatoes' explanation is given
> other than "it's another object you have to deal with" and "it can be
> done more simply using rs.execute"
>
> I try pretty hard to code according to the recommendations of those
> people I respect, like Adrian, but this just seems 'out there.'
>
> The SP's I'm dealing with take as many as 20 parameters and I just
> don't see how using cn.execute would be easier, or eliminating that
> single object could reduce the code complexity. Am I just missing
> something or is this just a bad example of where it doesn't really
> apply to my use.
>
> [member of active-server-pages group at Yahoo groups]
>
>
> My response:
> I've always taken it to be suggested simply for the sake of not
> creating unnecessary overhead.
>
>
> Is there more to it than that though?
>

If you want to use output parameters, or you need to read the value of the
return parameter, you HAVE to use a Command object. I am a strong advocate
of using output parameters as opposed to returning values in recordsets when
cursor functionality is not needed. It cuts down on network traffic due to
not needing to pass metadata as well as data to the client, and resource
utilization, due to not requiring a cursor to be built to process the
returned data.

If that functionality (output/return parameters) is not required, then I
would still prefer a Command object if the procedure-as-connection-method
technique was not supported, due to the strong parameter typing provided by
the Parameters collection (bulletproof protection against sql injection) and
the avoidance of the need to escape certain string characters in the
parameter values being passed.

I think everyone here knows my preference for the
procedure-as-connection-method technique.

Aaron prefers the dynamic sql technique due to the ability to resonse.write
it if there are problems, making it easier to troubleshoot. I admit he has a
point here: I have to fire up SQL Profiler if I'm having a problem (which is
rare <grin> ). But I still prefer to avoid the overhead of string
concatenation and Replace statements required to build these strings.

Bob Barrows

--
HTH,
Bob Barrows - ASP MVP
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.

[Bob Barrows]

Ray at <%=sLocation%> wrote:

>
> I'll have to go with Aaron on this one. IMO, it's best to memorize
> all the constants, just like you should have the ascii chart
> memorized.
>

LOL
Right! And comments!!! Comments are for sissies! No real programmer comments
his code. :-)

--
HTH,
Bob Barrows - ASP MVP
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.

[Ray at]

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:ON73BuxkDHA.2216@TK2MSFTNGP12.phx.gbl...

> Ray at <%=sLocation%> wrote:

> >
> > I'll have to go with Aaron on this one. IMO, it's best to memorize
> > all the constants, just like you should have the ascii chart
> > memorized.
> >

> LOL
> Right! And comments!!! Comments are for sissies! No real programmer

comments

> his code. :-)
>

Hey now, comment are a whole other story. I comment man!

Do you have to go to the phone book to call your mother, or do you just know
her number?

Ray at work

[Bob Barrows]

Ray at <%=sLocation%> wrote:

> "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:ON73BuxkDHA.2216@TK2MSFTNGP12.phx.gbl...

>> Ray at <%=sLocation%> wrote:

>>>
>>> I'll have to go with Aaron on this one. IMO, it's best to memorize
>>> all the constants, just like you should have the ascii chart
>>> memorized.
>>>

>> LOL
>> Right! And comments!!! Comments are for sissies! No real programmer
>> comments his code. :-)
>>

>
> Hey now, comment are a whole other story. I comment man!
>
> Do you have to go to the phone book to call your mother, or do you
> just know her number?
>

You ARE serious! :-0

No, I did not have to go to the phone book for my mother's number, but
neither did I tell somebody who asked for her number to go look it up :-)
Or, better analogy: I never sent somebody a phone number and told them to
look up the name of the person in the phone book.

I use constants all the time. Not only are they helpful for me, but they
will help the guy who has to maintain my code when I leave. Personally, I
hate maintaining/reading code that contains magic numbers.

Besides, I've got better use for my brainpower than memorizing a lot of
magic numbers. Maybe if I wasn't using VID for development, where the
intellisense autocompletes all my constants, in addition to giving me lists
to pick from ... but even then, I would still use constants. It's not like
they're impacting performance: the compiler resolves all constants at
compile-time, so they do not affect performance at run-time.

--
HTH,
Bob Barrows - ASP MVP
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.

[Ray at]

> You ARE serious! :-0

Yeah.

>
> I never sent somebody a phone number and told them to
> look up the name of the person in the phone book.

That's funny. :] Point well taken.

Ray at work

[Ray at]

You know, if I didn't suck at analogies, I'd be able to argue my point
better. Here is my weak attempt.

Prior to life as a programmer, I spent the majority of my time working on my
cars. I never complained about bolts not being labeled as to their size.
At first, you'd just grab a few sockets that looked close to the size, or if
you really wanted to waste time, you'd look up the size in a Chilton's
manual or something. But, after time, you get to know the size just by
looking at it. Either that, are you remember what bolts are what size, and
if they're metric or SAE. Like I said, I suck at analogies. But I guess
what I'm saying is I'm more interested in knowing the values and having
those values be in my head as second nature.

00820097012100320097011600320119011101140107



"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:uQRBStykDHA.2012@TK2MSFTNGP12.phx.gbl...

>
> No, I did not have to go to the phone book for my mother's number, but
> neither did I tell somebody who asked for her number to go look it up :-)
> Or, better analogy: I never sent somebody a phone number and told them to
> look up the name of the person in the phone book.
>
> I use constants all the time. Not only are they helpful for me, but they
> will help the guy who has to maintain my code when I leave. Personally, I
> hate maintaining/reading code that contains magic numbers.
>
> Besides, I've got better use for my brainpower than memorizing a lot of
> magic numbers. Maybe if I wasn't using VID for development, where the
> intellisense autocompletes all my constants, in addition to giving me

lists

> to pick from ... but even then, I would still use constants. It's not like
> they're impacting performance: the compiler resolves all constants at
> compile-time, so they do not affect performance at run-time.
>
> --
> HTH,
> Bob Barrows - ASP MVP
> 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.
>
>