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

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

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
>