implicit connections

Discussion in 'ASP General' started by rocketboy2000@shaw.ca, May 22, 2008.

  1. Guest

    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.
     
    , May 22, 2008
    #1
    1. Advertising

  2. wrote:
    > 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.



    --
    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.
     
    Bob Barrows [MVP], May 22, 2008
    #2
    1. Advertising

  3. Guest

    On 22 May, 10:14, "Bob Barrows [MVP]" <>
    wrote:
    > wrote:
    > > 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.
    >
    > --
    > 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!
     
    , May 22, 2008
    #3
  4. wrote:
    >
    >
    > 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.

    --
    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.
     
    Bob Barrows [MVP], May 22, 2008
    #4
  5. <> wrote in message
    news:...
    On 22 May, 10:14, "Bob Barrows [MVP]" <>
    wrote:
    > wrote:
    > > 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


    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

    --
    Mike Brind
    Microsoft MVP - ASP/ASP.NET
     
    Mike Brind [MVP], May 22, 2008
    #5
  6. Mike Brind [MVP] wrote:
    >
    > 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?
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], May 22, 2008
    #6
  7. "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Mike Brind [MVP] wrote:
    >>
    >> 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?


    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
     
    Mike Brind [MVP], May 23, 2008
    #7
  8. Guest

    On 22 May, 10:57, "Bob Barrows [MVP]" <>
    wrote:
    > wrote:
    >
    > > 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.
    >
    > --
    > 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?
     
    , May 23, 2008
    #8
  9. wrote:
    > On 22 May, 10:57, "Bob Barrows [MVP]" <>
    > wrote:
    >> wrote:
    >>
    >>> 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.
    >>

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

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], May 24, 2008
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Progman

    Implicit localization

    Progman, Mar 2, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    400
    Progman
    Mar 2, 2006
  2. Jacob
    Replies:
    7
    Views:
    433
    Dale King
    Oct 3, 2003
  3. cvelusc
    Replies:
    5
    Views:
    3,675
    Sudsy
    Jun 3, 2004
  4. jobs
    Replies:
    2
    Views:
    915
  5. Mark J. McGinty

    Implicit connections

    Mark J. McGinty, May 10, 2005, in forum: ASP General
    Replies:
    10
    Views:
    260
    Mark J. McGinty
    May 12, 2005
Loading...

Share This Page