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 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.


    <<Previous: More on the Command Object ^Up to Mayukh's World^ Next: Creating Parameter Objects >>

    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.