cx-oracle-users

[cx-oracle-users] setinputsizes problem?

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

Hello -

I'm using the cx_oracle module for an insertion whose sql is created
dynamically.  The relevant code is:

                 while staging_row != None:
                    sqlstring = sectionconfig["INSERTIONSQL1"]
                    valuesstring = " VALUES ("
                    rownum += 1
                    isfirst = True
                    clobs = []
                    inputsizes = []
                    for cidx in range(len(colnames)):
                        if (isfirst):
                            isfirst = False
                        else:
                            valuesstring += ","
                        try:
                            curval = staging_row[colnames[cidx]]
                            if (len(curval) > 4000):
                                clobs.append(curval)
                                inputsizes.append(cx_Oracle.CLOB)
                                valuesstring += ":" + str(len(clobs))
                            else:
                                inputsizes.append(None)
                                valuesstring += curval
                        except Exception:
                            log.exception("Unable to get field value)

                    executestring = sqlstring + valuesstring + ")"
                    #log.debug(executestring)
                    try:
                        #log.debug(executestring)
                        if (len(clobs) == 0):
                            destination_cursor.setinputsizes(inputsizes)
                            destination_cursor.execute(executestring)
                        else:
                            destination_cursor.execute(executestring, clobs)
                    except Exception:
                        e = sys.exc_info()[1]
                        log.error("Could not insert: %s" % e)
                        log.error(executestring)
I get the following error:
Could not insert: expecting an array of two elements [type, numelems]


I thought the argument to setinputsizes was supposed to be an array whose
length was the number of columns.  Why does it expect a 2-element array?
Note that I need to construct the sql dynamically and use CLOB by position,
so explicitly doing omething like setinputsizes(value1=cx_Oracle.CLOB, . .
.) isn't an option.

Running v5.03 for python 2.6

Thanks for any help.

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

[Amaury F. d'A. <ama...@gm...>]

Hi,

2010/7/3 Jason Boorn <jb...@gm...>:
[...]
>                             destination_cursor.setinputsizes(inputsizes)
[...]
> I get the following error:
> Could not insert: expecting an array of two elements [type, numelems]

You pass only one argument to cursor.setinputsize; this will define
only one variable, this is certainly not what you want. Try with
    destination_cursor.setinputsizes(*inputsizes)
to define multiple variables.

-- 
Amaury Forgeot d'Arc

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

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

Thank you, but I'm using python - could you tell me what kind of a structure
the argument should be?  And how to populate it?

My original function call was called with an array argument, an array
containing all of the fields being inserted.

On Sat, Jul 3, 2010 at 3:28 PM, Amaury Forgeot d'Arc <ama...@gm...>wrote:

> Hi,
>
> 2010/7/3 Jason Boorn <jb...@gm...>:
> [...]
> >                             destination_cursor.setinputsizes(inputsizes)
> [...]
> > I get the following error:
> > Could not insert: expecting an array of two elements [type, numelems]
>
> You pass only one argument to cursor.setinputsize; this will define
> only one variable, this is certainly not what you want. Try with
>    destination_cursor.setinputsizes(*inputsizes)
> to define multiple variables.
>
> --
> 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-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

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

[Amaury F. d'A. <ama...@gm...>]

2010/7/3 Jason Boorn <jb...@gm...>:
> Thank you, but I'm using python - could you tell me what kind of a structure
> the argument should be?  And how to populate it?
>
> My original function call was called with an array argument, an array
> containing all of the fields being inserted.

In the docs: http://cx-oracle.sourceforge.net/html/cursor.html#Cursor.setinputsizes
you should not call setinputsizes() with one argument, but with one
argument per variable.
since you already have a list containing the various sizes,
    cursor.setinputsizes(*inputsizes)
(note the * symbol) will "explode" the list and pass each list items
as separate arguments to the function.

-- 
Amaury Forgeot d'Arc

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

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

Thanks again - sorry for the confusion with the * operator - I hadn't used
it before.

On Sat, Jul 3, 2010 at 5:11 PM, Amaury Forgeot d'Arc <ama...@gm...>wrote:

> 2010/7/3 Jason Boorn <jb...@gm...>:
> > Thank you, but I'm using python - could you tell me what kind of a
> structure
> > the argument should be?  And how to populate it?
> >
> > My original function call was called with an array argument, an array
> > containing all of the fields being inserted.
>
> In the docs:
> http://cx-oracle.sourceforge.net/html/cursor.html#Cursor.setinputsizes
> you should not call setinputsizes() with one argument, but with one
> argument per variable.
> since you already have a list containing the various sizes,
>    cursor.setinputsizes(*inputsizes)
> (note the * symbol) will "explode" the list and pass each list items
> as separate arguments to the function.
>
> --
>  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-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

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

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

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))
Where clobs is an array of one long string: 'asdfasdfasdfasd  . . '

By name,
 My SQL expression reads : INSERT INTO . . . values (:value1, null . . .)
And my execution is (cursor.execute(sql, *clobs))
Where clobs is an array of one long string: value1='asdfasdfasdfasd  . . '

Both of these give the same error
On Sat, Jul 3, 2010 at 6:11 PM, Jason Boorn <jb...@gm...> wrote:

> Thanks again - sorry for the confusion with the * operator - I hadn't used
> it before.
>
>
> On Sat, Jul 3, 2010 at 5:11 PM, Amaury Forgeot d'Arc <ama...@gm...>wrote:
>
>> 2010/7/3 Jason Boorn <jb...@gm...>:
>> > Thank you, but I'm using python - could you tell me what kind of a
>> structure
>> > the argument should be?  And how to populate it?
>> >
>> > My original function call was called with an array argument, an array
>> > containing all of the fields being inserted.
>>
>> In the docs:
>> http://cx-oracle.sourceforge.net/html/cursor.html#Cursor.setinputsizes
>> you should not call setinputsizes() with one argument, but with one
>> argument per variable.
>> since you already have a list containing the various sizes,
>>    cursor.setinputsizes(*inputsizes)
>> (note the * symbol) will "explode" the list and pass each list items
>> as separate arguments to the function.
>>
>> --
>>  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-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>
>

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

[Amaury F. d'A. <ama...@gm...>]

2010/7/4 Jason Boorn <jb...@gm...>:
> 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

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

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

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 <ama...@gm...>wrote:

> 2010/7/4 Jason Boorn <jb...@gm...>:
> > 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-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

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

[Anthony T. <ant...@gm...>]

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 <jb...@gm...> 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 <ama...@gm...>
> wrote:
>>
>> 2010/7/4 Jason Boorn <jb...@gm...>:
>> > 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-...@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
>
>

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

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

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 <ant...@gm...
> 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 <jb...@gm...> 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 <ama...@gm...
> >
> > wrote:
> >>
> >> 2010/7/4 Jason Boorn <jb...@gm...>:
> >> > 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-...@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
>

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

[Anthony T. <ant...@gm...>]

On Tue, Jul 6, 2010 at 7:43 AM, Jason Boorn <jb...@gm...> 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

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

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

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 <ant...@gm...
> wrote:

> On Tue, Jul 6, 2010 at 7:43 AM, Jason Boorn <jb...@gm...> 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-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

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

[Anthony T. <ant...@gm...>]

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

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

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

Do I need to quote the position parameter?

like:

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

as opposed to:

 INSERT INTO TEST  VALUES (:1, null null)


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

> On Tue, Jul 6, 2010 at 8:52 AM, Jason Boorn <jb...@gm...> wrote:
> > Ya, I already tried it without the * - same error.
>
> Hmm, interesting. The problem with the illegal variable name/number
> problem is that its almost impossible to diagnose the problem. You
> have to carefully examine the SQL looking for any colons that may have
> strayed outside of quotes or where an extra character is found after
> the colon without a space as you were intending, etc. Eventually you
> will find it but its always irritating having to search for it -- it
> would be nice if Oracle went to the trouble of telling you __what__
> variable name/number was illegal!
>
> To clarify again, the method signature is
>
> cursor.execute(sql, parameters)
>
> Where parameters is either a dictionary (named parameters) or sequence
> (positional parameters). So if you have a tuple
>
> clobs = ('This is the clob value',)
>
> or sequence
>
> clobs = ['This is the clob value']
>
> You would call
>
> cursor.execute(sql, clobs)
>
> Likewise, if you have keywords you would do the following:
>
> clobs = { "value1" : "This is the clob value" }
> cursor.execute(sql, clobs)
>
> I have added the ability to do the following as well since it is often
> convenient
>
> cursor.execute(sql, value1 = "This is the clob value")
>
> The first form is useful if you are building up the parameters
> dynamically and the second for known queries with known numbers of
> parameters.
>
> > The final SQL string that gets built is:
> >
> > INSERT INTO import_CAMTARGET
> >
> (ACCOUNTKEY,CAMPAIGNKEY,CAMTARGETKEY,ZIP,DOMAIN,SEARCHTERM,SEARCHANYALL,COOKIE,RDBPROFILE,NEGATETARGET,CITY,AREACODE,SEGMENTANYALL,RECORDSTATE,WHENCREATED,WHENMODIFIED,WHOCREATED,WHOMODIFIED,TEMPLATEID,ALLNONMOBILEDEVICE)
> > VALUES
> >
> (1789,3946029,1765718,null,null,null,'A',null,null,'N',:1,null,'A','L',to_date('2010-06-03
> > 16:28:59','YYYY-MM-DD HH24:MI:SS'),to_date('2010-06-18
> 13:37:17','YYYY-MM-DD
> > HH24:MI:SS'),'matthew.kay_guardian.co.uk','APIMaxifier',null,'N')
> >
> > My clobs array contains one string element (note the string contains
> newline
> > characters could this be an issue?):
>
> No, the newlines are not a problem. Using bind variables means that
> you don't have to worry about special characters of any sort. The data
> is passed directly to Oracle without interpretation.
>
> >  'GB:NEW MILTON
> > GB:WALTHAM FOREST
> > GB:CAMDEN
> > GB:CHELMSFORD
> > GB:SOMPTING
> > GB:LEATHERHEAD
> > GB:WINSFORD
> > GB:GAINSBOROUGH
> > GB:DINGWALL
> > GB:WEST KILBRIDE
> > GB:CHESTERFIELD
> > GB:BLETCHLEY
> > GB:HALESWORTH
> > GB:GUILDFORD
> > GB:RAGLAN
> > GB:HOLLINGWORTH
> > GB:ROSLIN
> > GB:WOOTTON
> > GB:NORMANDY
> > GB:EDENBRIDGE
> > GB:HEATH
> > GB:NEWTOWNABBEY
> > GB:TAMWORTH
> > GB:TOWNHILL
> > GB:FAILSWORTH
> > GB:KETTERING
> > GB:PELTON
> > GB:RYDE
> > GB:LYNDHURST
> > GB:NORTH BERWICK
> > GB:BIRKENHEAD
> > GB:LOCHEARNHEAD
> > GB:DARVEL
> > GB:HOLMES CHAPEL
> > GB:STORRINGTON
> > GB:SPRINGFIELD
> > GB:CARLUKE
> > GB:BURY ST EDMUNDS
> > GB:DORKING
> > GB:CLOGHER
> > GB:CROMER
> > GB:LANCING
> > GB:LONGFIELD
> > GB:BROADFORD
> > GB:ASHTON-UNDER-LYNE
> > GB:CROWLAS
> > GB:HOLYWELL
> > GB:LOOE
> > GB:STAPLEFORD
> > GB:PAISLEY
> > GB:PORTRUSH
> > GB:PLYMPTON
> > GB:BRACKLEY
> > GB:FARNHAM
> > GB:GOLBORNE
> > GB:HASLINGDEN
> > GB:GRANTOWN-ON-SPEY
> > GB:GERRARDS CROSS
> > GB:MARKET HARBOROUGH
> > GB:CLYDACH
> > GB:LYDIATE
> > GB:DEVIZES
> > GB:BIRTLEY
> > GB:STRATHPEFFER
> > GB:SHOREHAM
> > GB:HANLEY
> > GB:WALSINGHAM
> > GB:WOODLEY
> > GB:BLAIRGOWRIE AND RATTRAY
> > GB:BARNET
> > GB:ASHLEY
> > GB:NOTTINGHAM
> > GB:MAUCHLINE
> > GB:PINXTON
> > GB:WOKINGHAM'
> >
> > As far as using named parameters (which I'm happy to do to get this
> > working): I understand how to establish the named parameters, but not how
> to
> > get them into the structure that is passed to execute.  As I understand
> it,
> > we need to pass in:
> >
> > cursor.execute(sql, value1:'asdfasdfasdfasdf', value2:'sadfasdfasdfasdf')
>
> See above. Either do this:
>
> parameters = { "value1" : "asdfadfadfadsfa", "value2" : "asfadsfadsfadsf" }
> cursor.execute(sql, parameters)
>
> or do this
>
> cursor.execute(sql, value1 = "asadfadsfsadf", value2 = "adfadfadsfad")
>
> > I can generate the string "value1" and I can generate the string
> > 'value1=asdfasdfasdfasdf' but I don't see how either of those help me -
> I'm
> > not passing a string, I'm passing a variable/string tuple correct?  How
> do I
> > dynamically generate that?
>
> You can do this to build the parameters up dynamically.
> parameters = {}
> parameters["value1"] = "adfadsfadf"
> parameters["value2"] = "asdfsadfafasdfdsaf"
> cursor.execute(sql, parameters)
>
> > Apologies is this is a very naive question.
>
> No problem. :-)
>
> Anthony
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

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

[Anthony T. <ant...@gm...>]

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

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

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

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
>

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

[Anthony T. <ant...@gm...>]

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

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

[Weber, G. <Geo...@PA...>]

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

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

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

Geoff -

Those are already part of the SQL statement - they were never in the
parameters list.

I got this working (thanks Anthony) using a dictionary for the parameters
without a call to setinputsizes.  I'm guessing this is not optimal, but I
could not get the call to setinputsizes to work as a dictionary, and I
wasn't sure if you could mix named parameters with array-style
setinputsizes.

I'm guessing that not using the setinputsizes function is not optimal, but I
can get it to work without it so that's what I'm going to do.

Thanks

On Wed, Jul 7, 2010 at 8:23 AM, Weber, Geoffrey
<Geo...@pa...>wrote:

> OK - your problem here is that you can't put "to_date()" or
> "to_timestamp()" functions in as part of the parameter list.  What you need
> instead is to put those functions as part of the SQL statement itself:
>
> sql = 'INSERT INTO import_camtarget
> (ACCOUNTKEY,CAMPAIGNKEY,CAMTARGETKEY,ZIP,DOMAIN,SEARCHTERM,SEARCHANYALL,COOKIE,RDBPROFILE,NEGATETARGET,CITY,AREACODE,SEGMENTANYALL,RECORDSTATE,WHENCREATED,WHENMODIFIED,WHOCREATED,WHOMODIFIED,TEMPLATEID,ALLNONMOBILEDEVICE)
> VALUES (:1, :2, :3, :4, :5... ,TO_DATE(:n, 'YYYY-MM-DD HH24:MI:SS'), :(n+1),
> ...)'
>
> and pass in your date strings just like any other bind value.  Any value
> you pass in as parameters is interpreted literally and not as a function
> call by Oracle.
>
> so then you'd have:
> cursor.execute(sql, params)
>
> ...just like Anthony has been describing to you before (either tuple/list
> or dictionary forms).
>
> Hope this makes sense to you, and good luck!
>
>    - Geoff
>
>
> ________________________________________
> From: Jason Boorn [jb...@gm...]
> Sent: Tuesday, July 06, 2010 4:43 PM
> To: cx-...@li...
> Subject: Re: [cx-oracle-users] setinputsizes problem?
>
> I just noticed something:
>
> > The final SQL string that gets built is:
> >
> > INSERT INTO import_CAMTARGET
> >
> (ACCOUNTKEY,CAMPAIGNKEY,CAMTARGETKEY,ZIP,DOMAIN,SEARCHTERM,SEARCHANYALL,COOKIE,RDBPROFILE,NEGATETARGET,CITY,AREACODE,SEGMENTANYALL,RECORDSTATE,WHENCREATED,WHENMODIFIED,WHOCREATED,WHOMODIFIED,TEMPLATEID,ALLNONMOBILEDEVICE)
> > VALUES
> >
> (1789,3946029,1765718,null,null,null,'A',null,null,'N',:1,null,'A','L',to_date('2010-06-03
> > 16:28:59','YYYY-MM-DD HH24:MI:SS'),to_date('2010-06-18
> 13:37:17','YYYY-MM-DD
> > HH24:MI:SS'),'','APIMaxifier',null,'N')
>
> I have time values which include colons in my SQL string.  Is it possible
> these are confusing the parser?  If so, is there a way to escape them?
>
> On Tue, Jul 6, 2010 at 5:35 PM, Anthony Tuininga <
> ant...@gm...<mailto:ant...@gm...>> wrote:
> No, do not quote the position parameter. Something like this is want you
> want:
>
> cursor.execute("insert into test values (:1, null, null)", ("This is
> the input string",))
>
> BTW, the test scripts can be a source of examples for how things can be
> written.
>
> Anthony
>
> On Tue, Jul 6, 2010 at 2:42 PM, Jason Boorn <jb...@gm...<mailto:
> jb...@gm...>> wrote:
> > Do I need to quote the position parameter?
> >
> > like:
> >
> > INSERT INTO TEST  VALUES (':1', null null)
> >
> > as opposed to:
> >
> > INSERT INTO TEST  VALUES (:1, null null)
> >
> > On Tue, Jul 6, 2010 at 11:43 AM, Anthony Tuininga
> > <ant...@gm...<mailto:ant...@gm...>> wrote:
>  >>
> >> On Tue, Jul 6, 2010 at 8:52 AM, Jason Boorn <jb...@gm...<mailto:
> jb...@gm...>> wrote:
> >> > Ya, I already tried it without the * - same error.
> >>
> >> Hmm, interesting. The problem with the illegal variable name/number
> >> problem is that its almost impossible to diagnose the problem. You
> >> have to carefully examine the SQL looking for any colons that may have
> >> strayed outside of quotes or where an extra character is found after
> >> the colon without a space as you were intending, etc. Eventually you
> >> will find it but its always irritating having to search for it -- it
> >> would be nice if Oracle went to the trouble of telling you __what__
> >> variable name/number was illegal!
> >>
> >> To clarify again, the method signature is
> >>
> >> cursor.execute(sql, parameters)
> >>
> >> Where parameters is either a dictionary (named parameters) or sequence
> >> (positional parameters). So if you have a tuple
> >>
> >> clobs = ('This is the clob value',)
> >>
> >> or sequence
> >>
> >> clobs = ['This is the clob value']
> >>
> >> You would call
> >>
> >> cursor.execute(sql, clobs)
> >>
> >> Likewise, if you have keywords you would do the following:
> >>
> >> clobs = { "value1" : "This is the clob value" }
> >> cursor.execute(sql, clobs)
> >>
> >> I have added the ability to do the following as well since it is often
> >> convenient
> >>
> >> cursor.execute(sql, value1 = "This is the clob value")
> >>
> >> The first form is useful if you are building up the parameters
> >> dynamically and the second for known queries with known numbers of
> >> parameters.
> >>
> >> > The final SQL string that gets built is:
> >> >
> >> > INSERT INTO import_CAMTARGET
> >> >
> >> >
> (ACCOUNTKEY,CAMPAIGNKEY,CAMTARGETKEY,ZIP,DOMAIN,SEARCHTERM,SEARCHANYALL,COOKIE,RDBPROFILE,NEGATETARGET,CITY,AREACODE,SEGMENTANYALL,RECORDSTATE,WHENCREATED,WHENMODIFIED,WHOCREATED,WHOMODIFIED,TEMPLATEID,ALLNONMOBILEDEVICE)
> >> > VALUES
> >> >
> >> >
> (1789,3946029,1765718,null,null,null,'A',null,null,'N',:1,null,'A','L',to_date('2010-06-03
> >> > 16:28:59','YYYY-MM-DD HH24:MI:SS'),to_date('2010-06-18
> >> > 13:37:17','YYYY-MM-DD
> >> > HH24:MI:SS'),'matthew.kay_guardian.co.uk<
> http://matthew.kay_guardian.co.uk/>','APIMaxifier',null,'N')
>  >> >
> >> > My clobs array contains one string element (note the string contains
> >> > newline
> >> > characters could this be an issue?):
> >>
> >> No, the newlines are not a problem. Using bind variables means that
> >> you don't have to worry about special characters of any sort. The data
> >> is passed directly to Oracle without interpretation.
> >>
> >> >  'GB:NEW MILTON
> >> > GB:WALTHAM FOREST
> >> > GB:CAMDEN
> >> > GB:CHELMSFORD
> >> > GB:SOMPTING
> >> > GB:LEATHERHEAD
> >> > GB:WINSFORD
> >> > GB:GAINSBOROUGH
> >> > GB:DINGWALL
> >> > GB:WEST KILBRIDE
> >> > GB:CHESTERFIELD
> >> > GB:BLETCHLEY
> >> > GB:HALESWORTH
> >> > GB:GUILDFORD
> >> > GB:RAGLAN
> >> > GB:HOLLINGWORTH
> >> > GB:ROSLIN
> >> > GB:WOOTTON
> >> > GB:NORMANDY
> >> > GB:EDENBRIDGE
> >> > GB:HEATH
> >> > GB:NEWTOWNABBEY
> >> > GB:TAMWORTH
> >> > GB:TOWNHILL
> >> > GB:FAILSWORTH
> >> > GB:KETTERING
> >> > GB:PELTON
> >> > GB:RYDE
> >> > GB:LYNDHURST
> >> > GB:NORTH BERWICK
> >> > GB:BIRKENHEAD
> >> > GB:LOCHEARNHEAD
> >> > GB:DARVEL
> >> > GB:HOLMES CHAPEL
> >> > GB:STORRINGTON
> >> > GB:SPRINGFIELD
> >> > GB:CARLUKE
> >> > GB:BURY ST EDMUNDS
> >> > GB:DORKING
> >> > GB:CLOGHER
> >> > GB:CROMER
> >> > GB:LANCING
> >> > GB:LONGFIELD
> >> > GB:BROADFORD
> >> > GB:ASHTON-UNDER-LYNE
> >> > GB:CROWLAS
> >> > GB:HOLYWELL
> >> > GB:LOOE
> >> > GB:STAPLEFORD
> >> > GB:PAISLEY
> >> > GB:PORTRUSH
> >> > GB:PLYMPTON
> >> > GB:BRACKLEY
> >> > GB:FARNHAM
> >> > GB:GOLBORNE
> >> > GB:HASLINGDEN
> >> > GB:GRANTOWN-ON-SPEY
> >> > GB:GERRARDS CROSS
> >> > GB:MARKET HARBOROUGH
> >> > GB:CLYDACH
> >> > GB:LYDIATE
> >> > GB:DEVIZES
> >> > GB:BIRTLEY
> >> > GB:STRATHPEFFER
> >> > GB:SHOREHAM
> >> > GB:HANLEY
> >> > GB:WALSINGHAM
> >> > GB:WOODLEY
> >> > GB:BLAIRGOWRIE AND RATTRAY
> >> > GB:BARNET
> >> > GB:ASHLEY
> >> > GB:NOTTINGHAM
> >> > GB:MAUCHLINE
> >> > GB:PINXTON
> >> > GB:WOKINGHAM'
> >> >
> >> > As far as using named parameters (which I'm happy to do to get this
> >> > working): I understand how to establish the named parameters, but not
> >> > how to
> >> > get them into the structure that is passed to execute.  As I
> understand
> >> > it,
> >> > we need to pass in:
> >> >
> >> > cursor.execute(sql, value1:'asdfasdfasdfasdf',
> >> > value2:'sadfasdfasdfasdf')
> >>
> >> See above. Either do this:
> >>
> >> parameters = { "value1" : "asdfadfadfadsfa", "value2" :
> "asfadsfadsfadsf"
> >> }
> >> cursor.execute(sql, parameters)
> >>
> >> or do this
> >>
> >> cursor.execute(sql, value1 = "asadfadsfsadf", value2 = "adfadfadsfad")
> >>
> >> > I can generate the string "value1" and I can generate the string
> >> > 'value1=asdfasdfasdfasdf' but I don't see how either of those help me
> -
> >> > I'm
> >> > not passing a string, I'm passing a variable/string tuple correct?
>  How
> >> > do I
> >> > dynamically generate that?
> >>
> >> You can do this to build the parameters up dynamically.
> >> parameters = {}
> >> parameters["value1"] = "adfadsfadf"
> >> parameters["value2"] = "asdfsadfafasdfdsaf"
> >> cursor.execute(sql, parameters)
> >>
> >> > Apologies is this is a very naive question.
> >>
> >> No problem. :-)
> >>
> >> Anthony
> >>
> >>
> >>
> ------------------------------------------------------------------------------
> >> This SF.net email is sponsored by Sprint
> >> What will you do first with EVO, the first 4G phone?
> >> Visit sprint.com/first<http://sprint.com/first> --
> http://p.sf.net/sfu/sprint-com-first
> >> _______________________________________________
> >> cx-oracle-users mailing list
> >> cx-...@li...<mailto:
> cx-...@li...>
> >> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
> >
> ------------------------------------------------------------------------------
> > This SF.net email is sponsored by Sprint
> > What will you do first with EVO, the first 4G phone?
> > Visit sprint.com/first<http://sprint.com/first> --
> http://p.sf.net/sfu/sprint-com-first
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...<mailto:
> cx-...@li...>
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first<http://sprint.com/first> --
> http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...<mailto:
> cx-...@li...>
>  https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

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

[Anthony T. <ant...@gm...>]

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