[Solved+Issue] Problem editing queries containing functions

[brassing]

I have just started with Base. I am using Version 3.0 in Vista SP1. When I create a query in design view and insert a function in the field entry of one of the columns (I have tried SIN, CONCAT, and CURDATE so far.) It works fine either when I run the query from within design view or when running the query subsequently. However if I then open the query for editing and try to run it (before doing anything else) I get:

The data could not be loaded. Column not found CURDATE()...........................

When I look in SQL view I find the problem - the editor has put quotes around CURDATE() giving "CURDATE()" which then I think gets interpreted as a column in a table rather than as a function. If I edit out the quotes in SQL view it runs OK but if I switch back to design view the editor puts the quotes back in and I get the error again. What seems to happen is when you type in the function intially the SQL gets created correctly but when existing SQL is read back in the editor inserts the quotes.

What am I doing wrong - Do I need to do something to make sure that the editor recognises CURDATE() as a function? (I am just typing CURDATE() directly into the field box)

[Note as a workround when using the SIN function I have tried typing in (SIN(20)*1) and this doesn't get the quotes put round it and works OK.]

I have also tried Base V2.4 under XP SP3 and get exactly the same problem.

[DrewJensen]

First - sounds like a bug in Base
Now for a possible work around ( sorry can't try it here at the moment, but will later )

Try using the CURRENT_DATE instead of CURDATE()

Note - no brackets after the string CURRENT_DATE.

[brassing]

Drew, Thanks for your reply. You're right, CURRENT_DATE works OK. I am interested in why you thought it would - do you have some idea as to what is going on?

[brassing]

I noticed someone had tagged this as solved. It isn't - DrewJensen suggested a workround for one specific function. However the general problem remains. if it is actually a bug in Base should I report it and if so how? I find it a bit surprising that the problem isn't already known.

[Hagar Delest]

I noticed someone had tagged this as solved.

Not me! I precise it because I think I'm known to tag the threads Solved when it seems so (but sometimes, I can miss a part of the thread telling it isn't).

However the general problem remains. if it is actually a bug in Base should I report it and if so how?

Yes, you should report it: [Tutorial] Reporting bugs or suggestions, perhaps a search will give you some existing report. In that case, post the url here.

[brassing]

I've done a bit more testing and it seems that the conditions under which query designer incorrectly puts quotes round functions are:

1. The function must take arguments (even if they are null arguments like CURDATE())
2. None of the arguments are column names
3. The function is not part of an expression i.e. it appears on its own

All 3 conditions must be satisfied for the problem to occur.

Thus the following produce the error:

CURDATE(), SIN(20), CONCAT('a','b')

The following are OK:

CURRENT_DATE, SIN("angle1"), SIN(20)-1, CONCAT("string1","string2"), DATEDIFF('yy',"DOB",CURDATE())

where angle1, string1, string2, DOB are column names.

Since normally you would use functions with column names as arguments, or as part of an expression the problem does not often arise.

I have now tagged this issue solved and I will report it as a bug.

[DrewJensen]

sorry for being so late in getting back to you.

hmm- just tried the bug scenarios using 3.0 and a snapshot for the upcoming 3.1 release...both cases under Linux not Vista and can't get the same problem you are getting. In other words the queries using them run as expected.

So - file the bug report and mark it as a Vista platform problem... I will go ahead and check this on XP but I'll be surprised if it fails there also..still one never knows do they.

Oh - about the idea to use the alias CURRENT_DATE
1) It is supported by most SQL dialects now
2) There had been a bug in one of the early 2.0.x releases where CURDATE() didn't work but the alias CURRENT_DATE did - it was the empty parenthesis IIRC that was the problem. Since the alias explicitly doesn't use any, seemed like a shot for a work around.

[brassing]

It's not specifically a Vista problem - I get the same behaviour using Base 2.4 under XP Pro.

In trying to reproduce the problem are you sure you are following the sequence I set out in my original post. You don't get the problem if you just create the query in design view, save it, exit query designer and run the query. That works OK. The problem arises when you subsequently edit the query (right click > edit). It is at that point that the quotes get added. You can also produce the problem in query designer by switching to SQL view and then back to design view. The problem seems to arise when existing SQL is read back into design view.

The quickest way to produce the problem is:

Click 'create query in design view'
Add any table.
Type CURDATE() in the field cell of the first column.
run the query from within query designer - it works OK.
turn design view off - the sql is SELECT CURDATE( ) FROM "Table1" AS "Table1" which is fine.
turn design view on again
run the query - you get the error
turn design view off and check the SQL again - you get SELECT "CURDATE( )" FROM "Table1" AS "Table1"
i.e. the quotes have been added.
[the SQL samples above have been cut and pasted from my test]

[DrewJensen]

arrrgh - yes that did it...

You want to open that bug report, or shall I.