Conference abbott::visual_basic

2483.0. "PC intermittant hang on ODBC SELECT statement" by AWECIM::HOWARD () Thu Jan 30 1997 12:56

Problem:  PC intermittant hangs on ODBC SELECT statement.

My VB 4.0 application opens a local .MDB database that contains a few local
tables and a few tables that are linked via ODBC to RDB.  We are using 
SQL Services classes that are attached to the Rdb database.

The nature of the application requires that multiple PC Clients poll one of 
the tables in the RDB database every 60 seconds.  Intermittently the PC hangs
on the select statement, utilizing 100% of the PC's CPU.  The PC users have 
read-only access to the remote table in the Rdb database.

The application runs for long periods without a hitch.  But the problem shows 
up every 48 hours or so and is not predictable.  It may be related to periods 
of heavy network usage, but I'm not sure. 

When the problem occurs there was no blocking going on, and no activity 
observed on the SQL Services processes.  The TCPIP socket on the SQL Services 
side seemed to still exist.  Seems likely that the connection was lost between 
PC and remote socket.

I am concerned that a network connection is maintained and failing periodically
after the query is made but before the response comes back.

At a minimum, can anyone suggest a way to have the SELECT statement timeout
so that I can continue by application?  The SELECT statement seems to block
the rest of my application from executing.  I tried enabling a VB timer before 
the SELECT statement but the timer routine never got called, as if the timer 
was ignored during the select statement.  Are there any other asynchronous
    controls I could try?

I set the LockTimeOut=20, and NetworkBufferSize=5000 in the RDBODBC.INI file.
The RDBODBC.LOG file reveals that the database access is read (DBA=R).  The
locktimeout works when I force a lock on the database.

Cross posted in Oracle ODBC RDB Notes.

Software Versions
-----------------
   PC:
	Windows NT 3.51 (with Service Pack 4)
	VB V4.0 32-bit
	ODBC RDB V2.10.11 32-bit with TCP/IP

   OpenVMS:
	DEC SQL V6.1-0
	DEC Rdb V6.1-0
	Rdb/Dispatch V6.1-0 (OpenVMS AXP)


ODBCRDB.LOG
-----------
	.
	.
	.
....0X0016B758: Rows Fetched =      0X00000007
....0X00194488: SELECT EVENTINDEX.EVENT_COUNTER FROM EVENTINDEX 
....0X00194488: Rows Fetched =      0X00000001
....0X00194488: SELECT EVENT_COUNTER  FROM EVENTINDEX  WHERE EVENT_COUNTER
.... = ?
....0X001BEC10: SELECT EVENTID ,REGISTRATIONID ,DATETIME ,SERVERDATETIME ,
....HOST ,APPLICATION ,COMPONENT ,VERSION ,INSTANCE ,PID ,CALLEDBY ,SEVERI
....TY ,EVENTCODE ,MESSAGE ,LONG_MESSAGE_FLAG  FROM EVENT WHERE (EVENTID >
.... 8588 ) 
....0X001BEC10: Rows Fetched =      0X00000007
....0X00194488: SELECT EVENTINDEX.EVENT_COUNTER FROM EVENTINDEX 
....0X00194488: Rows Fetched =      0X00000001
....0X00194488: SELECT EVENT_COUNTER  FROM EVENTINDEX  WHERE EVENT_COUNTER
.... = ?
....0X00192A78: SELECT EVENTID ,REGISTRATIONID ,DATETIME ,SERVERDATETIME ,
....HOST ,APPLICATION ,COMPONENT ,VERSION ,INSTANCE ,PID ,CALLEDBY ,SEVERI
....TY ,EVENTCODE ,MESSAGE ,LONG_MESSAGE_FLAG  FROM EVENT WHERE (EVENTID >
.... 8595 ) 
    ^
    |
    Application hangs here during SELECT.  Note that the previous SELECT 
    was successful.

    I have had the same bad experience with RDB tables attached to an
    Access database. For me the solution was simple: have a separate
    connection to RDB using RDO and ODBC and scrap the linked tables.
    They're a pain to maintain, in any case, for dumb users when the RDB db
    account details change etc.
    
    Cheers,
    Brian

    
    Thanks for your reply.
    
    In my case, there are alot of advantages to opening a single database from
    VB that contains local Access tables and linked Rdb tables.  I can
    consider moving away from here, but it would require a significant
    recoding effort.
    
    Did you find a specific problem in maintaining a connection over time 
    using linked tables?  Did the connection problems go away entirely when 
    you used RDO and ODBC?  I'm not sure I understand why linked tables would 
    cause this problem.
    
    -John

    >Did you find a specific problem in maintaining a connection over time 
    >using linked tables?  
    
    Yes I did. In particular the application would sometimes just hang in
    the middle of accessing one of the linked RDB tables. The only way out
    was to switch off the power because the PC was also locked up by this. 
    This is obviously not acceptable in any environment let alone a
    production environment.
    
    >Did the connection problems go away entirely when you used RDO and ODBC?
    Yes. 
    
    Cheers,
    Brian