Hi,

I am using EWB with EDB as the database and am wanting to get all records based on a certain date, where the actual database field is a timestamp.

Is that quite straight forward?

Thanks for any pointers.

Richard

Richard,

> I am using EWB with EDB as the database and am wanting to get all records based on a certain date, where the actual database field is a timestamp.

Have you already defined a dataset (query)? If so: what exactly is the
problem?

Uli

Hi Uli,

<< Have you already defined a dataset (query)? If so: what exactly is the
problem? >>

I hadn't, I was just querying how it was best to do that, but think I have it now.

I'll update here, if I have any probs.

Richard

It can't seem to find the record.

Here is my code, where DS is a TDataSet and the _created field is actually a TimeStamp within EDB:

 with PR do
   begin
     Open;
     InitFind;
     DS.Columns['_created'].AsDate := Now;
     DS.Columns['Sample_Type'].AsInteger := 1;
     if Find(False,True) then
       begin
         with PR do
           begin
             ShowMessage('Found record');
             Id := DS.Columns['Id'].AsInteger;
           end;
       end;
   end;

Basically, the ShowMessage is not happening.

Any ideas?

Thanks

Richard,

> It can't seem to find the record.

In your first post you mentioned (...to get *all* records based on a
certain date...), with your code you will get *one* record anyway.

The only way to get what you want is to create a dataset based on a
query. You have to define it.

Try something like this:
SELECT * FROM MyTable
WHERE CAST("_created" AS DATE)=DATE{"_created"='2018-7-19'}

And what you are doing here:

> DS.Columns['_created'].AsDate := Now;

will never work. From the manual:

Elevate Web Builder 2 Manual » Component Reference » TDataValue
property AsDate: DateTime
TDataValue.AsDate Property

What you are trying is to look for a record with the *current*
timestamp. This record doesn't exist of course.

Uli

Uli Becker wrote:

> Try something like this:
> SELECT * FROM MyTable
> WHERE CAST("_created" AS DATE)=DATE{"_created"='2018-7-19'}

Hi Uli,

Would I put this SQL into the TDataset? e.g.

Dataset.Params.Add('SELECT * FROM MyTable') etc.

If so, do I then do an Open, to execute the SQL and then do a Dataset.First?

Thanks for your help

Richard

Richard,

<< Would I put this SQL into the TDataset? e.g.

Dataset.Params.Add('SELECT * FROM MyTable') etc. >>

No, SQL always goes into the dataset on the *server* side of the equation.  In the case of EWB, you would put it in the dataset that you've defined using the Database Manager in the IDE.

Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

<No, SQL always goes into the dataset on the *server* side of the equation.  In the case of EWB, you would put it in the dataset that you've defined using the Database Manager in the IDE.>

Ah, yes, I see that now. Thanks.

Could you give me an example of syntax?

I effectively want to create a SQL statement like below.

Select * from MyTable where created_date = passed_in_date and anInterger = a_passed_in_integer

My created_date field is a TIMESTAMP but I only want to pass in a date and get all records for that date.

Thanks in advance

Richard

Richard,

> Could you give me an example of syntax?

I already posted a sample:

SELECT * FROM MyTable
WHERE CAST("_created" AS DATE)=DATE{"_created"='2018-7-19'}

As for integers:

SELECT * FROM MyTable
WHERE CAST("_created" AS DATE)=DATE{"_created"='2018-7-19'}
AND anInteger={anInteger=7}

I suggest to start with studying the sample applications and/or reading
the manual on this basic stuff.

Uli

Hi Uli/Tim,

I'm getting an error when trying to load from a dataset.

The dataset, is SQL as follows:

SELECT * FROM PWReadings
WHERE CAST("_created" AS DATE)=DATE{'SearchDate'}

Where the field "_created" is a TIMESTAMP.

and I pass in a parameter, within EWB such as:

Dataset.Params.Add('SearchDate=' + DateToStr(Now));
DBase.LoadRows(Dataset);

However, I am getting the error:

"ElevateDB Error #700 An error was found in the statement at line 2 and column 32 (ElevateDB Error #401 The column DATENULL does not exist in the table PWReadings)"

Any further pointers would be appreciated.

Thanks

Richard