Re: [cx-oracle-users] Update problem

[Jesse G. L. <jg...@be...>]

On Wed, 17 Jan 2007 10:32:03 +0100
<dan...@co...> wrote:

> On Tuesday, January 16, 2007 10:15 PM, Jesse G. Lands wrote:
> 
> > I have some code that works fine when I query, but when try an
> > update statement I get a response back of
> > "Expecting string or none"
> 
> [snip]
> 
> > 	     sql = """UPDATE T_PERSONNEL SET last_name =
> > '%s',first_name = '%s' WHERE login = '%s'""" % (row['Last Name'],
> > row['First Name'], row['ID'])
> >          print sql
> >          cursor.execute(sql)
> 
> I am not sure about the readexcel() functionality, but I assume
> row['ID'] returns a number and not a string.
> 
> I strongly recommend to use bind variables instead of constructing the
> SQL string manually. It is faster and safer. Refer for example to
> http://www.joelonsoftware.com/items/2006/11/01.html for an explanation
> why bind variables are the way to go.
> 
> So a better approach would be:
> 
> sql = """
> UPDATE T_PERSONNEL
>    SET last_name = :lastname,
>        first_name = :firstname
>  WHERE login = :login
> """
> cursor.prepare(sql)
> for sheet in sheetnames:
>     for row in xl.getiter(sheet):
>         cursor.execute(None, [
>                row['Last Name'],
>                row['First Name'],
>                row['ID']
>                ])
> cursor.close()
> 
> Now implicit data type conversion takes place, if necessary. Of course
> you can always make explicit data type conversion if you would like
> to.
> 
> HTH,
> Daniel
Daniel,
ID actually refers to a login ID for a gui frontend to the Oracle
database.  They are something along the lines of "User001" so strings
and not numbers.  Thanks for the suggestion though.  I will look at
implementing your change recommendation.
Jesse
-- 
JLands
Arch Current                   
Registered Linux User #290053
 We fashion wood for a house,
but it is the emptiness inside
that makes it livable.