 |
Chapter 9: Command Objects and Stored Procedures
|
In the previous chapter, we saw how to use a Command object to perform insert, update
and delete queries on a database. In this chapter we will explore how to use a Command object
to execute stored procedures. The assumption here is that you have a database that supports
stored procedures (such as SQL Server or Oracle). First we will examine the Quick-N-Dirty
method to do things. The example assumes a SQL Server database engine, but it could easily be
modified for just about any database engine that supports stored procedures
(Oracle, InterBase, Firebird etc.) by merely changing the ConnectionString. The rest
of the code remains the same for any database engine. Assuming a SQL Server database engine
and a stored procedure called addPerson that takes 4 arguments, a naive way to handle stored
procedures would go like this:
|
Quick-N-Dirty Stored Procedures
from win32com.client import Dispatch
from adoconstants import *
# Create Connection object and connect to database.
oConn = Dispatch('ADODB.Connection')
oConn.ConnectionString = "Provider=SQLOLEDB.1;Data Source=192.168.1.100;" + \
"uid=my_user_name;pwd=my_password;database=my_database_name"
oConn.Open()
# Now ask the user for input
while 1:
# Ask the user for input
fname = raw_input("Enter First Name: ")
if (fname == ""):
break
lname = raw_input("Enter Last Name: ")
age = raw_input("Enter your age: ")
income = raw_input("Enter your income: ")
# Now prepare the stored procedure statement.
sql = "addPerson @first_name = '" + fname + "', " + \
"@last_name = '" + lname + "', " + \
"@age = " + age + ", " + \
"@income = " + income
# Now execute the stored proc statement that we prepared above.
oConn.Execute(sql)
# Close up and clean up
oConn.Close()
oConn = None
|
As in the previous chapter, this naive method suffers from the same two issues -- first, we're building
a new statement each time we receive input from the user when the only thing changing is the
parameter values. Secondly, a name like "O'Bannon" will still cause problems when we build the
SQL statement, unless we escape the characters properly ourselves. Now, let's see how to
overcome both problems using a Command object and Parameter objects.
|
Better Method for Stored Procedures
from win32com.client import Dispatch
from adoconstants import *
# Create DB and connect to database.
oConn = Dispatch('ADODB.Connection')
oConn.ConnectionString = "Provider=SQLOLEDB.1;Data Source=192.168.1.100;" + \
"uid=my_user_name;pwd=my_password;database=my_database_name"
oConn.Open()
# Now create a command object and prepare the query
oCmd = Dispatch('ADODB.Command')
oCmd.ActiveConnection = oConn
# Create a query that accepts four parameters
oCmd.CommandType = adCmdStoredProc
oCmd.CommandText = "addPerson @first_name = ?, @last_name = ?, @age = ?, @income = ?"
# Now create the Parameter objects
oParam1 = oCmd.CreateParameter('@first_name', adVarChar, adParamInput, 50)
oParam2 = oCmd.CreateParameter('@last_name', adVarChar, adParamInput, 50)
oParam3 = oCmd.CreateParameter('@age', adInteger, adParamInput)
oParam4 = oCmd.CreateParameter('@income', adNumeric, adParamInput)
oParam4.Precision = 6
oParam4.NumericScale = 2
oCmd.Parameters.Append(oParam1)
oCmd.Parameters.Append(oParam2)
oCmd.Parameters.Append(oParam3)
oCmd.Parameters.Append(oParam4)
# Request the query to be prepared ahead of time
oCmd.Prepared = True
while 1:
# Ask the user for input
fname = raw_input("Enter First Name: ")
if (fname == ""):
break
lname = raw_input("Enter Last Name: ")
age = raw_input("Enter your age: ")
income = raw_input("Enter your income: ")
# Set the Parameter values and execute our stored procedure
oParam1.Value = fname
oParam2.Value = lname
oParam3.Value = age
oParam4.Value = income
oCmd.Execute()
# Close and clean up
oCmd = None
oConn.Close()
oConn = None
In the above code, we create a Command object and assign it a SQL query which contains four
parameters. We then create four Parameter objects to correspond to the four stored procedure
parameters. Then we prepare the statement so that the database server knows ahead of time
that we intend to execute this statement multiple times. Then we request the user for input,
set the appropriate parameter values and execute the statement. This is more efficient because
we are not rebuilding the SQL statement each time we get a new set of values. Also, since we're
using Parameter objects, the code automatically escapes any apostrophe characters and
other special characters by itself. Hence an input of "O'Bannon" will be accepted and
processed correctly.
Note that in the above code, we created parameters of type adVarChar, adInteger and adNumeric.
If you look carefully, you'll notice that CreateParameter() was called with different numbers
of arguments depending on the parameter type and also that some parameter types need additional
properties to be set. Chapter 10 details how to create different parameter types.
|
Command Objects, Stored Procedures and RecordSets
|
Now we will examine how to execute a stored procedure that accepts parameters and returns a
RecordSet, using a Command object. We already saw how to do this with a Connection object back
when we examined the RecordSet object. This method is superior
since it accepts Parameters.
|
from win32com.client import Dispatch
from adoconstants import *
# Create DB and connect to database.
oConn = Dispatch('ADODB.Connection')
oConn.ConnectionString = "Provider=SQLOLEDB.1;Data Source=192.168.1.100;" + \
"uid=my_user_name;pwd=my_password;database=my_database_name"
oConn.Open()
# Now create a command object and prepare the query
oCmd = Dispatch('ADODB.Command')
oCmd.ActiveConnection = oConn
# Create a query that accepts a parameter
oCmd.CommandType = adCmdStoredProc
oCmd.CommandText = "selPersons @age = ?"
# Now create the Parameter object
oParam1 = oCmd.CreateParameter('@age', adInteger, adParamInput)
oCmd.Parameters.Append(oParam1)
# Request the query to be prepared ahead of time
oCmd.Prepared = True
while 1:
age = raw_input("Enter your age: ")
if (age == ""):
break
# Set the Parameter values and execute our stored procedure
oParam1.Value = age
(oRS, status) = oCmd.Execute()
# Print out the query results
while not oRS.EOF:
print oRS.Fields("first_name"), oRS.Fields("last_name")
oRS.MoveNext()
oRS.Close()
oRS = None
# Close and clean up
oCmd = None
oConn.Close()
oConn = None
TECH NOTE: On SQL Server, it may happen that your stored procedure executes fine from
the query analyzer and returns a result set, but doesn't work when you try to use it from ADO.
Instead, it may give you an error that oRS.EOF cannot be evaluated on a closed object. If this
happens, you need to add the line: SET NOCOUNT ON at the top of your procedure and
SET NOCOUNT OFF at the bottom of your procedure. If you don't have these two lines, the
program will complain that oRS is not open. The author spent a long time trying to figure out
where the bug lay in his code, before a little googling found the true solution. This is an
issue with ADO and SQL Server that is not specific to using Python alone.
|
|
In the previous few examples, we've created different parameter types. The next chapter will
merely revisit how to create different parameter types, so that they are all in one page.
|
|