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

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

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
>