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