Re: [cx-oracle-users] setinputsizes problem?
Brought to you by:
atuining
Menu
▾
▴
Re: [cx-oracle-users] setinputsizes problem?
|
From: Jason B. <jb...@gm...> - 2010-07-07 19:23:21
|
Geoff -
Those are already part of the SQL statement - they were never in the
parameters list.
I got this working (thanks Anthony) using a dictionary for the parameters
without a call to setinputsizes. I'm guessing this is not optimal, but I
could not get the call to setinputsizes to work as a dictionary, and I
wasn't sure if you could mix named parameters with array-style
setinputsizes.
I'm guessing that not using the setinputsizes function is not optimal, but I
can get it to work without it so that's what I'm going to do.
Thanks
On Wed, Jul 7, 2010 at 8:23 AM, Weber, Geoffrey
<Geo...@pa...>wrote:
> OK - your problem here is that you can't put "to_date()" or
> "to_timestamp()" functions in as part of the parameter list. What you need
> instead is to put those functions as part of the SQL statement itself:
>
> sql = '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 (:1, :2, :3, :4, :5... ,TO_DATE(:n, 'YYYY-MM-DD HH24:MI:SS'), :(n+1),
> ...)'
>
> and pass in your date strings just like any other bind value. Any value
> you pass in as parameters is interpreted literally and not as a function
> call by Oracle.
>
> so then you'd have:
> cursor.execute(sql, params)
>
> ...just like Anthony has been describing to you before (either tuple/list
> or dictionary forms).
>
> Hope this makes sense to you, and good luck!
>
> - Geoff
>
>
> ________________________________________
> From: Jason Boorn [jb...@gm...]
> Sent: Tuesday, July 06, 2010 4:43 PM
> To: cx-...@li...
> Subject: Re: [cx-oracle-users] setinputsizes problem?
>
> I just noticed something:
>
> > 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'),'','APIMaxifier',null,'N')
>
> I have time values which include colons in my SQL string. Is it possible
> these are confusing the parser? If so, is there a way to escape them?
>
> On Tue, Jul 6, 2010 at 5:35 PM, Anthony Tuininga <
> ant...@gm...<mailto:ant...@gm...>> wrote:
> No, do not quote the position parameter. Something like this is want you
> want:
>
> cursor.execute("insert into test values (:1, null, null)", ("This is
> the input string",))
>
> BTW, the test scripts can be a source of examples for how things can be
> written.
>
> Anthony
>
> On Tue, Jul 6, 2010 at 2:42 PM, Jason Boorn <jb...@gm...<mailto:
> jb...@gm...>> wrote:
> > 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...<mailto:ant...@gm...>> wrote:
> >>
> >> On Tue, Jul 6, 2010 at 8:52 AM, Jason Boorn <jb...@gm...<mailto:
> 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<
> http://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://sprint.com/first> --
> http://p.sf.net/sfu/sprint-com-first
> >> _______________________________________________
> >> cx-oracle-users mailing list
> >> cx-...@li...<mailto:
> cx-...@li...>
> >> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
> >
> ------------------------------------------------------------------------------
> > This SF.net email is sponsored by Sprint
> > What will you do first with EVO, the first 4G phone?
> > Visit sprint.com/first<http://sprint.com/first> --
> http://p.sf.net/sfu/sprint-com-first
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...<mailto:
> cx-...@li...>
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first<http://sprint.com/first> --
> http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...<mailto:
> cx-...@li...>
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
>
> ------------------------------------------------------------------------------
> 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
>
|
