|
Chapter 10: Creating Parameters
In the previous chapters, we created various types of parameter objects (i.e.) varchar, integer etc.
If you read the code carefully, you'll notice some parameter objects take more arguments
than others. This page details how to create some common parameter object types.
|
Creating ADO Parameters of Various Types
from win32com.client import Dispatch
from adoconstants import *
#
# We assume oCmd is an already created ADODB.Command object
#
# Creating a parameter of type integer
oIntParam = oCmd.CreateParameter('intparam', adInteger, adParamInput)
# Creating a parameter of type varchar (assuming max size is 20 chars)
oStrParam = oCmd.CreateParameter('stringparam', adVarChar, adParamInput, 20)
# Another way to do the same thing.
oStrParam = oCmd.CreateParameter('stringparam', adVarChar, adParamInput)
oStrParam.Size = 20
# Creating a parameter of type char (assuming max size is 30 chars)
oStrParam = oCmd.CreateParameter('stringparam', adChar, adParamInput, 30)
# Another way to do the above
oStrParam = oCmd.CreateParameter('stringparam', adChar, adParamInput)
oStrParam.Size = 30
# Creating a parameter of type widechar (assuming max size is 20 chars)
oStrParam = oCmd.CreateParameter('stringparam', adWChar, adParamInput, 20)
# Creating a parameter of type wide varchar (assuming max size is 30 chars)
# widechar and wide varchar are suitable for holding Unicode characters.
oStrParam = oCmd.CreateParameter('stringparam', adVarWChar, adParamInput, 30)
# Another way to do the above
oStrParam = oCmd.CreateParameter('stringparam', adVarWChar, adParamInput)
oStrParam.Size = 30
# Creating a parameter of type DBDate
oDateParam = oCmd.CreateParameter('dateparam', adDBDate, adParamInput)
# Creating a parameter of type numeric (assuming field is numeric(6,2))
oNumParam = oCmd.CreateParameter('numparam', adNumeric, adParamInput)
oNumParam.Precision = 6
oNumParam.NumericScale = 2
As you can see in the above code, different parameter types take different arguments or may need
certain other attributes (such as Size, Precision, NumericScale etc.) to be set. If you don't
set all the attributes for a parameter correctly, then when you try to Append() it to the command
object (or Execute() the command object):
oCmd.Parameters.Append(oStrParam)
python will throw ugly COM error messages like the following:
com_error: (-2147352567, 'Exception occurred.', (0, 'ADODB.Parameters',
'Parameter object is improperly defined. Inconsistent or incomplete information was provided.',
'C:\WINNT\HELP\ADO270.CHM', 1240657, -2146824580), None)
com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Provider for SQL Server',
'The precision is invalid.', None, 0, -2147467259), None)
If you see these types of messages, you now know what the problem is.
|
This brings the tutorial to an end. We have covered the usage of some of the common ADO objects
and how to access them from Python. All that remains is to post a few handy links and credits
for this article.
|
|