Accessing logical fields in paradox tables via ODBC 
Author Message
 Accessing logical fields in paradox tables via ODBC

I am trying to access a paradox database, containing logical fields,
via ODBC. However attempting to bind a parameter to a logical field
requires a long integer data type as there appears to be no logical
SQLDataType. The LongInt type is dictated by the underlying data type
in the Paradox table structure being four bytes. Surely this is wrong
as this is database specific and will fail if I then move the tables
to a database in which the underlying type is not four bytes.

Also there appears to be a problem in retrieving values using queries
based on a logical field;

e.g.
        'SELECT FldVal, FldBool from ATable Where FldBool = true'
works, but
        'SELECT FldVal, FldBool from ATable Where FldBool = :QryBool'
fails to find any true records if the query condition is defined as;
        FldBool := LongInt(True);
even though using the first case shows LongInt(true) and ColLongInt[2]
to both evaluate to 1. Queries on false values in the fields work in
both cases. To get this to work for true values it appears to be
necessary to set FldBool := -1, but again this is tied to the
underlying representation of a true value in the Paradox database
structure.

This is all implemented using Paradox 5 tables under Paradox 7 and the
microsoft paradox drivers (3.51.171300 / MDAC 2.0) with test code in
delphi 3 using ODBC Express v 4.53.

What am I doing wrong here? How do I remove the explicit connection to
the underlying types and values in a database?

Jarvis N. Brand




Wed, 18 Jun 1902 08:00:00 GMT  
 Accessing logical fields in paradox tables via ODBC
I am trying to access a paradox database, containing logical fields,
via ODBC. However attempting to bind a parameter to a logical field
requires a long integer data type as there appears to be no logical
SQLDataType. The LongInt type is dictated by the underlying data type
in the Paradox table structure being four bytes. Surely this is wrong
as this is database specific and will fail if I then move the tables
to a database in which the underlying type is not four bytes.

Also there appears to be a problem in retrieving values using queries
based on a logical field;

e.g.
        'SELECT FldVal, FldBool from ATable Where FldBool = true'
works, but
        'SELECT FldVal, FldBool from ATable Where FldBool = :QryBool'
fails to find any true records if the query condition is defined as;
        FldBool := LongInt(True);
even though using the first case shows LongInt(true) and ColLongInt[2]
to both evaluate to 1. Queries on false values in the fields work in
both cases. To get this to work for true values it appears to be
necessary to set FldBool := -1, but again this is tied to the
underlying representation of a true value in the Paradox database
structure.

This is all implemented using Paradox 5 tables under Paradox 7 and the
microsoft paradox drivers (3.51.171300 / MDAC 2.0) with test code in
Delphi 3 using ODBC Express v 4.53.

What am I doing wrong here? How do I remove the explicit connection to
the underlying types and values in a database?

Jarvis N. Brand




Wed, 18 Jun 1902 08:00:00 GMT  
 Accessing logical fields in paradox tables via ODBC
If I were you I'd get hold of the current Paradox ODBC drivers (buy a
copy of Paradox - it's only about $100 !). Alternatively, why not use
the BDE directly ?

-- Mike Irwin [Paradox CTech] It's not because things are hard that
[P9.00.367, V8.372,           we don't dare them: it's because we don't
D9.337, W9.0.0.528]           dare them that they are hard - Seneca



Wed, 18 Jun 1902 08:00:00 GMT  
 Accessing logical fields in paradox tables via ODBC
On Sun, 06 Jun 1999 01:25:47 EDT, Mike Irwin [CTech]

Quote:

>If I were you I'd get hold of the current Paradox ODBC drivers.

Are there known problems with the Microsoft drivers which are solved
with the Paradox drivers? The latest problem we have returns mixed
driver errors (some are ODBC level 2, others level 3.x) - it appears
that the Microsoft driver is, if not at fault, then at least
inconsistent so we'll be trying the MDAC 2.1, Intersolv and Paradox
drivers.

Our latest problem - the system fails to recognise BLOBs as insertable
or updatable fields. Inserting blobs reports 'Unknown field name'
,which is a blatant lie as we can reference the field in a SELECT
statement, and attempting to update the field reports either 'field
not updatable' or 'Invalid argument'.

Quote:
>Alternatively, why not use  the BDE directly ?

Our current solution uses the BDE but we are trying to get away from
using it as it ties us to using Paradox tables, isn't scalable, can't
handle the volume of data we anticipate and, given our past
experience, does not appear to be sufficiently stable for our use.
What we don't want to do is end up using a paradox ODBC driver which
is just an ODBC compliant interface to the BDE.

Jarvis N. Brand




Wed, 18 Jun 1902 08:00:00 GMT  
 Accessing logical fields in paradox tables via ODBC
Taking your final point first, with all respect, I would like to disagree. I hope you'll forgive
me for dissecting your answer:

1. "Our current solution uses the BDE but we are trying to get away from using it as it ties us
to using Paradox tables"

a. Your statement appears to be in error. The BDE operates with Paradox, dBase, and Access
tables, accepts ODBC and SQL Link entries in order to provide a wide range of connections.

B. If you are going to use Paradox tables anyway, why are you worried about the BDE tieing you to
Paradox tables ?

2. "[The BDE], Isn't scalable, "

a. Please define what you mean by "scalable". In that the BDE can be dispersed throughout your
system by having one installation on each machine I fail to see your problem. If you are
referring to the Paradox net file use, you'll find that the ODBC driver needs this too.

3. "[The BDE] Can't handle the volume of data we anticipate"

a. Well, you haven't specified any quantitive estimate, so I can hardly defend it, but I can
assure you that the ODBC drivers will be no better.

4. And, given our past experience, [the BDE] does not appear to be sufficiently stable for our
use.

a. I would be interested in hearing some specific instances in detail. I realise that there are a
number of known bugs with the BDE, but these can be located in a list at www.inprise.com, unlike
those of many companies' products, and all can be worked around.

5. What we don't want to do is end up using a paradox ODBC driver which
is just an ODBC compliant interface to the BDE.

a. Well, what you're using right now is an ODBC driver that's compliant with Paradox tables level
4.5 and, mostly, 5.0. It will restrict you in what you can achieve with your software, and it
will almost certainly be slower than a BDE-based alternative.

However, after all that, I have to wonder what you are intending to build. If you really expect
high voloume, I'd suggest that you look at InterBase instead of Paradox. It is also very
reasonably priced, and a highly reliable C/S database system.

-- Mike Irwin [Paradox CTech] It's not because things are hard that
[P9.00.367, V8.372,           we don't dare them: it's because we don't
D9.337, W9.0.0.528]           dare them that they are hard - Seneca



Wed, 18 Jun 1902 08:00:00 GMT  
 Accessing logical fields in paradox tables via ODBC
On Sun, 06 Jun 1999 21:11:29 EDT, Mike Irwin [CTech]

Quote:

>Taking your final point first, with all respect, I would like to disagree. I hope you'll forgive
>me for dissecting your answer:

Feel free. Being proved wrong would be the best option as far as I'm
concerned.

A bit of context would probably help here.

We have a message archive and retrieval system. Handling just telexes,
Paradox and the BDE is more than adequate. We now handle fax and telex
and have sites recording up to 300Mb of data a day and archiving it on
2Tb optical jukeboxes. Those sites want to extend the system to handle
yet more fax systems and email by which point we could be recording
several Gb of data per day. At present we handle the jukeboxes using
Paradox by careful partitioning of the data but, as the volume
increases we will reach a point where Paradox will fail as we may need
to handle files over 2Gb.

Being tied to Paradox is an artifact of our user base, if possible we
want a solution that allow the user to keep existing archives and not
require them to port their entire archive from one database format to
another. (This is what ties us to Paradox - my statement about BDE
tieing us to Paradox can only be explained by the lateness of the hour
and too much caffeine when I sent the original message ;-)).

BDE not being scalable is true in two respects; 1- BDE and paradox
will not handle databases of the size we expect and 2 - in our
experience BDE becomes unstable as more clients are added and data
throughput increases.

I don't have a detailed list of the ways in which BDE has failed on us
but some of the problems which come to mind are;
        - Data loss from tables!
        - Clients leaving locks in place preventing tables being
accessed
        - BDE reporting out of disk space on while having n * 4Gb free
space.
        - Indexes becoming corrupt
        - Queries not removing temporary files after use.
        - Queries not freeing resources after use
        - Simultaneous access from two or more process leaving tables
locked.

We have work rounds for most of these problems. Others we have no
choice but to install repair utilities at site in anticipation of
needing them.

Hence our decision to investigate alternative approaches. We know that
the microsoft drivers are restricted to Paradox 5 but for evaluation
of ODBC it was reasonable to use those. Our main aims in looking at
ODBC are to remove code dependency on any specific database, retain
backward compatability with existing installations, allow the option
of supporting large database systems and, if possible, to remove BDE
from the system.

I am a little surprised by your assertion that ODBC will be slower
than BDE. Most of the claims I have heard, admittedly from ODBC driver
suppliers and ODBC component documentation, suggest that the opposite
would be true.

Jarvis N. Brand




Wed, 18 Jun 1902 08:00:00 GMT  
 Accessing logical fields in paradox tables via ODBC
Hi Jarvis !

Ok ! <waaah> ! So much data !!!

To be honest, the BDE isn't as bad as you are painting it. Let me examine the problems that you have presented (in the
knowledge that you have had more, but lets look at these, at least).

1 - Data loss from tables!
    This is _not_ a normal occurrence. There are, to the best of my knowledge, no bugs in the BDE that are the
    equivalent of the Jet Engine's ability to post one record over the top of another. Any of the items described
    in #2 may also cause data loss.
2 - Clients leaving locks in place preventing tables being accessed
    There are a number of ways of doing this: turn your machine off while Paradox is running; crash your machine;
    break your network connection; use the Task Manager to terminate the instance of Paradox that you're running.
3 - BDE reporting out of disk space on while having n * 4Gb free space.
    I do not know which version of the BDE you are using, but I would suggest an upgrade !
4 - Indexes becoming corrupt
    See #2
5 - Queries not removing temporary files after use.
    This I haven't seen. Do you have an example ?
6 - Queries not freeing resources after use
    Have you an example I could try out, please ?
7 - Simultaneous access from two or more process leaving tables locked.
    This is causable by an incorrect setup in the BDE, or by optimistic locking on a Win NT or Win 9x machine.
8 - I am a little surprised by your assertion that ODBC will be slower than BDE. Most of the claims I have heard,
    admittedly from ODBC driver suppliers and ODBC component documentation, suggest that the opposite would be true.
    In this case it is highly unlikely, as Paradox is a native format for the BDE. Some ODBC drivers are highly
    efficient; for example, the InterBase ODBC driver is at least on a par with the SQL Links InterBase driver for
    Paradox.

Now, what I would like to suggest.

I don't think that your worthy idea of{*filter*} on to Paradox is going to fly, to be honest. This is because of the sheer
amount of data that you're playing with.

I would seriously suggest that you look at the way in which you have designed your system and see how you can move
towards a tiered architecture asap.

In this I mean that you should look to remove all dependencies on the tables being Paradox. Obviously you have also come
to the same conclusion because you are looking at ODBC; however, if you use ODBC with Paradox then Paradox hands the SQL
request off to the BDE, which then passes it on to the ODBC driver ..... so you're not losing the BDE. In addition to
this, however, a fair chunk of Paradox functionality was pushed down into the BDE at some stage; I have no idea why, but
one example is the infamous formatting problem where the instruction string "cc" has not capitalised the first letters of
all words in a string for several versions of the BDE. For Paradox 9, Corel replaced the call into the BDE with one of
its own to do the work within Paradox instead.

So, accepting that we're stuck with the existance of the BDE, let's look at what _I_ think you should do:

1. Get a copy of Paradox 9 Developer's Edition when it is released in June. This will give you a more stable platform to
develop on (than 7 or 8), a RunTime distribution system, and a copy of Borland's SQL Links.

2. If the Developer's Edition does not contain a copy of InterBase, get hold of a copy of "Local InterBase". This is a
2-user developer's version that is packaged with Enterprise Editions of Borland software. You should be able to get hold
of a copy second-hand or reasonably cheap.

3. Redevelop a portion of your system (just a little bit !), retaining Paradox as the database, but keeping a fierce eye
on the field types allowed by InterBase. Design it so that _everything_ is aliased (tables and databases), and that
tCursors are never used (certainly not for scans !). What you are trying to do is create an SQL-based piece of code that
uses Paradox for its backend.

4. Once that works, convert it to InterBase (which should be easy, but won't be, the first time, cos you'll have
forgotten lost of things <g> !). Get help from the TeamB crowd over on the Borland newsgroups, and from the CTechs on the
Corel newsgroups. Ask questions about everything !

5. Finally, look towards using Paradox as the front end for an InterBase C/S database. Then think about web access
development !

Regards

PS No caffeine, so I'm off to kip !

-- Mike Irwin [Paradox CTech] It's not because things are hard that
[P9.00.367, V8.372,           we don't dare them: it's because we don't
D9.337, W9.0.0.528]           dare them that they are hard - Seneca



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Logical fields in Paradox tables

2. Filter a Paradox table on a Logical field

3. Problems appending to Access Tables via ODBC w/ Delphi 1.0

4. Storing BLOB data in MS-Access tables via ODBC

5. Trouble posting to Access table via ODBC

6. accessing ms-access via ODBC

7. Paradox LOGICAL fields

8. Saving TMemo field data to a Blob field (Interbase Table) via SQL Update Query

9. ISAM or ODBC driver for Attaching Paradox 5 tables to MS Access 2.0

10. Accessig Paradox via ODBC ??!?

11. Change memo field via ODBC?

12. Reading Blob-Memo Fields Via ODBC

 

 
Powered by phpBB® Forum Software