Case insensitivity in a query run from OOoBasic ?

[cornouws]

Hi *,

From within OOoBasic I run a query to retrieve data from a datasource.

Code: Select all

   "SELECT " & """" & sSearchName & """" & ", " & sNames & " FROM " & P_SNameDataTabel &_
  " WHERE "& """" & sSearchName & """" & " LIKE " & sFind & " ORDER BY " & """" & P_SCurSearchName & """"

(Don't bother the many quotes - I want quotes around the row names).
This works. But I would like to get rid of case sensitivines.

The OOo wiki shows info about functions.http://wiki.services.openoffice.org/wik ... Procedures

It shows the UCASE / UPPER functions.
However, I get an error when using it as follows:

Code: Select all

  ...   " WHERE UCASE("& """" & sSearchName & """" & ") LIKE " & UCASE(sFind) & " ORDER BY " ....

The resulting query looks like
... FROM Sheet3 WHERE UCASE("Voornm") LIKE 'JA%' ORDER
This in connection with a flat, file based datasource.
I heard from some else with a postgresSQL, that it works there...
Replacing UCASE with upper: same problems.

No doubt that there are some differences in designing queries for
- internal HSQLDB
- connected flat databases
- database x with its own driver ...
THus I think that is the rot cause for my problem.

Nevertheless my question: is there a way to have query, to a random datasource from within OOo Basic, that is not case sensitive ...

Thanks,
Cor

[Villeroy]

Try UPPER and LOWER. According to http://hsqldb.org/doc/guide/ch09.html#stored-section they are alternatives to LCASE and UCASE.
I would declare the field type as VARCHAR_IGNORECASE rather than VARCHAR.

Nevertheless my question: is there a way to have query, to a random datasource from within OOo Basic, that is not case sensitive ...

Base is too far away from handling random datasources equally correct (or equally bad). It's SQL parser is awful.

[cornouws]

Thanks for replying, Villeroy,

I would declare the field type as VARCHAR_IGNORECASE rather than VARCHAR.

This is not an option in my case. I've no idea what kind of datasource will be called.
See http://extensions.services.openoffice.o ... %20fastNAW.

Thanks for your warning; I'll try to find a reasonable way out