🚀 go-pugleaf

On Fri, 09 Jul 1999 12:21:10 +0200, Travis Elkins <telkins@plusdata.com>
wrote:

>1. We have a database on a server that is relatively large and slow.  We
>are developing some client applications that will display statistical
>information from this data source.  Retrieving the data is a pretty slow
>process.  So, currently all activity stops while waiting for the data
>(TTable and TQuery components) to open.  I would say that this behavior
>doesn't affect the main goal of the application, since it's just meant
>to be watched and updated every 5-15 minutes, but it is slightly
>irritating.

[...]

I am not well versed enough in the creation an duse of threads to fully
explain this. One thing I can comment on is what the introduction of
threading may or may not give you.

A thread would enable the application to be concurrently retrieving data
from the database and be performing other actions. It will not, however,
speed up the data access. That is a factor of a number of things,
including: the database type, what you meant by "server", the availability
of indexes, TTable versus TQuery and whether you are using local or SQL
databases, and other factors.

>2. I had some quick questions about AUTOINC fields.  I remember reading
>somewhere that one should/could use triggers instead to implement such
>behavior.  Is this a better alternative?  I would guess not, but I am
>not sure.  Nor do I remember what I read or where I read it, so I don't
>konw what else to do other than to ask others.

In some SQL database systems, the *only* way to implement a column with an
automatically generated value is using a stored procedure called by a
trigger. In those cases it is not an alternative, but business as usual. In
other database systems, like local databases where there are no stored
procedures, you have a specialized type of auto-increment column and need
to use that. And in other database systems, such as Microsoft Access, you
can use either approach. And, automatically generating column values *can*
also be done in the front-end application.

Which is better? There is no single blanket answer for that question. It
depends a lot of how well the database system of choice handles the value
generation operation and whether you wish to code the process in the
application.

>Also, I was hoping someone could tell me what the upper limit is with
>AUTOINC fields.  I'm quite sure it won't just keep counting forever and
>ever.  Is it a standard integer or what?  Or, does it depend on the
>database?  (I'm using Paradox.)  And, will it use all possible values,
>or just stop when it reaches a maximum, regardless of the total record
>count.

The maximum capacity of an auto-increment field will vary from one database
system to the next. I am not positive, but I believe the Paradox auto-inc
field is a LongInt and has a maximum value of 2,147,483,647.

But then, if you code your own unique values, you can make the values
whatever you want and the range is almost infinite. For instance, a
customer number might be the first three characters of a company's name
plus a 20-digit number (converted to string and concatenated with the first
three characters).

>Finally, I want to know what happens when the upper limit is reached.
>Will it "roll over" and start filling in any "missing" values?  Look at
>my example:
>The upper limit is 10.  I add 8 records giving me the AUTOINC values of
>1-8.  Then, I delete records 3 and 5 leaving me with 6 records with
>AUTOINC values of 1-2, 4, and 6-8.  Then, I add two more.  This adds 9
>and 10.  What if I add another.  Will it then find that 3 and 5 are free
>and use that?  If so, what then happens when 3 and 5 are both filled up?

I do not believe the BDE would start reusing empty numbers. Rather, it
would likely raise some sort of overflow exception.

>3. I have had some difficulties creating a Paradox table using SQL with
>a field described as NOT NULL.  I create a simple table with two
>fields.  One is AUTOINC, and the other is VARCHAR(x) NOT NULL.  I
>receive an error when attempted to execute the statement from SQL
>Explorer.  When I remove NOT NULL from the statement, it works fine.
>Can anyone explain this behavior?  Can anyone suggest an alternative?

Local SQL (what the BDE uses for dBASE, Paradox, and FoxPro tables) is a
subset of SQL-92. The only types of constraints it supports are primary
indexes and unique secondary indexes. Local SQL does not support the NOT
NULL constraint. To do that, would need to modify an already existing table
using the BDE API function DbiDoRestructure. Use that function to add
constraints to the table. BDE API functions are documented in the BDE
online help, BDE32.HLP, found in the main BDE directory.

Use DbiDoRestructure to modify tables programmatically at runtime. It can
also be done interactively at design-time using the Database Desktop
utility.

BTW, the Paradox table specification does not support true VARCHAR columns.
Both CHAR and VARCHAR are translated to the Paradox field type known as
Alpha, which takes up a constant amount of space in the table regardless of
actual data content. I mention this just so you won't think you were
actually getting a variable-length (VARCHAR) field.

>4.  According to the localsql.hlp help file, FOREIGN KEY is not
>supported in local SQL.  It's my desire to properly implement
>referential integrity during table creation.  Are there any alternative
>methods to implementing foreign keys and referential integrity
>programmatically from Delphi with local Paradox tables?

Again, the local SQL help file reflects the situation as it is. To
implement a FOREIGN KEY constraint (Referential Integrity in Paradox
parlance), you would need to use ther BDE API function DbiDoRestructure.

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Steve Koterski     "An inventor is a person who makes an ingenious
Felton, CA         arrangement of wheels, levers and springs, and believes
                   it civilization."          -- Ernest Dimnet (1866-1954)