• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

SP and Command object

Hi All, Just a few questions:

1.  Is the command object theonly way to execute a stored procedure for a SQL server database?

2.  What are the benefits of using the command object?

3.  What are the differences between the connection object and the command object
0
apresto
Asked:
apresto
  • 5
  • 3
  • 2
  • +2
4 Solutions
 
deighcCommented:
> 1.  Is the command object theonly way to execute a stored procedure for a SQL server database?

No.
You can also execute a stored procedure by calling the Open method of the recordset object ...

rsObj.Open "<sp name and parameter values>", <connection object>, <cursor type>, <lock type>, adCmdStoredProc ' ADO constant = 4

or by calling the the Execute method of the connection object

set rsObj = connObj.Execute("<sp name and parameter values>, , adCmdStoredProc)

> 2.  What are the benefits of using the command object?

The only time you really have to use a command object is when you want to access output parameters or return values from a stored procedure. If you have to do this then the using the command object is not so much a "benefit" as a necessity.

You could argue that another includes the fact the command object includes a nice clear API for calling paramterized queries. Not a benefit as such, but a "nice touch".

> 3.  What are the differences between the connection object and the command object

These two ADO objects represent 2 distinct pieces of functionality. The connection object represents the, er, connection to the database. It allows you specify which database you want to connect to, a connection timeout property and functionality for connect to and disconnecting from the database. A command object represents an executable command given to the database. It can specify input parameters, output parameters and return values, and it can return datasets into recordset objects. It operates 'over' a connection object.
0
 
deighcCommented:
Hey, wait...

Is this homework ??
0
 
Kamoku-WanCommented:
Probably lol.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
aprestoAuthor Commented:
No its not - i havent done homework for 2 years :o)

Its for a job i was offered, i start on monday and they asked if i knew the command object and stored procedures - i said "yes" thinking, how hard can it be - Im very familair with most other methods i just havent touched on these!
0
 
aprestoAuthor Commented:
Thanks for the info deighc

Do you know of any good links or references to example of how command object is used and how getting output parameters is done etc? BAsically example of what you explained above?
0
 
fozyletCommented:
0
 
aprestoAuthor Commented:
Thanks fozylet,

Thats 2 out of three now guys, Kamoku-Wan if you give some useful information that could be a full house!

Ill have a look now thanks fozylet
0
 
Kamoku-WanCommented:
0
 
aprestoAuthor Commented:
I meant something that hadnt been explained but a handy article anyway - The guys from rolla! How they helped me in my novice days!

Thanks guys

Im gonna have a look over the weekend and post back, Thanks
0
 
hujiCommented:
Hi there apresto.
I've asked related questions here in EE. Links:
http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_21303339.html
http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_21302977.html
http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_21303283.html

Actually you are not supposed to read all of them. So I'm posting the conclusions. As I'm speaking based on that, I hope I'm not overlapping one other's post above.

1) Well there are ways to call a SP. Some methods are:
conn.Execute("SPname");
rs.Open("SPname",conn);
conn.Execute("exec SPname");
...
and my favorite method: to use a command object for it!

cmd.CommandText = "SPname"
cmd.CommandType = 4          'adCmdStoredProc

and if you have parameters, you can safely send them along with:

cmd.Parameters.Append cmd.CreateParameter("@colName",200,1,10,"lorem")
Where 200 stands for adVarChar and 1 stands for adParamInput

2) Benefits? I can count at least two:
(i)If you use a command object instead of a recordset object, then you can save your resources. Recordsets use more memory and CPU.
(ii)You can pass parameters in a very safe way, which is guaranteed not to be injected. (There is one silly exception: If the SP's code just creats another SQL statement and runs it!!) So you never need to check for single quotes, semicolons, etc.

3) As deighc said, the connection object, and the command object are two seperate things. Their names show this too! The connection object is needed when you want to connect to a DB, and a command/recordset/field/parameter/record property can do nothing to a DB without such a connection. On the other hand, after connecting to a database, you need to send your orders to it. The lazy method (which is open to injection) is to simply use conn.execute.

4)  Final thing:
The other great benefit in SPs:   The ASP programmer looks at the SP as a black box. He sends things to it as parameters, and recieve things in a recordset. The only thing he should know is the name of the table columns, name of the SP, and name of the desired parameters, and their types. So the SQL programmer can change the SQL part, without needing to modify a line of an ASP file. The other benefit is, when a single SQL command is going to be repeated in several ASP files, why not to have ONE instance of that code as an SP?

From the moment I learnt SP, I've never ran a code with direct SQL statements!

Wish I can help
Huji
0
 
aprestoAuthor Commented:
Thanks everyone, thanks for the contribution Huji, helped out alot, Same with the rest of you!good info here for the EE search :o)!

Ciao for now

Apresto
0
 
hujiCommented:
Ciao!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now