Board index » delphi » Query Question

Query Question

My application would be running in Paradox first and then, I will port
to Interbase later.  So, I have the question as following:
-       I would like to know about Query that should I put one Query component
per Query or should I put only one, then, use the code to make different
for each task.
-       Is there any way to run SQL without to put Query on the form?  I would
like to get the result set from SQL only. (Ex. How many people that has
higher salary than 5000?)
-       Which one is better compare by write the code in Delphi (I mean
Pascal) or SQL?

Thank you.

 

Re:Query Question


You can use one query component or one component per query. It is up to you.
Most people use one component per query because it requires less code. If
you do not want to put a query component on the form or data module you can
create it at runtime using:

MyQuery := TQuery.Create(Self);

and then assign the TQuery's properties in code. This is more work so I am
not sure why you would want to do it but you can.

Quote
>>- Which one is better compare by write the code in Delphi (I mean Pascal)

or SQL?

Sorry but I do not understand what you are asking.

--
Bill

Re:Query Question


Quote
"Bill Todd (TeamB)" wrote:

> You can use one query component or one component per query. It is up to you.
> Most people use one component per query because it requires less code. If
> you do not want to put a query component on the form or data module you can
> create it at runtime using:

> MyQuery := TQuery.Create(Self);

> and then assign the TQuery's properties in code. This is more work so I am
> not sure why you would want to do it but you can.

        I'm curiously to know about the performance.  Is there any different
between using code or component?

Quote
> >>- Which one is better compare by write the code in Delphi (I mean Pascal)
> or SQL?

> Sorry but I do not understand what you are asking.

        Sorry that I didn't explain it very clear.   I mean the different
method to get the same thing.  Ex. My program has to assign Customer ID
base on Customer Name automatically.  (Ex. Customer Name=Ozlem, then,
CustID will be O125) This can be done by using TTable component to run
into each record to search for Max ID number and put Max+1 to it.  It
can also done by using TQuery (SQL) to find the maximum ID and put Max +
1. Will there be any different? or just the different method to do that?

Thank you for your help.

Re:Query Question


Quote
"Ozlem" <ozlem...@hotmail.com> wrote in message

news:3B3CBCBC.7CEA7B12@hotmail.com...

Quote

> Sorry that I didn't explain it very clear.   I mean the different
> method to get the same thing.  Ex. My program has to assign Customer ID
> base on Customer Name automatically.  (Ex. Customer Name=Ozlem, then,
> CustID will be O125) This can be done by using TTable component to run
> into each record to search for Max ID number and put Max+1 to it.  It
> can also done by using TQuery (SQL) to find the maximum ID and put Max +
> 1. Will there be any different? or just the different method to do that?

Against Paradox there won't be much difference either way because the
records have to be brought from the database to the BDE anyway. When you use
an RDBMS like Interbase (or Oracle, etc), it makes a *big* difference. SQL
statements execute inside the RDBMS and it only sends the information you
need back, where as doing it in code causes all records to be brought back.
Never do anything in code if it can be done in an SQL statement when working
with an RDBMS.

It sounds like you are describing a way to get the next primary key for a
new record (finding MAX of current keys). If your application is going to be
multiuser then you *must not* use this method, you run the risk of duplicate
keys.

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
"Bandwagons are like streetcars, there'll be another along in a few
minutes" - Me!

Re:Query Question


Quote
"Wayne Niddery [TeamB]" wrote:
> Against Paradox there won't be much difference either way because the
> records have to be brought from the database to the BDE anyway. When you use
> an RDBMS like Interbase (or Oracle, etc), it makes a *big* difference. SQL
> statements execute inside the RDBMS and it only sends the information you
> need back, where as doing it in code causes all records to be brought back.
> Never do anything in code if it can be done in an SQL statement when working
> with an RDBMS.

        Thank you for explain to me.  My understanding is that to use SQL or
not will not have much different for local database. However, SQL will
make the different in case of RDBMS.  Am I correct?

Quote
> It sounds like you are describing a way to get the next primary key for a
> new record (finding MAX of current keys). If your application is going to be
> multiuser then you *must not* use this method, you run the risk of duplicate
> keys.

        Yes, that's what I'm gonna do for that code.  However, that's only the
way that I can think about.  (I'm very new to Client/Server
Programming.) Can you suggest me any other way that will be best for
multiuser environment?

Thank you,

Re:Query Question


Quote
"Ozlem" <ozlem...@hotmail.com> wrote in message

news:3B3D0143.A01994DB@hotmail.com...

Quote

> Thank you for explain to me.  My understanding is that to use SQL or
> not will not have much different for local database. However, SQL will
> make the different in case of RDBMS.  Am I correct?

Correct.

Quote
> > It sounds like you are describing a way to get the next primary key for
a
> > new record (finding MAX of current keys). If your application is going
to be
> > multiuser then you *must not* use this method, you run the risk of
duplicate
> > keys.

> Yes, that's what I'm gonna do for that code.  However, that's only the
> way that I can think about.  (I'm very new to Client/Server
> Programming.) Can you suggest me any other way that will be best for
> multiuser environment?

For Paradox you can use AutoIncrement fields, for an RDBMS, most offer some
mechanism for this, in Interbase you would use a Generator, in Oracle a
Sequence. In any database you can devise your own scheme, the most common is
to have a table with fields holding the next key for other tables. You would
lock this record, increment that field, unlock it and then use that number
as te new key in the other table. The locking process protects against
duplicates because your lock will if someone else if currently getting the
next number and you will know to retry.

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
"Bandwagons are like streetcars, there'll be another along in a few
minutes" - Me!

Re:Query Question


Quote
"Wayne Niddery [TeamB]" wrote:
> For Paradox you can use AutoIncrement fields, for an RDBMS, most offer some
> mechanism for this, in Interbase you would use a Generator, in Oracle a
> Sequence. In any database you can devise your own scheme, the most common is
> to have a table with fields holding the next key for other tables. You would
> lock this record, increment that field, unlock it and then use that number
> as te new key in the other table. The locking process protects against
> duplicates because your lock will if someone else if currently getting the
> next number and you will know to retry.

        Thank you for suggestion.  If you don't tell me, I may know that later
only when I encounter with that problem and have to make a lot of
correction.  Really useful for me.

Thank you,

Other Threads