Re: Oracle ODBC (Native) and Visual Basic ADO 2.5 Timeouts and Hanging Clients

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 5 Jan 2003 14:35:27 -0800
Message-ID: <92eeeff0.0301051435.62ec845d_at_posting.google.com>


sara.k.koike_at_accenture.com (Sara Koike) wrote in message news:<d52e54dc.0301030824.7c3d45cb_at_posting.google.com>...
> We have a program using Visual Basic ADO 2.5 with Oracle ODBC Native
> for 8.1.7 and 8.1.6 connecting an Oracle 8.1.7.4 database. In the
> field, many Visual Basic clients upload data via Oracle ODBC to a
> central database. The network we are finding out is not the best.
> Intermittently we have network blips, although the network folks have
> been unable to find anything. What we see is the following:
>
> Intermittently connections and commands to the database will hang and
> wait for a TCP/IP response back. We never see a session still
> residing on the database. Indicating that the session went inactive
> on the db and was cleaned up by Oracle, but the client never receives
> an error, meaning Net8 or ODBC never raises an error.
>
> We have reproduced this in a very hokey way. We coded up a vb client
> and a stored proc. The vb client calls the proc, and the proc loops
> until we insert a record into table. At the exact same time the
> record is inserted we pull the network connection out of the client
> for a split second and put it back in. Presumably, the stored proc
> returns a response to the vb client, but b/c the client is off the
> network, the packet is lost. As a result, the client hangs and no
> error is raised. Yesterday, Oracle Tech Support said this is by
> design, which is very bizarre.
>
> We have obtained SQLNET logs for the clients hanging. The trace is at
> the bottom of the post.
>
> We also have talked to Microsoft re: ADO 2.5. They have indicated
> that the network handle should occur at the ODBC level.
>
> The hang can occur on a ado.connection open, or any type of
> ado.command execution. We use the Connection Timeout in ADO, but we
> do not see any errors raised from it - the connection open stills hang
> even with the connection timeout set. Recently we have changed to
> asynchronous processing on the ado.connection open, but we haven't
> seen the error again, so we don't know if it works. We have the
> Command Timeout property set that should presumably handle hangs on
> executes but this only works 9 out of 10 times. Sometimes a hung
> command will timeout and sometimes it won't.
>
> If anyone has seen any behavior that is similar, I would appreciate
> any guidance or thoughts you would have on this. Oracle has been
> bouncing us between the Network group and the ODBC group, and they
> haven't given us much guidance.
>
> Thanks for your help in advance,
> Sara K. Koike
>
> (1032) [21-NOV-2002 00:00:40] nspsend: 01 4E 00 00 06 00 00 00
> |.N......|
> (1032) [21-NOV-2002 00:00:40] nspsend: 00 00 11 69 0A 4C A1 C1
> |...i.L..|
> (1032) [21-NOV-2002 00:00:40] nspsend: 01 01 00 00 00 01 00 00
> |........|
> (1032) [21-NOV-2002 00:00:40] nspsend: 00 03 5E 0B 29 04 04 00
> |..^.)...|
> (1032) [21-NOV-2002 00:00:40] nspsend: 00 00 00 00 1C BB C1 01
> |........|
> (1032) [21-NOV-2002 00:00:40] nspsend: 9A 00 00 00 10 05 C1 01
> |........|
> (1032) [21-NOV-2002 00:00:40] nspsend: 0A 00 00 00 00 00 00 00
> |........|
> (1032) [21-NOV-2002 00:00:40] nspsend: 34 05 C1 01 00 00 00 00
> |4.......|
> (1032) [21-NOV-2002 00:00:40] nspsend: 01 00 00 00 00 00 00 00
> |........|
> (1032) [21-NOV-2002 00:00:40] nspsend: 5C BA C1 01 01 00 00 00
> |\.......|
> (1032) [21-NOV-2002 00:00:40] nspsend: 00 00 00 00 00 00 00 00
> |........|
> (1032) [21-NOV-2002 00:00:40] nspsend: 00 00 00 00 00 00 00 00
> |........|
> (1032) [21-NOV-2002 00:00:40] nspsend: 36 05 C1 01 FE 40 42 65
> |6...._at_Be|
> (1032) [21-NOV-2002 00:00:40] nspsend: 67 69 6E 20 55 50 44 41 |gin
> UPDA|
> (1032) [21-NOV-2002 00:00:40] nspsend: 54 45 42 55 4C 4B 49 4E
> |TEBULKIN|
> (1032) [21-NOV-2002 00:00:40] nspsend: 53 45 52 54 5F 30 30 32
> |SERT_002|
> (1032) [21-NOV-2002 00:00:40] nspsend: 5F 50 4B 47 2E 55 50 44
> |_PKG.UPD|
> (1032) [21-NOV-2002 00:00:40] nspsend: 41 54 45 42 55 4C 4B 49
> |ATEBULKI|
> (1032) [21-NOV-2002 00:00:40] nspsend: 4E 53 45 52 54 5F 30 30
> |NSERT_00|
> (1032) [21-NOV-2002 00:00:40] nspsend: 32 5F 50 52 43 28 27 4D
> |2_PRC('M|
> (1032) [21-NOV-2002 00:00:40] nspsend: 4B 45 54 48 27 2C 40 27
> |KETH',_at_'|
> (1032) [21-NOV-2002 00:00:40] nspsend: 4D 31 45 36 32 55 39 30
> |M1E62U90|
> (1032) [21-NOV-2002 00:00:40] nspsend: 52 27 2C 27 32 30 2F 31
> |R','20/1|
> (1032) [21-NOV-2002 00:00:40] nspsend: 31 2F 32 30 30 32 20 32
> |1/2002 2|
> (1032) [21-NOV-2002 00:00:40] nspsend: 33 3A 35 35 3A 31 32 27
> |3:55:12'|
> (1032) [21-NOV-2002 00:00:40] nspsend: 2C 27 30 2E 30 35 27 2C
> |,'0.05',|
> (1032) [21-NOV-2002 00:00:40] nspsend: 27 31 30 33 27 2C 27 36
> |'103','6|
> (1032) [21-NOV-2002 00:00:40] nspsend: 5F 54 53 54 27 2C 27 30
> |_TST','0|
> (1032) [21-NOV-2002 00:00:40] nspsend: 31 2F 30 31 2F 32 30 1A
> |1/01/20.|
> (1032) [21-NOV-2002 00:00:40] nspsend: 39 39 20 30 30 3A 30 30 |99
> 00:00|
> (1032) [21-NOV-2002 00:00:40] nspsend: 3A 30 30 27 2C 20 3A 72 |:00',
> :r|
> (1032) [21-NOV-2002 00:00:40] nspsend: 73 65 74 29 3B 20 45 6E |set);
> En|
> (1032) [21-NOV-2002 00:00:40] nspsend: 64 3B 00 01 00 00 00 01
> |d;......|
> (1032) [21-NOV-2002 00:00:40] nspsend: 00 00 00 00 00 00 00 00
> |........|
> (1032) [21-NOV-2002 00:00:40] nspsend: 00 00 00 00 00 00 00 00
> |........|
> (1032) [21-NOV-2002 00:00:40] nspsend: 00 00 00 00 00 00 00 08
> |........|
> (1032) [21-NOV-2002 00:00:40] nspsend: 00 00 00 00 00 00 00 04
> |........|
> (1032) [21-NOV-2002 00:00:40] nspsend: 00 00 00 01 66 01 00 00
> |....f...|
> (1032) [21-NOV-2002 00:00:40] nspsend: 01 00 00 00 00 00 00 00
> |........|
> (1032) [21-NOV-2002 00:00:40] nspsend: 00 00 00 00 00 00 00 00
> |........|
> (1032) [21-NOV-2002 00:00:40] nspsend: 00 00 00 00 00 00 00 00
> |........|
> (1032) [21-NOV-2002 00:00:40] nspsend: 07 04 00 00 00 00 00 00
> |........|
> [21-NOV-2002 00:00:40] nspsend: normal exit
> [21-NOV-2002 00:00:40] nsdofls: exit (0)
> [21-NOV-2002 00:00:40] snsbitts_ts: entry
> [21-NOV-2002 00:00:40] snsbitts_ts: acquired the bit
> [21-NOV-2002 00:00:40] snsbitts_ts: normal exit
> [21-NOV-2002 00:00:40] nsdo: nsctxrnk=0
> [21-NOV-2002 00:00:40] snsbitcl_ts: entry
> [21-NOV-2002 00:00:40] snsbitcl_ts: normal exit
> [21-NOV-2002 00:00:40] nsdo: normal exit
> [21-NOV-2002 00:00:40] nsdo: entry
> [21-NOV-2002 00:00:40] nsdo: cid=0, opcode=85, *bl=0, *what=0,
> uflgs=0x0, cflgs=0x3
> [21-NOV-2002 00:00:40] snsbitts_ts: entry
> [21-NOV-2002 00:00:40] snsbitts_ts: acquired the bit
> [21-NOV-2002 00:00:40] snsbitts_ts: normal exit
> [21-NOV-2002 00:00:40] nsdo: rank=64, nsctxrnk=0
> [21-NOV-2002 00:00:40] snsbitcl_ts: entry
> [21-NOV-2002 00:00:40] snsbitcl_ts: normal exit
> [21-NOV-2002 00:00:40] nsdo: nsctx: state=8, flg=0x420d, mvd=0
> [21-NOV-2002 00:00:40] nsdo: gtn=127, gtc=127, ptn=10, ptc=2023
> [21-NOV-2002 00:00:40] snsbitts_ts: entry
> [21-NOV-2002 00:00:40] snsbitts_ts: acquired the bit
> [21-NOV-2002 00:00:40] snsbitts_ts: normal exit
> [21-NOV-2002 00:00:40] snsbitcl_ts: entry
> [21-NOV-2002 00:00:40] snsbitcl_ts: normal exit
> [21-NOV-2002 00:00:40] nsdo: switching to application buffer
> [21-NOV-2002 00:00:40] nsrdr: entry
> [21-NOV-2002 00:00:40] nsrdr: recving a packet
> [21-NOV-2002 00:00:40] nsprecv: entry
> [21-NOV-2002 00:00:40] nsprecv: reading from transport...
> [21-NOV-2002 00:00:40] nttrd: entry

Have you tried to bypass ADO/ODBC and connect directly over Net8 using Sqlplus and if so... do you still see the same behaviour?

Regards
/Rauf Sarwar Received on Sun Jan 05 2003 - 23:35:27 CET

Original text of this message