Bug 79487 - Other: Base should interpret "set term" command
Summary: Other: Base should interpret "set term" command
Status: RESOLVED INVALID
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.1.0.4 release
Hardware: Other All
: medium normal
Assignee: Lionel Elie Mamane
URL:
Whiteboard: BSA
Keywords:
Depends on:
Blocks:
 
Reported: 2014-05-31 16:55 UTC by Julien Nabet
Modified: 2014-10-06 19:16 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Screen capture of how to execute running an SQL command directly (21.89 KB, image/png)
2014-06-26 04:07 UTC, topcyde
Details
Test database with create stored procedure query and query getting stored proc result (3.05 KB, application/vnd.sun.xml.base)
2014-10-06 09:30 UTC, reinierolislagers
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Julien Nabet 2014-05-31 16:55:36 UTC
Problem description: 
"set term" has at least 2 usages:
1) in Firebird, it's used to change the "statement terminator" (see http://www.firebirdfaq.org/faq78/)
2) in Oracle, to display "output generated by commands in a script that is executed with @, @@ or START" (see http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12040.htm#i2683459)

Steps to reproduce:
1) Open Base
2) Menu Tools/SQL
3) Type
set term !! ;
set term ; !!


Current behavior:
Status window shows:
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 1, column 5
*term
caused by
'isc_dsql_prepare'

Expected behavior:
The command should be successful
              
Operating System: All
Version: 4.1.0.4 release
Comment 1 Julien Nabet 2014-06-03 21:28:58 UTC
I tried to add this on connectivity/source/parse/sqlflex.l
TERM                {SQL_NEW_KEYWORD(SQL_TOKEN_TERM);  }

but then I'm stuck with Bison part.
I don't know at all Lex/Flex and Bison.

Lionel: any idea?
Comment 2 topcyde 2014-06-25 02:20:01 UTC
If using an embedded HypersonicSQL databse SET TERM is not an HSQL command.

To pass a command to a linked database go into the query design view. Enter command and click "Run SQL Command Directly".

See Wiki entries:
https://help.libreoffice.org/Common/Run_SQL_command_directly
https://help.libreoffice.org/Common/Executing_SQL_Commands
https://help.libreoffice.org/Common/Execute_SQL_statement

Please verify this gives expected results.
Comment 3 Julien Nabet 2014-06-25 17:15:48 UTC
(In reply to comment #2)
...
> To pass a command to a linked database go into the query design view. Enter
> command and click "Run SQL Command Directly".
> 
> See Wiki entries:
> https://help.libreoffice.org/Common/Run_SQL_command_directly
> https://help.libreoffice.org/Common/Executing_SQL_Commands
> https://help.libreoffice.org/Common/Execute_SQL_statement
> 
> Please verify this gives expected results.

I don't see the "Run Sql command directly", can you attach a screenshot or give precise steps. The wiki entry didn't help me :-(
Comment 4 topcyde 2014-06-26 04:07:33 UTC
Created attachment 101779 [details]
Screen capture of how to execute running an SQL command directly
Comment 5 Julien Nabet 2014-06-26 05:46:23 UTC
Thank you for your help!

Now when running the sql without "run direct sql", I got:
SQL Status: HY000
Error code: 1000
syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

and with it, I've got:
Error code: 1

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 1, column 5
*term
caused by
'isc_dsql_prepare'
Comment 6 reinierolislagers 2014-10-03 03:33:12 UTC
@Julien Nabet:
SET TERM is not a Firebird SQL command but a client (isql, Flamerobin,...) command so it is expected that you get an SQL error from the server.
See http://www.firebirdfaq.org/faq78/

Note: most Firebird DDL scripts with trigger/stored procedure/generator creation will use SET TERM so having the ability to parse this would be useful in Libreoffice. 

*If* Libreoffice is not able to parse a script like this:
CREATE PROCEDURE givetheanswer
RETURNS (ANSWER INTEGER)
AS 
BEGIN
  -- bigger procedures often need comments.
  FOR
    select 42 from rdb$database into :ANSWER
  DO
    SUSPEND;
END^

... then parsing SET TERM would be *necessary*:
SET TERM ^ ;

CREATE PROCEDURE givetheanswer
RETURNS (ANSWER INTEGER)
AS 
BEGIN
  -- bigger procedures often need comments.
  FOR
    select 42 from rdb$database into :ANSWER
  DO
    SUSPEND;
END^

SET TERM ; ^

Sorry, cannot test myself, don't have working Firebird connectivity with my LibreOffice.
Comment 7 Julien Nabet 2014-10-03 06:19:50 UTC
(In reply to reinierolislagers from comment #6)
> Sorry, cannot test myself, don't have working Firebird connectivity with my
> LibreOffice.
Firebird support is experimental. To enable it, you must do:
- Tools/Options/Advanced
- check "Enable experimental features"
- restart LibreOffice
Comment 8 reinierolislagers 2014-10-03 06:28:52 UTC
I know. I'm facing bug 84618.
Comment 9 reinierolislagers 2014-10-06 09:30:05 UTC
(In reply to reinierolislagers from comment #6)
> *If* Libreoffice is not able to parse a script like this:
> CREATE PROCEDURE givetheanswer
> RETURNS (ANSWER INTEGER)
> AS 
> BEGIN
>   -- bigger procedures often need comments.
>   FOR
>     select 42 from rdb$database into :ANSWER
>   DO
>     SUSPEND;
> END^


Oops, that should have been the last end without ^
CREATE PROCEDURE givetheanswer
RETURNS (ANSWER INTEGER)
AS 
BEGIN
  -- bigger procedures often need comments.
  FOR
    select 42 from rdb$database into :ANSWER
  DO
    SUSPEND;
END;

Just created a new query, set SQL mode to true and ran it. Although the query complained about not returning a result set, that was the only error.
I tried calling the created stored procedure with this query:
select answer from givetheanswer
standard mode gave The statement contains a cyclic reference to one or more subqueries
Native SQL mode: Table not found in statement [SELECT answer FROM gettheanswer]
This query does work in Firebird tools (e.g. Flamerobin).

Adding database with queries (test3.odb).
Comment 10 reinierolislagers 2014-10-06 09:30:49 UTC
Created attachment 107408 [details]
Test database with create stored procedure query and query getting stored proc result
Comment 11 Lionel Elie Mamane 2014-10-06 16:36:07 UTC
Your test database is an HSQLDB database, but seems to try to be using Firebird syntax. Try it on a firebird DB and it will work, if you change the "givetheanswer" to proper upper case, that is:
 SELECT "ANSWER" FROM "GIVETHEANSWER"

Saved queries in LibreOffice are rather hardcoded/expected to be SELECT-type queries, that return a result. As such, maybe the "no result returned" message overrides another error message. I didn't check if that is what is happening in in your example; if it is, this is unfortunate, but then it is another bug than what is discussed here.

For action queries, I'd rather recommend menu Tools / SQL, or executing by a scroipt (using "execute" and/or "executeUpdate" instad of "executeQuery").

"SET TERM" is not necessary in LibreOffice because whether in a saved query or in Tools/SQL, the whole string is passed as one command (statement) to the database. That is, LibreOffice always uses *no* statement terminator, so no need to change it.

The examples that need SET TERM in isql / flamerobin / ... should work without it in LibreOffice, *if* they are being executed one by one in the LibreOffice environment.
Comment 12 Julien Nabet 2014-10-06 19:16:15 UTC
Lionel: I created this tracker reading this:
https://bugs.freedesktop.org/show_bug.cgi?id=69949#c6 (see https://bugs.freedesktop.org/show_bug.cgi?id=69949#c15)