Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 7 of 7 total
Thread issues with 4.48
Thu, Aug 1 2019 9:55 AMPermanent Link

Susan Myran

This query causes DBISAM Engine Error # 11279 An Unknown error (“0.0’ is not a valid timestamp’) occurred with the connection to the database server at ‘x.x.x.x’, please check the server log.  

     SQL.Add('select *');
     SQL.Add('from main_insverif');
     SQL.Add('where chart = :chart');
     SQL.Add('and extract(month from month) = extract(month from :dt)');
     SQL.Add('and extract(year from month) = extract(year from :dt)');
     ParamByName('chart').AsString := tblVisit.FieldByName('chart').AsString;
     ParamByName('dt').AsDate := tblVisit.FieldByName('visit_date').AsDateTime;

But if I change to this, it works fine:
     SQL.Add('select *');
     SQL.Add('from main_insverif');
     SQL.Add('where chart = :chart');
     SQL.Add('and extract(month from month) = :mo');
     SQL.Add('and extract(year from month) = :yr');
     ParamByName('chart').AsString := tblVisit.FieldByName('chart').AsString;
     ParamByName('mo').AsInteger := MonthOf(tblVisit.FieldByName('visit_date').AsDateTime);
     ParamByName('yr').AsInteger := YearOf(tblVisit.FieldByName('visit_date').AsDateTime);

This issue is more worrying. In 4.48b5, this set of 2 queries yesterday produced 42 records and 25 records respectively. After rolling back to 4.45b5, I got a more normal 42 records and 240 records.

Select distinct chart, specimen_date, cl
into memory\tmp
from labhist
Where print_status is null;

Select d.*
from labhist d
inner join memory\tmp d1
on d.chart = d1.chart
and d.specimen_date = d1.specimen_date
and d.cl = d1.cl
where d.print_status <> 'N';
Thu, Aug 1 2019 6:13 PMPermanent Link

Fernando Dias

Team Elevate Team Elevate

Hello,

Is "month" the name of a column in the table?
Because extract(month from month) or extract(year from month) is a bit odd..

--
Fernando Dias
[Team Elevate]
Fri, Aug 2 2019 2:53 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Susan


I'm guessing here since I switched over to ElevateDB ages ago and don't have an up to date DBISAM.

With the first one I doubt dt is set when you open the connection and the actual problem is NULL isn't a valid timestamp. Changing to discrete variable allows the sql to be compiled. It could be the strangeness that Fernando points out and month is a reserved word so you need to wrap in double quotes (or whatever the convention is for DBISAM)

The second also looks like its a NULL problem What do you get if you change

where d.print_status <> 'N';

to

where d.print_status <> 'N' OR d.print_status IS NULL;


Roy Lambert
Fri, Aug 2 2019 1:21 PMPermanent Link

Susan Myran

It is.

Fernando Dias wrote:

Hello,

Is "month" the name of a column in the table?
Because extract(month from month) or extract(year from month) is a bit odd..

--
Fernando Dias
[Team Elevate]
Fri, Aug 2 2019 1:23 PMPermanent Link

Susan Myran

I'll have to install 4.48 again to see. But having to look for nulls specifically every time I use a <> would be a HUGE change! Did that really happen?


Roy Lambert wrote:

Susan


I'm guessing here since I switched over to ElevateDB ages ago and don't have an up to date DBISAM.

With the first one I doubt dt is set when you open the connection and the actual problem is NULL isn't a valid timestamp. Changing to discrete variable allows the sql to be compiled. It could be the strangeness that Fernando points out and month is a reserved word so you need to wrap in double quotes (or whatever the convention is for DBISAM)

The second also looks like its a NULL problem What do you get if you change

where d.print_status <> 'N';

to

where d.print_status <> 'N' OR d.print_status IS NULL;


Roy Lambert
Sat, Aug 3 2019 9:44 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Susan


>I'll have to install 4.48 again to see. But having to look for nulls specifically every time I use a <> would be a HUGE change! Did that really happen?

As I said I don't use DBISAM any more but you should have the change logs and should be able to see. I could well be wrong. If its not, and no one else on the forum has an idea, that I'd suggest opening a support ticket with Tim.

Roy
Wed, Jun 3 2020 1:37 PMPermanent Link

Susan Myran

I finally got back to dealing with this issue today. I was getting ready to zip up a reproducible example, and then update 4.49b1 came and fixed my problem! The universe works in funny ways sometimes!

Thank you. Smile
Image