cx-oracle-users — Primary discussion list for users (and developers) of cx_Oracle

Re: [cx-oracle-users] Decimal and european NLS_LANG

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

2010/10/25 Michael Bayer <mi...@zz...>:
> ah yes and I see it is your fix, and yes the above test case is fixed.
> So, you're sending Decimal objects to cx_oracle.
> How are you getting them back ?    Our current approach is kind of a nightmare
> as we use an output handler and parse from string, code fragments are below:
[...]
Or you could ask Anthony and beg for a "cursor.numbersAsDecimals" option...

-- 
Amaury Forgeot d'Arc

Re: [cx-oracle-users] Decimal and european NLS_LANG

[Michael B. <mi...@zz...>]

On Oct 25, 2010, at 2:37 PM, Amaury Forgeot d'Arc wrote:

> 2010/10/25 Michael Bayer <mi...@zz...>:
>> I have a user (a user of the database access layer SQLAlchemy)  who wants to do this:
>> 
>>        import os
>>        import cx_Oracle
>>        import decimal
>> 
>>        os.environ["NLS_LANG"] = "GERMAN"
>> 
>>        dsn = cx_Oracle.makedsn('localhost', '1521', 'xe')
>>        conn = cx_Oracle.connect('scott', 'tiger', dsn)
>>        cursor = conn.cursor()
>>        cursor.execute("select :foo from dual", foo=decimal.Decimal("56.5"))
>>        print cursor.fetchall()
>> 
>> The error is:
>> 
>>        cx_Oracle.DatabaseError: OCI-22062: invalid input string [56.5]
> 
> A similar issue was fixed in cx_Oracle 5.0.3:
> http://cx-oracle.sourceforge.net/HISTORY.txt
> see item 14)
> 
> Are you using an older version?

ah yes and I see it is your fix, and yes the above test case is fixed.    So, you're sending Decimal objects to cx_oracle.  How are you getting them back ?    Our current approach is kind of a nightmare as we use an output handler and parse from string, code fragments are below:

_to_decimal = Decimal
"""Convert string -> Decimal"""
    
def _detect_decimal(value):
    """Convert string -> Decimal if decimal point present, otherwise int.
    
    We want simple phrases like "select some_seq.nextval from dual" 
    to return int.
    
    """
    if "." in value:
        return Decimal(value)
    else:
        return int(value)

def _detect_decimal_char(connection):
    """See what the decimal separator due to NLS_LANG.

   We run this just once when the first connection is made.
  """
    cursor = connection.cursor()
    cursor.execute("SELECT 0.1 FROM DUAL")
    val = cursor.fetchone()[0]
    cursor.close()
    char = re.match(r"([\.,])", val).group(1)
    if char != '.':
        _detect_decimal = lambda value: _detect_decimal(value.replace(char, '.'))
        _to_decimal = lambda value: Decimal(value.replace(char, '.'))
    
def output_type_handler(cursor, name, defaultType, size, precision, scale):
    """Output type handler.  Attempts to return Decimal / int as appropriate.

       We associate this with all cx_Oracle.Connection objects.

    """
    
    if defaultType == cx_Oracle.NUMBER and precision and scale > 0:
        return cursor.var(
                    cx_Oracle.STRING, 
                    255, 
                    outconverter=_to_decimal, 
                    arraysize=cursor.arraysize)
    elif defaultType == cx_Oracle.NUMBER \
            and not precision and scale <= 0:
        return cursor.var(
                    cx_Oracle.STRING, 
                    255, 
                    outconverter=_detect_decimal, 
                    arraysize=cursor.arraysize)

Re: [cx-oracle-users] Decimal and european NLS_LANG

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

2010/10/25 Michael Bayer <mi...@zz...>:
> I have a user (a user of the database access layer SQLAlchemy)  who wants to do this:
>
>        import os
>        import cx_Oracle
>        import decimal
>
>        os.environ["NLS_LANG"] = "GERMAN"
>
>        dsn = cx_Oracle.makedsn('localhost', '1521', 'xe')
>        conn = cx_Oracle.connect('scott', 'tiger', dsn)
>        cursor = conn.cursor()
>        cursor.execute("select :foo from dual", foo=decimal.Decimal("56.5"))
>        print cursor.fetchall()
>
> The error is:
>
>        cx_Oracle.DatabaseError: OCI-22062: invalid input string [56.5]

A similar issue was fixed in cx_Oracle 5.0.3:
http://cx-oracle.sourceforge.net/HISTORY.txt
see item 14)

Are you using an older version?

-- 
Amaury Forgeot d'Arc

[cx-oracle-users] Decimal and european NLS_LANG

[Michael B. <mi...@zz...>]

I have a user (a user of the database access layer SQLAlchemy)  who wants to do this:

	import os
	import cx_Oracle
	import decimal

	os.environ["NLS_LANG"] = "GERMAN"

	dsn = cx_Oracle.makedsn('localhost', '1521', 'xe')
	conn = cx_Oracle.connect('scott', 'tiger', dsn)
	cursor = conn.cursor()
	cursor.execute("select :foo from dual", foo=decimal.Decimal("56.5"))
	print cursor.fetchall()

The error is:

	cx_Oracle.DatabaseError: OCI-22062: invalid input string [56.5]

of course, because "GERMAN" wants "56,5" and I'm assuming cx_Oracle is calling str() on the Decimal.

It also appears that Python Decimal uses "." as the decimal point no matter what you do with the "locale" module.

So I just need to give the user an answer, pick one:

	1. You shouldn't be using NLS_LANG=GERMAN with cx_Oracle, at least use GERMAN_AMERICA

	2. You shouldn't be using Python Decimal objects as bind parameters ...
	2a. .... when NLS_LANG=GERMAN

	3. You should be using a custom inputtypehandler which does the appropriate string conversion (can we send strings here ?)

	4. cx_oracle should handle this automatically

There's also the other side of it, Decimal objects as results, but since cx_oracle doesn't provide that directly we will have to add more conditionals to our custom outputtypehandler, would be nice if cx_oracle could someday return Decimal() directly since we have a lot of guesswork on that side as well.

Re: [cx-oracle-users] Missleading Error-Message within cx_Oracle unicode-build

[Marco De P. <dep...@gm...>]

The point is about the someway "missleading error message"
Harald is suggesting to correct the message specifying that an unicode is
expected...

On Wed, Oct 20, 2010 at 10:55 PM, John Nielson <mr....@gm...>wrote:

> Try sql = u"select 1 from dual"
>
> On Wed, Oct 20, 2010 at 9:47 AM, Massa, Harald Armin <ch...@gh...> wrote:
> > Hello,
> > just transfering a project to cx_Oracle 5.0.4, Unicode built
> > So sometimes I still have strings as sqls, trying to execute them give me
> a
> > missleading errormessage:
> > ocn = cx_Oracle.connect(...)
> > ocs = ocn.cursor()
> > sql = "select 1 from dual"
> > ocs.execute(sql)
> > raises a
> > TypeError: expecting None or a string
> > when, infact, "sql" IS a string, but cx_Oracle as an unicode build
> > rightfully expects an unicode.
> > I suggest to fix the type error,
> > Harald
> >
> > --
> > GHUM GmbH
> > Harald Armin Massa
> > Spielberger Straße 49
> > 70435 Stuttgart
> > 0173/9409607
> >
> > Amtsgericht Stuttgart, HRB 734971
> > -
> > persuadere.
> > et programmare
> >
> >
> ------------------------------------------------------------------------------
> > Nokia and AT&T present the 2010 Calling All Innovators-North America
> contest
> > Create new apps & games for the Nokia N8 for consumers in  U.S. and
> Canada
> > $10 million total in prizes - $4M cash, 500 devices, nearly $6M in
> marketing
> > Develop with Nokia Qt SDK, Web Runtime, or Java and Publish to Ovi Store
> > http://p.sf.net/sfu/nokia-dev2dev
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
>
>
>
> --
> John Nielson
> mr....@gm...
> (504) 344.7225
>
>
> ------------------------------------------------------------------------------
> Nokia and AT&T present the 2010 Calling All Innovators-North America
> contest
> Create new apps & games for the Nokia N8 for consumers in  U.S. and Canada
> $10 million total in prizes - $4M cash, 500 devices, nearly $6M in
> marketing
> Develop with Nokia Qt SDK, Web Runtime, or Java and Publish to Ovi Store
> http://p.sf.net/sfu/nokia-dev2dev
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] Missleading Error-Message within cx_Oracle unicode-build

[John N. <mr....@gm...>]

Ahhhh my bad!

On Wed, Oct 20, 2010 at 4:00 PM, Massa, Harald Armin <ch...@gh...> wrote:
> John,
>>
>> Try sql = u"select 1 from dual"
>>
> yes, I know that solves the problem. My improvement-suggestion is concerned
> with the error message:
>
>>
>> > sql = "select 1 from dual"
>> > ocs.execute(sql)
>> > raises a
>> > TypeError: expecting None or a string
>> > when, infact, "sql" IS a string, but cx_Oracle as an unicode build
>
> that error should be "Type Error: expecting None or a unicode
> (without the bold)
> Harald
>
> --
> GHUM GmbH
> Harald Armin Massa
> Spielberger Straße 49
> 70435 Stuttgart
> 0173/9409607
>
> Amtsgericht Stuttgart, HRB 734971
> -
> persuadere.
> et programmare
>
> ------------------------------------------------------------------------------
> Nokia and AT&T present the 2010 Calling All Innovators-North America contest
> Create new apps & games for the Nokia N8 for consumers in  U.S. and Canada
> $10 million total in prizes - $4M cash, 500 devices, nearly $6M in marketing
> Develop with Nokia Qt SDK, Web Runtime, or Java and Publish to Ovi Store
> http://p.sf.net/sfu/nokia-dev2dev
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>



-- 
John Nielson
mr....@gm...
(504) 344.7225

Re: [cx-oracle-users] Missleading Error-Message within cx_Oracle unicode-build

[Massa, H. A. <ch...@gh...>]

John,

Try sql = u"select 1 from dual"
>
> yes, I know that solves the problem. My improvement-suggestion is concerned
with the error message:


> > sql = "select 1 from dual"
> > ocs.execute(sql)
> > raises a
> > TypeError: expecting None or a string
> > when, infact, "sql" IS a string, but cx_Oracle as an unicode build
>
that error should be "Type Error: expecting None or a *unicode*

(without the bold)

Harald

-- 
GHUM GmbH
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607

Amtsgericht Stuttgart, HRB 734971
-
persuadere.
et programmare

Re: [cx-oracle-users] Missleading Error-Message within cx_Oracle unicode-build

[John N. <mr....@gm...>]

Try sql = u"select 1 from dual"

On Wed, Oct 20, 2010 at 9:47 AM, Massa, Harald Armin <ch...@gh...> wrote:
> Hello,
> just transfering a project to cx_Oracle 5.0.4, Unicode built
> So sometimes I still have strings as sqls, trying to execute them give me a
> missleading errormessage:
> ocn = cx_Oracle.connect(...)
> ocs = ocn.cursor()
> sql = "select 1 from dual"
> ocs.execute(sql)
> raises a
> TypeError: expecting None or a string
> when, infact, "sql" IS a string, but cx_Oracle as an unicode build
> rightfully expects an unicode.
> I suggest to fix the type error,
> Harald
>
> --
> GHUM GmbH
> Harald Armin Massa
> Spielberger Straße 49
> 70435 Stuttgart
> 0173/9409607
>
> Amtsgericht Stuttgart, HRB 734971
> -
> persuadere.
> et programmare
>
> ------------------------------------------------------------------------------
> Nokia and AT&T present the 2010 Calling All Innovators-North America contest
> Create new apps & games for the Nokia N8 for consumers in  U.S. and Canada
> $10 million total in prizes - $4M cash, 500 devices, nearly $6M in marketing
> Develop with Nokia Qt SDK, Web Runtime, or Java and Publish to Ovi Store
> http://p.sf.net/sfu/nokia-dev2dev
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>



-- 
John Nielson
mr....@gm...
(504) 344.7225

[cx-oracle-users] Missleading Error-Message within cx_Oracle unicode-build

[Massa, H. A. <ch...@gh...>]

Hello,

just transfering a project to cx_Oracle 5.0.4, Unicode built

So sometimes I still have strings as sqls, trying to execute them give me a
missleading errormessage:

ocn = cx_Oracle.connect(...)
ocs = ocn.cursor()

sql = "select 1 from dual"

ocs.execute(sql)

raises a

TypeError: expecting None or a string

when, infact, "sql" IS a string, but cx_Oracle as an unicode build
rightfully expects an unicode.

I suggest to fix the type error,

Harald


-- 
GHUM GmbH
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607

Amtsgericht Stuttgart, HRB 734971
-
persuadere.
et programmare

[cx-oracle-users] list

[Robert <web...@gm...>]

cx-oracle-users mailing list

Re: [cx-oracle-users] cx_Oracle.InterfaceError: not a query

[John N. <mr....@gm...>]

About the string concatenation, do you usually just use dictionaries?
I understand that's more Pythonic.  I should probably do that.

At any rate, my workaround was a dump of all the SQL statements to a
file and running that against the database.

On Sun, Oct 10, 2010 at 10:05 AM, Robert <web...@gm...> wrote:
>>>>>                 if update_flag:
>>>>>                     #
>>>>>                     # If an update is necessary, print row's PK value
>>>>>                     print "\tChange needed for %s = %d" % (uid_col, uid)
>>>>>                     update_query = "update " + full_name + " " + \
>>>>>                                    "set " + column + \
>>>>>                                    " = '" + returned_string + "' " +\
>>>>>                                    "where " + uid_col + \
>>>>>                                    " = " + str(uid)
>>>>>                     cur.execute(query)
>>>>>
>>>>> conn.close()
>
>
> personally I hate writing and reading string concat code like this.
> I  would dump the SQLs to a file and read back in and execute in iteration.
> I like viewing exactly what I send to Oracle.
> ------------------------------------------------------------------------------
> Beautiful is writing same markup. Internet Explorer 9 supports
> standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
> Spend less time writing and  rewriting code and more time creating great
> experiences on the web. Be a part of the beta today.
> http://p.sf.net/sfu/beautyoftheweb
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>



-- 
John Nielson
mr....@gm...
(504) 344.7225

Re: [cx-oracle-users] cx_Oracle.InterfaceError: not a query

[Robert <web...@gm...>]

>>>>                 if update_flag:
>>>>                     #
>>>>                     # If an update is necessary, print row's PK value
>>>>                     print "\tChange needed for %s = %d" % (uid_col, uid)
>>>>                     update_query = "update " + full_name + " " + \
>>>>                                    "set " + column + \
>>>>                                    " = '" + returned_string + "' " +\
>>>>                                    "where " + uid_col + \
>>>>                                    " = " + str(uid)
>>>>                     cur.execute(query)
>>>>
>>>> conn.close()


personally I hate writing and reading string concat code like this.
I  would dump the SQLs to a file and read back in and execute in iteration.
I like viewing exactly what I send to Oracle.

Re: [cx-oracle-users] cx_Oracle.InterfaceError: not a query

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

No problem. :-)

Marco's comment is correct, though. You are fetching from a cursor and
attempting to execute an update statement with the same cursor. You
need to either use cursor.fetchall() so that all of the rows are
fetched from the cursor before reusing it, or you need to use a
different cursor to execute the update. Either way should resolve your
"not a query" error.

Anthony

On Fri, Oct 8, 2010 at 2:47 PM, John Nielson <mr....@gm...> wrote:
> I should let you know that I tacked the cur.execute(query) statement
> at the end of my code only in this e-mail.  I've since edited it (for
> the sake of getting my work done at the job) to generate an external
> SQL script containing all the update statements.  So, for the sake of
> troubleshooting, pretend that my code says cur.execute(update_query)
>
> My bad, y'all!
>
>
>
> On Fri, Oct 8, 2010 at 3:41 PM, Marco De Paoli <dep...@gm...> wrote:
>> there something strange: on the final lines you have just defined an
>> update_query string and you call cur.execute(query)
>>
>> probably you intended "cur.execute(update_query)" in place of
>> "cur.execute(query)" (may be a copy/paste problem?)
>>
>> in this case, you are executing two different sql statements using the same
>> cursor: while you are fetching rows from a select, you use *the same cursor*
>> to execute an update
>>
>> Marco
>>
>> On Fri, Oct 8, 2010 at 6:01 PM, John Nielson <mr....@gm...>
>> wrote:
>>>
>>> tab_col_map = {
>>>         'E_APDS':['APD_CORRECTION_REMARK'],
>>>         'E_APD_ATTACHMENTS':['ATT_NAME'],
>>>         'E_APD_NOTES':['NOTE_SUBJECT','NOTE_TEXT'],
>>>         'E_APD_QAS':['QA_RESPONSE_TXT'],
>>>         'E_APD_STATUSES':['APD_STATUS_RMKS'],
>>>         'E_APMS':['APM_OP_DESC','CORRECTION_DESC'],
>>>         'E_APM_ATTACHMENTS':['ATT_NAME'],
>>>         'E_APM_NOTES':['NOTE_TEXT'],
>>>         'E_APM_QAS':['QA_RESPONSE_TXT'],
>>>         'E_APM_STATUSES':['APM_STATUS_RMKS'],
>>>         'E_CORRECTIONS':['CORRECTION_DESC'],
>>>         'E_CORRECTION_STATUSES':['STATUS_REMARKS'],
>>>         'E_END_WELL_OP_REPORTS':['OPERATIONAL_NARRATIVE'],
>>>         'E_EOR_COMPLETIONS':['REMARK'],
>>>         'E_EOR_HC_BEARING_INTERVALS':['INTERVAL_NAME'],
>>>         'E_EOR_NOTES':['NOTE_TEXT'],
>>>         'E_EOR_STATUSES':['STATUS_REMARKS'],
>>>         'E_INCIDENT_REPORT_ATTACHMENTS':['ATT_NAME'],
>>>         'E_INCIDENT_REPORT_CONTACTS':['EMAIL_ADDR'],
>>>         'E_INCIDENT_REPORT_STATUSES':['STATUS_REMARKS'],
>>>         'E_PIPELINE_DAMAGES':['PPL_DAMAGE_DESC'],
>>>         'E_REG_DEPARTURES':['REG_DEPARTURE_TXT'],
>>>
>>> 'E_RIG_MOVE_NOTIFICATIONS':['REMARKS','RIG_OBSTRUCTION_DESC','RIG_REP_PHONE'],
>>>         'E_STORM_PIPELINE_DAMAGES_HIST':['DAMAGE_DESC'],
>>>
>>> 'E_STORM_PLATFORM_DAMAGES_HIST':['ADDITIONAL_REMARKS','DAMAGE_DESC'],
>>>         'E_STORM_POLLUTIONS_HIST':['POLLUTION_REMARKS'],
>>>         'E_STORM_REPORT_STATUSES_HIST':['STORM_REPORT_STATUS_RMKS'],
>>>         'E_STORM_STR_PROD_CURTAILS_HIST':['CURTAILMENT_REMARKS'],
>>>         'E_WAR_NOTES':['NOTE_TEXT'],
>>>         'E_WAR_STATUSES':['STATUS_REMARKS'],
>>>
>>> 'E_WELL_ACTIVITY_REPORTS':['WAR_CORRECTION_NARRATIVE','WAR_SIGNIF_EVENT_NARRATIVE']
>>>         }
>>>
>>>     for table in tab_col_map:
>>>         print table
>>>         #
>>>         # First, get the primary key column of the table we're analyzing
>>>         con_query = "select owner, column_name " + \
>>>                     "from all_cons_columns " + \
>>>                     "where constraint_name = ( " + \
>>>                     "select constraint_name " + \
>>>                     "from all_constraints " + \
>>>                     "where table_name = '" + table + "' " + \
>>>                     "and constraint_type = 'P') " + \
>>>                     "and table_name = '" + table + "'"
>>>
>>>         constraints = cur.execute(con_query)
>>>         for constraint in constraints:
>>>             owner = constraint[0]
>>>             uid_col = constraint[1]
>>>
>>>         full_name = owner + "." + table
>>>         print full_name + " : " + uid_col
>>>
>>>         #
>>>         # Next, check each column in list mapped to current table to see
>>>         # if it contains any unsupported characters.
>>>         for column in tab_col_map[table]:
>>>             print "\t%s" % column
>>>             #
>>>             # Oracle dump() function is used to find 8-bit ASCII code for
>>>             # each character returned
>>>             query = "select " + uid_col + ", dump(" + column + ") " + \
>>>                     "from " + full_name
>>>             try:
>>>                 rows = cur.execute(query)
>>>             except cx_Oracle.DatabaseError, e:
>>>                 print "\t" + str(e)
>>>                 continue
>>>             count = 0
>>>             for row in rows:
>>>                 #
>>>                 # update_flag = False if row doesn't contain
>>>                 # the unsupported characters
>>>                 update_flag = False
>>>                 text_dump = row[1]
>>>                 uid = row[0]
>>>                 if text_dump.strip() != 'NULL':
>>>                     codes = text_dump.split(':')[1].strip().split(',')
>>>                     returned_string = ""
>>>                     for code in codes:
>>>                         code = int(code)
>>>                         if code in char_map.keys():
>>>                             count += 1
>>>                             returned_string += char_map[code]
>>>                             if code != 39:
>>>                                 #
>>>                                 # 39 is the code for the straight single
>>> quote.
>>>                                 # While this needs to be accounted for
>>> (i.e.
>>>                                 # single quotes must be escaped to
>>> avoid terminating
>>>                                 # the update string), the presence of a
>>> single
>>>                                 # straight quote in a row does not
>>> necessitate an
>>>                                 # update.
>>>                                 update_flag = True
>>>                         else:
>>>                             returned_string += chr(code)
>>>                 if update_flag:
>>>                     #
>>>                     # If an update is necessary, print row's PK value
>>>                     print "\tChange needed for %s = %d" % (uid_col, uid)
>>>                     update_query = "update " + full_name + " " + \
>>>                                    "set " + column + \
>>>                                    " = '" + returned_string + "' " +\
>>>                                    "where " + uid_col + \
>>>                                    " = " + str(uid)
>>>                     cur.execute(query)
>>>
>>> conn.close()
>>>
>>>
>>> On Fri, Oct 8, 2010 at 10:55 AM, Anthony Tuininga
>>> <ant...@gm...> wrote:
>>> >
>>> > Ok, can you show the code that is causing the problem? Is it
>>> > consistently failing with that one statement or does it have to be
>>> > after a bunch of other statements first? If you can send me a simple
>>> > script that demonstrates the problem I'll look at it. I've never run
>>> > into that problem myself unless I accidentally attempted to fetch from
>>> > the cursor -- but that doesn't mean there isn't some strange bug
>>> > lurking.... :-)
>>> >
>>> > Anthony
>>> >
>>> > On Fri, Oct 8, 2010 at 8:52 AM, John Nielson <mr....@gm...>
>>> > wrote:
>>> > > Right...
>>> > >
>>> > > I'm using cursor.execute(my_statement) to perform the update.  I am
>>> > > aware
>>> > > that update statements aren't queries; however, I'm also aware that
>>> > > the
>>> > > execute method is used to perform updates.  The script iterates
>>> > > through a
>>> > > whole slew of update statements that work just fine before blowing up
>>> > > on
>>> > > this one.
>>> > >
>>> > >
>>> > >
>>> > > On Thu, Oct 7, 2010 at 10:28 AM, Anthony Tuininga
>>> > > <ant...@gm...> wrote:
>>> > >>
>>> > >> Hi,
>>> > >>
>>> > >> Not sure exactly what you are trying to accomplish but showing the
>>> > >> exact Python code you are executing would be helpful. In any case,
>>> > >> the
>>> > >> statement shown below is definitely not a query -- it looks very much
>>> > >> like an update statement which does not query information but updates
>>> > >> information in the database. So you should be simply doing this
>>> > >>
>>> > >> cursor.execute(your_statement)
>>> > >>
>>> > >> Do not attempt to fetch from the cursor, though. That will generate
>>> > >> the "not a query" exception. Make sense?
>>> > >>
>>> > >> Anthony
>>> > >>
>>> > >> On Thu, Oct 7, 2010 at 8:29 AM, John Nielson
>>> > >> <mr....@gm...>
>>> > >> wrote:
>>> > >> > All:
>>> > >> >
>>> > >> > I have a script that performs a bunch of updates.  However, after
>>> > >> > attempting
>>> > >> > to perform the following query (carriage returns intentional):
>>> > >> >
>>> > >> > update NIELSONJ.MYTABLE set COLUMN_1 = '"double quotes"
>>> > >> > ''single quotes''
>>> > >> > En - dash
>>> > >> > Em-dash
>>> > >> > .       bullet
>>> > >> > ' where COLUMN_2 = -2395
>>> > >> >
>>> > >> >
>>> > >> > I get the following error:
>>> > >> >
>>> > >> > cx_Oracle.InterfaceError: not a query
>>> > >> >
>>> > >> >
>>> > >> > I've copied and pasted the query into my IDE, executed it, and it
>>> > >> > runs
>>> > >> > just
>>> > >> > fine.  It's definitely a query!  I am mystified.
>>> > >> >
>>> > >> > Please note what kinds of quotes those are and that all single
>>> > >> > quotes
>>> > >> > within
>>> > >> > the query are doubled to avoid terminating the update string.  The
>>> > >> > script is
>>> > >> > intended to cleanse the database of data containing weird
>>> > >> > characters
>>> > >> > like
>>> > >> > smart quotes (e.g. left single quotes are replaced with straight
>>> > >> > single
>>> > >> > quotes).
>>> > >> >
>>> > >> > --
>>> > >> > John Nielson
>>> > >> > mr....@gm...
>>> > >> > (504) 344.7225
>>> > >> >
>>> > >> >
>>> > >> >
>>> > >> > ------------------------------------------------------------------------------
>>> > >> > Beautiful is writing same markup. Internet Explorer 9 supports
>>> > >> > standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
>>> > >> > Spend less time writing and  rewriting code and more time creating
>>> > >> > great
>>> > >> > experiences on the web. Be a part of the beta today.
>>> > >> > http://p.sf.net/sfu/beautyoftheweb
>>> > >> > _______________________________________________
>>> > >> > cx-oracle-users mailing list
>>> > >> > cx-...@li...
>>> > >> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>> > >> >
>>> > >> >
>>> > >>
>>> > >>
>>> > >>
>>> > >> ------------------------------------------------------------------------------
>>> > >> Beautiful is writing same markup. Internet Explorer 9 supports
>>> > >> standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
>>> > >> Spend less time writing and  rewriting code and more time creating
>>> > >> great
>>> > >> experiences on the web. Be a part of the beta today.
>>> > >> http://p.sf.net/sfu/beautyoftheweb
>>> > >> _______________________________________________
>>> > >> cx-oracle-users mailing list
>>> > >> cx-...@li...
>>> > >> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>> > >
>>> > >
>>> > >
>>> > > --
>>> > > John Nielson
>>> > > mr....@gm...
>>> > > (504) 344.7225
>>> > >
>>> > >
>>> > > ------------------------------------------------------------------------------
>>> > > Beautiful is writing same markup. Internet Explorer 9 supports
>>> > > standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
>>> > > Spend less time writing and  rewriting code and more time creating
>>> > > great
>>> > > experiences on the web. Be a part of the beta today.
>>> > > http://p.sf.net/sfu/beautyoftheweb
>>> > > _______________________________________________
>>> > > cx-oracle-users mailing list
>>> > > cx-...@li...
>>> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>> > >
>>> > >
>>> >
>>> >
>>> > ------------------------------------------------------------------------------
>>> > Beautiful is writing same markup. Internet Explorer 9 supports
>>> > standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
>>> > Spend less time writing and  rewriting code and more time creating great
>>> > experiences on the web. Be a part of the beta today.
>>> > http://p.sf.net/sfu/beautyoftheweb
>>> > _______________________________________________
>>> > cx-oracle-users mailing list
>>> > cx-...@li...
>>> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>>
>>>
>>>
>>> --
>>> John Nielson
>>> mr....@gm...
>>> (504) 344.7225
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Beautiful is writing same markup. Internet Explorer 9 supports
>>> standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
>>> Spend less time writing and  rewriting code and more time creating great
>>> experiences on the web. Be a part of the beta today.
>>> http://p.sf.net/sfu/beautyoftheweb
>>> _______________________________________________
>>> cx-oracle-users mailing list
>>> cx-...@li...
>>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>> ------------------------------------------------------------------------------
>> Beautiful is writing same markup. Internet Explorer 9 supports
>> standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
>> Spend less time writing and  rewriting code and more time creating great
>> experiences on the web. Be a part of the beta today.
>> http://p.sf.net/sfu/beautyoftheweb
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>
>
>
> --
> John Nielson
> mr....@gm...
> (504) 344.7225
>
> ------------------------------------------------------------------------------
> Beautiful is writing same markup. Internet Explorer 9 supports
> standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
> Spend less time writing and  rewriting code and more time creating great
> experiences on the web. Be a part of the beta today.
> http://p.sf.net/sfu/beautyoftheweb
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] cx_Oracle.InterfaceError: not a query

[John N. <mr....@gm...>]

I should let you know that I tacked the cur.execute(query) statement
at the end of my code only in this e-mail.  I've since edited it (for
the sake of getting my work done at the job) to generate an external
SQL script containing all the update statements.  So, for the sake of
troubleshooting, pretend that my code says cur.execute(update_query)

My bad, y'all!



On Fri, Oct 8, 2010 at 3:41 PM, Marco De Paoli <dep...@gm...> wrote:
> there something strange: on the final lines you have just defined an
> update_query string and you call cur.execute(query)
>
> probably you intended "cur.execute(update_query)" in place of
> "cur.execute(query)" (may be a copy/paste problem?)
>
> in this case, you are executing two different sql statements using the same
> cursor: while you are fetching rows from a select, you use *the same cursor*
> to execute an update
>
> Marco
>
> On Fri, Oct 8, 2010 at 6:01 PM, John Nielson <mr....@gm...>
> wrote:
>>
>> tab_col_map = {
>>         'E_APDS':['APD_CORRECTION_REMARK'],
>>         'E_APD_ATTACHMENTS':['ATT_NAME'],
>>         'E_APD_NOTES':['NOTE_SUBJECT','NOTE_TEXT'],
>>         'E_APD_QAS':['QA_RESPONSE_TXT'],
>>         'E_APD_STATUSES':['APD_STATUS_RMKS'],
>>         'E_APMS':['APM_OP_DESC','CORRECTION_DESC'],
>>         'E_APM_ATTACHMENTS':['ATT_NAME'],
>>         'E_APM_NOTES':['NOTE_TEXT'],
>>         'E_APM_QAS':['QA_RESPONSE_TXT'],
>>         'E_APM_STATUSES':['APM_STATUS_RMKS'],
>>         'E_CORRECTIONS':['CORRECTION_DESC'],
>>         'E_CORRECTION_STATUSES':['STATUS_REMARKS'],
>>         'E_END_WELL_OP_REPORTS':['OPERATIONAL_NARRATIVE'],
>>         'E_EOR_COMPLETIONS':['REMARK'],
>>         'E_EOR_HC_BEARING_INTERVALS':['INTERVAL_NAME'],
>>         'E_EOR_NOTES':['NOTE_TEXT'],
>>         'E_EOR_STATUSES':['STATUS_REMARKS'],
>>         'E_INCIDENT_REPORT_ATTACHMENTS':['ATT_NAME'],
>>         'E_INCIDENT_REPORT_CONTACTS':['EMAIL_ADDR'],
>>         'E_INCIDENT_REPORT_STATUSES':['STATUS_REMARKS'],
>>         'E_PIPELINE_DAMAGES':['PPL_DAMAGE_DESC'],
>>         'E_REG_DEPARTURES':['REG_DEPARTURE_TXT'],
>>
>> 'E_RIG_MOVE_NOTIFICATIONS':['REMARKS','RIG_OBSTRUCTION_DESC','RIG_REP_PHONE'],
>>         'E_STORM_PIPELINE_DAMAGES_HIST':['DAMAGE_DESC'],
>>
>> 'E_STORM_PLATFORM_DAMAGES_HIST':['ADDITIONAL_REMARKS','DAMAGE_DESC'],
>>         'E_STORM_POLLUTIONS_HIST':['POLLUTION_REMARKS'],
>>         'E_STORM_REPORT_STATUSES_HIST':['STORM_REPORT_STATUS_RMKS'],
>>         'E_STORM_STR_PROD_CURTAILS_HIST':['CURTAILMENT_REMARKS'],
>>         'E_WAR_NOTES':['NOTE_TEXT'],
>>         'E_WAR_STATUSES':['STATUS_REMARKS'],
>>
>> 'E_WELL_ACTIVITY_REPORTS':['WAR_CORRECTION_NARRATIVE','WAR_SIGNIF_EVENT_NARRATIVE']
>>         }
>>
>>     for table in tab_col_map:
>>         print table
>>         #
>>         # First, get the primary key column of the table we're analyzing
>>         con_query = "select owner, column_name " + \
>>                     "from all_cons_columns " + \
>>                     "where constraint_name = ( " + \
>>                     "select constraint_name " + \
>>                     "from all_constraints " + \
>>                     "where table_name = '" + table + "' " + \
>>                     "and constraint_type = 'P') " + \
>>                     "and table_name = '" + table + "'"
>>
>>         constraints = cur.execute(con_query)
>>         for constraint in constraints:
>>             owner = constraint[0]
>>             uid_col = constraint[1]
>>
>>         full_name = owner + "." + table
>>         print full_name + " : " + uid_col
>>
>>         #
>>         # Next, check each column in list mapped to current table to see
>>         # if it contains any unsupported characters.
>>         for column in tab_col_map[table]:
>>             print "\t%s" % column
>>             #
>>             # Oracle dump() function is used to find 8-bit ASCII code for
>>             # each character returned
>>             query = "select " + uid_col + ", dump(" + column + ") " + \
>>                     "from " + full_name
>>             try:
>>                 rows = cur.execute(query)
>>             except cx_Oracle.DatabaseError, e:
>>                 print "\t" + str(e)
>>                 continue
>>             count = 0
>>             for row in rows:
>>                 #
>>                 # update_flag = False if row doesn't contain
>>                 # the unsupported characters
>>                 update_flag = False
>>                 text_dump = row[1]
>>                 uid = row[0]
>>                 if text_dump.strip() != 'NULL':
>>                     codes = text_dump.split(':')[1].strip().split(',')
>>                     returned_string = ""
>>                     for code in codes:
>>                         code = int(code)
>>                         if code in char_map.keys():
>>                             count += 1
>>                             returned_string += char_map[code]
>>                             if code != 39:
>>                                 #
>>                                 # 39 is the code for the straight single
>> quote.
>>                                 # While this needs to be accounted for
>> (i.e.
>>                                 # single quotes must be escaped to
>> avoid terminating
>>                                 # the update string), the presence of a
>> single
>>                                 # straight quote in a row does not
>> necessitate an
>>                                 # update.
>>                                 update_flag = True
>>                         else:
>>                             returned_string += chr(code)
>>                 if update_flag:
>>                     #
>>                     # If an update is necessary, print row's PK value
>>                     print "\tChange needed for %s = %d" % (uid_col, uid)
>>                     update_query = "update " + full_name + " " + \
>>                                    "set " + column + \
>>                                    " = '" + returned_string + "' " +\
>>                                    "where " + uid_col + \
>>                                    " = " + str(uid)
>>                     cur.execute(query)
>>
>> conn.close()
>>
>>
>> On Fri, Oct 8, 2010 at 10:55 AM, Anthony Tuininga
>> <ant...@gm...> wrote:
>> >
>> > Ok, can you show the code that is causing the problem? Is it
>> > consistently failing with that one statement or does it have to be
>> > after a bunch of other statements first? If you can send me a simple
>> > script that demonstrates the problem I'll look at it. I've never run
>> > into that problem myself unless I accidentally attempted to fetch from
>> > the cursor -- but that doesn't mean there isn't some strange bug
>> > lurking.... :-)
>> >
>> > Anthony
>> >
>> > On Fri, Oct 8, 2010 at 8:52 AM, John Nielson <mr....@gm...>
>> > wrote:
>> > > Right...
>> > >
>> > > I'm using cursor.execute(my_statement) to perform the update.  I am
>> > > aware
>> > > that update statements aren't queries; however, I'm also aware that
>> > > the
>> > > execute method is used to perform updates.  The script iterates
>> > > through a
>> > > whole slew of update statements that work just fine before blowing up
>> > > on
>> > > this one.
>> > >
>> > >
>> > >
>> > > On Thu, Oct 7, 2010 at 10:28 AM, Anthony Tuininga
>> > > <ant...@gm...> wrote:
>> > >>
>> > >> Hi,
>> > >>
>> > >> Not sure exactly what you are trying to accomplish but showing the
>> > >> exact Python code you are executing would be helpful. In any case,
>> > >> the
>> > >> statement shown below is definitely not a query -- it looks very much
>> > >> like an update statement which does not query information but updates
>> > >> information in the database. So you should be simply doing this
>> > >>
>> > >> cursor.execute(your_statement)
>> > >>
>> > >> Do not attempt to fetch from the cursor, though. That will generate
>> > >> the "not a query" exception. Make sense?
>> > >>
>> > >> Anthony
>> > >>
>> > >> On Thu, Oct 7, 2010 at 8:29 AM, John Nielson
>> > >> <mr....@gm...>
>> > >> wrote:
>> > >> > All:
>> > >> >
>> > >> > I have a script that performs a bunch of updates.  However, after
>> > >> > attempting
>> > >> > to perform the following query (carriage returns intentional):
>> > >> >
>> > >> > update NIELSONJ.MYTABLE set COLUMN_1 = '"double quotes"
>> > >> > ''single quotes''
>> > >> > En - dash
>> > >> > Em-dash
>> > >> > .       bullet
>> > >> > ' where COLUMN_2 = -2395
>> > >> >
>> > >> >
>> > >> > I get the following error:
>> > >> >
>> > >> > cx_Oracle.InterfaceError: not a query
>> > >> >
>> > >> >
>> > >> > I've copied and pasted the query into my IDE, executed it, and it
>> > >> > runs
>> > >> > just
>> > >> > fine.  It's definitely a query!  I am mystified.
>> > >> >
>> > >> > Please note what kinds of quotes those are and that all single
>> > >> > quotes
>> > >> > within
>> > >> > the query are doubled to avoid terminating the update string.  The
>> > >> > script is
>> > >> > intended to cleanse the database of data containing weird
>> > >> > characters
>> > >> > like
>> > >> > smart quotes (e.g. left single quotes are replaced with straight
>> > >> > single
>> > >> > quotes).
>> > >> >
>> > >> > --
>> > >> > John Nielson
>> > >> > mr....@gm...
>> > >> > (504) 344.7225
>> > >> >
>> > >> >
>> > >> >
>> > >> > ------------------------------------------------------------------------------
>> > >> > Beautiful is writing same markup. Internet Explorer 9 supports
>> > >> > standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
>> > >> > Spend less time writing and  rewriting code and more time creating
>> > >> > great
>> > >> > experiences on the web. Be a part of the beta today.
>> > >> > http://p.sf.net/sfu/beautyoftheweb
>> > >> > _______________________________________________
>> > >> > cx-oracle-users mailing list
>> > >> > cx-...@li...
>> > >> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>> > >> >
>> > >> >
>> > >>
>> > >>
>> > >>
>> > >> ------------------------------------------------------------------------------
>> > >> Beautiful is writing same markup. Internet Explorer 9 supports
>> > >> standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
>> > >> Spend less time writing and  rewriting code and more time creating
>> > >> great
>> > >> experiences on the web. Be a part of the beta today.
>> > >> http://p.sf.net/sfu/beautyoftheweb
>> > >> _______________________________________________
>> > >> cx-oracle-users mailing list
>> > >> cx-...@li...
>> > >> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>> > >
>> > >
>> > >
>> > > --
>> > > John Nielson
>> > > mr....@gm...
>> > > (504) 344.7225
>> > >
>> > >
>> > > ------------------------------------------------------------------------------
>> > > Beautiful is writing same markup. Internet Explorer 9 supports
>> > > standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
>> > > Spend less time writing and  rewriting code and more time creating
>> > > great
>> > > experiences on the web. Be a part of the beta today.
>> > > http://p.sf.net/sfu/beautyoftheweb
>> > > _______________________________________________
>> > > cx-oracle-users mailing list
>> > > cx-...@li...
>> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>> > >
>> > >
>> >
>> >
>> > ------------------------------------------------------------------------------
>> > Beautiful is writing same markup. Internet Explorer 9 supports
>> > standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
>> > Spend less time writing and  rewriting code and more time creating great
>> > experiences on the web. Be a part of the beta today.
>> > http://p.sf.net/sfu/beautyoftheweb
>> > _______________________________________________
>> > cx-oracle-users mailing list
>> > cx-...@li...
>> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>>
>> --
>> John Nielson
>> mr....@gm...
>> (504) 344.7225
>>
>>
>> ------------------------------------------------------------------------------
>> Beautiful is writing same markup. Internet Explorer 9 supports
>> standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
>> Spend less time writing and  rewriting code and more time creating great
>> experiences on the web. Be a part of the beta today.
>> http://p.sf.net/sfu/beautyoftheweb
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
> ------------------------------------------------------------------------------
> Beautiful is writing same markup. Internet Explorer 9 supports
> standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
> Spend less time writing and  rewriting code and more time creating great
> experiences on the web. Be a part of the beta today.
> http://p.sf.net/sfu/beautyoftheweb
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>



-- 
John Nielson
mr....@gm...
(504) 344.7225

Re: [cx-oracle-users] cx_Oracle.InterfaceError: not a query

[Marco De P. <dep...@gm...>]

there something strange: on the final lines you have just defined an
update_query string and you call cur.execute(query)

probably you intended "cur.execute(update_query)" in place of
"cur.execute(query)" (may be a copy/paste problem?)

in this case, you are executing two different sql statements using the same
cursor: while you are fetching rows from a select, you use *the same cursor*
to execute an update

Marco

On Fri, Oct 8, 2010 at 6:01 PM, John Nielson <mr....@gm...>wrote:

> tab_col_map = {
>         'E_APDS':['APD_CORRECTION_REMARK'],
>         'E_APD_ATTACHMENTS':['ATT_NAME'],
>         'E_APD_NOTES':['NOTE_SUBJECT','NOTE_TEXT'],
>         'E_APD_QAS':['QA_RESPONSE_TXT'],
>         'E_APD_STATUSES':['APD_STATUS_RMKS'],
>         'E_APMS':['APM_OP_DESC','CORRECTION_DESC'],
>         'E_APM_ATTACHMENTS':['ATT_NAME'],
>         'E_APM_NOTES':['NOTE_TEXT'],
>         'E_APM_QAS':['QA_RESPONSE_TXT'],
>         'E_APM_STATUSES':['APM_STATUS_RMKS'],
>         'E_CORRECTIONS':['CORRECTION_DESC'],
>         'E_CORRECTION_STATUSES':['STATUS_REMARKS'],
>         'E_END_WELL_OP_REPORTS':['OPERATIONAL_NARRATIVE'],
>         'E_EOR_COMPLETIONS':['REMARK'],
>         'E_EOR_HC_BEARING_INTERVALS':['INTERVAL_NAME'],
>         'E_EOR_NOTES':['NOTE_TEXT'],
>         'E_EOR_STATUSES':['STATUS_REMARKS'],
>         'E_INCIDENT_REPORT_ATTACHMENTS':['ATT_NAME'],
>         'E_INCIDENT_REPORT_CONTACTS':['EMAIL_ADDR'],
>         'E_INCIDENT_REPORT_STATUSES':['STATUS_REMARKS'],
>         'E_PIPELINE_DAMAGES':['PPL_DAMAGE_DESC'],
>         'E_REG_DEPARTURES':['REG_DEPARTURE_TXT'],
>
> 'E_RIG_MOVE_NOTIFICATIONS':['REMARKS','RIG_OBSTRUCTION_DESC','RIG_REP_PHONE'],
>         'E_STORM_PIPELINE_DAMAGES_HIST':['DAMAGE_DESC'],
>
> 'E_STORM_PLATFORM_DAMAGES_HIST':['ADDITIONAL_REMARKS','DAMAGE_DESC'],
>         'E_STORM_POLLUTIONS_HIST':['POLLUTION_REMARKS'],
>         'E_STORM_REPORT_STATUSES_HIST':['STORM_REPORT_STATUS_RMKS'],
>         'E_STORM_STR_PROD_CURTAILS_HIST':['CURTAILMENT_REMARKS'],
>         'E_WAR_NOTES':['NOTE_TEXT'],
>         'E_WAR_STATUSES':['STATUS_REMARKS'],
>
> 'E_WELL_ACTIVITY_REPORTS':['WAR_CORRECTION_NARRATIVE','WAR_SIGNIF_EVENT_NARRATIVE']
>         }
>
>     for table in tab_col_map:
>         print table
>         #
>         # First, get the primary key column of the table we're analyzing
>         con_query = "select owner, column_name " + \
>                     "from all_cons_columns " + \
>                     "where constraint_name = ( " + \
>                     "select constraint_name " + \
>                     "from all_constraints " + \
>                     "where table_name = '" + table + "' " + \
>                     "and constraint_type = 'P') " + \
>                     "and table_name = '" + table + "'"
>
>         constraints = cur.execute(con_query)
>         for constraint in constraints:
>             owner = constraint[0]
>             uid_col = constraint[1]
>
>         full_name = owner + "." + table
>         print full_name + " : " + uid_col
>
>         #
>         # Next, check each column in list mapped to current table to see
>         # if it contains any unsupported characters.
>         for column in tab_col_map[table]:
>             print "\t%s" % column
>             #
>             # Oracle dump() function is used to find 8-bit ASCII code for
>             # each character returned
>             query = "select " + uid_col + ", dump(" + column + ") " + \
>                     "from " + full_name
>             try:
>                 rows = cur.execute(query)
>             except cx_Oracle.DatabaseError, e:
>                 print "\t" + str(e)
>                 continue
>             count = 0
>             for row in rows:
>                 #
>                 # update_flag = False if row doesn't contain
>                 # the unsupported characters
>                 update_flag = False
>                 text_dump = row[1]
>                 uid = row[0]
>                 if text_dump.strip() != 'NULL':
>                     codes = text_dump.split(':')[1].strip().split(',')
>                     returned_string = ""
>                     for code in codes:
>                         code = int(code)
>                         if code in char_map.keys():
>                             count += 1
>                             returned_string += char_map[code]
>                             if code != 39:
>                                 #
>                                 # 39 is the code for the straight single
> quote.
>                                 # While this needs to be accounted for
> (i.e.
>                                 # single quotes must be escaped to
> avoid terminating
>                                 # the update string), the presence of a
> single
>                                 # straight quote in a row does not
> necessitate an
>                                 # update.
>                                 update_flag = True
>                         else:
>                             returned_string += chr(code)
>                 if update_flag:
>                     #
>                     # If an update is necessary, print row's PK value
>                     print "\tChange needed for %s = %d" % (uid_col, uid)
>                     update_query = "update " + full_name + " " + \
>                                    "set " + column + \
>                                    " = '" + returned_string + "' " +\
>                                    "where " + uid_col + \
>                                    " = " + str(uid)
>                     cur.execute(query)
>
> conn.close()
>
>
> On Fri, Oct 8, 2010 at 10:55 AM, Anthony Tuininga
> <ant...@gm...> wrote:
> >
> > Ok, can you show the code that is causing the problem? Is it
> > consistently failing with that one statement or does it have to be
> > after a bunch of other statements first? If you can send me a simple
> > script that demonstrates the problem I'll look at it. I've never run
> > into that problem myself unless I accidentally attempted to fetch from
> > the cursor -- but that doesn't mean there isn't some strange bug
> > lurking.... :-)
> >
> > Anthony
> >
> > On Fri, Oct 8, 2010 at 8:52 AM, John Nielson <mr....@gm...>
> wrote:
> > > Right...
> > >
> > > I'm using cursor.execute(my_statement) to perform the update.  I am
> aware
> > > that update statements aren't queries; however, I'm also aware that the
> > > execute method is used to perform updates.  The script iterates through
> a
> > > whole slew of update statements that work just fine before blowing up
> on
> > > this one.
> > >
> > >
> > >
> > > On Thu, Oct 7, 2010 at 10:28 AM, Anthony Tuininga
> > > <ant...@gm...> wrote:
> > >>
> > >> Hi,
> > >>
> > >> Not sure exactly what you are trying to accomplish but showing the
> > >> exact Python code you are executing would be helpful. In any case, the
> > >> statement shown below is definitely not a query -- it looks very much
> > >> like an update statement which does not query information but updates
> > >> information in the database. So you should be simply doing this
> > >>
> > >> cursor.execute(your_statement)
> > >>
> > >> Do not attempt to fetch from the cursor, though. That will generate
> > >> the "not a query" exception. Make sense?
> > >>
> > >> Anthony
> > >>
> > >> On Thu, Oct 7, 2010 at 8:29 AM, John Nielson <
> mr....@gm...>
> > >> wrote:
> > >> > All:
> > >> >
> > >> > I have a script that performs a bunch of updates.  However, after
> > >> > attempting
> > >> > to perform the following query (carriage returns intentional):
> > >> >
> > >> > update NIELSONJ.MYTABLE set COLUMN_1 = '"double quotes"
> > >> > ''single quotes''
> > >> > En - dash
> > >> > Em-dash
> > >> > .       bullet
> > >> > ' where COLUMN_2 = -2395
> > >> >
> > >> >
> > >> > I get the following error:
> > >> >
> > >> > cx_Oracle.InterfaceError: not a query
> > >> >
> > >> >
> > >> > I've copied and pasted the query into my IDE, executed it, and it
> runs
> > >> > just
> > >> > fine.  It's definitely a query!  I am mystified.
> > >> >
> > >> > Please note what kinds of quotes those are and that all single
> quotes
> > >> > within
> > >> > the query are doubled to avoid terminating the update string.  The
> > >> > script is
> > >> > intended to cleanse the database of data containing weird characters
> > >> > like
> > >> > smart quotes (e.g. left single quotes are replaced with straight
> single
> > >> > quotes).
> > >> >
> > >> > --
> > >> > John Nielson
> > >> > mr....@gm...
> > >> > (504) 344.7225
> > >> >
> > >> >
> > >> >
> ------------------------------------------------------------------------------
> > >> > Beautiful is writing same markup. Internet Explorer 9 supports
> > >> > standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
> > >> > Spend less time writing and  rewriting code and more time creating
> great
> > >> > experiences on the web. Be a part of the beta today.
> > >> > http://p.sf.net/sfu/beautyoftheweb
> > >> > _______________________________________________
> > >> > cx-oracle-users mailing list
> > >> > cx-...@li...
> > >> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > >> >
> > >> >
> > >>
> > >>
> > >>
> ------------------------------------------------------------------------------
> > >> Beautiful is writing same markup. Internet Explorer 9 supports
> > >> standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
> > >> Spend less time writing and  rewriting code and more time creating
> great
> > >> experiences on the web. Be a part of the beta today.
> > >> http://p.sf.net/sfu/beautyoftheweb
> > >> _______________________________________________
> > >> cx-oracle-users mailing list
> > >> cx-...@li...
> > >> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > >
> > >
> > >
> > > --
> > > John Nielson
> > > mr....@gm...
> > > (504) 344.7225
> > >
> > >
> ------------------------------------------------------------------------------
> > > Beautiful is writing same markup. Internet Explorer 9 supports
> > > standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
> > > Spend less time writing and  rewriting code and more time creating
> great
> > > experiences on the web. Be a part of the beta today.
> > > http://p.sf.net/sfu/beautyoftheweb
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-...@li...
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > >
> > >
> >
> >
> ------------------------------------------------------------------------------
> > Beautiful is writing same markup. Internet Explorer 9 supports
> > standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
> > Spend less time writing and  rewriting code and more time creating great
> > experiences on the web. Be a part of the beta today.
> > http://p.sf.net/sfu/beautyoftheweb
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
>
> --
> John Nielson
> mr....@gm...
> (504) 344.7225
>
>
> ------------------------------------------------------------------------------
> Beautiful is writing same markup. Internet Explorer 9 supports
> standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
> Spend less time writing and  rewriting code and more time creating great
> experiences on the web. Be a part of the beta today.
> http://p.sf.net/sfu/beautyoftheweb
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] cx_Oracle.InterfaceError: not a query

[John N. <mr....@gm...>]

tab_col_map = {
        'E_APDS':['APD_CORRECTION_REMARK'],
        'E_APD_ATTACHMENTS':['ATT_NAME'],
        'E_APD_NOTES':['NOTE_SUBJECT','NOTE_TEXT'],
        'E_APD_QAS':['QA_RESPONSE_TXT'],
        'E_APD_STATUSES':['APD_STATUS_RMKS'],
        'E_APMS':['APM_OP_DESC','CORRECTION_DESC'],
        'E_APM_ATTACHMENTS':['ATT_NAME'],
        'E_APM_NOTES':['NOTE_TEXT'],
        'E_APM_QAS':['QA_RESPONSE_TXT'],
        'E_APM_STATUSES':['APM_STATUS_RMKS'],
        'E_CORRECTIONS':['CORRECTION_DESC'],
        'E_CORRECTION_STATUSES':['STATUS_REMARKS'],
        'E_END_WELL_OP_REPORTS':['OPERATIONAL_NARRATIVE'],
        'E_EOR_COMPLETIONS':['REMARK'],
        'E_EOR_HC_BEARING_INTERVALS':['INTERVAL_NAME'],
        'E_EOR_NOTES':['NOTE_TEXT'],
        'E_EOR_STATUSES':['STATUS_REMARKS'],
        'E_INCIDENT_REPORT_ATTACHMENTS':['ATT_NAME'],
        'E_INCIDENT_REPORT_CONTACTS':['EMAIL_ADDR'],
        'E_INCIDENT_REPORT_STATUSES':['STATUS_REMARKS'],
        'E_PIPELINE_DAMAGES':['PPL_DAMAGE_DESC'],
        'E_REG_DEPARTURES':['REG_DEPARTURE_TXT'],
        'E_RIG_MOVE_NOTIFICATIONS':['REMARKS','RIG_OBSTRUCTION_DESC','RIG_REP_PHONE'],
        'E_STORM_PIPELINE_DAMAGES_HIST':['DAMAGE_DESC'],
        'E_STORM_PLATFORM_DAMAGES_HIST':['ADDITIONAL_REMARKS','DAMAGE_DESC'],
        'E_STORM_POLLUTIONS_HIST':['POLLUTION_REMARKS'],
        'E_STORM_REPORT_STATUSES_HIST':['STORM_REPORT_STATUS_RMKS'],
        'E_STORM_STR_PROD_CURTAILS_HIST':['CURTAILMENT_REMARKS'],
        'E_WAR_NOTES':['NOTE_TEXT'],
        'E_WAR_STATUSES':['STATUS_REMARKS'],
        'E_WELL_ACTIVITY_REPORTS':['WAR_CORRECTION_NARRATIVE','WAR_SIGNIF_EVENT_NARRATIVE']
        }

    for table in tab_col_map:
        print table
        #
        # First, get the primary key column of the table we're analyzing
        con_query = "select owner, column_name " + \
                    "from all_cons_columns " + \
                    "where constraint_name = ( " + \
                    "select constraint_name " + \
                    "from all_constraints " + \
                    "where table_name = '" + table + "' " + \
                    "and constraint_type = 'P') " + \
                    "and table_name = '" + table + "'"

        constraints = cur.execute(con_query)
        for constraint in constraints:
            owner = constraint[0]
            uid_col = constraint[1]

        full_name = owner + "." + table
        print full_name + " : " + uid_col

        #
        # Next, check each column in list mapped to current table to see
        # if it contains any unsupported characters.
        for column in tab_col_map[table]:
            print "\t%s" % column
            #
            # Oracle dump() function is used to find 8-bit ASCII code for
            # each character returned
            query = "select " + uid_col + ", dump(" + column + ") " + \
                    "from " + full_name
            try:
                rows = cur.execute(query)
            except cx_Oracle.DatabaseError, e:
                print "\t" + str(e)
                continue
            count = 0
            for row in rows:
                #
                # update_flag = False if row doesn't contain
                # the unsupported characters
                update_flag = False
                text_dump = row[1]
                uid = row[0]
                if text_dump.strip() != 'NULL':
                    codes = text_dump.split(':')[1].strip().split(',')
                    returned_string = ""
                    for code in codes:
                        code = int(code)
                        if code in char_map.keys():
                            count += 1
                            returned_string += char_map[code]
                            if code != 39:
                                #
                                # 39 is the code for the straight single quote.
                                # While this needs to be accounted for (i.e.
                                # single quotes must be escaped to
avoid terminating
                                # the update string), the presence of a single
                                # straight quote in a row does not
necessitate an
                                # update.
                                update_flag = True
                        else:
                            returned_string += chr(code)
                if update_flag:
                    #
                    # If an update is necessary, print row's PK value
                    print "\tChange needed for %s = %d" % (uid_col, uid)
                    update_query = "update " + full_name + " " + \
                                   "set " + column + \
                                   " = '" + returned_string + "' " +\
                                   "where " + uid_col + \
                                   " = " + str(uid)
                    cur.execute(query)

conn.close()


On Fri, Oct 8, 2010 at 10:55 AM, Anthony Tuininga
<ant...@gm...> wrote:
>
> Ok, can you show the code that is causing the problem? Is it
> consistently failing with that one statement or does it have to be
> after a bunch of other statements first? If you can send me a simple
> script that demonstrates the problem I'll look at it. I've never run
> into that problem myself unless I accidentally attempted to fetch from
> the cursor -- but that doesn't mean there isn't some strange bug
> lurking.... :-)
>
> Anthony
>
> On Fri, Oct 8, 2010 at 8:52 AM, John Nielson <mr....@gm...> wrote:
> > Right...
> >
> > I'm using cursor.execute(my_statement) to perform the update.  I am aware
> > that update statements aren't queries; however, I'm also aware that the
> > execute method is used to perform updates.  The script iterates through a
> > whole slew of update statements that work just fine before blowing up on
> > this one.
> >
> >
> >
> > On Thu, Oct 7, 2010 at 10:28 AM, Anthony Tuininga
> > <ant...@gm...> wrote:
> >>
> >> Hi,
> >>
> >> Not sure exactly what you are trying to accomplish but showing the
> >> exact Python code you are executing would be helpful. In any case, the
> >> statement shown below is definitely not a query -- it looks very much
> >> like an update statement which does not query information but updates
> >> information in the database. So you should be simply doing this
> >>
> >> cursor.execute(your_statement)
> >>
> >> Do not attempt to fetch from the cursor, though. That will generate
> >> the "not a query" exception. Make sense?
> >>
> >> Anthony
> >>
> >> On Thu, Oct 7, 2010 at 8:29 AM, John Nielson <mr....@gm...>
> >> wrote:
> >> > All:
> >> >
> >> > I have a script that performs a bunch of updates.  However, after
> >> > attempting
> >> > to perform the following query (carriage returns intentional):
> >> >
> >> > update NIELSONJ.MYTABLE set COLUMN_1 = '"double quotes"
> >> > ''single quotes''
> >> > En - dash
> >> > Em-dash
> >> > .       bullet
> >> > ' where COLUMN_2 = -2395
> >> >
> >> >
> >> > I get the following error:
> >> >
> >> > cx_Oracle.InterfaceError: not a query
> >> >
> >> >
> >> > I've copied and pasted the query into my IDE, executed it, and it runs
> >> > just
> >> > fine.  It's definitely a query!  I am mystified.
> >> >
> >> > Please note what kinds of quotes those are and that all single quotes
> >> > within
> >> > the query are doubled to avoid terminating the update string.  The
> >> > script is
> >> > intended to cleanse the database of data containing weird characters
> >> > like
> >> > smart quotes (e.g. left single quotes are replaced with straight single
> >> > quotes).
> >> >
> >> > --
> >> > John Nielson
> >> > mr....@gm...
> >> > (504) 344.7225
> >> >
> >> >
> >> > ------------------------------------------------------------------------------
> >> > Beautiful is writing same markup. Internet Explorer 9 supports
> >> > standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
> >> > Spend less time writing and  rewriting code and more time creating great
> >> > experiences on the web. Be a part of the beta today.
> >> > http://p.sf.net/sfu/beautyoftheweb
> >> > _______________________________________________
> >> > cx-oracle-users mailing list
> >> > cx-...@li...
> >> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >> >
> >> >
> >>
> >>
> >> ------------------------------------------------------------------------------
> >> Beautiful is writing same markup. Internet Explorer 9 supports
> >> standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
> >> Spend less time writing and  rewriting code and more time creating great
> >> experiences on the web. Be a part of the beta today.
> >> http://p.sf.net/sfu/beautyoftheweb
> >> _______________________________________________
> >> cx-oracle-users mailing list
> >> cx-...@li...
> >> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
> >
> > --
> > John Nielson
> > mr....@gm...
> > (504) 344.7225
> >
> > ------------------------------------------------------------------------------
> > Beautiful is writing same markup. Internet Explorer 9 supports
> > standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
> > Spend less time writing and  rewriting code and more time creating great
> > experiences on the web. Be a part of the beta today.
> > http://p.sf.net/sfu/beautyoftheweb
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
>
> ------------------------------------------------------------------------------
> Beautiful is writing same markup. Internet Explorer 9 supports
> standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
> Spend less time writing and  rewriting code and more time creating great
> experiences on the web. Be a part of the beta today.
> http://p.sf.net/sfu/beautyoftheweb
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users



--
John Nielson
mr....@gm...
(504) 344.7225

Re: [cx-oracle-users] cx_Oracle.InterfaceError: not a query

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

Ok, can you show the code that is causing the problem? Is it
consistently failing with that one statement or does it have to be
after a bunch of other statements first? If you can send me a simple
script that demonstrates the problem I'll look at it. I've never run
into that problem myself unless I accidentally attempted to fetch from
the cursor -- but that doesn't mean there isn't some strange bug
lurking.... :-)

Anthony

On Fri, Oct 8, 2010 at 8:52 AM, John Nielson <mr....@gm...> wrote:
> Right...
>
> I'm using cursor.execute(my_statement) to perform the update.  I am aware
> that update statements aren't queries; however, I'm also aware that the
> execute method is used to perform updates.  The script iterates through a
> whole slew of update statements that work just fine before blowing up on
> this one.
>
>
>
> On Thu, Oct 7, 2010 at 10:28 AM, Anthony Tuininga
> <ant...@gm...> wrote:
>>
>> Hi,
>>
>> Not sure exactly what you are trying to accomplish but showing the
>> exact Python code you are executing would be helpful. In any case, the
>> statement shown below is definitely not a query -- it looks very much
>> like an update statement which does not query information but updates
>> information in the database. So you should be simply doing this
>>
>> cursor.execute(your_statement)
>>
>> Do not attempt to fetch from the cursor, though. That will generate
>> the "not a query" exception. Make sense?
>>
>> Anthony
>>
>> On Thu, Oct 7, 2010 at 8:29 AM, John Nielson <mr....@gm...>
>> wrote:
>> > All:
>> >
>> > I have a script that performs a bunch of updates.  However, after
>> > attempting
>> > to perform the following query (carriage returns intentional):
>> >
>> > update NIELSONJ.MYTABLE set COLUMN_1 = '"double quotes"
>> > ''single quotes''
>> > En - dash
>> > Em-dash
>> > .       bullet
>> > ' where COLUMN_2 = -2395
>> >
>> >
>> > I get the following error:
>> >
>> > cx_Oracle.InterfaceError: not a query
>> >
>> >
>> > I've copied and pasted the query into my IDE, executed it, and it runs
>> > just
>> > fine.  It's definitely a query!  I am mystified.
>> >
>> > Please note what kinds of quotes those are and that all single quotes
>> > within
>> > the query are doubled to avoid terminating the update string.  The
>> > script is
>> > intended to cleanse the database of data containing weird characters
>> > like
>> > smart quotes (e.g. left single quotes are replaced with straight single
>> > quotes).
>> >
>> > --
>> > John Nielson
>> > mr....@gm...
>> > (504) 344.7225
>> >
>> >
>> > ------------------------------------------------------------------------------
>> > Beautiful is writing same markup. Internet Explorer 9 supports
>> > standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
>> > Spend less time writing and  rewriting code and more time creating great
>> > experiences on the web. Be a part of the beta today.
>> > http://p.sf.net/sfu/beautyoftheweb
>> > _______________________________________________
>> > cx-oracle-users mailing list
>> > cx-...@li...
>> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>> >
>> >
>>
>>
>> ------------------------------------------------------------------------------
>> Beautiful is writing same markup. Internet Explorer 9 supports
>> standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
>> Spend less time writing and  rewriting code and more time creating great
>> experiences on the web. Be a part of the beta today.
>> http://p.sf.net/sfu/beautyoftheweb
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
>
> --
> John Nielson
> mr....@gm...
> (504) 344.7225
>
> ------------------------------------------------------------------------------
> Beautiful is writing same markup. Internet Explorer 9 supports
> standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
> Spend less time writing and  rewriting code and more time creating great
> experiences on the web. Be a part of the beta today.
> http://p.sf.net/sfu/beautyoftheweb
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] cx_Oracle.InterfaceError: not a query

[John N. <mr....@gm...>]

Right...

I'm using cursor.execute(my_statement) to perform the update.  I am aware
that update statements aren't queries; however, I'm also aware that the
execute method is used to perform updates.  The script iterates through a
whole slew of update statements that work just fine before blowing up on
this one.



On Thu, Oct 7, 2010 at 10:28 AM, Anthony Tuininga <
ant...@gm...> wrote:

> Hi,
>
> Not sure exactly what you are trying to accomplish but showing the
> exact Python code you are executing would be helpful. In any case, the
> statement shown below is definitely not a query -- it looks very much
> like an update statement which does not query information but updates
> information in the database. So you should be simply doing this
>
> cursor.execute(your_statement)
>
> Do not attempt to fetch from the cursor, though. That will generate
> the "not a query" exception. Make sense?
>
> Anthony
>
> On Thu, Oct 7, 2010 at 8:29 AM, John Nielson <mr....@gm...>
> wrote:
> > All:
> >
> > I have a script that performs a bunch of updates.  However, after
> attempting
> > to perform the following query (carriage returns intentional):
> >
> > update NIELSONJ.MYTABLE set COLUMN_1 = '"double quotes"
> > ''single quotes''
> > En - dash
> > Em-dash
> > .       bullet
> > ' where COLUMN_2 = -2395
> >
> >
> > I get the following error:
> >
> > cx_Oracle.InterfaceError: not a query
> >
> >
> > I've copied and pasted the query into my IDE, executed it, and it runs
> just
> > fine.  It's definitely a query!  I am mystified.
> >
> > Please note what kinds of quotes those are and that all single quotes
> within
> > the query are doubled to avoid terminating the update string.  The script
> is
> > intended to cleanse the database of data containing weird characters like
> > smart quotes (e.g. left single quotes are replaced with straight single
> > quotes).
> >
> > --
> > John Nielson
> > mr....@gm...
> > (504) 344.7225
> >
> >
> ------------------------------------------------------------------------------
> > Beautiful is writing same markup. Internet Explorer 9 supports
> > standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
> > Spend less time writing and  rewriting code and more time creating great
> > experiences on the web. Be a part of the beta today.
> > http://p.sf.net/sfu/beautyoftheweb
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
>
>
> ------------------------------------------------------------------------------
> Beautiful is writing same markup. Internet Explorer 9 supports
> standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
> Spend less time writing and  rewriting code and more time creating great
> experiences on the web. Be a part of the beta today.
> http://p.sf.net/sfu/beautyoftheweb
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>



-- 
John Nielson
mr....@gm...
(504) 344.7225

Re: [cx-oracle-users] cx_Oracle.InterfaceError: not a query

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

Hi,

Not sure exactly what you are trying to accomplish but showing the
exact Python code you are executing would be helpful. In any case, the
statement shown below is definitely not a query -- it looks very much
like an update statement which does not query information but updates
information in the database. So you should be simply doing this

cursor.execute(your_statement)

Do not attempt to fetch from the cursor, though. That will generate
the "not a query" exception. Make sense?

Anthony

On Thu, Oct 7, 2010 at 8:29 AM, John Nielson <mr....@gm...> wrote:
> All:
>
> I have a script that performs a bunch of updates.  However, after attempting
> to perform the following query (carriage returns intentional):
>
> update NIELSONJ.MYTABLE set COLUMN_1 = '"double quotes"
> ''single quotes''
> En - dash
> Em-dash
> .       bullet
> ' where COLUMN_2 = -2395
>
>
> I get the following error:
>
> cx_Oracle.InterfaceError: not a query
>
>
> I've copied and pasted the query into my IDE, executed it, and it runs just
> fine.  It's definitely a query!  I am mystified.
>
> Please note what kinds of quotes those are and that all single quotes within
> the query are doubled to avoid terminating the update string.  The script is
> intended to cleanse the database of data containing weird characters like
> smart quotes (e.g. left single quotes are replaced with straight single
> quotes).
>
> --
> John Nielson
> mr....@gm...
> (504) 344.7225
>
> ------------------------------------------------------------------------------
> Beautiful is writing same markup. Internet Explorer 9 supports
> standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
> Spend less time writing and  rewriting code and more time creating great
> experiences on the web. Be a part of the beta today.
> http://p.sf.net/sfu/beautyoftheweb
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

[cx-oracle-users] cx_Oracle.InterfaceError: not a query

[John N. <mr....@gm...>]

All:

I have a script that performs a bunch of updates.  However, after attempting
to perform the following query (carriage returns intentional):

update NIELSONJ.MYTABLE set COLUMN_1 = '"double quotes"
''single quotes''
En - dash
Em-dash
.       bullet
' where COLUMN_2 = -2395


I get the following error:

cx_Oracle.InterfaceError: not a query


I've copied and pasted the query into my IDE, executed it, and it runs just
fine.  It's definitely a query!  I am mystified.

Please note what kinds of quotes those are and that all single quotes within
the query are doubled to avoid terminating the update string.  The script is
intended to cleanse the database of data containing weird characters like
smart quotes (e.g. left single quotes are replaced with straight single
quotes).

-- 
John Nielson
mr....@gm...
(504) 344.7225

[cx-oracle-users] Re. Greenlet compatibility for cx_oracle?

[Jerry S. <jsp...@gm...>]

Amaury Forgeot wrote:

> I'm afraid it will be difficult to do the same
> with Oracle. psycopg does have an asynchronous
> mode where all commands are nonblocking.
>
> Oracle has nothing like this. OCIStmtExecute()
> is a blocking call, and won't play nicely with
> Greenlets. Asynchronous framework like twisted
> execute Oracle queries in a dedicated thread.
> Maybe you could do the same.

It looks like Eventlet's db_pool is a similar
feature to the one in Twisted.

We'll start looking there for a solution.


Thanks for the prompt reply!
Jerry S.

Re: [cx-oracle-users] Greenlet compatibility for cx_oracle?

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

Hi,

2010/9/27 Jerry Spicklemire <jsp...@gm...>:
> Greetings,
>
> I've been scouring the web for information on how to use
> cx-oracle in a compatible way with Greenlets.
>
> Well, specifically, with Eventlet, which uses greenlets as
> the foundation of its async / concurrency features.
>
> Recently, when the PostgreSQL folks added async hooks,
> and psycopg2 was updated to take advantage of them,
> lots of things "just work", with Eventlet "monkeypatching".
>
> Any hope that cx_oracle will grow similar abilities,
> any time soon?

I'm afraid it will be difficult to do the same with Oracle.
psycopg does have an asynchronous mode where all commands
are nonblocking.

Oracle has nothing like this. OCIStmtExecute() is a blocking call,
and won't play nicely with Greenlets.
Asynchronous framework like twisted execute Oracle queries in
a dedicated thread. Maybe you could do the same.

-- 
Amaury Forgeot d'Arc

[cx-oracle-users] Greenlet compatibility for cx_oracle?

[Jerry S. <jsp...@gm...>]

Greetings,

I've been scouring the web for information on how to use
cx-oracle in a compatible way with Greenlets.

Well, specifically, with Eventlet, which uses greenlets as
the foundation of its async / concurrency features.

Recently, when the PostgreSQL folks added async hooks,
and psycopg2 was updated to take advantage of them,
lots of things "just work", with Eventlet "monkeypatching".

https://lists.secondlife.com/pipermail/eventletdev/2010-May/000816.html

All fine and dandy, except the word has come down
that we must use Oracle 11i for our backend.

Any hope that cx_oracle will grow similar abilities,
any time soon?

Thanks in advance for any advice,
Jerry S.

Re: [cx-oracle-users] cx_Oracle opens an excessive number of sessions

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

Hello,

We just encountered a similar issue where there were too many processes on the
DB server.  the number of rows in v$process was much higher that the number in
v$session.  Eventually some programs get an error: "ORA-00020: maximum number
of processes exceeded"

Here we have a bunch of processes that use a database connection, then "go to
sleep" for a while. They do close the connection, but the object is kept in a
global variable.

I think this is a bug in cx_Oracle: when connection.close() is called,
OCISessionEnd() is called, but OCIServerDetach() is not.  It will be called
only by connection.__del__, when the connection object is itself destroyed.

I tried to move the call to OCIServerDetach() in Connection_Close, and indeed
the occurrence in v$process disappears when I call connection.close().
Since a closed connection is unusable and the OCIServer handle cannot be
reused, it makes more sense to free this resource sooner.

Or course the workaround is to delete the connection object, with a
"conn=None" or similar.  But cx_Oracle should be changed IMO.
What do you think?

2010/7/28 rwe <rog...@to...>:
> Hi,
> I have been chasing a similar problem.
> The same code runs on several constellations without problem, except for
> one where I get an
> excessove number of processes on the DB server. Killing the process gets
> rid of them.
>
> I will check to see what version of cx_Oracle, Oracle client and Oracle
> are in use, as I
> am pretty sure that it is a praticular combination that produces the
> problem.

-- 
Amaury Forgeot d'Arc

Re: [cx-oracle-users] Oracle Workspace Manager

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

Hi,

I have not done so but cx_Oracle can do anything that you can do
through SQL*Plus, for example. So if there are stored procedures that
you can call or select statements that you can issue you can do those
through cx_Oracle just as easily as anything else.

Anthony

On Thu, Sep 23, 2010 at 3:47 AM, Colin White
<col...@op...> wrote:
> Hi,
>
> I am wondering whether anyone has previously used cx_oracle to
> successfully work with the Workspace Manager in order to load data into
> versioned tables.
>
> Thanks in advance,
>
> Colin
>
> ------------------------------------------------------------------------------
> Start uncovering the many advantages of virtual appliances
> and start using them to simplify application deployment and
> accelerate your shift to cloud computing.
> http://p.sf.net/sfu/novell-sfdev2dev
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>