[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Strange error when looping thru recordset

Posted on 2004-09-15
11
Medium Priority
?
645 Views
Last Modified: 2010-04-05
Hi all,

I have a strange problem.
I am using a TQuery to retrieve a recordset (1772 records).
I need to loop thru this recordset and retrieve values from other tables using the recordset-values.
For this purpose I use a second TQuery.

So, basicly:

qry1 = first recordset
qry2 = sub query

while not qry1.eof
begin
  //do some work, store some field in a string
  //then based on the value from field 'X' retrieve other values
  qry2.Close();
  qry2.SQL.Clear();
  qry2.SQL.Append('the sql statement');
  qry2.ExecSQL;
  qry2.Open()
  if qry2.RecordCount=1 then
  begin
     //add some values to my big string
  end;
  qry2.Close();
  //write the big string to a textfile
  qry1.next;
end;

This didn't work at first because the BDE reported "Insufficient memory". But using this q (http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_20304916.html?query=insufficient+memory&topics=85), that problem is solved.
Now another error occurs, but without any good debug information. The error is "access violation at...".

The strange thing is that when I read the first 114 records and run the loop, it works fine.
But any number above, it fails.

Can anybody help me?
Am I doing this wrong?

D'Mzz!
RoverM

0
Comment
Question by:roverm
  • 6
  • 3
  • 2
11 Comments
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12072221
"I need to loop thru this recordset and retrieve values from other tables using the recordset-values"

Why don't use SQL join between these 2 tables. For e.g. :

SELECT PRODUCT.NAME, SALES.PRICE
FROM PRODUCT, SALES
WHERE PRODUCT.PRODUCT_ID = SALES.PRODUCT_ID

This way you won't need Query2...
0
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12072235
Now I noticed something else:

  qry2.ExecSQL;
  qry2.Open()

You use both of them which is not needed ... If you have a SQL, which returns a dataset - use OPEN, for something like INSERT, UPDATE or DELETE use ExecSQL...
0
 
LVL 10

Accepted Solution

by:
Jacco earned 2000 total points
ID: 12072238
Code looks fine.

Just one thing I don't understand is why you do an ExecSQL followed by an Open. I personally use ExecSQL only with Update/Insert queries but I don't Open these. I only use Open when SQL contains a select query.

What you seem to do is check if how many records the ExecSQL affected.

You can use the RowsAffected property for that.

Regards Jacco
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 12

Author Comment

by:roverm
ID: 12072282
@ivanov_g:
I can't use a join because the selection comes from different tables. It's not just 2 tables...

@both:
I know that ExecSQL should be nessacary but for some strange reason, when I remove that line the code doesn't work at all! "No current SQL Statement"

If I remove the code for the subquery, that only the main loop is running, then everything works fine.

Thanks so far!

D'Mzz!
RoverM
0
 
LVL 12

Author Comment

by:roverm
ID: 12072295
edit: that should be:


I know that ExecSQL should NOT be nessacary ....
0
 
LVL 10

Expert Comment

by:Jacco
ID: 12072350
Maybe this error: 'No SQL statement available'

It is in the SEmptySQLStatement constant. The error is raise when

Length(SQL.Text) < 1

or

SQL.Count = 0

Maybe you can check this. It seems that qry2.SQL.Append('the sql statement') does not always have text?

Regards Jacco
0
 
LVL 12

Author Comment

by:roverm
ID: 12072374
Nope, sql statement is not empty... too bad :)
0
 
LVL 10

Expert Comment

by:Jacco
ID: 12072385
Does the error state exactly: "No current SQL Statement"?

What database do you use?

Is there a BLOB involved?

Regards Jacco
0
 
LVL 12

Author Comment

by:roverm
ID: 12072465
It's a paradox db, no blob involved.
I will give the exact error shortly (don't have the code here now).
0
 
LVL 12

Author Comment

by:roverm
ID: 12072517
Yeah! It was the ExexSQL after all!
You guys put me on the right track!

Thanks!
Problem solved!

D'Mzz!
RoverM
0
 
LVL 12

Author Comment

by:roverm
ID: 12072524
@Ivanov_G:
Please get your points here:
http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_21133177.html

I think it's only fair (and well deserved) to give you both 500 points and an A-grade.

Thank you both!

D'Mzz!
RoverM
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Screencast - Getting to Know the Pipeline
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month19 days, 5 hours left to enroll

834 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