Re: Cobol MS-Access

James J. Gavan wrote:
foxgrove@xxxxxxx wrote:
<snipped examples of accessing MS Excel via OLE and MS ACCESS via ODBC using
Micro Focus COBOL.>

The consideration that needs to be examined here is how important efficiency
is to the end result.

While I wouldn't agree that either of the solutions offered is "ugly",
neither of them is particularly "elegant" either.

ODBC is flexisble and allows connection to any database that provides an
ODBC driver, sometimes without requiring any change to the COBOL code, BUT
it is NOT efficient (compared to other options).

Cursors in ODBC hold the DB connection for as long as the cursor is open.
This can have a very serious effect on throughput where multiple databases
are being accessed from multiple terminals. New connections may have to wait
to obtain a connection and this can be a "bottleneck" which degrades
overall system throughput. This factor alone is a major one in the
deprecation of Embedded SQL (ESQL). (Many programmers see ESQL as being
obsolete, replacing it with multiset constructs and triggered stored
procedures...There are some very compelling arguments for doing this
anyway...)

Automation (OLE originally, now COM) provides platform independent access
and (for MS ACCESS in particular, but not exclusively) enables the use of
DAO or ADO, both of which are many times more efficient than ODBC. ADO can
be used with a number of databases besides MS-ACCESS and it runs a single
query which returns a resultset, then disconnects. Connections are held for
milliseconds rather than minutes and the resultset is processed in memory.
DAO, although deprecated by Microsoft in favour of ADO, is a "no frills"
package which runs very efficiently with ACCESS. You can run ADO and DAO
from COBOL but you will need to use COM/OLE to establish easy communication
with these subsystems. (COM is not the ONLY way but it is probably the best
way for COBOL...)

I tried using ADO with dynamic SQL from COBOL and found that even with the
dynamic PREPAREs it was STILL considerably faster than ESQL across ODBC.

These days I use LINQ with C# to access databases and I have no intention of
going back to ESQL and ODBC; it is simply a different world.

The point of my post is that before we label anything as "ugly" we should
look at the context it is intended for. Many things that we think are
"cool", in a broader context can be seen to be not so...

Pete.
--
"I used to write COBOL...now I can do anything."


.

Relevant Pages

  • Re: ADODB.Connection object not returning correct ConnectonString prop
    ... Opens the connection (successfully) ... I'm not positive but a guess is that ADO is using the MSDASQL Provider to ... provide OLE DB support for ODBC. ... ODBC Driver. ...
    (microsoft.public.data.ado)
  • Re: SQL Server extremely slow
    ... my personal experience with a similar setup as your (Sql Server ODBC ... against the sql server (ADO was specifically designed for use with sql ... the connection is closed. ...
    (comp.databases.ms-access)
  • Re: SQL Server extremely slow
    ... my personal experience with a similar setup as your (Sql Server ODBC ... My fix was to use ADO ... With ODBC the connection remains open the ...
    (comp.databases.ms-access)
  • Re: Access to file DBase with ADO
    ... I am not using DBase and thus my response might seem odd to you... ... As I am upgrading from D5 to D7 and changing my connectivity from ODBC to ... ADO I am doing away with my ODBC datasources and strictly using ADO ... define the communications I use the connection string 'builder' in the ...
    (borland.public.delphi.database.ado)
  • Connecting to another system
    ... I had considered the sql server groups, ... I have seen talk of ODBC to connect to cobol flat ... get an ODBC driver and what I need to do on the unix system as well in order ...
    (microsoft.public.dotnet.languages.csharp)