Base knows SQL functions?

users
#1

It seems that Base has a list of keywords and functions for SQL, is
that right?

On the one hand it might be a good thing, on the other, unless the
list is kept up to date then it's a real pain.

For example, "ADD_DATE" (or DATEADD) in MariaDb, doesn't seem to be on
its list. That means that, even though it's perfectly acceptable SQL
for the backend, and even though Base does no querying itself, it
won't pass on a query with this function in it. There are (many)
other examples.

Any thoughts?

Mark Stanton
One small step for mankind...

#2

Hi :slight_smile:
Time for a bug-report? I think post now and then if it turns out to be
"not a bug" you can always close it. For most people i'd hope for a bit
more discussion first jic but one of the people i'd want to hear from would
be you. There are quite a few people here on this mailing list who know
quite a lot about Base but you are definitely one of them.
Regards from
Tom :slight_smile:

#3

If you are using Base, you can use function in Base, no mather what database you attach to it.

If you decide to use function(s) that are database specific, you tie the application you are creating to that database. It will no longer be functional if yout switch from i.e. MariaDB to MSSQL.

#4

Hi Tom,

Time for a bug-report?

I thought I'd just test the water here first. I've not had time to work with
Base for a (very) long time now, but this might bring me back (and other
situations are easing) if I'm right.

Mark Stanton
One small step for mankind...

#5

If you decide to use function(s) that are database specific, you tie the
application you are creating to that database. It will no longer be
functional if yout switch from i.e. MariaDB to MSSQL.

That's a good point, but not the one I was making. If I choose to change
database then I would of course have to change any product specific
funtionality but

If you are using Base, you can use function in Base, no mather what
database you attach to it.

Ok, having tested a bit further, it in fact seems to be my problem. A
difference between the documentation I was looking at and the version of
MaraDb on my machine.

So, to explain it fully, the (very prety and worthwhile) design mode *does*
appear to have "a list" of what it knows about... Perhaps it has to... Even
with that switched off Base still checks and only accepts code it knows
about, but pressing the "SQL" button puts it into passthrough mode and it
*doesn't* check (hurrah!). I think I should've know all that before :frowning:

If course if, like me, you use stuff your database server doesn't recognise
it still won't work :frowning:

The way to check is to put the query into the server "directly" through its
own console, or something like phpMyAdmin if you're also running a
webserver.

Regards
Mark Stanton
One small step for mankind...

#6

Hi Mark,

So, to explain it fully, the (very prety and worthwhile) design mode *does*
appear to have "a list" of what it knows about... Perhaps it has to... Even
with that switched off Base still checks and only accepts code it knows
about, but pressing the "SQL" button puts it into passthrough mode and it
*doesn't* check (hurrah!). I think I should've know all that before :frowning:

I see you found out for yourself :wink:
My general advice would be to test all backend specific functions in the
SQL pass-thru mode first.

As to whether there is a list of what works, and what doesn't, even in
pass-thru mode, to my knowledge there isn't.

Alex

#7

It seems that Base has a list of keywords and functions for SQL, is
that right?

On the one hand it might be a good thing, on the other, unless the
list is kept up to date then it's a real pain.

For example, "ADD_DATE" (or DATEADD) in MariaDb, doesn't seem to be on
its list. That means that, even though it's perfectly acceptable SQL
for the backend, and even though Base does no querying itself, it
won't pass on a query with this function in it. There are (many)
other examples.

Any thoughts?

Mark Stanton
One small step for mankind...

A list of functions that should work with all types of database
connections because they are built into Base:

http://www.openoffice.org/dba/specifications/file_based_functions.html

Functions of the embedded HSQLDB 1.8:

http://www.hsqldb.org/doc/1.8/guide/ch09.html#stored-section

Anything beyond depends on your connected database engine.