Commit returns SQL_NO_DATA_FOUND and empty err_list causing exception.

[GoogleCodeExporter]

What steps will reproduce the problem?
1. I call a stored procedure.  Not sure why it works most times and not others
2. SP executes fine, but I get an error on the commit.
3. I suspect this starts when the network can not look up my DNS

What is the expected output? What do you see instead?
I would expect that the commit does not throw an error.

What version of the product are you using? On what operating system?
Downloaded today (12/11)

Please provide any additional information below.
So I'm calling a SP, the execute seems to work fine.  When I try to commit an 
error is thrown.  I traced it to line 947 in pypyodbc. 

            state = err_list[0][0]

In my case err_list = [], so the exception is the 'IndexError: list index out 
of range'.   This is all inside of the ctrl_err method.

So the return value of ODBC_func100 (NO DATA).  The first thing it does is try 
to 

Original issue reported on code.google.com by spra...@gmail.com on 11 Dec 2013 at 9:29

[GoogleCodeExporter]

can you post the full error log?

Original comment by jiangwen...@gmail.com on 16 Dec 2013 at 3:01

• Changed state: Started

[GoogleCodeExporter]

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = 
<DirectorIT.Foundation.ExecutionCoordinator.ExecutionCoordinator_Common.clECComm
on object at 0x00000000040250B8>

    def purge_Database(self):
        print('Purging database')

        # call the database stored procedure to clean up the database
        lCursor = self._GlobalDBConnection.cursor()

        try:
            # Must reserve space for the input/output parameters.
            """
                @aDisplayMessage varchar(4000)
                    ,@aReturnCode int
                    ,@aLogMessageInfo varchar(4000)
                    ,@aConfirm VARCHAR(50)
                """
            displayMsg = ''
            returnCode = 0
            logMsgInfo = ''
            confirm = 'PurgeAll'

            # Call our SP to purge and start clean.
    #            lCursor.execute("{exec DirectorIT_Global.Planning.PurgeFulfillmentAndPick('{0}',{1},'{2}','{3}')}".format(displayMsg, returnCode, logMsgInfo, confirm))
            lCursor.execute("{CALL Planning.PurgeFulfillmentAndPick(?,?,?,?)}",
                       (displayMsg, returnCode, logMsgInfo, confirm))

>           lCursor.commit()

File 
"C:\SPE\GII_2013_R1\40-AutomatedTests\IntegrationTests\Python\src\DirectorIT\Fou
ndation\ExecutionCoordinator\ExecutionCoordinator_Common.py", line 207

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <pypyodbc.Cursor object at 0x000000000402A9B0>

    def commit(self):
>       self.connection.commit()

File "C:\SPE\GII_2013_R1\40-AutomatedTests\PyDirector\TPP\pypyodbc.py", line 
2191

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <pypyodbc.Connection object at 0x0000000004025438>

    def commit(self):
        if not self.connected:
            raise ProgrammingError('HY000','Attempt to use a closed connection.')

        ret = SQLEndTran(SQL_HANDLE_DBC, self.dbc_h, SQL_COMMIT)
        if ret != SQL_SUCCESS:
>           check_success(self, ret)

File "C:\SPE\GII_2013_R1\40-AutomatedTests\PyDirector\TPP\pypyodbc.py", line 
2428

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

ODBC_obj = <pypyodbc.Connection object at 0x0000000004025438>, ret = -1

    def check_success(ODBC_obj, ret):
        """ Validate return value, if not success, raise exceptions based on the handle """
        if ret not in (SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA):
            if isinstance(ODBC_obj, Cursor):
                ctrl_err(SQL_HANDLE_STMT, ODBC_obj.stmt_h, ret, ODBC_obj.ansi)
            elif isinstance(ODBC_obj, Connection):
>               ctrl_err(SQL_HANDLE_DBC, ODBC_obj.dbc_h, ret, ODBC_obj.ansi)

File "C:\SPE\GII_2013_R1\40-AutomatedTests\PyDirector\TPP\pypyodbc.py", line 952

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

ht = 2, h = c_void_p(5303808), val_ret = -1, ansi = False

    def ctrl_err(ht, h, val_ret, ansi):
        """Classify type of ODBC error from (type of handle, handle, return value)
        , and raise with a list"""

        if ansi:
            state = create_buffer(22)
            Message = create_buffer(1024*10)
            ODBC_func = ODBC_API.SQLGetDiagRec
            if py_v3:
                raw_s = lambda s: bytes(s,'ascii')
            else:
                raw_s = str_8b
        else:
            state = create_buffer_u(22)
            Message = create_buffer_u(1024*10)
            ODBC_func = ODBC_API.SQLGetDiagRecW
            raw_s = unicode
        NativeError = ctypes.c_int()
        Buffer_len = c_short()
        err_list = []
        number_errors = 1

        while 1:
            ret = ODBC_func(ht, h, number_errors, state, \
                NativeError, Message, len(Message), ADDR(Buffer_len))
            if ret == SQL_NO_DATA_FOUND:
                #No more data, I can raise
                #print(err_list[0][1])
>               state = err_list[0][0]
E               IndexError: list index out of range

File "C:\SPE\GII_2013_R1\40-AutomatedTests\PyDirector\TPP\pypyodbc.py", line 917
IndexError
------------------------------- 

Here is how we setup our DB Object.

    _DBCatalog = 'DirectorIT_Global'
    _GlobalConnectString = 'DSN={};UID=sa;PWD=BOB'.format(_DBCatalog)

    def __init__(self):
        #  This creates our MD Manager connection used for the rest of the test
        self._GlobalDBConnection = pypyodbc.connect(self._GlobalConnectString)

Original comment by spra...@gmail.com on 16 Dec 2013 at 4:16

[GoogleCodeExporter]

Can you print out the ret value in below code when error occours?

File "C:\SPE\GII_2013_R1\40-AutomatedTests\PyDirector\TPP\pypyodbc.py", line 
2191

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <pypyodbc.Connection object at 0x0000000004025438>

    def commit(self):
        if not self.connected:
            raise ProgrammingError('HY000','Attempt to use a closed connection.')

        ret = SQLEndTran(SQL_HANDLE_DBC, self.dbc_h, SQL_COMMIT)
        if ret != SQL_SUCCESS:
>           check_success(self, ret)

Original comment by jiangwen...@gmail.com on 17 Dec 2013 at 3:26

[GoogleCodeExporter]

Any resolution or work-around for this?  I am having the same issue.

Original comment by ronsmit...@gmail.com on 3 Feb 2014 at 7:33

[GoogleCodeExporter]

I never had it fixed in pypyodbc.  My resolution was to let pypyodbc handle 
transaction, not the sp.

Original comment by spra...@gmail.com on 3 Feb 2014 at 7:36