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

[Jason B. <jb...@gm...>]

Do I need to quote the position parameter?

like:

INSERT INTO TEST  VALUES (':1', null null)

as opposed to:

 INSERT INTO TEST  VALUES (:1, null null)


On Tue, Jul 6, 2010 at 11:43 AM, Anthony Tuininga <
ant...@gm...> wrote:

> 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
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>