Re: [cx-oracle-users] setinputsizes problem?

[Anthony T. <ant...@gm...>]

On Tue, Jul 6, 2010 at 8:52 AM, Jason Boorn <jb...@gm...> wrote:
> Ya, I already tried it without the * - same error.

Hmm, interesting. The problem with the illegal variable name/number
problem is that its almost impossible to diagnose the problem. You
have to carefully examine the SQL looking for any colons that may have
strayed outside of quotes or where an extra character is found after
the colon without a space as you were intending, etc. Eventually you
will find it but its always irritating having to search for it -- it
would be nice if Oracle went to the trouble of telling you __what__
variable name/number was illegal!

To clarify again, the method signature is

cursor.execute(sql, parameters)

Where parameters is either a dictionary (named parameters) or sequence
(positional parameters). So if you have a tuple

clobs = ('This is the clob value',)

or sequence

clobs = ['This is the clob value']

You would call

cursor.execute(sql, clobs)

Likewise, if you have keywords you would do the following:

clobs = { "value1" : "This is the clob value" }
cursor.execute(sql, clobs)

I have added the ability to do the following as well since it is often
convenient

cursor.execute(sql, value1 = "This is the clob value")

The first form is useful if you are building up the parameters
dynamically and the second for known queries with known numbers of
parameters.

> The final SQL string that gets built is:
>
> INSERT INTO import_CAMTARGET
> (ACCOUNTKEY,CAMPAIGNKEY,CAMTARGETKEY,ZIP,DOMAIN,SEARCHTERM,SEARCHANYALL,COOKIE,RDBPROFILE,NEGATETARGET,CITY,AREACODE,SEGMENTANYALL,RECORDSTATE,WHENCREATED,WHENMODIFIED,WHOCREATED,WHOMODIFIED,TEMPLATEID,ALLNONMOBILEDEVICE)
> VALUES
> (1789,3946029,1765718,null,null,null,'A',null,null,'N',:1,null,'A','L',to_date('2010-06-03
> 16:28:59','YYYY-MM-DD HH24:MI:SS'),to_date('2010-06-18 13:37:17','YYYY-MM-DD
> HH24:MI:SS'),'matthew.kay_guardian.co.uk','APIMaxifier',null,'N')
>
> My clobs array contains one string element (note the string contains newline
> characters could this be an issue?):

No, the newlines are not a problem. Using bind variables means that
you don't have to worry about special characters of any sort. The data
is passed directly to Oracle without interpretation.

>  'GB:NEW MILTON
> GB:WALTHAM FOREST
> GB:CAMDEN
> GB:CHELMSFORD
> GB:SOMPTING
> GB:LEATHERHEAD
> GB:WINSFORD
> GB:GAINSBOROUGH
> GB:DINGWALL
> GB:WEST KILBRIDE
> GB:CHESTERFIELD
> GB:BLETCHLEY
> GB:HALESWORTH
> GB:GUILDFORD
> GB:RAGLAN
> GB:HOLLINGWORTH
> GB:ROSLIN
> GB:WOOTTON
> GB:NORMANDY
> GB:EDENBRIDGE
> GB:HEATH
> GB:NEWTOWNABBEY
> GB:TAMWORTH
> GB:TOWNHILL
> GB:FAILSWORTH
> GB:KETTERING
> GB:PELTON
> GB:RYDE
> GB:LYNDHURST
> GB:NORTH BERWICK
> GB:BIRKENHEAD
> GB:LOCHEARNHEAD
> GB:DARVEL
> GB:HOLMES CHAPEL
> GB:STORRINGTON
> GB:SPRINGFIELD
> GB:CARLUKE
> GB:BURY ST EDMUNDS
> GB:DORKING
> GB:CLOGHER
> GB:CROMER
> GB:LANCING
> GB:LONGFIELD
> GB:BROADFORD
> GB:ASHTON-UNDER-LYNE
> GB:CROWLAS
> GB:HOLYWELL
> GB:LOOE
> GB:STAPLEFORD
> GB:PAISLEY
> GB:PORTRUSH
> GB:PLYMPTON
> GB:BRACKLEY
> GB:FARNHAM
> GB:GOLBORNE
> GB:HASLINGDEN
> GB:GRANTOWN-ON-SPEY
> GB:GERRARDS CROSS
> GB:MARKET HARBOROUGH
> GB:CLYDACH
> GB:LYDIATE
> GB:DEVIZES
> GB:BIRTLEY
> GB:STRATHPEFFER
> GB:SHOREHAM
> GB:HANLEY
> GB:WALSINGHAM
> GB:WOODLEY
> GB:BLAIRGOWRIE AND RATTRAY
> GB:BARNET
> GB:ASHLEY
> GB:NOTTINGHAM
> GB:MAUCHLINE
> GB:PINXTON
> GB:WOKINGHAM'
>
> As far as using named parameters (which I'm happy to do to get this
> working): I understand how to establish the named parameters, but not how to
> get them into the structure that is passed to execute.  As I understand it,
> we need to pass in:
>
> cursor.execute(sql, value1:'asdfasdfasdfasdf', value2:'sadfasdfasdfasdf')

See above. Either do this:

parameters = { "value1" : "asdfadfadfadsfa", "value2" : "asfadsfadsfadsf" }
cursor.execute(sql, parameters)

or do this

cursor.execute(sql, value1 = "asadfadsfsadf", value2 = "adfadfadsfad")

> I can generate the string "value1" and I can generate the string
> 'value1=asdfasdfasdfasdf' but I don't see how either of those help me - I'm
> not passing a string, I'm passing a variable/string tuple correct?  How do I
> dynamically generate that?

You can do this to build the parameters up dynamically.
parameters = {}
parameters["value1"] = "adfadsfadf"
parameters["value2"] = "asdfsadfafasdfdsaf"
cursor.execute(sql, parameters)

> Apologies is this is a very naive question.

No problem. :-)

Anthony