Ingres Database Reference
-----------------------------------------------------------------------------
I N G R E S / O D B C
F R E Q U E N T L Y A S K E D Q U E S T I O N S ( F A Q )
This version: 1.25 19-MAR-96
Last version: 1.24 28-FEB-96
Archived at ftp.naiua.org
Copyright (C) 1994-1996 by Jon Machtynger
This document is COPYRIGHTED to control its distribution and to prevent
it from being used for profit. PLEASE DO NOT DISTRIBUTE IT WITHOUT THIS
DISCLAIMER OR THE COPYRIGHT NOTICE. Replication of this document by any
means is explicitly encouraged, provided that the content is not altered
in any way.
------------------------ D I S C L A I M E R --------------------------
COMPUTER ASSOCIATES TAKE ABSOLUTELY NO RESPONSIBILITY FOR THE CONTENT OF
THIS FAQ. I PREPARED IT IN MY OWN TIME, WITHOUT THE PERMISSION,
ENDORSEMENT OR KNOWLEDGE OF ANY REPRESENTATIVE OF COMPUTER ASSOCIATES.
This is a voluntary effort by many contributors, coordinated by me, who
may or may not be identified in "Contributors." Their respective employers
are in no way responsible for the content of this FAQ. Nothing in this FAQ
should be regarded as true. Any item of information may become obsolete or
out of date at any time.
The coordinator, contributors, and distributors of this document make
no representations as to the correctness of the information contained
herein, and make no promises to correct any errors or omissions. You
use this information at your own peril.
No responsibility is given for neglect to mention Company tradenames or
trademarks.
Any trademarks or tradenames are mentioned only in conjunction with the
use of ODBC and the Ingres product for informative purposes. No
recommendation of these products is directly or indirectly sponsored.
+--------------------------------------------------------------------+
| Jon Machtynger(jonm@ingres.com) +--------------+ |
| Boulevard de la Woluwe | Aussie in | |
| 34 Bte. 13. Brussels. | Overijse.. | |
| Ph: 02-774 49 23 Fax: 02-773 28 09 +--------------+ |
+--------------------------------------------------------------------+
If you don't see the '---- END OF FAQ ----' line, something is missing.
-----------------------------------------------------------------------
TABLE OF CONTENTS
01. Notes
01.001 Contributors
01.002 What this FAQ is NOT
01.003 Improvements to this FAQ
02. ODBC Basics
02.001 What is ODBC?
02.002 What are advantages/disadvantages for using ODBC?
02.003 Where can I get ODBC?
02.004 ODBC Architecture.
02.005 What is a data source?
02.006 What is a single tiered or multi-tiered model?
02.007 What database types can I access using ODBC?
02.008 Can I run ODBC over network ABC?
02.009 What are the different ODBC Conformance Levels?
03. The Ingres ODBC Driver
03.001 What level of compliancy is the Ingres ODBC Driver?
03.002 How do I install the ODBC Driver?
03.003 Can ODBC Access Ingres Database Procedures
03.004 How do I use stored procedures from ODBC?
03.005 Can ODBC Access Knowledge Management facilities?
03.006 What versions of the Ingres ODBC Driver exist?
04. Alternative ODBC Drivers
04.001 What alternative ODBC drivers are available to access Ingres?
05. Practicalities of ODBC
05.001 If my database changes do I need to change my client application?
05.002 What Ingres Software does ODBC Require?
05.003 Is ODBC secure?
06. The ODBC FAQ
06.001 Where is the ODBC FAQ?
06.002 Can I get the FAQ by mail?
07. More Information on ODBC
07.001 What extra sources of information exist on ODBC?
08. ODBC Add-Ons
08.001 What is the compatibility Layer?
08.002 What facilities exist to trace ODBC performance?
08.003 Alternatives to ODBC.
09. Performance Issues
09.001 ODBC is very slow using Visual Basic. What can be done?
10. Miscellaneous
10.001 Can I use ODBC with University Ingres?
10.002 Can I access Database events through ODBC?
10.003 Can I use a 6.4 ODBC driver against OpenIngres?
10.004 Can I use ODBC under Windows95?
11. Common Problems
11.001 I have problems accessing tables with an underscore in them.
11.002 I am using Visual Basic and can't use Transactions?
11.003 Powerbuilder GPF's when attaching to the Ingres Database...
11.004 I cannot use Lotus123 (Previous to Version 5) with the ...
11.005 I've just installed Paradox 5 and trying to use the...
11.006 I get an 'Read Only' or 'Table is not Updatable' error...
11.007 I cannot reliably access and update data to Ingres...
11.008 How can I set Lockmodes using the Ingres ODBC Driver?
11.009 Can I use ODBC with OpenROAD?
11.010 When using attached tables with MSAccess and the...
11.011 I can't use MULTINATIONAL dates with the new Intersolv driver.
11.012 When I access information from attached tables using...
11.013 I've tried installing my 16 bit ODBC Driver under NT...
11.014 Can I use Ingres Net/ODBC with Windows-95?...
11.015 I have had problems using blank dates in MSAccess.
11.016 I cannot use Intersolv's 32 bit driver under NT 3.51....
11.017 I can't install the Intersolv driver properly...
11.018 Date/time in MSAccess is always out by a number of hours.
=======================================================================
01.001 Contributors
This FAQ is the work of numerous individuals working on it in their
own time. The layout of this FAQ has been blatantly copied from
the initial FAQ produced by Roy Hann. As such, much of the material in
this FAQ is copied directly (where appropriate). This is NOT an FAQ on
Ingres the database as there already exists a comprehensive ingres FAQ.
This is an FAQ on implementing ODBC with the Ingres Database. (At least)
the following people contributed (and some didn't even know that they
contributed!) in alphabetical order:
Ken Garove kgarove@charm.net
Roy Hann rhann@tnc.com
Bruce Horrocks bh@granby.demon.co.uk
James Logajan jameslCyBowr.5uK@netcom.com
Jon Machtynger jonm@ingres.com
Duncan Mackinder duncanm@ingres.com
Paul Mergener pmerge@pacersoft.com
Ronny Ong ronnyong.40.0016B8DA@unicomp.net
Dan Osterhout doster01@aol.com
Manny Power Manny.Powers@SanDiegoCA.ATTGIS.COM
Tom Ross tomr@ingres.com
RW Salnick salnick.71qp@dejavu.spk.wa.us
John Vandermay john.vandermay@marcam.com
Peter Write Peter@gendev.demon.co.uk
Please direct all corrections, suggestions and complaints to Jon Machtynger
(flames especially, by private E-mail please). New contributors are always
welcome. General expressions of praise and gratitude can be directed to
any and all of the above.
01.001 What this FAQ is NOT.
This FAQ is not a be-all and end-all performance tuning guide to using ODBC.
This FAQ will get you started by showing you what ODBC is and who you can
approach for more information. The area of performance tuning is very
application/project dependent. Some guidelines will be mentioned as these
will be generic across most vendors. Where performance tuning suggestions
have been provided, keep in mind that their use was apt for the situation in
which they provided benefit.
01.002 Improvements to this FAQ
If you have any comments about this FAQ regarding accuracy of information
or you would like more sections relating to different aspects of ODBC, then
LET ME KNOW.
02.001 What is ODBC
ODBC is Microsoft's implementation of a Call Level Interface specification
defined by the SQL Access Group. It provides a standard API that allows
a client (ie Windows program) to access a wide range of servers (ie data
sources). The SQL grammer is supported at 3 levels of conformance.
Applications must make assumptions about what minimum level of conformance
they can accept from a data source.
The data source you access can be a flat file or a relational database
depending on your requirements. It is also possible to access multiple
data sources simultaneously regardless of the database provided.
There has been wide spread industry support of ODBC such that new drivers
are appearing quite regularly. A list of the currently known drivers
also appears in this FAQ.
02.002 What are advantages/disadvantages for using ODBC?
ODBC is the most universally supported API of its kind that is actually
available in shipping implementations, with support from more third parties
than any other alternative (including non-shipping ones). ODBC makes it
simple to develop generic, scalable client applications for distributed
systems.
On the other hand, in order to level the field of proprietary database
vendors (non compliant SQL etc), ODBC takes a least-common denominator
approach to what is usable in your applications. It is also an additional
layer between the application's code and the data. This means that ODBC may
restrict you from taking maximum advantage of your specific DBMS features
(such as Groups/Roles/Database events) and ODBC will slow down your
applications runtime performance.
NOTE: Some vendors claim that use of ODBC can increase the performance of
your application.
02.003 Where can I get ODBC
Since ODBC is just a standard you can 'get' ODBC by using drivers provided
by vendors. The Ingres ODBC driver used to come free with Ingres/NET.
Since the introduction of the Intersolv driver, you can now purchase
this. If however you, were given the previous driver free and were
licenced for it, you should be able to upgrade to the Intersolv driver
free of charge.
There are alternative drivers available for ingres via other vendors. See
the section 4.001 for other vendor details.
02.004 ODBC Architecture.
ODBC follows a 4 layer model.
+------------------+
| ODBC Application | Your Visual Basic or Excel program
+------------------+
\
+------------------+
| Driver Manager | This is ODBC.DLL
+------------------+
\
+------------------+
| ODBC Driver | This is the Driver (e.g. SQING.DLL)
+------------------+
\
+------------------+
| Data Source | Your data Ingres database or xbase file.
+------------------+
02.005 What is a data source?
A data source is a logical name for your data repository. This is
not necessarily the same as your remote name in the NETU utility.
You define these in the ODBC manager. When using an Ingres/NET
based ODBC driver, think of the data source as a combination of a NETU
entry (i.e. machine name, Ingres installation within that machine,
username and user password) and a database name.
02.006 What is a single tiered or multi-tiered model?
A data source can be a direct data source example xbase file or Btrieve
file. This is a single-tiered data source.
A data source can be an indirect data source such as an ODBC database
across a network. An example of this is your Ingres database. This is a
multi-tiered data source.
02.007 What database types can I access using ODBC?
ODBC is supposed to provide an open platform from which to access data
sources. This means that if you have an ODBC driver for a particular
database, you can access that data base (data source) with any ODBC
compliant interface (such as Excel/Visual Basic etc).
Given the range of database types you can access through an ingres
database (ie via their gateways), not having an ODBC driver for an
RMS database is not a problem if you have the Ingres RMS gateway.
ODBC provides for being able to utilise any form of data source. With regard
to the Ingres ODBC Driver, this means you have access to any data source you
can access through Ingres or an associated Driver:
These are at least the following:
Ingres (Not University Ingres)
Allbase (Via Ingres Gateway)
RMS (Via Ingres Gateway)
DB2 (Via Ingres Gateway)
02.008 Can I run ODBC over network ABC?
What you run ODBC across matters little since the ODBC driver will utilise
the underlying Ingres/NET connection. A driver such as Visionware or
OpenLink does not use Ingres/NET so you will have to contact them for
information as to what their driver supports.
There are INGRES/NET network adaptors for at least: Taken from Ingres
Networking for Windows Release Notes 6.4/04 (net.win/01).
DRIVER Network Description
TCP_WOL TCP/IP Wollongong Pathway Access 2.0 or above
TCP_LMX TCP/IP HP ARPA 2.1 Rev B.03.00, Microsoft Lan
Manager 2.1 LM/X
NETBIOS NetBIOS Used in a LAN with the Ingres OS/2
Server, Microsoft Lan Manager 2.0 or 2.1.
DECNET DECnet Pathworks dor DOS 4.1
TCP_NFS TCP/IP PCNFS 4.0a or 5.0
LAN_WORKPLACE TCP/IP Lan Workplace for DOS 4.1
TCP_FTP TCP/IP PCTCP 2.1 or 2.2
TCP_DEC TCP/IP DEC TCP/IP 2.0
NVL_SPX NETWARE Netware 3.10
WINSOCK TCP/IP Used in a network with Windows 3.1 and
a winsock.dll or rev 1.1 or above.
Be sure to contact your Computer Associates office for authoritative
information.
Example: Is it possible to run Ingres/NET over trumpet winsock using
either a LAN or PPP?
Since Trumpet is winsock 1.1 compliant, Ingres/NET will communicate at
the winsock layer to Trumpet. The media being used to transport your
packets should not be important. So yes, this should work. I have not
tried this but then again I have seen Windows 4GL working with Trumpet
and I have seen replicator working across PPP. ODBC is only going to
use the underlying Ingres/NET layer.
02.009 What are the different ODBC Conformance Levels?
ODBC provides 3 conformance levels:
CORE
LEVEL 1
LEVEL 2
With ODBC 3.0 about to be released, you should contact your ODBC
vendor for more information on their compliance.
03.001 What level of compliancy is the Ingres ODBC Driver?
Traditionally, ASK and then CA distributed a Core and Level 1 compliant
driver. The current ODBC driver (Intersolv 2.11) is documented has having
CORE, LEVEL 1, and LEVEL 2 API functions (listed in an appendix).
03.002 How do I install the ODBC Driver?
This will really depend on what driver you are loading. Most drivers
provide their own quirks but in general will require that you provide
in some way a host to connect to and a database name.
Any driver that uses Ingres/NET will probably use the virtual node as
a fair way of determining your server and the installation.
Below is a brief description of installing the original Ingres driver
and a first data source:
Copy the files from the ODBC Disk to a temporary directory. From the
file manager double-click on the SETUP.EXE program.
You are then given the opportunity to create a data source.
These are the values you should be entering.
Data Source Name:
Description:
Virtual Node:
Database:
Server Type:
Done! Now test it using VB or something similar.
03.003 Can ODBC Access Ingres Database Procedures
The ability to execute database procedures is limited by the driver
interface you are using. Testing of this failed under the Ingres driver
version 1.01.1617 and earlier. I have tested this against the Intersolv
Ingres driver and it still does not work. Expect a E_US09C4 Syntax
error message. This also failed using the Q+E driver (Version 1.02)
(with their patch). This would imply that this is an ODBC compliancy
issue. This succeeded using OpenLink's Driver (Version 1.06.1010).
03.004 How do I use stored procedures from ODBC?
Assuming you cannot use an execute procedure statement from your
application, one solution is to use a trigger table. Inserting a row
into this table along with any arguments necessary can fire off a rule
that can execute the database procedure.
03.005 Can ODBC Access Knowledge Management facilities?
The KM facilities are accessed transparently. In the case of rules being
fired etc, these should activate whether you are using Ingres via a native
interface or through an ODBC connection.
With regard to Rules/Groups. The first version of the Ingres Driver did
not provide this functionality. This is provided with other drivers and
is only limited by the connection information passed to the Ingres server.
You should check your ODBC driver documentation for confirmation on
whether this is provided or not.
For more info on implications of using Ingres Rules with ODBC, see the
Jet White Paper and the "Implementing Ingres and ODBC/Visual Basic" Article.
03.006 What versions of the Ingres ODBC Driver exist?
Intersolv has now released version 2.11 of its Ingres driver. However,
the most recent version of the original Level 1 compliant Ingres ODBC
Driver is 1.01.1617. This should have patches 3142 and 3174 applied as
this resolves problems with underscores in table names and random GPF'ing.
The most stable Level 1 compliant version previous to this is 1.01.1518.
Where 1.01.1617 seems to fall over, it is advisable to regress back to
1.01.1518.
04.001 What alternative ODBC drivers are available to access Ingres?
The following are a list of currently known ODBC Drivers that allow access
to the Ingres Database. In order to provide some idea of the performance/
experiences with the drivers, I have 'quoted' from comments/emails etc.
These are not ultimate judgments on the products, but rather a non-sales
brochure opinion from real users. These opinions do not express my
opinions of the products nor do they express the opinions of Computer
Associates.
1) Intersolv ODBC driver, this uses Ingres/NET. The latest release is
2.11. For the moment, the CA recommended Driver.
Requires Ingres/NET?: YES
Address (UK)
Ph: +44 1727 811177
Fx: +44 1727 848991
Opinions/Experiences:
2) SCO (previously Visionware). Old description follows:
SQL Retriever from VisionWare, this uses VisionWare's own
comms protocol (hence they claim performance advantages), but you
require a VisionWare communications module on the server side
as well, and I think they currently only have these for the major
Unix platforms. The Visionware driver is definitely not available for
NT. I was told by SCO that they have no plans to deploy on NT in the
future (18-01-96).
Requires Ingres/NET?: NO
Address (UK):
SCO Ltd. Vision House
Waterside
Kirkstall Rd.
Leeds LS4 2QB
Ph: +44 113 2512000
Fx: +44 113 2512001
old email: support@visionware.co.uk.
info@visionware.co.uk.
No new email addresses available to date.
Opinions/Experiences:
I have used the visionware driver and found it very easy to install.
The very first connection was a little slow but the data transfer rate
was quite good.
3) Pacer Software
Requires Ingres/NET?: UNKNOWN
Address (US)
Pacer Software
Ph: +1 508 898 3300
email: pmerge@pacersoft.com
Opinions/Experiences:
4) VMARK Software
Requires Ingres/NET?: UNKNOWN
Address (US)
VMARK Software
30 Speech (or Speen) Street
Framinham MA 01701
Ph: +1 800 966 9875 of (+1 800 729 3553)
Opinions/Experiences:
5) Apple Computing
Requires Ingres/NET?: UNKNOWN
Provide an ODBC driver for the Mac.
Address: (somewhere in silicon valley?)
Opinions/Experiences:
6) Independence Technologies Inc.
Provide a DAL/ODBC client as well as a DAL server for VAX/VMS.
Requires Ingres/NET?: UNKNOWN
Address (US)
Ph: +1 510 438 2000
Opinions/Experiences:
7) OpenLink Software
Besides the standard Microsoft type interfaces that you can use
to access various back-ends, OpenLink also provide a UNIX interface
to ODBC. In other words, you can also use the ODBC API under UNIX.
Provide 'UDBC' Universal Database Connectivity.
UDBC is a compatibility layer enabling ODBC/SAG CLI development
in UNIX (SCO, LINUX, AT&T UNIX 5.4, OSF/1, SOLARIS, SUNOS, HP-UX,
DG-UX, SINIX, IRIX etc..), OS/2, and VMS environments.
UDBC will allow developers write ODBC/SAG compliant Client
Applications capable of accessing local or remote ORACLE, SYBASE,
INGRES,INFORMIX, PROGRESS, UNIFY 2000 Database Engines without
the enforced dependency on DBMS specific comms products such as
SQL*Net, CTLIB/NETLIB, I-NET, INGRES NET, PROGRESS CLIENT, and
UNIFY NET.
Requires Ingres/NET?: NO
For More Information:
Contact:
Tel: ++44-181-681-7701 Fax: ++44-181-681-7702
Email: 72662,3403@compuserve.com
WWW: http://www.openlink.co.uk
Opinions/Experiences:
Very easy to install. Very fast. In some cases the database agent
may take a second or two to start up but then successive connections
are instantaneous.
8) AUGUST SOFTWARE CORPORATION
A Macintosh ODBC Driver for Ingres to work with NT.
Requires Ingres/NET?: UNKNOWN
Contact: Thomas Gray
23232 Peralta Drive. Suite 202
Laguna Hills, California USA 92653-1437
Telephone (714) 454-9007 x443
Fax (714) 454-9032
Opinions/Experiences:
9) Visigenic Software
Visigenic has ODBC drivers available for Ingres 6.4, Informix,
Oracle, Sybase, MS SQL Server, and soon DB2. The Ingres 6.4
driver was added to the ODBC DriverSet 1.02 released in early
May 1995.
In 1994 Visigenic partnered with Microsoft with an exclusive license to
port ODBC to all non-Windows platforms. Visigenic has the source
code for the entire Windows SDK and currently has SDKs and
ODBC DriverSets available on UNIX (Solaris, SunOS, HP-UX, IBM AIX,
SCO, SGI...), OS/2 and Mac (68K and PMac). ODBC Test and ODBC Spy
will be entering beta soon.
Requires Ingres/NET?: Yes
Address (US)
Visigenic Software, Inc.
951 Mariner's Island Blvd. Ste. 460
San Mateo, CA 94404
Contact: Neil Blumenfield
Tel: +1 415-286-1900 Fax: +1 415-286-2464
Email: info@visigenic.com
support@visigenic.com
WWW: http://www.visigenic.com
Opinions/Experiences:
05.001 If my database changes do I need to change my client application?
This will depend on the application you are writing. Some client
applications load the data types of the data sources at application
startup (such as an SQL monitor or dynamic Query Builder). If you have
committed to data types and table structures in your application, the
database table structure should mirror this. The exact database you are
using should not matter though.
A change of database name or ingres installation in which the database
resides merely requires a change to the NETU or Data Source definitions.
05.002 What Ingres Software does ODBC Require?
I have just bought ODBC driver 'x'. Will I need any Ingres software
to use my application (e.g. MSAccess) against my Ingres database?
This will depend on the driver you purchase. Look at the section
describing the ODBC drivers available. Depending on which driver you buy,
you may need to use/buy Ingres/NET. Some drivers will provide their own
alternative.
05.003 Is ODBC secure?
Since ODBC is supposed to be open, there are proprietary doors closed as
far as keeping your front end secure. That is to say, you can't expect
to be open without compromising security to some extent.
At the end of the day, any ODBC sniffer will be able to trace *everything*
from an ODBC perspective. This includes data, usernames, passwords etc.
However, if you are using an ODBC driver that provides encryption, you
increase your level of security.
To really provide a secure environment and still use ODBC, you will have
to bypass ODBC for the secure issues. You can use an embedded SQL DLL
that performs individual secure statements.
It would be wise to assign groups and roles to the databases accessible by
ODBC. Since any front-end tool could feasibly modify your databases, you
must enforce your security at a server level.
The security of TCP/IP is questionable anyway.
Question on the net:
'I am using Gupta Windows 5.0 to access an Ingres 6.4/04 database. The
problem is that using the SQLWindows "sqlconnect" function to create an
initial connect does not seem to do any checking of the actual password
which is entered in a login screen. The value of the password is copied
to the SqlPassword value but when I use truss on the iigcn process I can
see that the password supplied to the server is always the one typed into
NETU on the client. Even after defining an empty password (using '*') in
NETU, this is sent to the server as the literal value which then fails.'
Answer by mdkale:
'The same problem appears in Powerbuilder. The same solution may well
work with Gupta. I believe the password being ignored is the 'fault' of
Ingres/NET. Installation passwords are not supported.
Try using a username within Ingres/NET of IIPROMPTALL, pw=*. This informs
Ingres/NET to put up a separate dialog box requesting username/password.
This works but you may soon tire of this new dialog box.
So we wrote a .DLL to capture the username/password from our login screen
and force-feed the Ingres dialog box. Users don't even see it pop-up'
06.001 Where is the ODBC FAQ?
The INGRES FAQ (~45) is archived at:
ftp.naiua.org (/pub/ingres)
The following is an example of the dialogue with the FTP server; lines
that include user input are marked with a `->' symbol:
->csh> ftp ftp.naiua.org
Connected to ftp.naiua.org
220- *** /etc/motd.ftp *** [much stuff deleted]...
220 FTP server (Version 5.92 Tue Dec 14 10:42:52 EST 1993) ready.
->Name (ftp.naiua.org) anonymous
331 Guest login ok, send ident as password.
->Password: juser@somewhere.org
230 Guest login ok, access restrictions apply.
->ftp> cd /pub/????
250 CWD command successful.
->ftp> get ODBC-FAQ
200 PORT command successful.
150 Opening ASCII mode data connection for ODBC-FAQ (nnnn bytes).
226 Transfer complete.
local: ODBC-FAQ remote: ODBC-FAQ
nnnn bytes received in 19 seconds (12 Kbytes/s)
->ftp> quit
221 Goodbye.
06.002 Can I get the FAQ by mail?
The FAQ is posted five or six times a year. Between postings it is
available from the archive site using ftp (see section 01.010). If
you do not have ftp access to the archive site, you can obtain the
FAQ by mail using one of the many ftpmail servers. (You can also use
the ftpmail servers to get the contributed tools mentioned in section
03.)
[The following information is provided by Bruce Horrocks and Tom Northey.]
The ftpmail servers use different software and the commands required
will differ from server to server. Try sending a message consisting of
just the word "help".
For some systems, the body of the message should be something like this:
open address [username [password]]
mode ascii
cd /pub/directory/etc
get filename
where address is an IP address, username and password are the username
and password to log into that machine as. Normally these are omitted
and they default to "anonymous" and your own mail address as the
password. Default transfer mode seems to be binary with files coming
over uuencoded, hence the switch into ascii mode.
In reply you get a confirmation of receipt mail message with an ID
number that lets you cancel your request. Assuming all goes well you
get the file requested as a mail message sometime later. Large files
are blocked up into 64k messages and need to be reassembled before
uudecoding.
Here is a list of ftpmail servers:
USA
===
ftpmail@decwrl.dec.com
ftpmail@sunsite.unc.edu
bitftp@pucc.princeton.edu
Europe
======
bitftp@vm.gmd.de
ftpmail@ftp.uni-stuttgart.de
ftpmail@ftp.inf.tu-dresden.de
ftpmail@grasp.insa-lyon.fr
bitftp@plearn.edu.pl
ftpmail@ftp.inf.tu-dresden.de
ftpmail@ftp.uni-stuttgart.de
ftpmail@doc.ic.ak.uk
Australia
=========
ftpmail@cs.uow.edu.au
07.001 What extra sources of information exist on ODBC?
* "Microsoft ODBC 2.0 Programmers reference and SDK guide"
by Microsoft Press; ISBN 1-55615-658-8.
A very good guide to ODBC but is filled with typo's and loads of bugs in
the code. I can understand why there is no disk with this as nothing would
compile if it was taken straight from the text. I lost many hours finding
misdeclared variables, missing declarations and pointers to nowhere.
* "Inside ODBC"
by Kyle Geiger; ISBN 0-471-10675-6
Kyle was one of the original ODBC architects from Microsoft.
* "Using ODBC 2"
by Robert Gryphon, Luc Charpentier, Jon Oelschager,
Andrew Shoemaker, Jim Cross and Albert W Lilley.
ISBN 0-7897-0015-8
Covers the ODBC API itself, and also the C++ MFC database classes,
as well as the VB data controls and Data Access Objects which interface
to ODBC.
* "Database Developers Guide with Visual C++"
by Roger Jennings and Peter Hipson; ISBN 0-672-30613-1
Discusses all aspects of writing Visual C++ DBMS applications all the
way from DB design and performance issues, through user interface design,
using MFC, ODBC, OLE through to packaging a finished application for
distribution.
* "Writing ODBC Drivers"
by Dennis R McCarthy (Dr Dobbs Journal)
Good explanation of Dr DeeBee tools to Build ISAM driver. I remember
the article being a comparison of a couple of drivers having been given
a budget and a time limit in which to build a driver. In any case, it
was quite good reading.
* The ODBC White papers (By Neil Black, and Stephen Hecht).
Available under compuserve and also on the ftp.naiua.org. This includes
both ODBC 1.1 and ODBC 2.0. There is plenty of information on ODBC in
general, but this tends to be centred around the Jet Engine which has
inherent problems of its own.
* Visual Basic Documentation
Look through the VB documentation on using ODBC through VB. The
principles will be the same whether you are using VB or another product
such as Powerbuilder.
* Implementing Ingres and ODBC/Visual Basic (By Jon Machtynger)
A DRAFT paper on some of the problems encountered around March '94. Can
be found at ftp.naiua.org.
* Microsoft ODBC FAQ (By Colleen Lambert)
'ODBC: Architecture, Performance and Tuning'. This is a document readable and
printable from any HTML friendly viewer. Address is:
http://www.microsoft.com/DEVONLY/STRATEGY/ODBC/ODBCBG.HTM
* ftp.microsoft.com
has a heap of information including the compatibility layer (if you're
interested in using Access 2.0 with VB3.0).
08.001 What is the compatibility Layer?
When Microsoft initially sold their Access 1.0/1.1 database packages,
they were making use of the Jet Engine. Access 2.0 had many additions
making access 1.0/1.1 databases incompatible with the Access 2.0 Engine.
Microsoft now provides a compatibility layer (found at ftp.microsoft.com)
that allows you to access Access 2.0 databases with Visual Basic. Look
for the file COMLYR.EXE.
08.002 What facilities exist to trace ODBC performance?
There are some commercial products available:
* ODBC Spy provided in the ODBC SDK 2.0. (Free)
Impressions:
It does an OK job. If you know the ODBC API, then it is probably quite
sufficient. However it lacks a really good interface.
* SQL Inspector (Call for details)
Blue Lagoon Software
6047 Tampa Ave., Suite 209
Tarzana, CA 91356
U.S.A.
Main: (818) 345-2200
Fax: (818) 345-8905
BBS: (818) 343-8433
CSERV: 70152,1601
Internet: blagoon@netcom.com
* Dr DeeBee Spy,
Syware Inc,
P.O. Box 91,
Kendall,
Cambridge, MA 02142.
USA
Tel: (617) 497 1376
Fax: (617) 497 8729
* ODBC Agent
The Leading Edge
2391 Baxter Crescent
Burlington, ONTARIO
CANADA
L7M 4C9
+1 (905) 319-8063
Internet: edge@wchat.on.ca
CompuServe: 76311,1426
Evaluation Copy available from compuserve (GO MSSQL)
Contact: Brendan Young.
Impressions:
Really nice interface. Very intuitive. Some things in the evaluation
copy did not work (such as logging trace to a file) but overall I was
very impressed. The price has recently come down on this tool so
you'll need to speak to them regarding current cost.
Some more Info on the Product follows:
ODBC agent provides comprehensive Spy/Trace analysis and fast & simple ad-hoc
ODBC query functionality. Its intuitive graphical displays show you the manner
in which ODBC compliant applications make use of the ODBC specification, and the
manner in which ODBC drivers handle the various API calls. It can display up to
999,999,999 rows of trace information on screen and output unlimited rows of
trace information to disk. The 16-bit version works with you applications now
and the 32-bit release for Windows NT and Windows95 will be work great with 32
bit applications.
You can easily save and open existing queries (*.sql files) capable of
containing multiple SQL commands and display up to 999,999,999 rows by
999,999,999 columns of data for select queries. Fire it up and get complete
driver information for each ODBC data source, full database table & column
information, all system tables, views, aliases and synonyms including
privileges.
You'll instantly see detailed information concerning all columns in each table
in the current data source, foreign & primary keys, references from a given
column as well as references to a given column. ODBC Agent offers full
Clipboard support, file-save and file-print functions.
08.003 Alternatives to ODBC.
ODBC is supposed to 'be' the alternative. However, it does provide its
fair share of problems. Most of these problems can be remedied by going
more proprietary. Some users are using DLL's to bypass ODBC performance.
For an example of this, see my paper on 'Implementing Ingres and ODBC/
Visual Basic' or March '94. This is a little out of date but gives
heaps of pointers.
* Due to overwhelming requests (and to cut down on personally mailing
everyone who asks), the DLL and sample application are now available at
the NAIUA ftp site.
Look for the VBISQL.ZIP file.
09.001 ODBC is very slow using Visual Basic. What can be done?
This is in effect a problem of the Jet Engine. The Jet Engine is
inherently slow against multi-tiered data sources.
ODBC has a problem in trying to be all things to all products. Performance
will also depend on the ODBC driver you are using as well as the ODBC
interface you are using.
I have run an application under Powerbuilder that is considerably faster
(in terms of connect time and data retrieval) than I could have created
under Visual Basic. This was using the Ingres Driver. Hence, performance
of the Ingres Driver was not necessarily the major bottle neck. This also
tells me that use of a much faster driver would have improved performance
dramatically under Powerbuilder.
Programming using the ODBC API directly is a little bit of work initially
but it may well be worth the effort. You will come up against *loads* of
problems with code examples and Visual Basic issues (if you are calling
directly from VB). You may want to create wrapper routines that convert
your FAR pointers to something more manageable by your host application.
For more information on performance tuning ODBC with Jet, see the documents
outlined in 07.001.
* Implementing Ingres and ODBC/Visual Basic
* Microsoft ODBC FAQ
10.001 Can I use ODBC with University Ingres?
No. University Ingres was created well before the ODBC standard.
Furthermore, Ingres89 uses a QUEL interface. ODBC requires SQL (also
known as the crippleware version of QUEL).
10.002 Can I access Database events through ODBC?
The ODBC Standard does not provide you with the facility to use database
events. However, there is a control that does provide this functionality.
It works as a VBX and is currently usable (i.e. tested) from within at
least Visual Basic 3.0 and SQLWindows 5.0. The current version 2.00
provides the alternative of using Ingres/NET or ODBC to raise DBEvents.
For more information contact Joli International Ltd. at:
(100436.1103@compuserve.com)
10.003 Can I use a 6.4 ODBC driver against OpenIngres?
Yes this is possible. Due to Ingres' very open structure, a 6.4 client
is totally unaware that it is in fact talking to an OpenIngres server.
In fact, due to the fact that GCA was designed to be upwardly compatible,
OpenIngres will do whatever the 6.4 client requests (within reason of course).
Now if you use an ODBC driver that uses Ingres net you can attach to an
OpenIngres server with the limitation that you won't have access to OpenIngres
specific data types such as DECIMAL/LONG VARCHAR. I have found relatively
few problems with this so far.
If you are not using a driver that relies on Ingres NET, you may have to be a
little bit more devious. The following examples assume you'll be using Ingres
local to your database access engine. In other words you're either using
something like OpenLink or Visonware (i.e. no Ingres net) or you're using your
ODBC driver locally (e.g. 32bit NT version of Intersolv or OpenLink NT).
For UNIX
~~~~~~~~
You have two choices here. You can set up a 6.4->OI gateway. This will
look after the requests from your OI database through truly portable GCA
(see option 1). Alternatively, you may be lucky enough to just switch
servers (See Option 2).
Option 1:
You can install an Ingres 6.4 client installation on the same machine as
your OI 1.x. Thie means your 6.4 installation only requires use of your
gcc/gcn. Your ODBC client always speaks to the 6.4 installation. You will
therefore need to set up your netu entries as a loopback from 6.4 to the
OpenIngres installation on the same machine. This is somewhat of an
inconvenience but is a good workaround while an OpenIngres ODBC driver
is being developed.
Your ODBC client (i.e. PC) will then have to specify the 'remote' database
(from the 6.4 installations perspective). In other words, on your PC you enter
'oinode::mydb' instead of 'mydb' for your database name. You will have to
have set up a netu entry for 'oinode' on the server (gateway) to your
server (database installation).
This means keeping your netu entries up to date with the UNIX passwords.
I have successfully used this with an OpenLink driver on HP.
Option 2:
Using OpenLink under HP, I was also able to modify the II_SYSTEM in the
oplrqb.ini to point from my original 6.4 installation to my new OI 1.2
installation. The request broker starts up fine (as you would expect -
no contact with Ingres yet). However, the Ingres agent (6.4 linked) seems
to speak to OI with no problems.
Quoting the OI 1.2 release notes for HP:
"CA-OpenIngres 1.2 provides support for 6.4 applications at the binary
level. You can run 6.4 applications that access a CA-OpenIngres 1.2
server without rebuilding the application. However it is recommended
that you rebuild all CA-Ingres 6.4 applications with CA-OpenIngres 1.2."
In other words, before throwing option 1 away, ensure you have something
similar to the above in your release notes. I didn't find anything like
this in the NT release notes. This explains the following workaround.
Under NT
~~~~~~~~
Since there is going to be a conflict of a 6.4 GCF architecture with the new
improved OI 1.2 architecture, you can run both a 6.4 client and OI 1.2 client
in the same installation (i.e. same II_SYSTEM).
Installation of (as a bare minimum) the OpenROAD Driver in your proposed
II_SYSTEM is necessary. This MUST be done BEFORE you install OpenIngres.
If you have already installed OpenIngres, then install OpenRoad in the
same directory and RE-install OpenIngres over the top. Your databases should
be quite safe since iidbdb etc already exist.
You can now install your 32 bit driver. Any 6.4 clients on the NT box will
require the 6.4 runtime system. Fortunately, this is now in your II_SYSTEM
since you installed the OpenROAD driver. Your 6.4 clients will be able to
speak to the local 1.2 server.
I have successfully used this with both the Intersolv 2.11 32 bit driver
and the OpenLink 32 bit NT ODBC driver.
10.004 Can I use ODBC under Windows95?
Yes it is possible to use ODBC against Ingres databases under Windows95.
You have two possibilities: You can use the 16 bit version of ODBC
together with the 16bit Ingres/NET *or* you can use the 32 bit version
of an ODBC driver.
If you're going to use Intersolv's 32 bit driver, you will need to make
sure you can run the 6.4 dependent EXE under Windows95. For this, I
installed the Windows95 OpenRoad driver, and copied the LIBQ32_1.DLL
to a file called LIBQ32.DLL. Then install your Intersolv driver
as per normal. Visual Basic 4.0 worked fine.
11.001 I have problems accessing tables with an underscore in them.
This was a problem with Driver 1.01.1617 (without patches). Install
the patch 3174 (or regress back to 1.01.1518). You can request the patch
from your nearest CA office. Recent ODBC delivery has included the patch.
11.002 I am using Visual Basic and can't use Transactions?
You will *not* be able to use Transactions using Visual Basic using
SQL_PASSTHROUGH. This is due to the limitation of ODBC level 1 compliancy
(or Visual Basic depending on whether Microsoft call it a deficiency in
your driver or their product). You can use transactions under VB by using
dynasets. For more information See the Implementing Ingres and ODBC/Visual
Basic Paper (section 7.001).
11.003 Powerbuilder GPF's when attaching to the Ingres Database using the
Ingres driver 1.01.1617.
This also occurs using Lotus 123 Version 5. Regress back to Ingres
driver 1.01.1518 or install patch 3174.
11.004 I cannot use Lotus123 (Previous to Version 5) with the Ingres Driver!
There have been many issues with using Lotus with the Ingres Driver. My
Recent testing of Lotus Version 5 using the Q+E Driver (Version 1.02) has
shown it to work but dates did not display properly. Lotus have been
investigating this.
11.005 I've just installed Paradox 5 and trying to use the Ingres ODBC
driver version 1.01.1617. When I try and open an Ingres table on
our Unix box I get a General protection Fault in sqing.dll.
The problem may be due to the Driver version. Regress back to ODBC driver
1.01.1518 or install patch 3174.
11.006 I get an 'Read Only' or 'Table is not Updatable' error message when
trying to update or delete a row in an ODBC table.
You must define a unique Index on the table being updated. This is a
restriction of ODBC. Jet will query the DBMS catalog for unique indexes
and will use the first one (in DBMS lexical order) it finds. It then uses
this unique index to maintain addressability to each row. If it finds none
then the recordset is treated as read only.
With the new Intersolv driver, you will need to either have a table created
with a unique index as part of the table (e.g. btree) or create a secondary
index. For columns of integer, I have found you need a secondary index.
The primary index is not enough.
11.007 I cannot reliably access and update data to Ingres using Access 2.0.
Also, I Get an ODBC_Call Failed error on reading multi-column
indexed tables.
When using large tables with a multi-column primary key, you will get
an ODBC call failed error when trying to read the table. You will be able
to edit/retrieve one record at a time (but of course this is slow). One
workaround is to eliminate multi-column primary keys or use the SnapShotOnly=1
([ODBC] section) parameter in the MSACC20.INI (which will cause the table to
be read-only).
Another workaround is by using a SQL Passthrough query as the source of the
data instead of an attached table. MSAccess tends to take a simple select
* from and convert it to a select with about 30-40 where clauses.
This tends to blow the Ingres optimiser. By making the source of the data
a SQL Passthrough Query you eliminate the opportunity for MSAccess to modify
its query from Access-SQL to Ingres-SQL.
This is a problem that appears on many drivers. This is invariably
due to Access 2.0. Access trys to read a data set of up to 10 rows at
a time. The Select statement generated by Access that allows it to
update any row that has changed is composed of 10 'OR's. With a multi-
column key, the WHERE clause is too complex. This has apparently been
resolved in Intersolv's 2.11 driver but I have not tested this yet.
11.008 How can I set Lockmodes using the Ingres ODBC Driver?
Driver 1.01.1617 does not allow the set lockmode statement to be sent as
commands. You can however, set up a data source with a locking strategy
of readlock = default/nolock/system.
The new Intersolv Driver does not allow you to set readlock. However, you
can set your default readlock strategy using ING_SET in your config.ing
file. i.e. 'ING_SET=set lockmode session where readlock=nolock'
11.009 Can I use ODBC with OpenROAD?
Up until recently, W4GL 3.0/02 (xxx.xxx/0x) and 6.4/04 (net.win/00) were
incompatible. This meant that using any ODBC driver that utilised Ingres/NET
would cause a conflict. The only alternative was using a driver that did not
use Ingres/NET such as Openlink or Visionware. From W4GL 3.0/03 (...), the
Ingres/NET conflict has been completely resolved so you can use an Ingres/NET
application (such as Crystal Reports) through ODBC and run your OpenRoad
application at the same time. You must install the 6.4/04 (net.win/01) for
this to work though. An added advantage of this version of Ingres/NET is
that it is far more performance.
If you are using ODBC through OpenROAD to access local database files, you
should find no problems at all.
The main difficulties in using ODBC through OpenROAD are in addressing your
handles properly and defining your API routines. Until you get them perfect,
you will get no end of GPF's. I stress that these GPF's are probably not
due to OpenROAD but down to getting your integer/long arguments correct
in your API function declarations. Try creating a wrapper DLL for the ODBC
API. This will behave far better and allow easier debuging of ODBC calls.
What is possible is to define a user class holding the ODBC attributes/methods
and provide the ODBC functionality through OpenROAD methods. This worked
quite well on local tables in the Watcom Database and also Ingres Databases
through ODBC. In theory this should work as well on other databases like
Informix and Sybase but I didn't have one around to try it.
11.010 When using attached tables with MSAccess and the Intersolv 2.0 driver,
if I don't save the user/password information at the time of attach,
the next time I go into MSAccess and try to use that table I get a GPF.
This will only occur on tables that have a unique secondary index. If you are
using the new Ingres/NET 6.4/04 (net.win/01), request patch 3627 from technical
support. It resolves this problem.
Alternatively, if you are using the Intersolv 2.10 driver or above, this
should be resolved.
11.011 I can't use MULTINATIONAL dates with the new Intersolv driver.
Strangely, only a date format of US works with the new driver. I can't see
a problem with this unless you're not in the States. This is resolved as from
the Intersolv driver 2.10 and above.
If you need to use multiple date formats, you can set up a batch file that
starts your application. Before application startup, assign the II_DATE_FORMAT
to MULTINATIONAL (for example).
11.012 When I access information from attached tables using the Intersolv
Driver, I get the following error message:
ODBC Specification Conformance Error (-7776). This error should
be reported to the ODBC Driver vendor.
This is the error message produced by not having your date set to US (as per
11.011). This problem should be resolved with version 2.1. Setting your date
format to US does not however, make any difference to your display on Access.
It will still show dates as per your control panel settings.
11.013 I've tried installing my 16 bit ODBC Driver under NT. It doesn't work.
This makes sense, since the 16 bit ODBC manager will try to install itself
in the NT control panel. This is more a by-product of the ODBC driver's
setup program than any 16 bit limitations. I have spoken to people to have
installed the Intersolv 16 bit driver under NT successfully. It is probably
preferable however to use the 32 bit driver. It works fine. I've written a
32 bit driver without coding any thinking and it was usable by a 16 bit
application such as ODBC test. I can only assume then, that the thinking is
being handled somewhere in the top two layers of ODBC. In other words, your
16 bit applications should still work OK with a 32 bit driver.
The only two 32bit drivers I have been made aware of so far are Intersolv
and OpenLink. Both work well against Ingres (and OpenIngres) under NT.
For more information see section 4.001.
11.014 Can I use Ingres Net/ODBC with Windows-95?
Theoretically you should be able to use any 16-bit application under
windows-95. I have successfully used Ingres/NET without having to fudge
anything through W95. If you can use a 16-bit ODBC manager and an application
that uses the Ingres 16-bit NET API, this should work. I have heard that it
has worked under NT.
11.015 I have had problems using blank dates in MSAccess.
Where you have a blank string for a date, you will get an ODBC specification
conformance error (-7776). All data when using Access shows up as #Name.
This has been reported as Ingres Bug #69085. In other words, this has been
reported back to Intersolv for resolution. The workaround is to use NULLs
instead.
11.016 I cannot use Intersolv's 32 bit driver under NT 3.51. On doing a
connect, the interface immediately disappears. The same problem
occurs using ODBC Test.
This due more to a lack of DLL's in your 6.4/OI 1.2. To work around
this problem, if you are using Ingres 6.4/05, install the OpenROAD
driver in the same installation as your Ingres server. If you are
using OI 1.2, install the OpenRoad driver and then re-install OI 1.2
on top again.
This problem did not appear in NT 3.50 and earlier.
11.017 I can't install the Intersolv driver properly... I get the following
error: "You may be low on memory and need to quite a few applications"
This is a very bad example of using one error message for every problem.
You may in fact be low on memory. However you should also check that DLLs
required by the driver are all readable and in your path.
Some of the main culprits: %II_SYSTEM%\ingres\bin\iiw3ing.dll
\windows\system\qeing407.dll
Under Windows95 and WindowsNT they could also be: libq32.dll
adf32.dll
gca32.dll
11.018 The date/time as I read data in MSAccess is always out by a number
of hours from that time I get on the server.
The Net/API will use the DOS environment variable TZ. Work out what offset
you need to set in DOS for this to work. e.g. place a SET TZ=-1 in your
autoexec.bat.
------------------------ END OF FAQ -----------------------------
Ingres Database Reference
To William's Home Page
© William Yuan 2000
Email William