Recordcount with SQL Query.

Hi..  
I am writing an application to connect to an Oracle database. I have the application connecting and executing a very simple SELECT statement giving me about 30 records. How do i get the application to display the number of records that are actually returned from the SQL query although I am able to get the total number of records in the database.

Please Help!!
LVL 2
Dane PickeringAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ocean9Commented:
Hi Dane,

try:

MySQL.Last;
ShowMessage(IntToStr(MySQL.RecNo));
0
simonetCommented:
Watching...
0
Dane PickeringAuthor Commented:
Ocean9,

I tried that (you are reffering to MySQL as a TQUERY ?? - If so follow on) although it returns a value of -1. I have checed the help and it tells me that it will return that value. It also returns that value for RecordCount as well. I think somehow I need to get a TTable object to contain the information in the SQL Query.

Please Help!!
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

simonetCommented:
The RecordCount property of the TQuery object doesn't work on Oracle tables (I know that from my previous experience w/ Oracle 7 and 8).

You can simply run another similar SQL SELECT statement, in order to retrieve the number of records.

Example:

Suppose your SELECT statement is something like this:

SELECT TABLE_NAME, PARENT_TABLE, IS_PARENT FROM USER_TABLES
WHERE TABLE_NAME LIKE "CUST%"

In order to know the actual number of records, run a similar statement, using the same table(s) and keeping the very same "WHERE" clause. The COUNT() is needed to be run on only one field:

SELECT count(TABLE_NAME) FROM USER_TABLES
WHERE TABLE_NAME LIKE "CUST%"

I strongly advise you *not* to use TTables against Oracle tables for this, since it will cause a much higer overhead than running the SQL above.

Yours,

Alex
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dane PickeringAuthor Commented:
Simonet,

this is close to what I am after. I won't reject your answer just yet.. I give you another go!

I will be needing to get the count a number of times for a number of different SQL queries. Is there an easier way of getting this count??  If not how do i get the count from the query into a string ??

I would prefer an easier way to get the count ,somehting which will just look at the results and give me the information..  

I await your response.
0
simonetCommented:
Dane,

You can simply call the TQuery's Refresh method and see if it works (I've had problems with the Refresh method on the TQuery component when using Personal Oracle 7.3.2.1.1). It it doesn't work, you can simply close it and reopen it. There's no need to prepare and unprepare the query when doing this, so you can work with cached data.

In order to have the resulting number on a string, create the Query like this:

SELECT count(TABLE_NAME) AS NumOfRecs FROM USER_TABLES
WHERE TABLE_NAME LIKE "CUST%"

Supposing you placed the SQL clause above in a TQuery component named QryCount, you can retrieve the record count with the follwing code:

var
  RecCount : integer;
begin
   RecCount := QryCount.fieldbyname('NumOfRecs').asinteger;

My only advise to you is: If you change the WHERE clause on the real SELECT statement, remember to make the WHERE clause for the "record count statement" the same as the former. If not, you'll be working with a number of records that don't actually match your last query. Let me give you an example:

Suppose your query goes like this:

SELECT A.Name, A.Age, B.Description as Position
FROM Employees A, Positions B
Where (A.PosID = B.PosID) And (A.Age between 30 and 40)

In the scenario above, the record count SQL must look like:

Select Count(A.Nome) as NumOfRecs
FROM Employees A, Positions B
Where (A.PosID = B.PosID) And (A.Age between 30 and 40)

If you change the age limits to 40 and 50 in the first SQL, that must also be changed in the second one.

Yours,

Alex
Athena's Place: http://www.bhnet.com.br/~simonet
0
GabeinOZCommented:
Dane

>If not how do i get the count from the query into a string ??

beat it in with a hammer mate ;P

Gabe
0
Dane PickeringAuthor Commented:
Thanks!!

I was hoping it would be a little easier to extract although looks like thats not possible..

Thanks for your help!!

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.