Problems with Oracle Attached Tables

Jim Dill  25 Mar 02


Updated 23 Apr 02

There are numerious problems using ChemFinder 7.0.1 to connect to Oracle databases.  Our long-term solution to these problems is the ADO / Oracle Cartridge scheme now under development for ChemFinder 8.0.  In the meantime, some important customers need better behavior sooner.  We are fixing some of these problems in 7.0.2, and may end up generating a 7.0.3 for the harder ones.

Related documents (mostly lengthy research notes):

The following table summarizes the problems.

Key:

gone or marked retest
fixed but not ready to retest
in progress
not for 7.0.3

 

Category Problem CSBR Notes
Setup      
  Mstfile attach problems.  Create a database and attach an external mstfile --  form generation and other operations cause the attachment to break  The name of the mstfile does not show up in list window in either DB or Table tab.  Fix committed 3/25/02. 28383 fixed in 7.0.2
  Read-only forms.  You cannot save a form such that it always opens read-only.  (Many Oracle operations are faster on a read-only db.)  The workaround is to set an attribute on the mdb file, which is not usually satisfactory.  Fix committed 3/25/02. 28384 fixed in 7.0.2
  Password problems.  If you attach to an Oracle table and do not choose to Save Password, then you will have to enter the password more than once.    
  Slow table list retrieve.  Doing Attach Table to a big Oracle database is slow to retrieve the list of tables, and ends up with a huge, unmanageable list.  Fixed in 7.0.2 -- we retrieve only the USER tables, not ALL. 28385 fixed in 7.0.2
  New database not updatable.  Do Attach Table to an Oracle database.  The resulting table is marked non-updatable, and prevents adding records.  Fixed 3/2/02 -- we create an index if the table has none. 28193 fixed in 7.0.2
Text Search      
  Cannot search exact.  There is no way to search a text field for a match on a specific token -- the query is generalized to a series of LIKE clauses, whether the user likes it or not.    
  Cannot find specific query.  Search over a memo field for some query like 'abc'.  It hits *abc and abc*, but does NOT hit 'abc' if that is the entire entry. 28135  
  Some queries slow in Access.  Searching over a memo field with a LIKE clause is unacceptably slow in Access, hence Jet, hence ChemFinder.  The same search works fine in SQL*Plus or Oracle ODBCTest.    
Mol Search      
  Tmp tables vs limit.  When a structure search gets more than 1000 hits, a temp-table mechanism kicks in, causing bad performance and other problems.  Contemplating limiting the total number of returned hits to 1000.    
  Misaligned mst/mdb.  If a relational table contains a random list of mol_id's, problems arise.  Searches go over the entire mstfile, even if the table contains a small subset.  Hits from the mst search may not correspond to id's in the table.    
Speed      
  Move Next if writable.  Opening a form with an attached table is reasonably fast, but on Move Next you get a long delay.  This goes away if the database is opened read-only.    
  Move Last.  Move Last on any sizeable attached table is unbearably slow.  This is also true in Access.  Not sure there's anything we can do about this.    

 


Useful tables found while browsing MSDN:

When you link an Oracle table in Microsoft Access:

Oracle Type

Maps to Access Type

number(1-4,0)

Number (Integer)

number(5-9,0)

Number (Long Integer)

number(10-15,0)

Number (Double)

number(16-38,0)

Text

number(1-15,n)

Number (Double)

number(16-38,n)

Text

float

Number (Double)

date

Date/Time

char(n)

Text(n)

raw(n)

Binary(n)

long

Memo

longraw

OLE Object

When you export a Microsoft Access table to Oracle: 

Access Type

Maps to Oracle Type

Yes/No

number(5,0)

Number (Byte)

number(5,0)

Number (Integer)

number(5,0)

Number (Long Integer)

number(10,0)

Number (Single)

float

Number (Double)

float

Currency

float

Date/Time

date

Counter

number(10,0)

Text(n)

char(20)

Memo

long

OLE Object

long raw

 For more, see Microsoft Jet Database Engine ODBC Connectivity White Paper (Word document)


Copyright 2002 CambridgeSoft Corp. All Rights Reserved.
Proprietary and confidential to CambridgeSoft Corp.
Last updated: 06/15/2002
Pagekeeper:  Jim

[ R&D Home | ChemFinder Home ]