Introduction to ADO - The Command Object
-
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"
-
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 -
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 = NothingWhere this can be done, it is encouraged. In this particular case, I am passing the dates as an array in the execute statement.
-
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
-
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?
-
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
-
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
-
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.
-
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.
-
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.
-
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
-
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/
-
These articles are from a ways back, Steve decided to re-run one of them - still relevant! Here are links to the others:
Connection Object:
http://www.sqlservercentral.com/columnists/awarren/introductiontotheadoconnectionobject.asp
Recordsets:
http://www.sqlservercentral.com/columnists/awarren/introductiontoadopart2recordsets.asp
Part 4 - http://www.sqlservercentral.com/columnists/awarren/introductiontoadopart4combiningitall.asp
-
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!
-
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 30 total)
You must be logged in to reply to this topic. Login to reply