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 similar to:
>> >
>> > CREATE VIRTUAL TABLE mainframe_file USING mainframe ( '<fileformat>',
>> > '<filename>', [<header_data>]); --> 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
>> _______________________________________________
>> 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
