Board index » cppbuilder » Need some help with connecting to databases via ODBC

Need some help with connecting to databases via ODBC


2004-09-30 09:06:34 AM
cppbuilder63
I need to make several thousand connections to a table in a database via
ODBC. The query statement is very simple i.e. Select customerid from
customertable where customerid = 'ABC1234'. All I am trying to find here is
whether the id already exists in the table or not.
My concern with the C++ Builder code is to ensure that I don't leave any
open database connections since so many queries can cause the database
server to slow down big time.
Can someone please advise on what the best way to do it. A sample of code
would help a lot. Thanks!
P.S.
I am using C++ Builder 3.
 
 

Re:Need some help with connecting to databases via ODBC

Hi,
With BCB3, I use a FREE ADO component :
(www.alohaoi.com/Software/Products/aoado/download.htm)
It works very good but the "aoADOStoredProcedure" component doesn't return
values ! (a bug perhaps).
You can leave the ODBC connection. and use this ADO connection.
"Home Mail" < XXXX@XXXXX.COM >a Иcrit dans le message de
Quote
I need to make several thousand connections to a table in a database via
ODBC. The query statement is very simple i.e. Select customerid from
customertable where customerid = 'ABC1234'. All I am trying to find here
is
whether the id already exists in the table or not.

My concern with the C++ Builder code is to ensure that I don't leave any
open database connections since so many queries can cause the database
server to slow down big time.

Can someone please advise on what the best way to do it. A sample of code
would help a lot. Thanks!

P.S.
I am using C++ Builder 3.


 

Re:Need some help with connecting to databases via ODBC

Hello,
Do you mean you have more than one instance of your application or
code running on the same workstation or it's just one application pooling
many times the same query to the database?
Jayme.
"Home Mail" < XXXX@XXXXX.COM >escreveu na mensagem
Quote
I need to make several thousand connections to a table in a database via
ODBC. The query statement is very simple i.e. Select customerid from
customertable where customerid = 'ABC1234'. All I am trying to find here
is
whether the id already exists in the table or not.

My concern with the C++ Builder code is to ensure that I don't leave any
open database connections since so many queries can cause the database
server to slow down big time.

Can someone please advise on what the best way to do it. A sample of code
would help a lot. Thanks!

P.S.
I am using C++ Builder 3.


 

{smallsort}

Re:Need some help with connecting to databases via ODBC

"Jayme Jeffman Filho" < XXXX@XXXXX.COM >wrote in message
Quote
Hello,

Do you mean you have more than one instance of your application or
code running on the same workstation or it's just one application pooling
many times the same query to the database?

Jayme.
Hi, thanks for your response. I'll just have one applicationg pooling many
times the same query to the database.
Thanks.
 

Re:Need some help with connecting to databases via ODBC

Hello,
If you're requesting data from an Oracle database, use a parameter
to input the value for "customerid" instead of a literal query ('ABC1234').
This way your application runs faster.
Have you thought about running a query getting all the existents "id's" and
perform a Locate method to check if it exists or not ?
HTH
Jayme.
"Home Mail" < XXXX@XXXXX.COM >escreveu na mensagem
Quote
"Jayme Jeffman Filho" < XXXX@XXXXX.COM >wrote in message
news:415bfbea$ XXXX@XXXXX.COM ...
>Hello,
>
>Do you mean you have more than one instance of your application or
>code running on the same workstation or it's just one application
pooling
>many times the same query to the database?
>
>Jayme.

Hi, thanks for your response. I'll just have one applicationg pooling many
times the same query to the database.

Thanks.


 

Re:Need some help with connecting to databases via ODBC

"Jayme Jeffman Filho" < XXXX@XXXXX.COM >wrote in message
Quote
Hello,

If you're requesting data from an Oracle database, use a parameter
to input the value for "customerid" instead of a literal query
('ABC1234').
This way your application runs faster.

Have you thought about running a query getting all the existents "id's"
and
perform a Locate method to check if it exists or not ?
That is a good idea. Do you think it would be faster this way? Pardon my
ignorance but how many rows can the data grid components handle? I will need
to work with around 5 million records.
 

Re:Need some help with connecting to databases via ODBC

"Jayme Jeffman Filho" < XXXX@XXXXX.COM >wrote in message
Quote
Have you thought about running a query getting all the existents "id's"
and
perform a Locate method to check if it exists or not ?
Just thought of something else. Since the system is live and dynamic there
could be a chance that the one time query can become out of date if someone
updates the system with new customer ids while my program is running.
 

Re:Need some help with connecting to databases via ODBC

TDBGrid will show as much records as TDataSet has (but I never used BCB 3).
However show 5000000 records to user is not a good idea - it is always
recommended to show up to 1000 records in UI.
//------------------------------------------
Regards,
Vassiliev V. V.
www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)
"Home Mail" < XXXX@XXXXX.COM >сообщи?сообщила ?новостях следующе?
Quote
"Jayme Jeffman Filho" < XXXX@XXXXX.COM >wrote in message
news: XXXX@XXXXX.COM ...
>Hello,
>
>If you're requesting data from an Oracle database, use a parameter
>to input the value for "customerid" instead of a literal query
>('ABC1234').
>This way your application runs faster.
>
>Have you thought about running a query getting all the existents "id's"
>and
>perform a Locate method to check if it exists or not ?

That is a good idea. Do you think it would be faster this way? Pardon my
ignorance but how many rows can the data grid components handle? I will
need
to work with around 5 million records.


 

Re:Need some help with connecting to databases via ODBC

The database do not refreshes data when someone else has updated
any record, so you should periodically refresh your query to have the
current data version on the client workstation.
Working with a big amount of data is not a good technique. As you
are looking for a special client or record you can type some of its
attributes and look for records that match the criteria and only show
those records. Fetching 5 million records could need too much wait
time to show the records and drive the user crazy.
HTH
Jayme.
"Home Mail" < XXXX@XXXXX.COM >escreveu na mensagem
Quote
"Jayme Jeffman Filho" < XXXX@XXXXX.COM >wrote in message
news: XXXX@XXXXX.COM ...
>Have you thought about running a query getting all the existents "id's"
>and
>perform a Locate method to check if it exists or not ?

Just thought of something else. Since the system is live and dynamic there
could be a chance that the one time query can become out of date if
someone
updates the system with new customer ids while my program is running.


 

Re:Need some help with connecting to databases via ODBC

"Jayme Jeffman Filho" < XXXX@XXXXX.COM >wrote in message
Quote
The database do not refreshes data when someone else has updated
any record, so you should periodically refresh your query to have the
current data version on the client workstation.

Working with a big amount of data is not a good technique. As you
are looking for a special client or record you can type some of its
attributes and look for records that match the criteria and only show
those records. Fetching 5 million records could need too much wait
time to show the records and drive the user crazy.
I have no option in this matter. I am only checking to see if a unique id
exists in the system. There is nothing more to it. There search cannot be
refined any further.
As for the code, would you be willing to perhaps phrase it out for me.
Thanks.
 

Re:Need some help with connecting to databases via ODBC

Hello,
There are too many options of code and I don't know even why you need
to check if a certain customer is recorded to your database. For example
you can force the creation of a new record with a customer that already
exists in your database and catch the errors when posting data, with
no need of checking if the customer exists, handle the error and its done.
If the field you are checking the existence against is the primary key of
the table, its highly recommended that this field should not be updated, so
you should not expose it to the user, and control its value by code.
HTH
Jayme.
"Home Mail" < XXXX@XXXXX.COM >escreveu na mensagem
Quote
"Jayme Jeffman Filho" < XXXX@XXXXX.COM >wrote in message
news:415d5655$ XXXX@XXXXX.COM ...
>The database do not refreshes data when someone else has updated
>any record, so you should periodically refresh your query to have the
>current data version on the client workstation.
>
>Working with a big amount of data is not a good technique. As you
>are looking for a special client or record you can type some of its
>attributes and look for records that match the criteria and only show
>those records. Fetching 5 million records could need too much wait
>time to show the records and drive the user crazy.

I have no option in this matter. I am only checking to see if a unique id
exists in the system. There is nothing more to it. There search cannot be
refined any further.

As for the code, would you be willing to perhaps phrase it out for me.
Thanks.


 

Re:Need some help with connecting to databases via ODBC

"Jayme Jeffman Filho" < XXXX@XXXXX.COM >wrote in message
Quote
Hello,

There are too many options of code and I don't know even why you need
to check if a certain customer is recorded to your database. For example
you can force the creation of a new record with a customer that already
exists in your database and catch the errors when posting data, with
no need of checking if the customer exists, handle the error and its done.

If the field you are checking the existence against is the primary key of
the table, its highly recommended that this field should not be updated,
so
you should not expose it to the user, and control its value by code.
I am not doing any inserts!!! I just need to check the existance of a unique
ID!
 

Re:Need some help with connecting to databases via ODBC

There are only two ways of checking if a certain record exists, using the
Locate method or performing a query to the database. You should try
and decide which one is better for your application. It depends on a
great variety of conditions like network traffic, database kind, workstation
RAM memory and others. I can't tell you which one will be faster !
For the Locate method use the TBDEDataSet::Locate help entry which
has a more complete description.
HTH
Jayme.
"Home Mail" < XXXX@XXXXX.COM >escreveu na mensagem
Quote
"Jayme Jeffman Filho" < XXXX@XXXXX.COM >wrote in message
news:41614843$ XXXX@XXXXX.COM ...
>Hello,
>
>There are too many options of code and I don't know even why you need
>to check if a certain customer is recorded to your database. For example
>you can force the creation of a new record with a customer that already
>exists in your database and catch the errors when posting data, with
>no need of checking if the customer exists, handle the error and its
done.
>
>If the field you are checking the existence against is the primary key
of
>the table, its highly recommended that this field should not be updated,
>so
>you should not expose it to the user, and control its value by code.

I am not doing any inserts!!! I just need to check the existance of a
unique
ID!


 

Re:Need some help with connecting to databases via ODBC

"Jayme Jeffman Filho" < XXXX@XXXXX.COM >wrote in message
Quote
There are only two ways of checking if a certain record exists, using the
Locate method or performing a query to the database. You should try
and decide which one is better for your application. It depends on a
great variety of conditions like network traffic, database kind,
workstation
RAM memory and others. I can't tell you which one will be faster !

For the Locate method use the TBDEDataSet::Locate help entry which
has a more complete description.

HTH

Jayme.
Alright Jayme. Thanks for all your help. I will probably end up using
Locate. In this way I will ensure a single connection to the database,
retrieve all records and perform locates locally rather than multiple
connections to the database.