[DB-SIG] How to use Prepared Statment?

Andy Todd andy47 at halfcooked.com
Fri Feb 3 09:28:52 CET 2006


python eager wrote:
> Hi this my code snippet. This code will not execute. And also this code 
> raise error.
>  
> DatabaseError: ORA-01036: illegal variable name/number
>  
> What is the problem. Please give me a solution. Is there any problem is 
> sql statment please give the correct statment, i will apply that statment.
>  
>  
> _Code Snippet :_
> __ 
> *def view_one(self,pid):
> *sql_pStmt = "SELECT FIRSTNAME,LASTNAME FROM PERSONALDETAILS   
>                      WHERE PID = ?" 
> cursor.execute(sql_pStmt,pid)
>  
> Thank you
>  
> regards
> Python Eager
> 

 From the cx_Oracle documentation (section 3) [1];

"""
execute(statement, [parameters], **keywordParameters)
   Execute a statement against the database. Parameters may be passed as 
a dictionary or sequence or as keyword arguments. If the arguments are a 
dictionary, the values will be bound by name and if the arguments are a 
sequence the values will be bound by position."
"""

 From the DB-API specification [2];

"""
   paramstyle

             String constant stating the type of parameter marker
             formatting expected by the interface. Possible values are
             [2]:

                 'qmark'         Question mark style,
                                 e.g. '...WHERE name=?'
                 'numeric'       Numeric, positional style,
                                 e.g. '...WHERE name=:1'
                 'named'         Named style,
                                 e.g. '...WHERE name=:name'
                 'format'        ANSI C printf format codes,
                                 e.g. '...WHERE name=%s'
                 'pyformat'      Python extended format codes,
                                 e.g. '...WHERE name=%(name)s'
"""

cx_Oracle uses the 'named' parameter style, your code uses the qmark 
style. To find out which style is applicable check the paramstyle 
attribute of your connection object. e.g.;

 >>> import cx_Oracle
 >>> myConnection = cx_Oracle.connect('andy47/andy47 at andy47')
 >>> myConnection.paramstyle
'named'

[1] http://starship.python.net/crew/atuining/cx_Oracle/html/cursorobj.html
[2] http://www.python.org/peps/pep-0249.html

Regards,
Andy
-- 
--------------------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/


More information about the DB-SIG mailing list