Solved

Strange error when looping thru recordset

Posted on 2004-09-15
11
633 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 500 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

831 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