Re: [cx-oracle-users] setinputsizes problem?
Brought to you by:
atuining
Menu
▾
▴
Re: [cx-oracle-users] setinputsizes problem?
|
From: Weber, G. <Geo...@PA...> - 2010-07-07 12:50:47
|
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
|
