FireDac Query Issue

[bazzer747 25]

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.

 

 

[Attila Kovacs 629]

if this works without a ".close" between the two ".open()" then I'm speechless.

[Attila Kovacs 629]

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 April 8, 2020 by Attila Kovacs

[bazzer747 25]

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)?

[Attila Kovacs 629]

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 April 8, 2020 by Attila Kovacs

[bazzer747 25]

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 🙂

[Serge_G 87]

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.