-4

I admit I am not experienced with using SQL directly in Delphi. But I tried now with a small project. It execute a this SQL

SELECT [Type], [Sum] = Count(*) 
FROM   exceptionrow 
WHERE LogDate >= :FromDate
AND LOGDATE <= :ToDate
GROUP  BY [type] 
ORDER  BY [sum] DESC

Where FromDate and ToDate is params as TDateTime. Here is the event to execute.

procedure TLogsStats.CollectTopExceptions(aFromDate, aToDate: TDateTime);
begin
  qryTopExceptions.ParamByName('FromDate').AsDate := aFromDate;
  qryTopExceptions.ParamByName('ToDate').AsDate   := aToDate;

  cdsTopExceptions.Close;
  cdsTopExceptions.Open;
end;

I do get result in a DBGrid but not the same as when running directly in SQL Studio with static date. There is too few rows. I copied the setup from this project. I think it is strange that 5 components is needed to just run a sql query. See image.

SQL Query

Can it be simplified ? Here are my declarations of the components.

SQLConnection: TSQLConnection;
qryTopExceptions: TSQLDataSet;
dspTopExceptions: TDataSetProvider;
cdsTopExceptions: TClientDataSet;
dsTopExceptions: TDataSource;

EDIT: Finally I found the reason it didn't work. I suspect faulty drivers etc but it was a simple stupid error. I was connected to a small test database instead of the bigger database. Now it works fine with 3 components and the parameters. Sorry for taken your time :)

SQLConnection: TSQLConnection;
qryTopExceptions: TSQLDataSet;
dsTopExceptions: TDataSource;

Regards

Roland Bengtsson
  • 5,058
  • 9
  • 58
  • 99
  • 2
    I'm not sure what your question is here. The subject asks about parameters, the body asks about reducing the number of components. You only need one component (`TADOQuery`) to run a query for SQL Server; the `TADOConnection` is a convenience used when you want to open one connection and share it among several different queries, the `TClientDataSet` and `TDataSetProvider` are used to create an in-memory copy of the query results, and the `TDataSource` is used to connect either the `TADOQuery` or the `TClientDataset` to data-aware controls. – Ken White Jan 25 '13 at 18:01
  • Looks good. What do you get, what do you expect to get? – jachguate Jan 25 '13 at 18:01
  • What is the type of the columns of your table `DATE` or `DATETIME`? – RRUZ Jan 25 '13 at 18:03
  • @Ken, the components used doesn't belong to ADO, but to DBExpress, so the TSQLConnection is, in fact, needed. (no matter if you share it or not with other data access objects) – jachguate Jan 25 '13 at 18:03
  • 1
    @jachguate: That's not what the question asked. It asked if it needed all those components to do a query against SQL Server, and I explained that it didn't but could be done with a simple `TADOQuery`. You're right, however, that I misread `SQLConnection` as `ADOConnection`. :-) – Ken White Jan 25 '13 at 18:05
  • The params is of type TDateTime. – Roland Bengtsson Jan 25 '13 at 19:49
  • Suspect number 1 are your date parameters. Change to (LogDate >= :FromDate) and (LogDate <= :ToDate), then add two datetime parameters to the TSQLDataSet and do ParamByName('FromDate').AsDateTime := TDateTime variable. – Jan Doggen Jan 25 '13 at 20:11
  • Also, I don't know the "[Sum] = Count()" syntax, it's probably OK, if not use "Count() as Sum". Sum is a bad name anyway, because it isn't a sum but a count, and it might conflict with built-in keywords, make it e.g. ExceptionCount – Jan Doggen Jan 25 '13 at 20:13
  • @RolandBengtsson, I'm asking about the data type the columns of you sql server table, not the type of the parameters. – RRUZ Jan 25 '13 at 20:13
  • Is there a reason you're not using `BETWEEN :FromDate AND :ToDate` instead? – Ken White Jan 25 '13 at 20:16
  • You're asking "`How can I get the same result from code as I get in SQL Studio`" - show the Visual Studio code. – Cosmin Prund Jan 25 '13 at 20:23
  • @CosminPrund I am using Delphi 2007 – Roland Bengtsson Jan 25 '13 at 20:34
  • @RRUZ SQL table LogDate have DateTime datatype – Roland Bengtsson Jan 25 '13 at 20:35
  • First time you posted here, you asked two very different questions. The next time I saw your question you removed the parameters and dates question, so I decided to write an answer (lunch got me just after hitting the button, so I wrote it when I came back), now I re-read your question and you added the parameters and data question again, so I think your question is not answerable (because of the two different questions). You can revert your last edit and maybe ask a new question with it, or I can delete my answer to make room to someone that wants to answer both questions in a row. – jachguate Jan 26 '13 at 00:02
  • But, IMHO, you have to ask only one question per question here. Because you're asking two: just read your title and the bold part of your edit. – jachguate Jan 26 '13 at 00:03
  • 1
    This question is a moving target. Your original one was totally different, and asked two separate (unrelated) things. I asked you to clarify it. It then changed to the total opposite of what the original seemed to indicate, and an answer was posted. It was then edited again to make it mean the other possibility from the original question without editing the main content or subject. Make up your mind, edit (or delete and repost) your **entire** question so that the subject and body match) and ask **the question you want answered and nothing else**. – Ken White Jan 26 '13 at 03:29
  • I've rolled this question back to it's original form. If you want it to be something else, please either accept an answer to the one asked here or delete this one and post an entire new question. I'm upvoting @jachguate's answer as it actually addresses what you originally asked. Changing the entire meaning of the question after you've received an answer is totally wrong and violates this site's guidelines. – Ken White Jan 26 '13 at 03:35

1 Answers1

8

Short Answer: No, you don't need 5 components

Running a query against a database from Delphi typically involves two components:

  • The connection component, which is responsible to establish the database connection, and
  • a query capable component, which is responsible to run the query and fetch the results.

Some data layers allow you to use directly the query capable component without an explicit connection component, but the connection component is always created behind the scenes. One of these are ADO.

So, if you use ADO, you can just put a TADOQuery in your Form/DataModule and perform your select (or any other) query against a supported database, for example SQL Server.

In order to do that, you set the ConnectionString property of the ADOQuery and the SQL property, then you Open your query and your data is there.

Further information

Data access

You can also choose between different data access available. There are ones supporting connections to multiple databases, like DBGo (ADO) and DBExpress (DBX), and there are others supporting connections to only one database, like Interbase Express (IBX) and Direct Oracle Access (DOA), among others.

As you can see, the different options available offer you a different set of functionality and unique characteristics.

So, why 5 components after all?

Well, you're maybe looking into an application writing by someone else, or following a tutorial.

The components are used because they add more advanced and sophisticated data access functionality.

  • TClientDataSet/TDataSetProvider: the TClientDataSet allows you to have a in-memory copy of the data in a fully navigable data set which also made possible to work disconnected from the Database, even store and retrieve the data to/from disk along with the modifications (delta) made to it, and later apply that changes (even days or months later).

    You can also sort the data, create in-memory indexes, calculate aggregates and more.

    The TDataSetProvider is the glue between your source DataSet (TSQLQuery for example) and the TClientDataSet and provides you the ability to connect both without any coding, to pull data from the source data set, but also to apply the delta of modifications to it at a later time.

    If you want to know more about TClientDataSets and TDataSetProviders, take a look at the ClientDataSets book by Cary Jensen

  • TDataSource: This is part of the Delphi database architecture, that allows you to use data aware components, like TDBEdit, TDBGrid, TDBLabel and others. A DataSource is also used to create master/detail relations. You don't need it if you don't want to use that components or relations.

jachguate
  • 16,976
  • 3
  • 57
  • 98