Anthony Tuininga | 5 Jul 22:10 2010
Picon

Re: setinputsizes problem?

Ok, let me see if I can clear things up. If you want to use positional
parameters, you need to do the following:

cursor.setinputsizes(cx_Oracle.CLOB)
cursor.execute("insert.... values (:1)", (clobValue,))

cursor.setinputsizes(None, None, cx_Oracle.CLOB)
cursor.execute("insert.... values (:1, :2, :3), (1, "String", clobValue))

If you want to use named parameters, you need to do the following:

cursor.setinputsizes(value1 = cx_Oracle.CLOB)
cursor.execute("insert.... values (:value1)", value1 = clobValue)

cursor.setinputsizes(value3 = cx_Oracle.CLOB)
cursor.execute("insert.... values (:value1, :value2, :value3)", value1
= 1, value2 = "String", value3 = clobValue)

Where clobValue refers to the actual string contents.

Hopefully that explains things well enough? If not, show me your exact
code and I'll attempt to show you where you are going wrong.

Anthony

On Mon, Jul 5, 2010 at 12:56 PM, Jason Boorn <jboorn@...> wrote:
>
> I set up clobs as {}, inputsizes as {} and populate a single value in the
> SQL statement:
>
(Continue reading)

Jason Boorn | 6 Jul 15:43 2010
Picon

Re: setinputsizes problem?

The named parameter option is not really going to work as well for me because my sql is dynamically generated.  So I'm trying to get the position parameter piece working.
 
In the code below, I set up an array containing 20 objects (there are 20 columns in this table) named inputsizes.  This goes through.  The code breaks on trying to execute.  I get the error:
 

Could not insert temp: ORA-01036: illegal variable name/number

The code below generates a sql execution string by creating an insert clause (with columns) and a values clause (with values to insert):
 
             while staging_row != None:
                sqlstring = "INSERT INTO %s (" % sectionconfig["TEMPORARYTABLE"]
                valuesstring = " VALUES ("
                clobs = []
                inputsizes = []
                firstelem = True
                for colname in colnames:
                    curval = staging_row[colname] 
                    if (firstelem == True):
                        firstelem = False
                    else:
                        sqlstring += ","
                        valuesstring += ","
                    sqlstring += colname
                    if (len(curval) > 0):
                        if (len(curval) > 4000):
                            clobs.append(curval)
                            valuesstring += ":" + str(len(clobs))
                            inputsizes.append(cx_Oracle.CLOB)
                        else:
                            inputsizes.append(None)
                            valuesstring += curval
                    else:
                        inputsizes.append(None)
                        valuesstring += "null"
                executestring = sqlstring + ")" + valuesstring + ")"
                try:
                    if (len(clobs) == 0):
                        destination_cursor.execute(executestring)
                    else:
                        destination_cursor.setinputsizes(*inputsizes)
                        destination_cursor.execute(executestring, *clobs)
                       
                except Exception:
                    e = sys.exc_info()[1]
                    log.error("Could not insert temp: %s" % e)
                    log.error(executestring)
                   
                staging_row = rowgenerator.next()
                if (rownum % 1000 == 0):
                    destinationConnection.commit()
 
 
My execute string looks like:
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')
 
which appears to be correct, and my clobs array contains one string which matches the value I want to include in position 1.

Thanks for taking the time to look at this - it's been a problem for me for a few days now.
 
On Mon, Jul 5, 2010 at 4:10 PM, Anthony Tuininga <anthony.tuininga-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
Ok, let me see if I can clear things up. If you want to use positional
parameters, you need to do the following:

cursor.setinputsizes(cx_Oracle.CLOB)
cursor.execute("insert.... values (:1)", (clobValue,))

cursor.setinputsizes(None, None, cx_Oracle.CLOB)
cursor.execute("insert.... values (:1, :2, :3), (1, "String", clobValue))

If you want to use named parameters, you need to do the following:

cursor.setinputsizes(value1 = cx_Oracle.CLOB)
cursor.execute("insert.... values (:value1)", value1 = clobValue)

cursor.setinputsizes(value3 = cx_Oracle.CLOB)
cursor.execute("insert.... values (:value1, :value2, :value3)", value1
= 1, value2 = "String", value3 = clobValue)

Where clobValue refers to the actual string contents.

Hopefully that explains things well enough? If not, show me your exact
code and I'll attempt to show you where you are going wrong.

Anthony

On Mon, Jul 5, 2010 at 12:56 PM, Jason Boorn <jboorn-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> I set up clobs as {}, inputsizes as {} and populate a single value in the
> SQL statement:
>
> INSERT INTO . . . VALUES (:value1, . . .)
>
> clobs['value1'] = <some string>
> inputsizes['value1']=cx.Oracle
>
> I call:
>
> destination_cursor.setinputsizes(inputsizes)
>
> destination_cursor.execute(executestring, clobs)
>
> I get an error:
>
> Could not insert temp: Variable_TypeByPythonType(): unhandled data type
>
> This was the original error which led me to use an array for clobs in the
> first place.  Ideas?
>
>
>
> On Sun, Jul 4, 2010 at 5:27 PM, Amaury Forgeot d'Arc <amauryfa-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> wrote:
>>
>> 2010/7/4 Jason Boorn <jboorn-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>:
>> > now I get the following:
>> >
>> >
>> > ORA-01036: illegal variable name/number
>> >
>> > I tried defining the input by both position (:1) and by named argument
>> > (:value1)
>> >
>> > By position,
>> > My SQL expression reads : INSERT INTO . . . values (:1, null . . .)
>> > And my execution is (cursor.execute(sql, *clobs))
>>
>> Here it's the other way round :-)
>> The documentation of cursor.execute:
>> http://cx-oracle.sourceforge.net/html/cursor.html#Cursor.execute
>> states that the parameters must be given as one sequence.
>> Try with
>>   cursor.execute(sql, *clobs)
>>
>>
>> --
>> Amaury Forgeot d'Arc
>>
>>
>> ------------------------------------------------------------------------------
>> 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-oracle-users-5NWGOfrQmneHXe+LvDLADg@public.gmane.orgrceforge.net
>> 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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> 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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
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-oracle-users@...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
Anthony Tuininga | 6 Jul 15:57 2010
Picon

Re: setinputsizes problem?

On Tue, Jul 6, 2010 at 7:43 AM, Jason Boorn <jboorn@...> wrote:
> The named parameter option is not really going to work as well for me
> because my sql is dynamically generated.  So I'm trying to get the position
> parameter piece working.

Sure. You can always do what I did before positional parameters were
supported: generate names like this

"v%d" % i

That's not ideal but its a fallback position if all else fails. :-)

> In the code below, I set up an array containing 20 objects (there are 20
> columns in this table) named inputsizes.  This goes through.  The code
> breaks on trying to execute.  I get the error:
>
>
> Could not insert temp: ORA-01036: illegal variable name/number

That means that the SQL string you generated and the parameters you
are passing don't match. If my suggestion below doesn't help, please
send the SQL string and a (small) representation of what you are
trying to pass.

> The code below generates a sql execution string by creating an insert clause
> (with columns) and a values clause (with values to insert):
>
>              while staging_row != None:
>                 sqlstring = "INSERT INTO %s (" %
> sectionconfig["TEMPORARYTABLE"]
>                 valuesstring = " VALUES ("
>                 clobs = []
>                 inputsizes = []
>                 firstelem = True
>                 for colname in colnames:
>                     curval = staging_row[colname]
>                     if (firstelem == True):
>                         firstelem = False
>                     else:
>                         sqlstring += ","
>                         valuesstring += ","
>                     sqlstring += colname
>                     if (len(curval) > 0):
>                         if (len(curval) > 4000):
>                             clobs.append(curval)
>                             valuesstring += ":" + str(len(clobs))
>                             inputsizes.append(cx_Oracle.CLOB)
>                         else:
>                             inputsizes.append(None)
>                             valuesstring += curval
>                     else:
>                         inputsizes.append(None)
>                         valuesstring += "null"
>                 executestring = sqlstring + ")" + valuesstring + ")"
>                 try:
>                     if (len(clobs) == 0):
>                         destination_cursor.execute(executestring)
>                     else:
>                         destination_cursor.setinputsizes(*inputsizes)

*** LOOK HERE ***

>                         destination_cursor.execute(executestring, *clobs)

You should do this instead:

destination_cursor.execute(executestring, clobs)

Note the removal of the "*". That "*" expands the parameters. Since
the parameter you are passing is a single element sequence, what
actually happens is the string itself is expanded and as many
parameters as there are characters in the string is passed! I'm sure
that's not what you wanted!

As for why execute() and setinputsizes() are different, consult the DB API. :-)

>                 except Exception:
>                     e = sys.exc_info()[1]
>                     log.error("Could not insert temp: %s" % e)
>                     log.error(executestring)
>
>                 staging_row = rowgenerator.next()
>                 if (rownum % 1000 == 0):
>                     destinationConnection.commit()
>
>
> My execute string looks like:
> 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')
>
> which appears to be correct, and my clobs array contains one string which
> matches the value I want to include in position 1.
> Thanks for taking the time to look at this - it's been a problem for me for
> a few days now.

You're welcome. Hopefully this works for you.

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
Jason Boorn | 6 Jul 16:52 2010

Re: setinputsizes problem?

Ya, I already tried it without the * - same error.
 
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?):

 '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')
 
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?
 
Apologies is this is a very naive question.
 
 


 
On Tue, Jul 6, 2010 at 9:57 AM, Anthony Tuininga <anthony.tuininga-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
On Tue, Jul 6, 2010 at 7:43 AM, Jason Boorn <jboorn-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> The named parameter option is not really going to work as well for me
> because my sql is dynamically generated.  So I'm trying to get the position
> parameter piece working.

Sure. You can always do what I did before positional parameters were
supported: generate names like this

"v%d" % i

That's not ideal but its a fallback position if all else fails. :-)

> In the code below, I set up an array containing 20 objects (there are 20
> columns in this table) named inputsizes.  This goes through.  The code
> breaks on trying to execute.  I get the error:
>
>
> Could not insert temp: ORA-01036: illegal variable name/number

That means that the SQL string you generated and the parameters you
are passing don't match. If my suggestion below doesn't help, please
send the SQL string and a (small) representation of what you are
trying to pass.

> The code below generates a sql execution string by creating an insert clause
> (with columns) and a values clause (with values to insert):
>
>              while staging_row != None:
>                 sqlstring = "INSERT INTO %s (" %
> sectionconfig["TEMPORARYTABLE"]
>                 valuesstring = " VALUES ("
>                 clobs = []
>                 inputsizes = []
>                 firstelem = True
>                 for colname in colnames:
>                     curval = staging_row[colname]
>                     if (firstelem == True):
>                         firstelem = False
>                     else:
>                         sqlstring += ","
>                         valuesstring += ","
>                     sqlstring += colname
>                     if (len(curval) > 0):
>                         if (len(curval) > 4000):
>                             clobs.append(curval)
>                             valuesstring += ":" + str(len(clobs))
>                             inputsizes.append(cx_Oracle.CLOB)
>                         else:
>                             inputsizes.append(None)
>                             valuesstring += curval
>                     else:
>                         inputsizes.append(None)
>                         valuesstring += "null"
>                 executestring = sqlstring + ")" + valuesstring + ")"
>                 try:
>                     if (len(clobs) == 0):
>                         destination_cursor.execute(executestring)
>                     else:
>                         destination_cursor.setinputsizes(*inputsizes)

*** LOOK HERE ***

>                         destination_cursor.execute(executestring, *clobs)

You should do this instead:

destination_cursor.execute(executestring, clobs)

Note the removal of the "*". That "*" expands the parameters. Since
the parameter you are passing is a single element sequence, what
actually happens is the string itself is expanded and as many
parameters as there are characters in the string is passed! I'm sure
that's not what you wanted!

As for why execute() and setinputsizes() are different, consult the DB API. :-)

>                 except Exception:
>                     e = sys.exc_info()[1]
>                     log.error("Could not insert temp: %s" % e)
>                     log.error(executestring)
>
>                 staging_row = rowgenerator.next()
>                 if (rownum % 1000 == 0):
>                     destinationConnection.commit()
>
>
> My execute string looks like:
> 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')
>
> which appears to be correct, and my clobs array contains one string which
> matches the value I want to include in position 1.
> Thanks for taking the time to look at this - it's been a problem for me for
> a few days now.

You're welcome. Hopefully this works for you.

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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
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-oracle-users@...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
Anthony Tuininga | 6 Jul 17:43 2010
Picon

Re: setinputsizes problem?

On Tue, Jul 6, 2010 at 8:52 AM, Jason Boorn <jboorn@...> 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
Jason Boorn | 6 Jul 22:42 2010
Picon

Re: setinputsizes problem?

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 <anthony.tuininga-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
On Tue, Jul 6, 2010 at 8:52 AM, Jason Boorn <jboorn-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
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-oracle-users@...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
Anthony Tuininga | 6 Jul 23:35 2010
Picon

Re: setinputsizes problem?

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 <jboorn@...> 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
> <anthony.tuininga@...> wrote:
>>
>> On Tue, Jul 6, 2010 at 8:52 AM, Jason Boorn <jboorn@...> 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-oracle-users@...
>> 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-oracle-users@...
> 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
Jason Boorn | 6 Jul 23:43 2010
Picon

Re: 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 <anthony.tuininga-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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 <jboorn-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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
> <anthony.tuininga-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>>
>> On Tue, Jul 6, 2010 at 8:52 AM, Jason Boorn <jboorn-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
>> 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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> 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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
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-oracle-users@...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
Anthony Tuininga | 7 Jul 00:33 2010
Picon

Re: setinputsizes problem?

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 <jboorn@...> 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
> <anthony.tuininga@...> 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 <jboorn@...> 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
>> > <anthony.tuininga@...> wrote:
>> >>
>> >> On Tue, Jul 6, 2010 at 8:52 AM, Jason Boorn <jboorn@...> 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-oracle-users@...
>> >> 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-oracle-users@...
>> > 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-oracle-users@...
>> 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-oracle-users@...
> 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
Weber, Geoffrey | 7 Jul 14:23 2010

Re: setinputsizes problem?

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 [jboorn@...]
Sent: Tuesday, July 06, 2010 4:43 PM
To: cx-oracle-users@...
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
<anthony.tuininga@...<mailto:anthony.tuininga@...>> 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
<jboorn@...<mailto:jboorn@...>> 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
>
<anthony.tuininga@...<mailto:anthony.tuininga@...>> wrote:
>>
>> On Tue, Jul 6, 2010 at 8:52 AM, Jason Boorn
<jboorn@...<mailto:jboorn@...>> 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-oracle-users@...<mailto:cx-oracle-users@...>
>> 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-oracle-users@...<mailto:cx-oracle-users@...>
> 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-oracle-users@...<mailto:cx-oracle-users@...>
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
Jason Boorn | 7 Jul 21:23 2010
Picon

Re: setinputsizes problem?

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 <Geoffrey.Weber-9sMr025MA47QT0dZR+AlfA@public.gmane.org> 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 [jboorn-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org]
Sent: Tuesday, July 06, 2010 4:43 PM
To: cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
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 <anthony.tuininga-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org<mailto:anthony.tuininga <at> gmail.com>> 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 <jboorn-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org<mailto:jboorn-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>> 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
> <anthony.tuininga-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org<mailto:anthony.tuininga-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>> wrote:
>>
>> On Tue, Jul 6, 2010 at 8:52 AM, Jason Boorn <jboorn-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org<mailto:jboorn-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>> 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-oracle-users-5NWGOfrQmneRv+LV9MX5uuRhgaa4a2kL@public.gmane.orgnet<mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org>
>> 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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org<mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org>
> 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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org<mailto:cx-oracle-users-5NWGOfrQmneHXe+LvDLADg@public.gmane.orgrceforge.net>
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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
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-oracle-users@...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
Anthony Tuininga | 8 Jul 00:34 2010
Picon

Re: setinputsizes problem?

On Wed, Jul 7, 2010 at 1:23 PM, Jason Boorn <jboorn@...> wrote:
> 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

You're welcome.

> 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 wouldn't suggest it even if technically you could get away with it.
:-) For setinputsizes(), you would need to do the following:

keywordArgs = {}
keywordArgs["value1"] = cx_Oracle.NUMBER
keywordArgs["value2"] = cx_Oracle.CLOB
cursor.setinputsizes(**keywordArgs)

OR

cursor.setinputsizes(value1 = cx_Oracle.NUMBER, value2 = cx_Oracle.CLOB)

> 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.

BTW, I use setinputsizes() as rarely as possible. The only time its
worth using is when you plan to execute a statement more than one time
in a row and the format of the data changes between executions -- or a
value is null (None) in one iteration but has a value in subsequent
iterations. In all other situations its probably faster to avoid the
setinputsizes() call.

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

Gmane