The World of Mayukh Bose

<< Back to Main Page Mayukh's world: e-mail me | about
Mayukh's World: Python Tutorials: ADO and Python Thursday, November 23, 2017
Python and ADO
  • 0. Introduction
  • 1. What is ADO
  • 2. Basic Objects
  • 3. Using Parameters
  • 4. ADO/Python Basics
  • 5. Connection Object
  • 6. RecordSet Object
  • 7. Command Object
  • 8. More Command Fun
  • 9. Command and Procs
  • 10. Creating Params
  • 11. Links and Credits
  • My Free Software
  • Delphi/C++ Builder
  • Pocket PC
  • FreeSpeech Chat
  • C/C++ Freebies
  • Perl
  • Python
  • Ruby
  • My Python Code
  • ADO and Python
  • Addlice
  • IE Controller
  • Python Tutorials and Software e-mail me

    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.


    <<Previous: Command Objects and Stored Procedures ^Up to Mayukh's World^ Next: Links and Credits >>

    Copyright © 2004 Mayukh Bose. All rights reserved.
    This work may be freely reproduced provided this copyright notice is preserved.
    OPTIONAL: Please consider linking to this website (http://www.mayukhbose.com/) as well.