Board index » delphi » Several Questions

Several Questions

Hello,

I have several questions I would like some help on.  For the most part,
they have something to do with database development, but there are some
related to DLLs/packages, (D)COM, threads, and other items.  Please, any
help or advice anyone might have would be greatly appreciated.

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.

We are quite sure that if we have some other process retrieve the data,
and let our (main) application know that it's finished, then we can
quickly retrieve that data.  Several ideas included a separate
DLL/package, thread, or (D)COM server that would retrieve the
information from the database server and then alert our application when
the process was complete.  Then, we could quickly retrieve the data from
the 'middleman' with a fraction of the delay.

If you have understood what I am looking for, then I hope that you can
offer some advice.  I've looked at (D)COM servers, but don't know how to
implement callback procedures or to transfer data (objects).  I've tried
threads, but I'm quite sure I'm leaking resources like mad and my
"simple" test apps aren't working as I intended.  I haven't tried
DLLs/packages yet, but I believe that so long as their in the same
process, then it wouldn't be any different than if it were in the main
EXE.  So, please, any advice would really be appreciated.

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.

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.

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?

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?

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?

Well, I believe that's the extent of my questions.  As I've mentioned,
any help would really be appreciated.  If I'm unclear about any of my
problems or questions, feel free to contact me and ask for further
information or more clear explanations.

I would also like to ask that respondents address me by email as well as
the newsgroups.  This allows me to know immediately when someone has
responded.  Plus, I have email access both at work and at home, but only
newsgroup access from work.  I've had people do one or the other in the
past...please do both.

Thanks again for your time.

Travis

  telkins.vcf
< 1K Download
 

Re:Several Questions


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

Quote
>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.

Quote
>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.

Quote
>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).

Quote
>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.

Quote
>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.

Quote
>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)

Other Threads