Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Recordcount with SQL Query.

Posted on 1999-07-19
8
Medium Priority
?
337 Views
Last Modified: 2010-04-06
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!!
0
Comment
Question by:Dane Pickering
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 

Expert Comment

by:ocean9
ID: 1389725
Hi Dane,

try:

MySQL.Last;
ShowMessage(IntToStr(MySQL.RecNo));
0
 
LVL 15

Expert Comment

by:simonet
ID: 1389726
Watching...
0
 
LVL 2

Author Comment

by:Dane Pickering
ID: 1389727
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Accepted Solution

by:
simonet earned 200 total points
ID: 1389728
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
 
LVL 2

Author Comment

by:Dane Pickering
ID: 1389729
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
 
LVL 15

Expert Comment

by:simonet
ID: 1389730
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
 

Expert Comment

by:GabeinOZ
ID: 1389731
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
 
LVL 2

Author Comment

by:Dane Pickering
ID: 1389732
Thanks!!

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

Thanks for your help!!

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question