Solved

VB4 Oracle ODBC Problem

Posted on 1997-03-19
11
756 Views
Last Modified: 2006-11-17
Does anybody know how to solve a problem with VB and Oracle using ODBC ?
I am using VB 4 16-bit under Windows 3.1 with Oracle 7 and the latest Oracle ODBC driver (1.16.3.1.5) on a Pentium with 16meg memory.
My VB project is getting reasonably large now and when I try to log on to Oracle I get an Error ORA-3146.
This only happens when I run the app directly from the VB IDE, if I make an EXE it all works fine.
This has been an intermittent problem in the past but it has been possible to get around it by closing and restarting windows. However, now it happens all the time and I therefore can't use debug anymore.
I assume from the symptoms that it is somehow memory related but temporarily adding another 16meg memory didn't make any difference, is it base memory ?
I have seen at least one other reference to this problem on the newsgroups, does anyone know what causes it and how it can be cured ?

andyb
Thanks for your help.
Problem IS on the opendatabase statement.
How do I check the DAO version ?
Below is ORACLE.INI and then ODBC.INI
------------------------------------------------------------
[Oracle]
ORACLE_HOME=C:\ORAWIN
CUSTOMER=CFI
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
ORAINST=C:\ORAWIN\dbs
MSHELP=C:\ORAWIN\mshelp
PATH_MODIFIED=TRUE
USERNAME=BWEALLEA
LOCAL=ELCID1
TIMESTAMP=0.0.0.0.0

[rsf73]
REPLICATE_BIN=commdll,dll
RDBMS73=C:\ORAWIN\RDBMS73
PRO18=C:\ORAWIN\PRO18
NLSRTL32=C:\ORAWIN\NLSRTL32
ORA_NLS32=C:\ORAWIN\NLSRTL32\DATA

[orainst]
REPLICATE_GROUPS=res:us,aux:us,prodmsg:us

[net23]
PRD_PATH=C:\ORAWIN\NETWORK\CFG
REPLICATE_VARIABLES=prd_path

[plus33]
SQLPATH=C:\ORAWIN\DBS
PLUS33=C:\ORAWIN\PLUS33
EXECUTE_SQL=PLUS33

[pdfdoc73]
REPLICATE_GROUPS=exec, info
------------------------------------------------------------
[ODBC Data Sources]
MS Access Databases=Access Data (*.mdb)
FoxPro Files=FoxPro Files (*.dbf)
dBase Files=dBase Files (*.dbf)
Paradox Files=Paradox Files (*.db )
NWind=dBase Files (*.dbf)
MS Access 2.0 Databases=Access 2.0 for MS Office (*.mdb)
ELCID1=Oracle73
CRDB2=CR DB2
CRGUP=CR SQLBase
CRINF5=CR Informix 5
CRORA=CR Oracle
CROR7=CR Oracle7
CRSS=CR SQLServer
CRSYB=CR Sybase SQL Server
CRXQL=CR Scaleable SQL
GxLocal=Access Data (*.mdb)
Craze sample data=Microsoft Access Driver (*.mdb)

[MS Access Databases]
Driver=C:\WINDOWS\SYSTEM\simba.dll
FileType=RedISAM
SingleUser=False
UseSystemDB=False

[FoxPro Files]
Driver=C:\WINDOWS\SYSTEM\simba.dll
FileType=FoxPro 2.5
SingleUser=False

[dBase Files]
Driver=C:\WINDOWS\SYSTEM\simba.dll
FileType=dBase4
SingleUser=False

[Paradox Files]
Driver=C:\WINDOWS\SYSTEM\simba.dll
FileType=Paradox
SingleUser=False

[NWind]
Driver=C:\WINDOWS\SYSTEM\simba.dll
FileType=dBase4
DataDirectory=C:\WINDOWS\MSAPPS\MSQUERY
SingleUser=False

[MS Access 2.0 Databases]
Driver=C:\WINDOWS\SYSTEM\ODBCJT16.DLL
DefaultDir=C:\MSOFFICE\ACCESS
JetIniPath=MSACC20.INI
UID=Admin

[ODBC]
TraceAutoStop=1
Trace=0
TraceFile=C:\GXLOCAL\SQL.LOG

[ELCID1]
Driver=C:\ORAWIN\odbc\sqora73n.dll
Description=EL-CID Oracle Database
Server=ELCID1
userID=GXOWNER
ODBCDatabase=ELCID1
LastUser=cfiuser

[CRDB2]
Driver=C:\WINDOWS\SYSTEM\crdb207.dll

[CRGUP]
Driver=C:\WINDOWS\SYSTEM\crgup07.dll

[CRINF5]
Driver=C:\WINDOWS\SYSTEM\crinf507.dll

[CRORA]
Driver=C:\WINDOWS\SYSTEM\crora07.dll

[CROR7]
Driver=C:\WINDOWS\SYSTEM\cror707.dll

[CRSS]
Driver=C:\WINDOWS\SYSTEM\crss07.dll

[CRSYB]
Driver=C:\WINDOWS\SYSTEM\crsyb07.dll

[CRXQL]
Driver=C:\WINDOWS\SYSTEM\crxql07.dll

[GxLocal]
Driver=C:\WINDOWS\SYSTEM\simba.dll
Description=Galaxy Local Data to Summary Level
FileType=RedISAM
DataDirectory=c:\gxlocal\galaxy.mdb
SingleUser=False
UseSystemDB=False

[Craze sample data]
Driver=C:\WINDOWS\SYSTEM\odbcjt16.dll
DBQ=S:\DESKTOP\CRW\craze.mdb
DriverId=25
JetIniPath=odbcddp.ini
UID=admin
------------------------------------------------------------
0
Comment
Question by:bwhealle
  • 6
  • 5
11 Comments
 
LVL 1

Expert Comment

by:andyb013197
Comment Utility
Sounds like the Oracle ODBC driver is falling over from the lack of a particular resource related to memory (e.g. an Oracle setup problem). Does the error occur on the opendatabase statement when you log on? I am sure there is a configuration (ini) file for the oracle driver somewhere in the windows directory, but I cant remember what its called, sorry (ora*.* springs to mind for some reason!).

If you find any likely files paste them here, they may give us a bearing on your problem.

Are you also sure your DAO version is correct? Have you tried a different one e.g. 2.5/3.0, 3.0 etc ?
0
 

Author Comment

by:bwhealle
Comment Utility
Edited text of question
0
 
LVL 1

Expert Comment

by:andyb013197
Comment Utility
Ok lets try a number of things,

Check in the C:\ORAWIN\NETWORK\CFG  and see what files are there
(if anything interesting paste it here as a comment)

Through control panel/odbc, or via the file with the ODBC section, turn ODBC tracing on (trace=1?) and see what is written in the sql.log file when you try to log on and it fails. Dont forget to turn this feature off afterwards as it will get *very* big over time.

Check the DAO version in the tools references menu in VB4. I dont think this has a bearing but we may as well know.

I hope this isnt sending you down the wrong track! I think the sql trace should be illuminating though....
0
 

Author Comment

by:bwhealle
Comment Utility
andyb
Thanks for your continuing interest.

Here is a dir list of C:\ORAWIN\NETWORK\CFG
-------------------------------------------
 Directory of C:\ORAWIN\NETWORK\CFG

.            <DIR>       09/10/96   14:13
..           <DIR>       09/10/96   14:13
WINDOWS  PRD         786 22/02/96   20:16
CFG23    VRF     107,989 30/04/96   22:43
CFG23    MAP       3,290 06/10/95   23:23
TEMPLATD TCP         278 06/09/95   21:50
TEMPLATE SPX         235 01/09/95   22:18
TEMPLATE NMP         274 01/09/95   22:18
TEMPLATE NTB         235 19/09/95   20:50
TEMPLATE DEC         283 19/09/95   22:03
TEMPLATE SNA         321 19/09/95   22:01
TEMPLATE TCP         232 06/09/95   21:50
TNSNAMES SPX         255 28/07/95   15:12
TNSNAMES TCP         280 28/07/95   15:13
TNSNAMED TCP         431 12/09/95    3:46
TNSNAMES NMP         284 28/07/95   15:14
TNSNAMES NTB         255 19/09/95   20:58
TNSNAMES DEC         287 19/09/95   22:06
TNSNAMES SNA         316 19/09/95   22:02
SQLNET   ORA         321 23/02/96   18:10
TNSNAMES ORA       1,048 27/09/95   18:13
CONFIG   ICO       1,078 21/09/95   21:47
SQLNETV2 INI         948 04/10/95   19:23
       23 file(s)      119,426 bytes
-----------------------------------------

Here is a listing of the SQL.LOG file from the ODBC Trace
-----------------------------------------
SQLAllocEnv(phenv350F0000);
SQLAllocConnect(henv350F0000, phdbc353F0000);
SQLSetConnectOption(hdbc353F0000, 103, 00000014);
SQLDriverConnect(hdbc353F0000, hwnd0E8C, "UID=cfiuser;PWD=*******;DATABASE=ELCID1;DSN=ELCID1", -3, szConnStrOut, 255, pcbConnStrOut, 1);
SQLError(henv350F0000, hdbc353F0000, hstmt00000000, szSqlState, pfNativeError, szErrorMsg, 8192, pcbErrorMsg);
SQLError(henv350F0000, hdbc353F0000, hstmt00000000, szSqlState, pfNativeError, szErrorMsg, 8126, pcbErrorMsg);
SQLError(henv350F0000, hdbc353F0000, hstmt00000000, szSqlState, pfNativeError, szErrorMsg, 8070, pcbErrorMsg);
SQLError(henv350F0000, hdbc353F0000, hstmt00000000, szSqlState, pfNativeError, szErrorMsg, 7971, pcbErrorMsg);
SQLError(henv350F0000, hdbc353F0000, hstmt00000000, szSqlState, pfNativeError, szErrorMsg, 7898, pcbErrorMsg);
SQLFreeConnect(hdbc353F0000);
SQLFreeEnv(henv350F0000);
SQLAllocEnv(phenv66170000);
SQLAllocConnect(henv66170000, phdbc65870000);
SQLSetConnectOption(hdbc65870000, 103, 00000014);
SQLDriverConnect(hdbc65870000, hwnd0E8C, "UID=cfiuser;PWD=*******;DATABASE=ELCID1;DSN=ELCID1", -3, szConnStrOut, 255, pcbConnStrOut, 1);
SQLError(henv66170000, hdbc65870000, hstmt00000000, szSqlState, pfNativeError, szErrorMsg, 8192, pcbErrorMsg);
SQLError(henv66170000, hdbc65870000, hstmt00000000, szSqlState, pfNativeError, szErrorMsg, 8126, pcbErrorMsg);
SQLError(henv66170000, hdbc65870000, hstmt00000000, szSqlState, pfNativeError, szErrorMsg, 8070, pcbErrorMsg);
SQLError(henv66170000, hdbc65870000, hstmt00000000, szSqlState, pfNativeError, szErrorMsg, 7971, pcbErrorMsg);
SQLError(henv66170000, hdbc65870000, hstmt00000000, szSqlState, pfNativeError, szErrorMsg, 7898, pcbErrorMsg);
SQLFreeConnect(hdbc65870000);
SQLFreeEnv(henv66170000);
SQLAllocEnv(phenv53370000);
SQLAllocConnect(henv53370000, phdbc531F0000);
SQLSetConnectOption(hdbc531F0000, 103, 00000014);
SQLDriverConnect(hdbc531F0000, hwnd0E8C, "UID=cfiuser;PWD=*******;DATABASE=ELCID1;DSN=ELCID1", -3, szConnStrOut, 255, pcbConnStrOut, 1);
SQLError(henv53370000, hdbc531F0000, hstmt00000000, szSqlState, pfNativeError, szErrorMsg, 8192, pcbErrorMsg);
SQLError(henv53370000, hdbc531F0000, hstmt00000000, szSqlState, pfNativeError, szErrorMsg, 8126, pcbErrorMsg);
SQLError(henv53370000, hdbc531F0000, hstmt00000000, szSqlState, pfNativeError, szErrorMsg, 8070, pcbErrorMsg);
SQLError(henv53370000, hdbc531F0000, hstmt00000000, szSqlState, pfNativeError, szErrorMsg, 7971, pcbErrorMsg);
SQLError(henv53370000, hdbc531F0000, hstmt00000000, szSqlState, pfNativeError, szErrorMsg, 7898, pcbErrorMsg);
SQLFreeConnect(hdbc531F0000);
SQLFreeEnv(henv53370000);
-----------------------------------------------------------------
The DAO version is 2.5
-----------------------------------------------------------------
One reply I got from usenet indicated the file handles as suspect.
Apparently VB uses 16 and the Oracle driver/SQL Net tends to try to grab the same ones.  I am awaiting a new version of SQL Net from Oracle as they think that the new one is less 'leaky' and might improve things (I'm not too hopeful about that though).  We're currently using SQLNet 2.3.2.1.3 and they're sending me 2.3.2.1.6

Anyway
all for now
Cheers,
Bruce

0
 
LVL 1

Expert Comment

by:andyb013197
Comment Utility
The sql trace text, unfortunatly, shows only one thing and that is that its failing on the api connect to the odbc driver. The question is why. Increasing file handles (in config.sys) sounds like a very good thing to try, you may as well increase buffers as well. I think files is set at about 120 (i might be wrong about this) in a typical oracle client, try increasing it by at least 50. Your base memory will go down of course.

BTW, how much base memory do you have *before* going into windows?

The two files SQLNET.ORA and TNSNAMES.ORA from what I remember are two of the more important configuration files for an Oracle session. However TNSNames I think only lists the ip addresses and associated Oracle schema names. If there is anything interesting in SQLNET.ORA, it should be a text file, list it here (its only 321 bytes).

0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:bwhealle
Comment Utility
Andy,
Have set FILES=120 and BUFFERS=40 without effect.
With these settings and with required network, cd drivers, smartdrv etc. I get 447K base memory before windows is loaded.
SQLNET.ORA is as follows
--------------------------------------------------------------
################
# Filename......: sqlnet.ora
# Name..........: SPXTCP.world
# Date..........: 03-OCT-96 17:00:49
################
AUTOMATIC_IPC = OFF
TRACE_LEVEL_CLIENT = OFF
SQLNET.EXPIRE_TIME = 0
NAMES.DEFAULT_DOMAIN = world
NAME.DEFAULT_ZONE = world
SQLNET.CRYPTO_SEED  = "-1460650597-1460620745"
NAMES.DIRECTORY_PATH = (TNSNAMES)
DISABLE_OOB = OFF
USE_DEDICATED_SERVER = OFF
TRACE_UNIQUE_CLIENT = OFF
TRACE_FILE_CLIENT = SQLNET
TRACE_DIRECTORY_CLIENT = C:\ORAWIN\NETWORK\TRACE
--------------------------------------------------------------
Cheers,
Bruce
0
 
LVL 1

Expert Comment

by:andyb013197
Comment Utility
This is getting interesting!

I cant help feeling we are missing something obvious though....

Anyway, 447k is not a lot to run windows in (in fact its very poor even though you have 16Mb of Ram). How confident are you that your config,sys and autoexec.bat are optimised to give you the most base memory? I hate to have to put you through more pasting but we may as well optimise these (if possible) before continuing. If you do paste them make sure you tell me whether you need all expanded, a mix, or all extended memory and also any drivers that you may have loaded that are not standard (e.g. video conferencing that sort of thing).

And just how many forms and modules (roughly) do you have in your project?


0
 

Author Comment

by:bwhealle
Comment Utility
Andy,
CONFIG.SYS is as follows
-----------------------------------------------------------------
DEVICE=C:\DOS\HIMEM.SYS
DOS=HIGH,UMB
DEVICE=C:\DOS\EMM386.EXE NOEMS
DEVICE=C:\DOS\SETVER.EXE
COUNTRY=044,,C:\DOS\COUNTRY.SYS
DEVICEhigh=C:\CDPRO\VIDE-CDD.SYS /D:MSCD001
FILES=120
BUFFERS=40
SHELL=C:\DOS\COMMAND.COM /P /E:2048
stacks=9,256
LASTDRIVE=Z
-----------------------------------------------------------------
....and AUTOEXEC.BAT
-----------------------------------------------------------------
@ECHO OFF
PROMPT $p$g
LOADHIGH C:\DOS\SMARTDRV.EXE /e:4096 2048 2048
path=C:\ORAWIN\BIN;c:\dos;c:\windows;c:\madge;c:\lwp51\bin;c:\nwclient;c:\pfe
loadhigh C:\DOS\MSCDEX.EXE /D:MSCD001 /V /L:D /M:10
SET COMSPEC=C:\DOS\COMMAND.COM
SET TEMP=C:\TEMP
loadhigh C:\DOS\KEYB UK,,C:\DOS\KEYBOARD.SYS
loadhigh c:\dos\share.exe /L:500 /F:5100
C:\BIN\KSET N-
PATH C:\NWCLIENT\;%PATH%
@CALL C:\NWCLIENT\STARTNET
call network.bat
-----------------------------------------------------------------
MEM /C (from within windows) produces (sorry about the word wrap)
-----------------------------------------------------------------

Modules using memory below 1Mb:

  Name           Total       =   Conventional   +   Upper Memory
  --------  ----------------   ----------------   ----------------
  SYSTEM      22,717   (22K)     22,685   (22K)         32    (0K)
  HIMEM        1,120    (1K)      1,120    (1K)          0    (0K)
  EMM386       4,096    (4K)      4,096    (4K)          0    (0K)
  COMMAND      4,880    (5K)      4,880    (5K)          0    (0K)
  win386      83,808   (82K)     76,512   (75K)      7,296    (7K)
  LSL         45,984   (45K)     45,984   (45K)          0    (0K)
  SMART       30,528   (30K)     25,248   (25K)      5,280    (5K)
  TCPIP       27,504   (27K)     21,264   (21K)      6,240    (6K)
  VLM         66,912   (65K)     66,912   (65K)          0    (0K)
  WIN          1,712    (2K)      1,712    (2K)          0    (0K)
  COMMAND      3,248    (3K)      3,248    (3K)          0    (0K)
  VIDE-CDD     5,024    (5K)          0    (0K)      5,024    (5K)
  SMARTDRV    31,072   (30K)          0    (0K)     31,072   (30K)
  MSCDEX      35,744   (35K)          0    (0K)     35,744   (35K)
  KEYB         6,400    (6K)          0    (0K)      6,400    (6K)
  FREE       381,680  (373K)    381,680  (373K)          0    (0K)

Memory summary:

  Type of Memory       Total   =    Used    +    Free
  ----------------  ----------   ----------   ----------
  Conventional         655,360      273,680      381,680
  Upper                 97,088       97,088            0
  Reserved             393,216      393,216            0
  Extended (XMS)    15,631,552   14,582,976    1,048,576
  ----------------  ----------   ----------   ----------
  Total memory      16,777,216   15,346,960    1,430,256

  Total under 1Mb      752,448      370,768      381,680

  Largest executable program size        381,664    (373K)
  Largest free upper memory block              0      (0K)
  PC DOS is resident in the high memory area.
-----------------------------------------------------------------
The VB project has 43 forms and two modules, the main module being quite large (app. 8500 lines).
Any more info ?
Cheers,
Bruce
0
 
LVL 1

Expert Comment

by:andyb013197
Comment Utility
Sorry for the delay,

There are only three things that appear to be changeable to give you more base memory :

in your startnet.bat file try selectively loading high (i.e. a prefix of 'lh ') your lsl (link support layer) driver (or other lines in there if not succesfull). This may work if you disable (i.e. rem) the smartdrv line in the autoexec.bat file and load low keyb (i.e. no lh) to free more upper memory. You also (bonus!) may be able to put setver high as well with a 'devicehigh='. The trick is to load high all of the bigger devices high and let the smaller ones sit below 640k.

If you are worried about performance degradation because of no disk caching, and only mainly use windows (3.11), try upping the windows cache setting (found in control panel, virtual memory at the bottom). It is surprising how quickly vb will load on the second attempt with this set at between 2-4Mb.

If you were feeling adventurous you could also disable share and enable it in your system.ini file (386enh section) by adding the line 'device=vshare.386'. This will only work however if you have windows 3.11.

Again I must stress we may be barking up the wrong tree here but the above suggestions will be worth trying.

Andy.

BTW, you have a driver called SMART loaded, what is it? Any ideas?
0
 
LVL 1

Accepted Solution

by:
andyb013197 earned 50 total points
Comment Utility
see above comment
0
 

Author Comment

by:bwhealle
Comment Utility
Andyb,
I have not had to try the answer so I can't really tell if it works.
Oracle came up with a new version of the SQL*Net Client, version 2.3.2.1.6, and this seems to have cured the problem.
The impression that I got was that the previous version(s) were a bit 'leaky' in the memory area and that this latest version is more prudent with its use of memory/file handles.
Ayway, thanks for your efforts.
Cheers, Bruce
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now