hdbsql: How to edit command buffer ?

[Mark_A_Parsons]

I'm trying to figure out how to edit the command buffer in my hdbsql session.

I've read (in several places) that I need to use the \e command/shortcut, but I'm not having any luck pulling the command buffer into a vi/editor session.

Here's an example of what I've tried so far:

$ export EDITOR=vi

$ export VISUAL=vi

$ hdbsql -d SYSTEMDB -U xxxx -m -A -j

select top 2 object_name from _SYS_REPO.ACTIVE_OBJECT;
| OBJECT_NAME            |
| ---------------------- |
| 72_iPad_Desktop_Launch |
| 72_iPad_Desktop_Launch |
2 rows selected (overall time 13.541 msec; server time 2302 usec)

hdbsql SYSTEMDB=> \e
open of  failed: 2 No such file or directory

hdbsql SYSTEMDB=> \e x

  ** while this opens a vi editor for a local file named 'x',
     there's nothing in 'x' (ie, vi editor screen is empty)

hdbsql SYSTEMDB=> \e /tmp/x

  ** same as before, vi editor opens for file '/tmp/x' but
     there's nothing in '/tmp/x' (ie, vi editor screen is empty)

The manuals provide this format and one-line explanation about <[file]>:

To export the contents of the command buffer to an external file, run the following command:

\e <[file]>

You must enter the complete file path and file name. If you do not specify a file, then SAP HANA HDBSQL generates a temporary file.

If I leave out <[file]> the manual says a temporary file should be used, but I'm getting an error (open of failed: ...).

If I provide <[file]> (either as a local file 'x', or with a full path like '/tmp/x'), the contents of the buffer are not being written to the (temporary) file for editing.

--------------

At this point I'm trying to figure out:

- how to edit the command buffer

- is there a way to designate a default (temp) file (eg, as a hdbsql command line option) to be used by the \e command

--------------

FWIW ...

HANA Express \status:

hdbsql HXE=> \s
host          : localhost:39013
sid           : HXE
dbname        : SYSTEMDB
user          : SYSTEM
kernel version: 2.00.022.00.1511184640
SQLDBC version:        libSQLDBCHDB 2.02.033.1510080117
autocommit    : ON
locale        : C
input encoding: UTF8

I'm getting the same behavior on 2 different linux machines.

machine #1 (same machine SID=HXE is running on 😞

$ cat /etc/issue
Welcome to SUSE Linux Enterprise Server for SAP Applications 12 SP2  (x86_64) - Kernel \r (\l).

$ hdbsql -v
HDBSQL version 2.2.33.1510080117, the SAP HANA Database interactive terminal.
Copyright 2000-2017 by SAP SE.

machine #2:

$ cat /etc/issue
Welcome to SUSE Linux Enterprise Server 11 SP4  (x86_64) - Kernel \r (\l).

$ hdbsql -v
HDBSQL version 2.2.33.1510080117, the SAP HANA Database interactive terminal.
Copyright 2000-2017 by SAP SE.

[former_member531293 · Accepted solution]

Hi Mark,

From my experiences, it seems that ,,,

• \e command is to be used after sub-prompt ">" while you are editing SQL. Not after the main prompt "hdbsql XXX=>".
• \e command should take one of "1" to "9" as a filename. For example \e 1.

Please try like below.

hdbsql HDB=> \mu 
Multiline mode switched ON 
hdbsql HDB=> select 
> current_schema 
> from 
> dummy 
> \e 1    <== Editor comes up and you can edit your SQL. 
> \g       <== SQL execution.

Once you execute the SQL in buffer, the buffer seems to be flushed and you lose your SQL.

Cheers,

Toshi

[lbreddemann]

I couldn't get it working either as documented.

Looking at the strace -e open output, it looks to me like a bug:

Welcome to the SAP HANA Database interactive terminal.
Type:  \h for help with commands
       \q to quit

hdbsql S12=> \e test.sql
open("test.sql", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 4
--- SIGCHLD {si_signo=SIGCHLD, si_code=CLD_EXITED, si_pid=20186, si_uid=1003, si_status=0, si_utime=2, si_stime=0} ---
open("test.sql", O_RDONLY)              = 4
hdbsql S12=> \p test.sql
select current_date from dummy;

hdbsql S12=> \e test.sql
open("test.sql", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 4
--- SIGCHLD {si_signo=SIGCHLD, si_code=CLD_EXITED, si_pid=20308, si_uid=1003, si_status=0, si_utime=2, si_stime=1} ---
open("test.sql", O_RDONLY)              = 4
hdbsql S12=> \e test.sql
open("test.sql", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 4
--- SIGCHLD {si_signo=SIGCHLD, si_code=CLD_EXITED, si_pid=20309, si_uid=1003, si_status=0, si_utime=3, si_stime=0} ---
open("test.sql", O_RDONLY)              = 4
hdbsql S12=> \e test2.sql
open("test2.sql", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 4
--- SIGCHLD {si_signo=SIGCHLD, si_code=CLD_EXITED, si_pid=20310, si_uid=1003, si_status=0, si_utime=4, si_stime=1} ---
open("test2.sql", O_RDONLY)             = 4
hdbsql S12=> \p
select current_date from dummy;

hdbsql S12=> \e test.sql
open("test.sql", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 4
--- SIGCHLD {si_signo=SIGCHLD, si_code=CLD_EXITED, si_pid=20432, si_uid=1003, si_status=0, si_utime=2, si_stime=0} ---
open("test.sql", O_RDONLY)              = 4
hdbsql S12=> \p
select current_date from dummy;

hdbsql S12=> \e test2.sql
open("test2.sql", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 4
--- SIGCHLD {si_signo=SIGCHLD, si_code=CLD_EXITED, si_pid=20433, si_uid=1003, si_status=0, si_utime=3, si_stime=0} ---
open("test2.sql", O_RDONLY)             = 4
hdbsql S12=> \p test2.sql
select current_user from dummy;

hdbsql S12=> \p
select current_user from dummy;

hdbsql S12=> \e test.sql
open("test.sql", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 4
--- SIGCHLD {si_signo=SIGCHLD, si_code=CLD_EXITED, si_pid=20555, si_uid=1003, si_status=0, si_utime=2, si_stime=0} ---
open("test.sql", O_RDONLY)              = 4
hdbsql S12=> \p test.sql
select current_user from dummy;

To me, this looks as if hdbsql actually requires a filename to handle its query buffer. It seems to only hold the query buffer once a filename has been provided, which is why the first \e filename opens an empty file.

Unfortunately, it always truncates the file when opening it and puts the current buffer content into it. This behaviour is then responsible for the overwriting of current_date with current_user in my example above.

As I'm not the developer of the tool, this is as much as I can comment on this. Maybe @Jeff Albion jeff.albion can comment on this?

cheers,

Lars