CLI .parameter command observations/request

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

CLI .parameter command observations/request

Martin-2
Hi, first a thankyou to everyone who helped with my 'date check clause' post.

This time I am grappling with the precompiled CLI .parameter command.
(The first line in the param.cli file is .echo on)

SQLite version 3.29.0 2019-07-10 17:32:03
sqlite> .read param.cli
.tables
-- select * from sqlite_parameters ;
Error: near line 4: no such table: sqlite_parameters
.param init
.tables
select * from sqlite_parameters ;
-- table now exists but not visible, where is it?

.param set $s      'abc'
.param set $slist  'abc, def'
.param set :int    1
.param set :real   1.2
.param set @datex  '2019-01-01'
.param set @date   date('2019-01-01')

select $s, $slist, :int, :real, @datex, @date ;
abc|abc, def|1|1.2|2017|2019-01-01

select 1 where instr($slist, 'def') > 0 ;
1
select 1 where '2019-01-01' = @date ;
1
.param unset @datex
.param clear
.param list
$s     'abc'
$slist 'abc, def'
:int   1
:real  1.2
@date  '2019-01-01'

--------
Initial observations wrt .parameter command:
  * INIT  works but the temp.table is not visible
          (even though one can explicitly insert into and delete from it.)
  * SET   works but there is some kind of evaluation of the VALUE
          (see @datex and @date; '2019-01-01' arithmetically is 2017)
  * UNSET works just fine
  * LIST  works just fine
  * CLEAR does nothing at all

As a result:
  * I feel that section 16 of the CLI documentation should mention VALUE treatment.

Additionally, dare I suggest
  * that CLEAR be renamed to DROP (action 'drop table') and made to work
  * that UNSET be augmented to allow .parameter unset ALL
    (ALL is not a valid parameter name; action being 'delete from')

I appreciate renaming clear to drop is probably a non-starter for compatibility.
On the other hand, clear currently does do nothing.

Otherwise, thanks to the development team for a tool that just gets better!
Regards, Martin.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CLI .parameter command observations/request

Simon Slavin-3
On 16 Aug 2019, at 4:40pm, Martin <[hidden email]> wrote:

> select * from sqlite_parameters

You are expected to create this table yourself, either directly or by using one of the parameter commands.  See

<https://sqlite.org/cli.html#sql_parameters>

This sequence works.  You should be able to use it to progress:

sqlite> .schema
sqlite> .param init
sqlite> .schema
CREATE TABLE temp.sqlite_parameters(
  key TEXT PRIMARY KEY,
  value ANY
) WITHOUT ROWID;
sqlite> .param list
sqlite> .param set nodeNum 67
sqlite> .param list
nodeNum 67
sqlite> SELECT * FROM temp.sqlite_parameters;
nodeNum|67
sqlite> SELECT * FROM sqlite_parameters;
nodeNum|67
sqlite>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users