implicit connections

[rocketboy2000]

i am maintaining a system that was developed using dream weaver. a lot
of the asp ado code looks like this:

set sp_yellow = Server.CreateObject("ADODB.Command")
sp_yellow.ActiveConnection = MM_MHR_CONN_STR_STRING
sp_yellow.CommandText = "Sp_HPMSQ007_GetSystemConstants"
sp_yellow.CommandType = 4
sp_yellow.CommandTimeout = 0
sp_yellow.Prepared = true
sp_yellow.Parameters.Append
sp_yellow.CreateParameter("@RETURN_VALUE", 3, 4)
sp_yellow.Parameters.Append sp_yellow.CreateParameter("@P_NAME_TXT",
129, 1, 17, "MLSTN_YELLOW_DAYS")
set rst_yellow = sp_yellow.Execute

someone on another forum expressed that dreamweaver "tends to drive
the programmer to creat implicit connections and their effect on
performance".

could someone explain this to me? a provide some better code? (we no
longer are required to use dream weaver to develop code for the app).

thanks in advance.

 

[Bob Barrows [MVP]]

i am maintaining a system that was developed using dream weaver. a lot
of the asp ado code looks like this:

set sp_yellow = Server.CreateObject("ADODB.Command")
sp_yellow.ActiveConnection = MM_MHR_CONN_STR_STRING
sp_yellow.CommandText = "Sp_HPMSQ007_GetSystemConstants"

Here is another problem: it is a bad idea to use the "sp_" prefix for
user-defined stored procedures. SQL Server assumes that procedures with
that prefix are system procedures, and will waste time looking for them
in the Master database, even if you specify the database name
explicitly. If you make the double mistake of giving your procedure the
same name as an actual system procedure, guess which one will be run
when you call it.

sp_yellow.CommandType = 4
sp_yellow.CommandTimeout = 0
sp_yellow.Prepared = true
sp_yellow.Parameters.Append
sp_yellow.CreateParameter("@RETURN_VALUE", 3, 4)
sp_yellow.Parameters.Append sp_yellow.CreateParameter("@P_NAME_TXT",
129, 1, 17, "MLSTN_YELLOW_DAYS")
set rst_yellow = sp_yellow.Execute

If you are not interested in the return value, using an explicit Command
object and appending the parameter objects is overkill.

someone on another forum expressed that dreamweaver "tends to drive
the programmer to creat implicit connections and their effect on
performance".

could someone explain this to me? a provide some better code? (we no
longer are required to use dream weaver to develop code for the app).

Explicit connections are best. Like this:

Set cn = CreateObject("ADODB.Connection")
cn.Open MM_MHR_CONN_STR_STRING 'ughhh
set rst_yellow = CreateObject("ADODB.Recordset")
'ugh - damn long variable names! let's fix this one
dim days
days = MLSTN_YELLOW_DAYS
cn.Sp_HPMSQ007_GetSystemConstants days, rst_yellow
If not rst_yellow.eof then
etc.

 

[rocketboy2000]

Here is another problem: it is a bad idea to use the "sp_" prefix for
user-defined stored procedures. SQL Server assumes that procedures with
that prefix are system procedures, and will waste time looking for them
in the Master database, even if you specify the database name
explicitly. If you make the double mistake of giving your procedure the
same name as an actual system procedure, guess which one will be run
when you call it.


If you are not interested in the return value, using an explicit Command
object and appending the parameter objects is overkill.





Explicit connections are best. Like this:

Set cn = CreateObject("ADODB.Connection")
cn.Open MM_MHR_CONN_STR_STRING 'ughhh
set rst_yellow = CreateObject("ADODB.Recordset")
'ugh - damn long variable names! let's fix this one
dim days
days = MLSTN_YELLOW_DAYS
cn.Sp_HPMSQ007_GetSystemConstants days, rst_yellow
If not rst_yellow.eof then
etc.

--
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.

awsome, thanks for the advice. i'm curious though...

the procedure i am calling actually has three parameters... two of
them have defaults. how do i use the names of the parameters in the
method that you outlined? and checking for an error condition
returned?

thanks again!

 

[Bob Barrows [MVP]]

awsome, thanks for the advice. i'm curious though...

the procedure i am calling actually has three parameters... two of
them have defaults. how do i use the names of the parameters in the
method that you outlined?
returned?

Actually, you really weren't using the parameter names: you could have
used

sp_yellow.Parameters.Append _
sp_yellow.CreateParameter("@not_the_real_name", _
129, 1, 17, "MLSTN_YELLOW_DAYS")

without raising an error.


With this technique (which many call the procedure-as-connection-method
technique), you cannot use the names of the parameters. You pass the
values to the procedure exactly the same way you would pass argument
values to a vbscript subroutine: in the order in which the arguments are
defined.

and checking for an error condition

do you mean the value of the Return parameter (the result of a RETURN
statement in the procedure)?
If you are interested in the Return parameter value, or you have output
parameters, then you have to use an explicit Command object. Just change

sp_yellow.ActiveConnection = MM_MHR_CONN_STR_STRING
to
Set sp_yellow.ActiveConnection = cn

And make sure you close your connection as soon as you are done with it.

 

[Mike Brind [MVP]]

Here is another problem: it is a bad idea to use the "sp_" prefix for
user-defined stored procedures. SQL Server assumes that procedures with
that prefix are system procedures, and will waste time looking for them
in the Master database, even if you specify the database name
explicitly. If you make the double mistake of giving your procedure the
same name as an actual system procedure, guess which one will be run
when you call it.


If you are not interested in the return value, using an explicit Command
object and appending the parameter objects is overkill.





Explicit connections are best. Like this:

Set cn = CreateObject("ADODB.Connection")
cn.Open MM_MHR_CONN_STR_STRING 'ughhh
set rst_yellow = CreateObject("ADODB.Recordset")
'ugh - damn long variable names! let's fix this one

LOL. I forgot to forewarn you when I directed you here of some people's
(Bob's) revulsion twoards the auto-generated variable names that DW
produces...

(and completely overlooked the procedure name beginning with sp_ ...)

And here's another Thread where Bob discusses explicit v. implicit
connections in more detail:
http://groups.google.co.uk/group/mi...=gst&q=activeconnection+=+mm#a6ed8b78c7fc0150

 

[Bob Barrows [MVP]]

LOL. I forgot to forewarn you when I directed you here of some
people's (Bob's) revulsion twoards the auto-generated variable names
that DW produces...

LOL ... and I forgot to insert some smileys to make sure everyone realized I
was partially joking

(and completely overlooked the procedure name beginning with sp_ ...)

And here's another Thread where Bob discusses explicit v. implicit
connections in more detail:
http://groups.google.co.uk/group/mi...=gst&q=activeconnection+=+mm#a6ed8b78c7fc0150

Thanks Mike, I hadn't had time to find that being at work and all ...

Congratulations on the MVP award ... I haven't seen you in the private
groups. Still finding your way around?

 

[Mike Brind [MVP]]

LOL ... and I forgot to insert some smileys to make sure everyone realized
I was partially joking

Thanks Mike, I hadn't had time to find that being at work and all ...

Congratulations on the MVP award ... I haven't seen you in the private
groups. Still finding your way around?

Thanks. I've found the groups, but not really spent any time there.
Spending way too much time over at forums.asp.net, basically

Mike

 

[rocketboy2000]

Actually, you really weren't using the parameter names: you could have
used

sp_yellow.Parameters.Append _
sp_yellow.CreateParameter("@not_the_real_name", _
129, 1, 17, "MLSTN_YELLOW_DAYS")

without raising an error.

With this technique (which many call the procedure-as-connection-method
technique), you cannot use the names of the parameters. You pass the
values to the procedure exactly the same way you would pass argument
values to a vbscript subroutine: in the order in which the arguments are
defined.


do you mean the value of the Return parameter (the result of a RETURN
statement in the procedure)?
If you are interested in the Return parameter value, or you have output
parameters, then you have to use an explicit Command object. Just change

sp_yellow.ActiveConnection = MM_MHR_CONN_STR_STRING
to
Set sp_yellow.ActiveConnection = cn

And make sure you close your connection as soon as you are done with it.

--
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.

thanks for all of that....

how _do_ i call a procedure with call-by-name parameters then?

 

[Bob Barrows [MVP]]

thanks for all of that....

how _do_ i call a procedure with call-by-name parameters then?

Ummm ... just the way I showed two messages ago ... ?
Here is another post I made on the topic:
http://groups.google.com/group/microsoft.public.scripting.vbscript/msg/61fedf4e1efd63a6