[Solved] Base SQL queries which use StarBasic functions

[sam-sams-15]

Hi all,

I've been trying to write some queries inside a macro using functions as Cint (or Convert also) in the body of the query but It throw an error pointing to the functions are'nt not allow somehow

Code: Select all

"SELECT ""sTime"", Cint(""sTime"") FROM ""Test"" WHERE CInt (""sTime"" )<120 "

(sTime is a string expressed time)
The error:

SQL Exception
Acces is denied:CINT in statement [""sTime", Cint(

Another:

Code: Select all

"SELECT CASE WHEN InStr(""sTime"",'h') >0 THEN  ""sTime"" ELSE '0'" END  from ""Test""

and here:

SQL Exception
Acces is denied:CINT in statement ""INSTR in statement [SELECT CASE WHEN InStr(

Somebody can throw some light here?

Thanks in advance

Edit: Changed subject, was LO BASE sql queries with functions  Make your post understandable by others  -- MrProgrammer, forum moderator

[MrProgrammer]

It [throws] an error pointing to the functions [which aren't allowed] …

The StarBasic language has functions, desribed in Help → Contents → Macros and Programming → Run-Time Functions. The SQL language has functions, described in the reference material for the database you're using. You do not say which database you've connected with. Different databases support different functions. It is your responsibility to locate the SQL language documentatation for the database you've chosen. Your database probably supports SQL functions for date/time conversions.

You can only use StarBasic functions when writing StarBasic statements in your macro. You can only use SQL functions in the SQL statement which you pass to your database. You can use StarBasic functions in your macro to help build an SQL statement which uses SQL functions. SQL does not execute the StarBasic functions because it only receives the completed SQL statement. Basic does not execute the SQL functions because they are just used as text strings in the macro.

Although CINT and INSTR are StarBasic functions, the error messages you received say that they are not SQL functions for the database you're using.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Villeroy]

Code: Select all

SELECT CAST("Time_Field" AS INTEGER) AS "Minutes" FROM "Test" WHERE  CAST("Time_Field" AS INTEGER) < 120

Should work with several types of databases, assuming that there is a "Time_Field" in table "Test" having numeric strings like "120". Forget StarBasic in the context of Base. It is even more complex than programming Calc, Writer and the other components. SQL is the universal language of relational databases.

[sam-sams-15]

Just for info, my database at the moment of creation was marqued as HSQLBD, only choice in the Base Gui menu. But I mark as solved as I understand that there are differents databases with differents requirements. I'll put some learning time into the Base/ StarBasic bussiness.

Meantime I modified the table to work directly with a simple format and it works. I keep in mind the CAST function to address futures problems.

Thanks for your help.

[Villeroy]

Embedded HSQL is documented here: http://www.hsqldb.org/doc/1.8/guide/ch09.html
It is fairly easy to get the HSQLDB out of the Base container: viewtopic.php?f=21&t=86071 (and use a more recent version of HSQL.
LibreOffice can also embed a Firebird database when "experimental features" are enabled.

What you need to learn is some basic level of SQL instead of Basic, database normalization and how to transfer relations to LibreOffice input forms in order to make related tables editable on one form: viewtopic.php?f=100&t=40444