possible bug: separator string and quote mode

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

possible bug: separator string and quote mode

p dev
I have been using SQLite for a small data-processing application.
I am hoping to get quote mode output with a field separator that is not a comma.Many thanks,
Pamela------------------
Problem

Quote mode output ignores separator string
Background
https://www.sqlite.org/cli.html5. Changing Output Formats
...
In "quote" mode, the output is formatted as SQL literals. Strings are enclosed in single-quotes and internal single-quotes are escaped by doubling. Blobs are displayed in hexadecimal blob literal notation (Ex: x'abcd'). Numbers are displayed as ASCII text and NULL values are shown as "NULL". All columns are separated from each other by a comma (or whatever alternative character is selected using ".separator").
...

Demonstration

SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
sqlite> create table tbl1(one varchar(10), two smallint);
sqlite> insert into tbl1 values('hello!',10);
sqlite> insert into tbl1 values('goodbye', 20);
sqlite> insert into tbl1 values(null, 30);
sqlite> select * from tbl1;
hello!|10
goodbye|20
|30

sqlite> .separator #
sqlite> select * from tbl1;
hello!#10
goodbye#20
#30

sqlite> .mode quote
sqlite> select * from tbl1;
'hello!',10
'goodbye',20
NULL,30


Expected Output

sqlite> select * from tbl1;'hello!'#10
'goodbye'#20
NULL#30

sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: on
        mode: quote
   nullvalue: ""
      output: stdout
colseparator: "#"
rowseparator: "\n"
       stats: off
       width:
    filename: data.db3

_______________________________________________
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: possible bug: separator string and quote mode

petern
Pamela.  Shell .mode quote uses hardwired separator, presumably to rule out
errors when the mode is requested to generate SQL compatible strings.

It seems to me the enhancement you're requesting could be made backward
compatible by using  p->colSeparator instead of the hardwired ',' and then
setting the  p->colSeparator to the backward compatible comma in the .mode
quote meta-command and then allowing the user to subsequently override with
the .separator command.

Maybe there is a different reason for the hardwiring or reason why it can't
be changed.  That would be Richard's decision.

Peter
------------------
/*from shell.c*/
    case MODE_Quote: {
      if( azArg==0 ) break;
      if( p->cnt==0 && p->showHeader ){
        for(i=0; i<nArg; i++){
          if( i>0 ) raw_printf(p->out, ",");
          output_quoted_string(p->out, azCol[i]);
        }
        raw_printf(p->out,"\n");
      }
      p->cnt++;
      for(i=0; i<nArg; i++){
        if( i>0 ) raw_printf(p->out, ",");
        if( (azArg[i]==0) || (aiType && aiType[i]==SQLITE_NULL) ){
          utf8_printf(p->out,"NULL");
        }else if( aiType && aiType[i]==SQLITE_TEXT ){
          output_quoted_string(p->out, azArg[i]);
        }else if( aiType && aiType[i]==SQLITE_INTEGER ){
          utf8_printf(p->out,"%s", azArg[i]);
        }else if( aiType && aiType[i]==SQLITE_FLOAT ){
          char z[50];
          double r = sqlite3_column_double(p->pStmt, i);
          sqlite3_snprintf(50,z,"%!.20g", r);
          raw_printf(p->out, "%s", z);
        }else if( aiType && aiType[i]==SQLITE_BLOB && p->pStmt ){
          const void *pBlob = sqlite3_column_blob(p->pStmt, i);
          int nBlob = sqlite3_column_bytes(p->pStmt, i);
          output_hex_blob(p->out, pBlob, nBlob);
        }else if( isNumber(azArg[i], 0) ){
          utf8_printf(p->out,"%s", azArg[i]);
        }else{
          output_quoted_string(p->out, azArg[i]);
        }
      }
      raw_printf(p->out,"\n");
      break;
    }


On Sun, Jan 14, 2018 at 9:29 AM, p dev <[hidden email]> wrote:

> I have been using SQLite for a small data-processing application.
> I am hoping to get quote mode output with a field separator that is not a
> comma.Many thanks,
> Pamela------------------
> Problem
>
> Quote mode output ignores separator string
> Background
> https://www.sqlite.org/cli.html5. Changing Output Formats
> ...
> In "quote" mode, the output is formatted as SQL literals. Strings are
> enclosed in single-quotes and internal single-quotes are escaped by
> doubling. Blobs are displayed in hexadecimal blob literal notation (Ex:
> x'abcd'). Numbers are displayed as ASCII text and NULL values are shown as
> "NULL". All columns are separated from each other by a comma (or whatever
> alternative character is selected using ".separator").
> ...
>
> Demonstration
>
> SQLite version 3.21.0 2017-10-24 18:55:49
> Enter ".help" for usage hints.
> sqlite> create table tbl1(one varchar(10), two smallint);
> sqlite> insert into tbl1 values('hello!',10);
> sqlite> insert into tbl1 values('goodbye', 20);
> sqlite> insert into tbl1 values(null, 30);
> sqlite> select * from tbl1;
> hello!|10
> goodbye|20
> |30
>
> sqlite> .separator #
> sqlite> select * from tbl1;
> hello!#10
> goodbye#20
> #30
>
> sqlite> .mode quote
> sqlite> select * from tbl1;
> 'hello!',10
> 'goodbye',20
> NULL,30
>
>
> Expected Output
>
> sqlite> select * from tbl1;'hello!'#10
> 'goodbye'#20
> NULL#30
>
> sqlite> .show
>         echo: off
>          eqp: off
>      explain: auto
>      headers: on
>         mode: quote
>    nullvalue: ""
>       output: stdout
> colseparator: "#"
> rowseparator: "\n"
>        stats: off
>        width:
>     filename: data.db3
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
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: possible bug: separator string and quote mode

Clemens Ladisch
In reply to this post by p dev
p dev wrote:
> https://www.sqlite.org/cli.html  5. Changing Output Formats
>
> All columns are separated from each other by a comma (or whatever alternative character is selected using ".separator").

This is indeed a bug in the documentation; quote mode ignores the .separator setting.

> I have been using SQLite for a small data-processing application.
> I am hoping to get quote mode output with a field separator that is not a comma.

Why?  What is the actual problem you're trying to solve?


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users