Introduction to ADO - The Command Object

  • RonKyle

    SSC-Dedicated

    Points: 31459

    quote:


    Both SiteServer and my user databases are using Windows Integrated security. Both have the required user set up and yet I still get Access Denied!


    Are you using "Trusted_Connection=Yes" in your connection string? For example:

    ConnectionString = "PROVIDER=SQLOLEDB;" & _

    "Data Source=" & mconDevelopmentServer & ";" & _

    "Initial Catalog=" & mconDevelopmentDatabase & ";" & _

    "Trusted_Connection=yes"

  • jscontreras

    Old Hand

    Points: 380

    Hello,

    As I was saying before this procedure does not work

    Public Function ExecuteSP(sProcName As String, ParamArray aParams()) As ADODB.Recordset

    Dim objCMD As ADODB.Command

    Set objCMD = New ADODB.Command

    With objCMD

    .ActiveConnection = Conn

    .CommandText = sProcName

    .CommandType = adCmdStoredProc

    If aParams(0) Is Nothing Then

    Set ExecuteSP = .Execute

    Else

    Set ExecuteSP = .Execute(, aParams)

    End

    End With

    Set objCMD = Nothing

    End Function

    What I'm trying to accomplish is pass an array of parameters and within the proc or function is to loop through the parameter names using the following:

    .Parameters.Append .CreateParameter(PARAM2, adVarChar, adParamInput, FS2, ParamValue2)

    I'm not an expert with the Command object to know all the methods and properties. My question is instead of writing my own array method, etc. Is there a method already prewritten that I can use? For example, in ADO I like to use the arrays to loop through recordsets, intially I was writing my own arrays to loop through recordsets before I found the "getrows" method which does the same thing.

    I have not be successful at finding good examples of all the methods and properties of the Command object. I hope I'm making some sense.

    Thanks

    JMC


    JMC

  • RonKyle

    SSC-Dedicated

    Points: 31459

    Here is a section from a document I wrote on ADO best practices:

    Implicit Parameters

    As explained above, implicit parameters can save code. For example, rather than create two lines of explicit parameters, you can do this:

    
    
    Set cmd = New ADODB.Command
    With cmd
    .CommandType = adCmdStoredProc
    .CommandText = "rsup_WorkInvoicedBetween"
    .ActiveConnection = mcnn
    .Execute , Array(dStartDate, dEndDate), adExecuteNoRecords
    End With
    Set cmd = Nothing

    Where this can be done, it is encouraged. In this particular case, I am passing the dates as an array in the execute statement.

  • Andy Warren

    SSC Guru

    Points: 119676

    Why a best practice? You save a couple lines of code, but you trade off the strong client side type checking. I tend to use a code generator, so using the params doesn't slow me down.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • RonKyle

    SSC-Dedicated

    Points: 31459

    My intent was to show how you can use an array to pass the values. That it is clipped from a best practices document is more a best practices for my company. We have programmers who want to write the least amount of lines, and the document that I wrote outlines when this is good and when it is not good.

    That said, the data type is checked when the variables are passed into the subroutines. In the code example, the two dates have been passed in as a date data type, so it is checked. Does that work?

  • Andy Warren

    SSC Guru

    Points: 119676

    Of course. Though if you had varchar parms, you'd have to used fixed length strings for your params, or do length checking inside the sub. It's a fair point. As long as the code remains readable/maintainable and you don't take a perf hit, I'm all for concise code. To me, using a code generator saves me enough time I don't care that it generates slightly more verbose code - and I guess I could always update the generator!

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • dUros

    Default port

    Points: 1446

    One question:

    I always use CommandType=adCmdText and CommandText=mySQL.

    MySQL is T-SQL statement or for execution stored procedures EXEC myStoredProc 'cParam1', nParam2, 'dParam3'.

    Example:

    If I don't need return data. 

    .............

    strSQL="EXEC myStoredProc '" & Param1 & "'"

    With cmd

            .ActiveConnection = cnn

            .CommandText = strSQL

            .CommandType = adCmdText

            .CommandTimeout = wTimeOut

            .Execute

    End With

    .............

    If I need recordset with data from SQL server.

    .............

    strSQL="EXEC myStoredProc '" & Param1 & "'"

    With cmd

            .ActiveConnection = cnn

            .CommandText = strSQL

            .CommandType = adCmdText

            .CommandTimeout = wTimeOut

    End With

    aRS.CursorLocation = adUseClient

    aRS.CursorType = adOpenForwardOnly

    aRS.LockType = adLockReadOnly

    aRS.Open cmd

    ...............   

    Question is: If using .Parameters is better then my way without .Parameters and with adCmdText? Which way is faster?

    Uros

  • Andy Warren

    SSC Guru

    Points: 119676

    In terms of performance I doubt there is a difference. I think the advantages to parameters is that you stay object oriented - which while that might sound ivory towerish, here is one great reason that helps - single quotes. If you're concatenating everything together you have to remember to double up your single quotes or you wind up with a bad sql string. Building strings up in code also tends to open the door to sql injection attacks for the same reason.

  • scott mcnitt

    SSC Eights!

    Points: 978

    A comment and a question.

    We also standardized on the "explicit" parameter declaration. It has been a long time since we made the decision, but from memory it was because it was the only way to get output return values -- thus making it general purpose and suitable to our "code generator" (copy and paste from templates).

    Question: what are the options/ pro-con/ best practice with passing NULL as the value to an input parm?

    In the example below, what if the field for "Description" was NULL-allowed and also allowed empty string and we want to pass in NULL (business rule difference may be "no description provided yet" vs "description intentionally left blank").

    params.Append cmd.CreateParameter("ParentID", adInteger, adParamInput, ,intParentId)

    params.Append cmd.CreateParameter("UserName", adVarChar, adParamInput, len(strUserName), strUserName)

    ==>params.Append cmd.CreateParameter("Description", adVarChar, adParamInput, len(vntDescr), vntDescr)

    I have handled this a few different ways -- both in the setup of the parms and in the sproc. What do you suggest?

    Thanks in advance and I will look for your next article.

  • Andy Warren

    SSC Guru

    Points: 119676

    I think its a bit Zen;-)

    First, I hate the idea of storing empty strings in a database. Whether you convert them to null client side or server side, I think thats the right thing to do. If you need it to represent something else, use a real value like 'UNK' or whatever, ugly though that can be.

    There are actually two pieces of this - should a developer ever send in a null value, and should they ever get back a null value.

    I like handling the conversion to nulls in the stored procedure. It frees up the developer from having to worry about it and I need to do the check anyway to make sure they are doing the right thing. Cheap and easy to add some NULLIF's where needed. I've also tried to standardize on the practice that any stored proc that returns values to a developer will never return a null - it will be empty string, zero, or some special value if the situation calls for it - saves writing a lot of special case code.

  • Ron Hinds

    SSC Veteran

    Points: 207

    Andy,

    The article says it is Part 3 of a 4 part series. But there are no links (as is usually the case) to Part 1 & 2. I'd be interested to read those articles, too.

    Ron

     

  • Paul Paiva

    SSCrazy

    Points: 2237

    Thanks for the article.

    I tend to avoid using the parameters collection and just concatenate a string with all the info for the stored proc.  I find this easier that wrestling with the parameters collection's idiosyncracies.  I use various string variables for each parameter, and I format with indenting so I can still make my code quite readable. 

    Also the syntax of the parameters collection will vary depending on the language (Dot Net versus Cold Fusion, for example).  But the concatenated string is something that all the app developers I work with will understand. 

    I'm certainly interested in being a convert to the parameters collection if I know there are benefits other than readability, like perhaps performance.  Can anyone give me a compelling reason to change, or do you think I am fine using concatenation?

    Paul

    - Paul

    http://paulpaivasql.blogspot.com/

  • Andy Warren

    SSC Guru

    Points: 119676

  • Andy Warren

    SSC Guru

    Points: 119676

    Paul,

    See my earlier reply too (about 2 back) but the biggest think I dont like about concatenated strings is that you have to always handle embededded sinqle quotes. Miss one and it will fail when you execute, plus you increase the risk of sql injection.

    As for the parameters collection, if you find it awkward - why not roll your own? VB6 doesn't do inheritance, but you can easily write an object that internally will generate and return the parameters collection. You can fix anything/everything you didnt like, still get the advantages of the params, and you could add a property that would indicate the output platform - CF or other. Objects always look like more work and more indirection - both true to an extent - but I've seen the value too many times to go back to strings!

     

  • scott mcnitt

    SSC Eights!

    Points: 978

    Last I checked, you could only get the values from out parms if you used the params collection. That was the deciding factor for our shop -- many years ago now. Maybe the world has changed.

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply