Re: [sqlite] .mode column .width .separator

[Don V Nielsen]

Not that I don't enjoy this conversation, but this should be end of this
thread. Thanks for telling me to look at shell.c. I did. Interesting read.
I can see that simply changing "  " to "" in the printf call, recompiling,
and tada, I have what I want.  Thanks David.

- HOWEVER -

New logic is required to make an appropriate update to shell.c such that
the current user experience is not changed. One cannot simply replace "  "
with p->colSeparator. That is because in column mode, the default .mode
column separator is hardcoded as "  ". However, the default colSeparator
value is a pipe '|' (which affects other modes.) The following
considerations have to be resolved:

a) .separator allows one to override colSeparator
b) specifying .separator and .column are not dependent on sequence in the
current implementation

So to maintain the current behavior:

c) some indicator must be in place to identify when colSeparator was changed
d) when mode is changed to .column, colSeparator would be internally set to
"  " only when colSeparator was not already changed by .separator
e) .separator would have to set the indicator when it was implemented

Not following c) thru e) would change the behaviors in a way that would
require the user to know:
f) changing modes will change internal default values, and to know what
those values are
g) .separator must be specified after .mode column to change the
colSeparator value from internal default

The above f) & g) are not currently required of the user. It's messy when
one thinks about it.

dvn

On Fri, Oct 14, 2016 at 2:27 PM, Don V Nielsen <donvniel...@gmail.com>
wrote:

> Thanks, David. I can read C, but I don't know how to compile it. :(  Just
> not a language I have learned.
>
> On Fri, Oct 14, 2016 at 10:22 AM, David Raymond <david.raym...@tomtom.com>
> wrote:
>
>> If you're using the CLI exclusively you can go into shell.c and get rid
>> of the double spaces. In the copy I'm looking at that's bundled with the
>> 3.14.2 amalgamation it looks like there're 5 lines to alter and you'll be
>> good.
>>
>> They're in
>> static int shell_callback(
>> ...
>> case MODE_Column: {
>> ...
>> Lines 1007, 1010, 1026 look like they deal with displaying the header line
>> Lines 1050 and 1054 deal with each row.
>>
>> All of those lines are the end of a utf8_printf( call and have...
>>
>> i==nArg-1 ? rowSep : "  ");
>>
>> I tried turning the 2 spaces there into "" recompiled it, and it seems to
>> display the way you're wanting it to.
>>
>> Of course since I don't know C, by doing that I have no clue what else I
>> might just have broken which relied on the old format, but hey :)
>>
>>
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Don V Nielsen
>> Sent: Friday, October 14, 2016 7:39 AM
>> To: SQLite mailing list
>> Subject: Re: [sqlite] .mode column .width .separator
>>
>> Thank you, Gunter. I will investigate virtual tables; it's a bit over my
>> head at the moment.
>>
>> My final implementation was to route the select via .output, employ a CTE
>> to prepare the data, use printf to setup the record format, and then
>> select
>> the data from the CTE. Again, I do not enjoy the redundancy of the two
>> selects, but it gets the job done. A script will handle the statement in
>> the future.
>>
>>
>> with pre_process as (
>>   select
>>   ... columns blah blah ...
>> case when piecerate in ('AF','RF') and version_id = '81' then '81'
>> else
>> segment end as segment,
>>   ... columns blah blah ...
>>   from address_txt
>> )
>> select
>> printf(
>>
>> '%-1s%-15s%-5s%-4s%-2s%-1s%-4s%-4s%-1s%-5s%-1s%-10s%-10s%-10
>> s%-1s%-20s%-20s%-15s%-1s%-10s%-20s%-20s%-50s%-50s%-50s%-50s%
>> -50s%-25s%-2s%-8s%-3s%-6s%-9s',
>>   ... columns, blah blah ...
>> )
>> from pre_process
>> ;
>>
>>
>> Works like a champ.
>> Have a good one.
>>
>> On Fri, Oct 14, 2016 at 2:58 AM, Hick Gunter <h...@scigames.at> wrote:
>>
>> > You can eliminate separators by using
>> > .mode list
>> > .sepa ""
>> >
>> > But then you need to format your values to the correct widths for the
>> > record description. Text processing is not the primary domain of SQLite
>> and
>> > is best left to the presentation layer.
>> >
>> > Alternatively you may consider writing a virtual table module to do the
>> > processing for you. Semantics could be s

Re: [sqlite] .mode column .width .separator

[Warren Young]

On Oct 14, 2016, at 1:27 PM, Don V Nielsen  wrote:
> 
> I can read C, but I don't know how to compile it.

https://www.sqlite.org/howtocompile.html

Once you get SQLite to build, make the suggested change and say “make” again.  
The sqlite3 program will be rebuilt, since make knows that sqlite3 depends on 
shell.c.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .mode column .width .separator

[Don V Nielsen]

Thanks, David. I can read C, but I don't know how to compile it. :(  Just
not a language I have learned.

On Fri, Oct 14, 2016 at 10:22 AM, David Raymond <david.raym...@tomtom.com>
wrote:

> If you're using the CLI exclusively you can go into shell.c and get rid of
> the double spaces. In the copy I'm looking at that's bundled with the
> 3.14.2 amalgamation it looks like there're 5 lines to alter and you'll be
> good.
>
> They're in
> static int shell_callback(
> ...
> case MODE_Column: {
> ...
> Lines 1007, 1010, 1026 look like they deal with displaying the header line
> Lines 1050 and 1054 deal with each row.
>
> All of those lines are the end of a utf8_printf( call and have...
>
> i==nArg-1 ? rowSep : "  ");
>
> I tried turning the 2 spaces there into "" recompiled it, and it seems to
> display the way you're wanting it to.
>
> Of course since I don't know C, by doing that I have no clue what else I
> might just have broken which relied on the old format, but hey :)
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Don V Nielsen
> Sent: Friday, October 14, 2016 7:39 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] .mode column .width .separator
>
> Thank you, Gunter. I will investigate virtual tables; it's a bit over my
> head at the moment.
>
> My final implementation was to route the select via .output, employ a CTE
> to prepare the data, use printf to setup the record format, and then select
> the data from the CTE. Again, I do not enjoy the redundancy of the two
> selects, but it gets the job done. A script will handle the statement in
> the future.
>
>
> with pre_process as (
>   select
>   ... columns blah blah ...
> case when piecerate in ('AF','RF') and version_id = '81' then '81' else
> segment end as segment,
>   ... columns blah blah ...
>   from address_txt
> )
> select
> printf(
>
> '%-1s%-15s%-5s%-4s%-2s%-1s%-4s%-4s%-1s%-5s%-1s%-10s%-10s%-
> 10s%-1s%-20s%-20s%-15s%-1s%-10s%-20s%-20s%-50s%-50s%-50s%-
> 50s%-50s%-25s%-2s%-8s%-3s%-6s%-9s',
>   ... columns, blah blah ...
> )
> from pre_process
> ;
>
>
> Works like a champ.
> Have a good one.
>
> On Fri, Oct 14, 2016 at 2:58 AM, Hick Gunter <h...@scigames.at> wrote:
>
> > You can eliminate separators by using
> > .mode list
> > .sepa ""
> >
> > But then you need to format your values to the correct widths for the
> > record description. Text processing is not the primary domain of SQLite
> and
> > is best left to the presentation layer.
> >
> > Alternatively you may consider writing a virtual table module to do the
> > processing for you. Semantics could be similar to:
> >
> > CREATE VIRTUAL TABLE mainframe_file USING mainframe ( '',
> > '', []); --> open filename, prepare to write in
> > specified format, write header
> > INSERT INTO mainframe_file SELECT ...; -> stuff in data, write record
> > DROP TABLE mainframe_file; -> close the file, write trailer record if
> > required
> >
> > -Ursprüngliche Nachricht-
> > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > Im Auftrag von Don V Nielsen
> > Gesendet: Donnerstag, 13. Oktober 2016 20:51
> > An: General Discussion of SQLite Database <sqlite-users@mailinglists.
> > sqlite.org>
> > Betreff: [sqlite] .mode column .width .separator
> >
> > I am using the command line shell SQLite 3.14.2 2016-09-12. I'm working
> > with mainframe data in a fixed format.
> >
> > I would like to use .mode column to create my output text file in a fixed
> > layout. I set all my column widths using .width. I then output my data
> to a
> > file. Unfortunately, there are two blanks separating each column, space I
> > don't want to be there. The .separator command does not provide any
> > mechanism for turning it off. Is there a way?
> >
> > I realize there is a printf function available. However, it appears that
> > output values must come from a table column, where as below, I could use
> > case statements in the sql select of the data.
> >
> > Any suggestions? I think I am overlooking a .separator option that says
> > "don't put spaces between output columns". I assume that John McKown has
> > faced this already, given his mainframe pedigree.
> >
> > Thanks for your time,
> > dvn
> >
> >
> >
> > Sample output:
> > "H  0NZOX0001687395  83501  5827  "...
> >
> > .mode column
> > .width 1 15 5 4 2 1 4 4 1 5 1 10 1

Re: [sqlite] .mode column .width .separator

[David Raymond]

If you're using the CLI exclusively you can go into shell.c and get rid of the 
double spaces. In the copy I'm looking at that's bundled with the 3.14.2 
amalgamation it looks like there're 5 lines to alter and you'll be good.

They're in 
static int shell_callback(
...
case MODE_Column: {
...
Lines 1007, 1010, 1026 look like they deal with displaying the header line
Lines 1050 and 1054 deal with each row.

All of those lines are the end of a utf8_printf( call and have...

i==nArg-1 ? rowSep : "  ");

I tried turning the 2 spaces there into "" recompiled it, and it seems to 
display the way you're wanting it to.

Of course since I don't know C, by doing that I have no clue what else I might 
just have broken which relied on the old format, but hey :)


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Don V Nielsen
Sent: Friday, October 14, 2016 7:39 AM
To: SQLite mailing list
Subject: Re: [sqlite] .mode column .width .separator

Thank you, Gunter. I will investigate virtual tables; it's a bit over my
head at the moment.

My final implementation was to route the select via .output, employ a CTE
to prepare the data, use printf to setup the record format, and then select
the data from the CTE. Again, I do not enjoy the redundancy of the two
selects, but it gets the job done. A script will handle the statement in
the future.


with pre_process as (
  select
  ... columns blah blah ...
case when piecerate in ('AF','RF') and version_id = '81' then '81' else
segment end as segment,
  ... columns blah blah ...
  from address_txt
)
select
printf(

'%-1s%-15s%-5s%-4s%-2s%-1s%-4s%-4s%-1s%-5s%-1s%-10s%-10s%-10s%-1s%-20s%-20s%-15s%-1s%-10s%-20s%-20s%-50s%-50s%-50s%-50s%-50s%-25s%-2s%-8s%-3s%-6s%-9s',
  ... columns, blah blah ...
)
from pre_process
;


Works like a champ.
Have a good one.

On Fri, Oct 14, 2016 at 2:58 AM, Hick Gunter <h...@scigames.at> wrote:

> You can eliminate separators by using
> .mode list
> .sepa ""
>
> But then you need to format your values to the correct widths for the
> record description. Text processing is not the primary domain of SQLite and
> is best left to the presentation layer.
>
> Alternatively you may consider writing a virtual table module to do the
> processing for you. Semantics could be similar to:
>
> CREATE VIRTUAL TABLE mainframe_file USING mainframe ( '',
> '', []); --> open filename, prepare to write in
> specified format, write header
> INSERT INTO mainframe_file SELECT ...; -> stuff in data, write record
> DROP TABLE mainframe_file; -> close the file, write trailer record if
> required
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Don V Nielsen
> Gesendet: Donnerstag, 13. Oktober 2016 20:51
> An: General Discussion of SQLite Database <sqlite-users@mailinglists.
> sqlite.org>
> Betreff: [sqlite] .mode column .width .separator
>
> I am using the command line shell SQLite 3.14.2 2016-09-12. I'm working
> with mainframe data in a fixed format.
>
> I would like to use .mode column to create my output text file in a fixed
> layout. I set all my column widths using .width. I then output my data to a
> file. Unfortunately, there are two blanks separating each column, space I
> don't want to be there. The .separator command does not provide any
> mechanism for turning it off. Is there a way?
>
> I realize there is a printf function available. However, it appears that
> output values must come from a table column, where as below, I could use
> case statements in the sql select of the data.
>
> Any suggestions? I think I am overlooking a .separator option that says
> "don't put spaces between output columns". I assume that John McKown has
> faced this already, given his mainframe pedigree.
>
> Thanks for your time,
> dvn
>
>
>
> Sample output:
> "H  0NZOX0001687395  83501  5827  "...
>
> .mode column
> .width 1 15 5 4 2 1 4 4 1 5 1 10 10 10 1 20 20 15 1 10 20 20 50 50 50 50 50
> 25 2 8 003 006 009
> .output vo_pwprep.txt
> select
>   recid,
>   z_num,
>   zip,
>   zip4,
>   dpbc,
>   ckdig,
>   cart,
>   lot,
>   lot_order,
>   walk_seq,
>   walk_seq_bic,
>   case when piecerate in ('AF','RF') and version_id = '81' then '81' else
> segment end as segment,
>   version_id,
>   message,
>   seed,
>   seed_id,
>   seed_key,
>   planet,
>   ocr_acr,
>   priority,
>   keycode,
>   custno,
>   name,
>   title,
>   firm_id,
>   addr1,
>   addr2,
>   city,
>   state,
>   seq_number,
>   srvc_type,
>   imb_mid,
>   imb_serial
> from address_txt
> ;
> ___
&

Re: [sqlite] .mode column .width .separator

[Don V Nielsen]

Thank you, Gunter. I will investigate virtual tables; it's a bit over my
head at the moment.

My final implementation was to route the select via .output, employ a CTE
to prepare the data, use printf to setup the record format, and then select
the data from the CTE. Again, I do not enjoy the redundancy of the two
selects, but it gets the job done. A script will handle the statement in
the future.


with pre_process as (
  select
  ... columns blah blah ...
case when piecerate in ('AF','RF') and version_id = '81' then '81' else
segment end as segment,
  ... columns blah blah ...
  from address_txt
)
select
printf(

'%-1s%-15s%-5s%-4s%-2s%-1s%-4s%-4s%-1s%-5s%-1s%-10s%-10s%-10s%-1s%-20s%-20s%-15s%-1s%-10s%-20s%-20s%-50s%-50s%-50s%-50s%-50s%-25s%-2s%-8s%-3s%-6s%-9s',
  ... columns, blah blah ...
)
from pre_process
;


Works like a champ.
Have a good one.

On Fri, Oct 14, 2016 at 2:58 AM, Hick Gunter <h...@scigames.at> wrote:

> You can eliminate separators by using
> .mode list
> .sepa ""
>
> But then you need to format your values to the correct widths for the
> record description. Text processing is not the primary domain of SQLite and
> is best left to the presentation layer.
>
> Alternatively you may consider writing a virtual table module to do the
> processing for you. Semantics could be similar to:
>
> CREATE VIRTUAL TABLE mainframe_file USING mainframe ( '',
> '', []); --> open filename, prepare to write in
> specified format, write header
> INSERT INTO mainframe_file SELECT ...; -> stuff in data, write record
> DROP TABLE mainframe_file; -> close the file, write trailer record if
> required
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Don V Nielsen
> Gesendet: Donnerstag, 13. Oktober 2016 20:51
> An: General Discussion of SQLite Database <sqlite-users@mailinglists.
> sqlite.org>
> Betreff: [sqlite] .mode column .width .separator
>
> I am using the command line shell SQLite 3.14.2 2016-09-12. I'm working
> with mainframe data in a fixed format.
>
> I would like to use .mode column to create my output text file in a fixed
> layout. I set all my column widths using .width. I then output my data to a
> file. Unfortunately, there are two blanks separating each column, space I
> don't want to be there. The .separator command does not provide any
> mechanism for turning it off. Is there a way?
>
> I realize there is a printf function available. However, it appears that
> output values must come from a table column, where as below, I could use
> case statements in the sql select of the data.
>
> Any suggestions? I think I am overlooking a .separator option that says
> "don't put spaces between output columns". I assume that John McKown has
> faced this already, given his mainframe pedigree.
>
> Thanks for your time,
> dvn
>
>
>
> Sample output:
> "H  0NZOX0001687395  83501  5827  "...
>
> .mode column
> .width 1 15 5 4 2 1 4 4 1 5 1 10 10 10 1 20 20 15 1 10 20 20 50 50 50 50 50
> 25 2 8 003 006 009
> .output vo_pwprep.txt
> select
>   recid,
>   z_num,
>   zip,
>   zip4,
>   dpbc,
>   ckdig,
>   cart,
>   lot,
>   lot_order,
>   walk_seq,
>   walk_seq_bic,
>   case when piecerate in ('AF','RF') and version_id = '81' then '81' else
> segment end as segment,
>   version_id,
>   message,
>   seed,
>   seed_id,
>   seed_key,
>   planet,
>   ocr_acr,
>   priority,
>   keycode,
>   custno,
>   name,
>   title,
>   firm_id,
>   addr1,
>   addr2,
>   city,
>   state,
>   seq_number,
>   srvc_type,
>   imb_mid,
>   imb_serial
> from address_txt
> ;
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .mode column .width .separator

[Hick Gunter]

You can eliminate separators by using
.mode list
.sepa ""

But then you need to format your values to the correct widths for the record 
description. Text processing is not the primary domain of SQLite and is best 
left to the presentation layer.

Alternatively you may consider writing a virtual table module to do the 
processing for you. Semantics could be similar to:

CREATE VIRTUAL TABLE mainframe_file USING mainframe ( '', 
'', []); --> open filename, prepare to write in 
specified format, write header
INSERT INTO mainframe_file SELECT ...; -> stuff in data, write record
DROP TABLE mainframe_file; -> close the file, write trailer record if required

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Don V Nielsen
Gesendet: Donnerstag, 13. Oktober 2016 20:51
An: General Discussion of SQLite Database <sqlite-users@mailinglists.sqlite.org>
Betreff: [sqlite] .mode column .width .separator

I am using the command line shell SQLite 3.14.2 2016-09-12. I'm working with 
mainframe data in a fixed format.

I would like to use .mode column to create my output text file in a fixed 
layout. I set all my column widths using .width. I then output my data to a 
file. Unfortunately, there are two blanks separating each column, space I don't 
want to be there. The .separator command does not provide any mechanism for 
turning it off. Is there a way?

I realize there is a printf function available. However, it appears that output 
values must come from a table column, where as below, I could use case 
statements in the sql select of the data.

Any suggestions? I think I am overlooking a .separator option that says "don't 
put spaces between output columns". I assume that John McKown has faced this 
already, given his mainframe pedigree.

Thanks for your time,
dvn



Sample output:
"H  0NZOX0001687395  83501  5827  "...

.mode column
.width 1 15 5 4 2 1 4 4 1 5 1 10 10 10 1 20 20 15 1 10 20 20 50 50 50 50 50
25 2 8 003 006 009
.output vo_pwprep.txt
select
  recid,
  z_num,
  zip,
  zip4,
  dpbc,
  ckdig,
  cart,
  lot,
  lot_order,
  walk_seq,
  walk_seq_bic,
  case when piecerate in ('AF','RF') and version_id = '81' then '81' else 
segment end as segment,
  version_id,
  message,
  seed,
  seed_id,
  seed_key,
  planet,
  ocr_acr,
  priority,
  keycode,
  custno,
  name,
  title,
  firm_id,
  addr1,
  addr2,
  city,
  state,
  seq_number,
  srvc_type,
  imb_mid,
  imb_serial
from address_txt
;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .mode column .width .separator

[Don V Nielsen]

Thanks John. I had a feeling you would have encountered this sort of stuff.

And thanks for your time, Simon.

All is appreciated.

And thanks in advance, Dr. Hipp, if you act on this...allowing a \0 row
separator in column mode.  It would simplify the life of us mainframers.

dvn

On Thu, Oct 13, 2016 at 3:31 PM, John McKown 
wrote:

> On Thu, Oct 13, 2016 at 2:42 PM, Don V Nielsen 
> wrote:
>
> > Thanks, but it appears that ".mode column" triggers it, and .separator
> does
> > not appear to have any influence in that mode.
> >
> > Out of curiosity, it appears that the row separator (in windows) is a
> > single character. Do you know to specify as the row separator?
> Everything
> > I attempt is taken as literal characters.  I've attempted: 0x0D0x0A,
> > 0x0D0A, 0Dx0Ax. Nothing appears to work.
> >
>
> ​The row separator is specified using the 2nd parameter of the .separator
> command. Example transcript:
>
>
> SQLite version 3.11.0 2016-02-15 17:29:24
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .mode column
> sqlite> .separator | -
> sqlite> create table a(one text,two text);
> sqlite> .width 10 10
> sqlite> insert into a values('1a','2a');
> sqlite> insert into a values('1b','2b');
> sqlite> select * from a;
> 1a  2a-1b  2b-sqlite>
>
> Note that the specified column separator is ignored in .mode column, but
> the row separator is not. Also, for fun, note what happens with negative
> widths
>
> sqlite> .width -10 -10
> sqlite> select * from a;
> 1a  2a-1b  2b-sqlite>
>
> Also, I have looked at the current sqlite3.c source code. In .mode column,
> the space separator character is "hard coded" and so cannot be set to any
> other character.​
>
> Lastly, you can specify a "control" character by using a C language escape.
> E.g. (continuing from above examples)
>
> sqlite> .separator - \n
> sqlite> select * from a;
> 1a  2a
> 1b  2b
>
> To address your desire, it would be necessary for the column separator
> character to be honored in .mode column mode and the separator be made a
> 0x00, or \0. If Dr. Hipp were to do this, this would eliminate the column
> separator entirely because \0 would result in "no" character between the
> columns. This appears, to me, to be a rather simple change in shell.c.
>
>
> > dvn
> >
> >
>
> --
> Heisenberg may have been here.
>
> Unicode: http://xkcd.com/1726/
>
> Maranatha! <><
> John McKown
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .mode column .width .separator

[John McKown]

On Thu, Oct 13, 2016 at 2:42 PM, Don V Nielsen 
wrote:

> Thanks, but it appears that ".mode column" triggers it, and .separator does
> not appear to have any influence in that mode.
>
> Out of curiosity, it appears that the row separator (in windows) is a
> single character. Do you know to specify as the row separator?  Everything
> I attempt is taken as literal characters.  I've attempted: 0x0D0x0A,
> 0x0D0A, 0Dx0Ax. Nothing appears to work.
>

​The row separator is specified using the 2nd parameter of the .separator
command. Example transcript:


SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode column
sqlite> .separator | -
sqlite> create table a(one text,two text);
sqlite> .width 10 10
sqlite> insert into a values('1a','2a');
sqlite> insert into a values('1b','2b');
sqlite> select * from a;
1a  2a-1b  2b-sqlite>

Note that the specified column separator is ignored in .mode column, but
the row separator is not. Also, for fun, note what happens with negative
widths

sqlite> .width -10 -10
sqlite> select * from a;
1a  2a-1b  2b-sqlite>

Also, I have looked at the current sqlite3.c source code. In .mode column,
the space separator character is "hard coded" and so cannot be set to any
other character.​

Lastly, you can specify a "control" character by using a C language escape.
E.g. (continuing from above examples)

sqlite> .separator - \n
sqlite> select * from a;
1a  2a
1b  2b

To address your desire, it would be necessary for the column separator
character to be honored in .mode column mode and the separator be made a
0x00, or \0. If Dr. Hipp were to do this, this would eliminate the column
separator entirely because \0 would result in "no" character between the
columns. This appears, to me, to be a rather simple change in shell.c.


> dvn
>
>

-- 
Heisenberg may have been here.

Unicode: http://xkcd.com/1726/

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .mode column .width .separator

[Don V Nielsen]

Thanks, but it appears that ".mode column" triggers it, and .separator does
not appear to have any influence in that mode.

Out of curiosity, it appears that the row separator (in windows) is a
single character. Do you know to specify as the row separator?  Everything
I attempt is taken as literal characters.  I've attempted: 0x0D0x0A,
0x0D0A, 0Dx0Ax. Nothing appears to work.

dvn

On Thu, Oct 13, 2016 at 2:11 PM, Simon Slavin  wrote:

>
> On 13 Oct 2016, at 7:51pm, Don V Nielsen  wrote:
>
> > The .separator command does not provide any
> > mechanism for turning it off. Is there a way?
>
> Can't try it now but does
>
> .separator ""
>
> do what you want ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .mode column .width .separator

[Simon Slavin]

On 13 Oct 2016, at 7:51pm, Don V Nielsen  wrote:

> The .separator command does not provide any
> mechanism for turning it off. Is there a way?

Can't try it now but does

.separator ""

do what you want ?

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .mode column .width .separator

[Don V Nielsen]

I can do this, which works. But the redundancy bothers me, and is prone to
finger-check errors.

with pre_process as (
  select
recid,
z_num,
zip,
zip4,
dpbc,
case when piecerate in ('AF','RF') and version_id = '81' then '81' else
segment end as segment,
  ... blah blah ...
  from address_txt
)
select
printf(
'%-1s%-15s%-5s%-4s%-2s%-1s%-4s%-4s%-1s%-5s%-1s%-10s%-10s%-10s%-1s%-20s%-20s%-15s%-1s%-10s%-20s%-20s%-50s%-50s%-50s%-50s%-50s%-25s%-2s%-8s%-003s%-006s%-009s',
recid,
z_num,
zip,
zip4,
dpbc,
segment
  ... blah blah ...
)
from pre_process
limit 10
;


On Thu, Oct 13, 2016 at 2:02 PM, Don V Nielsen 
wrote:

> These are simply blanks, 0x20, use to create separation of the output
> columns. I'm assuming this is an inherent behavior for readability. If the
> output was not being directed to the output file, it would be directed to
> the display.
>
> I'm trying to avoid pre processing (creating a table or view of the
> preprocessed data) and post processing (having to pass 10g of text data to
> removed blanks -- which is dangerous on its own.)
>
> dvn
>
> On Thu, Oct 13, 2016 at 1:56 PM, Simon Slavin 
> wrote:
>
>>
>> On 13 Oct 2016, at 7:51pm, Don V Nielsen  wrote:
>>
>> > Unfortunately, there are two blanks separating each column
>>
>> Can you tell what characters these are ?  Perhaps use a hexdump facility.
>>
>> My guess at this point is that you should continue with the file you have
>> already developed and then post-process it to remove the blank characters.
>> If those characters are used only in the blanks you don't want, it should
>> be possible to use a simple find/replace utility to do it.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .mode column .width .separator

[Don V Nielsen]

These are simply blanks, 0x20, use to create separation of the output
columns. I'm assuming this is an inherent behavior for readability. If the
output was not being directed to the output file, it would be directed to
the display.

I'm trying to avoid pre processing (creating a table or view of the
preprocessed data) and post processing (having to pass 10g of text data to
removed blanks -- which is dangerous on its own.)

dvn

On Thu, Oct 13, 2016 at 1:56 PM, Simon Slavin  wrote:

>
> On 13 Oct 2016, at 7:51pm, Don V Nielsen  wrote:
>
> > Unfortunately, there are two blanks separating each column
>
> Can you tell what characters these are ?  Perhaps use a hexdump facility.
>
> My guess at this point is that you should continue with the file you have
> already developed and then post-process it to remove the blank characters.
> If those characters are used only in the blanks you don't want, it should
> be possible to use a simple find/replace utility to do it.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .mode column .width .separator

[Simon Slavin]

On 13 Oct 2016, at 7:51pm, Don V Nielsen  wrote:

> Unfortunately, there are two blanks separating each column

Can you tell what characters these are ?  Perhaps use a hexdump facility.

My guess at this point is that you should continue with the file you have 
already developed and then post-process it to remove the blank characters.  If 
those characters are used only in the blanks you don't want, it should be 
possible to use a simple find/replace utility to do it.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] .mode column .width .separator

[Don V Nielsen]

I am using the command line shell SQLite 3.14.2 2016-09-12. I'm working
with mainframe data in a fixed format.

I would like to use .mode column to create my output text file in a fixed
layout. I set all my column widths using .width. I then output my data to a
file. Unfortunately, there are two blanks separating each column, space I
don't want to be there. The .separator command does not provide any
mechanism for turning it off. Is there a way?

I realize there is a printf function available. However, it appears that
output values must come from a table column, where as below, I could use
case statements in the sql select of the data.

Any suggestions? I think I am overlooking a .separator option that says
"don't put spaces between output columns". I assume that John McKown has
faced this already, given his mainframe pedigree.

Thanks for your time,
dvn



Sample output:
"H  0NZOX0001687395  83501  5827  "...

.mode column
.width 1 15 5 4 2 1 4 4 1 5 1 10 10 10 1 20 20 15 1 10 20 20 50 50 50 50 50
25 2 8 003 006 009
.output vo_pwprep.txt
select
  recid,
  z_num,
  zip,
  zip4,
  dpbc,
  ckdig,
  cart,
  lot,
  lot_order,
  walk_seq,
  walk_seq_bic,
  case when piecerate in ('AF','RF') and version_id = '81' then '81' else
segment end as segment,
  version_id,
  message,
  seed,
  seed_id,
  seed_key,
  planet,
  ocr_acr,
  priority,
  keycode,
  custno,
  name,
  title,
  firm_id,
  addr1,
  addr2,
  city,
  state,
  seq_number,
  srvc_type,
  imb_mid,
  imb_serial
from address_txt
;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users