Base - MySQL: Trouble with Date_Add Function [closed]

asked 2012-08-21 05:57:19 +0100

Zolerii gravatar image

updated 2012-08-21 08:03:56 +0100

I am using LibreOffice Base to make attractive reports based on data I have stored in my MySQL Server. I am having trouble writing a query. More specifically, I have a query that I cannot make work in LibreOffice.

The first image is proof that the query runs as expected when input directly into MySQL.

This query works.

As you can see above, there is nothing wrong with the output (other than prehypertension). The same query (with the addition of grave accents (`) where LibreOffice wants them) fails with an SQL Status of HY000 and an Error Code of 1000.

This fails.

Here is the syntax for the DATE_ADD and DATE_SUB functions in MySQL. It lists the expr part as a string, but enclosing the 2 in single quotes ('), double quotes ("), or grave accents (`) still produces an error. The only way to get the query to save is to enclose the entire INTERVAL 2 WEEK section in single quotes as seen below.

Saves But Fails

While this will save and allow you to go on your merry little way, it fails when the query is run. I believe this is because this is syntactically correct according to LibreOffice, but it is incorrect according to MySQL.

Before I move this to Bugzilla, am I doing something incorrect? I believe this is a true bug, but before I waste a developer's time, I wanted to ask the community.

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-10-04 18:19:29.064349

Comments

I have never had grave accents work in SQL. I believe that MySQL will accept single quotes (apostrophie) or double quotes, but LibreOffice won't. I got your query to work using double quotes around the column names. You need to run the SQL command directly in base.

select avg("sys"),avg("dia") from "reading" where "date" >=date_sub(now(),interval 2 week);
w_whalley gravatar imagew_whalley ( 2012-08-21 16:16:15 +0100 )edit

w_whalley: thank you for your answer. Are you sure you've connected your LibreOffice Base to an existing MySQL database server when you ran your query? Normally, you are correct. Base will use double quotes, but in this instance, it changes them to grave accents.

Zolerii gravatar imageZolerii ( 2012-08-30 14:26:19 +0100 )edit

Hi @Zolerii,

Still having issues with MySQL? If installing the latest release doesn't seem to fix this problem, please file a bug and provide a list of steps that can reproduce your problem. The QA team will be happy to help you track down this issue!

Please post a link to any bugs you file in a comment below using the format "fdo#123456".

Thanks!

qubit gravatar imagequbit ( 2013-02-22 09:05:47 +0100 )edit

I am having the exact same problem with Mysql and DATE_ADD(). What is the fix here?

RealAg gravatar imageRealAg ( 2013-08-17 04:35:46 +0100 )edit