Jump to content
bazzer747

FireDac Query Issue

Recommended Posts

Hi,

I'm using a FireDac query to select different sets of data in an MSSQL database. I open the first query like this:

dm.fdq.Open('SELECT * FROM tblUsers WHERE MSL = :pMG ORDER BY Sname, Fname',[ 1 ] );

which works fine.

I then need to select a different set of records so I issue this (assuming this replaces the above statement):

dm.fdq.Open('SELECT DISTINCT Fullname, Fname FROM vPGCEnrolments WHERE MatchGroup = :pMG AND MatchYear = :pY AND YES = 1',[ gvMatchGroup, gvYear ]);

This works but I start to get Access violation error messages. The select works fine though, as the correct data displays. 

As I think this has something to do with the select statement I issue the following statement at the end of this procedure:

dm.fdq.SQL.Clear;

.. but when I look at the SQL string with ShowMessage( dm.SQL.text ); it still shows the Select Distinct line shown above. The Clear statement doesn't seem to clear anything.

 

I've saved the SQL (assigning dm.fdq.SQL.text to a variable: cSQL, at the start of the procedure), and at the end of the procedure restore with dm.fdq.SQL.Add( cSQL ); but this does nothing either.

 

I've a feeling I'm not understanding how the above should work correctly. Any advice would be appreciated.

 

 

Share this post


Link to post
Posted (edited)

anyway, you could consider using temporary queries instead of one (or more) particular queries as a swiss knife on a datamodule:
 

procedure DoSomething;
var
 Q: TFDQuery;
begin
 Q := TFDQuery.Create(nil);
 try
   Q.Connection := dm.FDConnection1;
   Q.Sql('....');
   <DoSomething>
   Q.Close;
 finally
   Q.Free;
 end;
end;

or similar..

or "Q" as a class Field like "FQxy" if you have to work with db controls...

Edited by Attila Kovacs
  • Like 1

Share this post


Link to post

Ah, thankyou for the post. I've learnt using FireDac mainly from youtube and the like, so no formal training. Trial and error a lot of the time, eventually that gets me there. The temp connection route you mentioned seems like a definite way.

 

So a 'close' on the query does what exactly (in terms of any SQL code in the query)?

Share this post


Link to post
Posted (edited)

It "closes" the dataset, then you can manipulate its properties including sql text etc.. then you set it active again with open()

or with sql() in firedac, I'm not familiar with the "sql()" method, but it's for sure a shortcut for setting the Sql text, parameters and opening the query.

 

 

Edited by Attila Kovacs

Share this post


Link to post

Many thanks for your help. I've now used the temporary query setup and it works a treat, no errors or glitches. Live and learn 🙂

Share this post


Link to post
On 4/8/2020 at 8:34 PM, bazzer747 said:

So a 'close' on the query does what exactly (in terms of any SQL code in the query)?

A close no, but an unprepare should be to check. If your first SQL works, other request just changing parameter works. i.e dm.fdq.Open('',[ 2 ] );

It's when you change the SQL and parameters the problem.

 

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×