The World of Mayukh Bose
<< Back to Main Page Mayukh's world: e-mail me | about
Mayukh's World: Python Tutorials: ADO and Python Tuesday, August 14, 2018
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 3: What is a Parameter and Why Use It?

    Parameters are a model that is used by several interfaces such as DBI, BDE, ODBC etc. ADO also provides for using them. However novice programmers usually don't know about them and never bother to use them at all. We will investigate several reasons why they are such a good thing in this chapter.

    Consider the following situation. You need to read several lines from a text file and insert each one into a SQL table. A naive way to do this would be:
      1. Open the text file.
      2. Read a line from the text file.
      3. Prepare a SQL statement using string concatenation:
         sql = "INSERT INTO tablename(column1) VALUES (' " + line + " ')"
      4. Execute the SQL statement that we prepared above. The database engine validates our statement as legal SQL, compiles it and then executes it.
      5. Check if there are any more lines in the file. If there are any more lines, repeat from step 2.

    A person who has dabbled with SQL before might note a problem with the above approach. If the line read in step 2 contains a quote character ('), then the SQL string in step 3 will not be formatted correctly. It will have similar problems if the line contains other reserved characters such as %, ?, newlines etc. To prevent this, we will need to rewrite step 3 as follows:
      3. Prepare a SQL statement using string functions to format the string properly:
         sql = "INSERT INTO tablename(column1) VALUES (' " + EscapeSpecialChars(line) + " ')"
    where EscapeSpecialChars() is a function you write to escape (or remove) the characters that may cause problems. Typically, this function should replace single quote (') with two single quotes ('') and similarly handle %, ? etc. The trouble is that different database engines might have different ideas of what characters are considered special and how they should be escaped.

    An additional problem with this approach is the fact that the SQL statement is being altered every time a line is read in. The only change to the SQL statement is the value of the variable "line", while the rest of it stays the same. The novice approach rebuilds the SQL string from scratch each time, which takes up more time. First, you have the overhead of joining strings together to form the SQL statement. Second, the database server will validate and compile the statement each time before executing it. This is a wasteful approach since the only thing that changes is the value of the line variable and there's no need to revalidate or recompile the statement after the first time around. Thirdly, there is the possiblity of SQL Injection, where a malicious person could run their own SQL statements on your server.

    A better approach is to use the parameter facility. Modern database servers are capable of taking a SQL statement with parameters and compiling it ahead of time. You can then pass a parameter value to it and execute it each time for new parameter values. This saves time because the database engine does not need to validate/recompile the statement each time. Instead it has the statement validated and compiled and simply puts the parameter value into it each time.

    When using parameters, the same problem can be solved as follows:
      1. Prepare a SQL statement using parameters and pass this to the database engine ahead of time. The database engine validates the statement as valid SQL, compiles it into bytecode and returns a handle which we can use to access this statement.
      2. Open the text file.
      3. Read a line from the text file.
      4. Execute the SQL statement via the handle we obtained in step 1 and pass the line as a parameter value. The database engine already has the statement validated and compiled, so all it does is substitute the parameter with the value that you pass to it.
      5. Check if there are any more lines in the file. If there are any more lines, repeat from step 3.

    Since the database engine has already validated/compiled our statement once, it doesn't bother to do it again for each new execution of the statement. When you have a lot of lines, this can result in increased performance. As an additional bonus, step 4 automatically knows to escape characters properly, so you don't have to write your own EscapeSpecialChars() function to do so. This makes SQL injection attacks impossible.

    It is possible to prepare a statement that takes multiple parameter arguments too. This allows you to pass values for multiple columns in a statement. You can also assign default values to parameters when creating them. Thus if you don't explicitly set the value of a parameter when executing the SQL statement, it will use the default value for that parameter. This is very handy if you need to insert multiple rows where the values of some columns only change sometimes.

    Parameters can also be used to supply values to a stored procedure object. This tutorial will demonstrate usage of parameters to execute both SQL queries as well as stored procedures. As you can see, the use of parameters has several advantages that make them essential for any serious database programmer.


    <<Previous: Basic ADO Objects ^Up to Mayukh's World^ Next: ADO and Python Basics >>

    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.