Oracle ODBC (Native) and Visual Basic ADO 2.5 Timeouts and Hanging Clients
Sorry for the duplicate post, I didn't insert the entire message text.
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{*filter*}. 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
(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
(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
