Updating the database with two parameters : newbie looking for help 1

[noyes99]

I have a database, where I need to make an update like

X = ''' UPDATE TABLE A
SET COL1 = 'VAL1',
COL2 = :2
WHERE COL3 = :1'''


I am making a call like
c.execute(X, 123, 3)

and it is not working.

where c is db.cursor() and I am connecting using DCOracle2.

Can someone help?

I have absolutely no idea.

 

[ericbrunson]

It's been a while since I used DCOracle...

Are you sure you're not supposed to be using a list with the variables? Also, I think in the later versions of DCOracle2 that went to conform to the Python DB APT the naming of the bind parameters don't change the order that the values are in the list.

 

[ericbrunson]

A quick look at the docs show that, no, you aren't supposed to pass the params as a list, that must have been something else.

I think you problem stems from the fact that you can't use param names to re order the params.

UPDATE TABLE A
SET COL2 = :2
WHERE COL3 = :1

is the same as

UPDATE TABLE A
SET COL2 = :1
WHERE COL3 = :2

is the same as

UPDATE TABLE A
SET COL2 = :23
WHERE COL3 = :1925

Try reversing the order of the params in the execute call.

 

[noyes99]

Thanks guys. I ordered the params differently and it worked fine. Not sure why you should need to do that though, as long as you specify the values correctly

 

[ericbrunson]

Because, as I pointed out, the :1, :2, etc. don't affect which parameter gets used, only the position in the statment.

This was annoying to me in the change from DCOracle to DCOracle2 because some of my code would construct sql like (made up example):

UPDATE sometable
SET col1 = :1, col2 = :2
WHERE col3 = :1

And I could execute it with c.execute( sql, val1, val2 )

The conformance to the PyDB API made me have to write:

c.execute( sql, va1, val2, val1 )

Plus, it seemed to get rid of the ability to write this:

sql = "UPDATE sometable SET col1 = :col1 WHERE col2 = :col2"
dict = function.returning( { col1: 'val1', col2: 'val2' } )
c.execute( sql, dict )

Which made my classes extremely useful.