Re: [cx-oracle-users] setinputsizes problem?
Brought to you by:
atuining
Menu
▾
▴
Re: [cx-oracle-users] setinputsizes problem?
|
From: Anthony T. <ant...@gm...> - 2010-07-06 22:33:57
|
Not likely or I'm sure there would be a lot of screams coming from
Oracle using people! This script does much the same thing using the
test data used in the test suite for cx_Oracle.
cursor.execute("""
select * from TestDates
where DateCol = to_date('2002-12-10 02:24:00', 'YYYY-MM-DD HH24:MI:SS')
and IntCol between :1 and :2""",
(1, 5))
for row in cursor:
print row
Of course, if you want to remove the colons, go ahead. The to_date()
method doesn't require them but you are including them in the format
you are using. They are not required -- its just much easier to read.
Anthony
On Tue, Jul 6, 2010 at 3:43 PM, Jason Boorn <jb...@gm...> wrote:
> 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...> 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...> 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...> 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
>> >
>> >
>> >
>> > ------------------------------------------------------------------------------
>> > 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
>> >
>> >
>>
>>
>> ------------------------------------------------------------------------------
>> 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
>
>
> ------------------------------------------------------------------------------
> 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
>
>
|
