Re: [sqlite] column output truncating
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/23/2010 08:42 AM, Derek Martin wrote: > It would be swell if sqlite3 had a -notruncate option or some such, You do know that the code is public domain, so you can copy it, modify it, redistribute it, use it to take over the world, charge for it or translate it into elven without anyone else's permission or control? The columns are output using printf. Grab a copy of the shell code (one file: shell.c) and make it behave however you want to. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkvR16kACgkQmOOfHg372QR1iwCdEa7s6fgWuzCrtcTx/LIWZdid wu0An0FQij9iLf264iaDOb2YHDajDluA =Yu3b -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column output truncating
On Fri, Apr 23, 2010 at 05:43:21PM +0100, Simon Davies wrote: > Pick the right width for the columns as per the following [...] Thanks, this might be helpful. -- Derek Martin Software Quality Assurance Engineer Akamai Technologies demar...@akamai.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column output truncating
On Fri, Apr 23, 2010 at 11:04:01AM -0500, P Kishor wrote: > On Fri, Apr 23, 2010 at 10:42 AM, Derek Martin wrote: > > Column mode (without any truncation) is the format desired for these > > reports... Picking large enough values is bad, because it means that > > I'd need to specify very large column widths for most of the columns, > > which in many cases would needlessly make the report tediously wide, > > to accomodate some less common cases where the reports generate very > > wide columns. Avoiding this is sort of the point of databases doing > > this for you... ;-) > > > Well, no... the job of a database is to store your data, By "this" I meant "producing columnar output..." > A perfect and most minimal database will preserve your data > integrity 100%. It will give you back whatever you put in, without any > interpretation. But sqlite3 doesn't do that... it truncates my fields to 10 chars. :) That's a rather poor interpretation of my data, as it turns out. > All the formatting that you want to do, you can and should do via > your client program. Right... sqlite3 is my client program. :) The format we want is the format already provided by virtually every SQL database's command-line front end. This *includes sqlite3*, except that at some arbitrary point which I have not determined and can't find any documentation about, sqlite3 starts truncating fields to 10 characters. I don't want to write code to duplicate this functionality, because lots of stuff already provides it, *including sqlite* -- sometimes. It's totally wasted effort for me to rewrite all that, and will slow down my program senselessly. > > It would be swell if sqlite3 had a -notruncate option or some such, > > What version of sqlite are you using? Whichever one is installed by my company on my desktop, which in this case is 3.4.2. I mention that because it's an important point... for our purposes, we need to use tools that are readily available by everyone who needs to work with this stuff. > Funnily, I have a table with lots of running text, and even though I > specify .mode column, I don't see any truncation at all. As was the case for me... until I did an outer left join on another table, adding 3 more columns, 2 of which range in size from about 30 to 50 characters. Like I said, this is an arbitrary -- and in my mind highly undesirable -- limitation which does not seem to be well documented. > Of course, the text wraps around in my terminal window and makes > everything look like nonsense, but that is another story. For this, there is (GNU) less -S. :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column output truncating
On 23 April 2010 16:42, Derek Martin wrote: > On Fri, Apr 23, 2010 at 08:52:37AM -0500, Jay A. Kreibich wrote: >> On Thu, Apr 22, 2010 at 06:31:07PM -0400, Derek Martin scratched on the wall: >> > Hi, > > Sadly that was the conclusion I had reached before asking here. > Needless to say, that was not the answer I was hoping for. > > Column mode (without any truncation) is the format desired for these > reports... Picking large enough values is bad, because it means that > I'd need to specify very large column widths for most of the columns, Pick the right width for the columns as per the following rem rem Produce tst db rem echo create table tst( a text, b text, c text );> creTable.sql echo insert into tst values( '', '22', '3' );>> creTable.sql echo insert into tst values( '111', '2', '33' );>> creTable.sql echo insert into tst values( '111', '2', '' );>> creTable.sql echo insert into tst values( '1', '2', '' );>> creTable.sql echo insert into tst values( '111', '22', '333' );>> creTable.sql echo insert into tst values( '111', '', '33' );>> creTable.sql echo insert into tst values( '', '222', '333' );>> creTable.sql sqlite3 tst.db < creTable.sql rem rem Generate report from tst db rem sqlite3 tst.db "select '.width ' || max( length( a ) ) ||', ' || max( length( b ) ) ||', ' || max( length( c ) ) from tst;" > setWidth.sql echo .mode column> report.sql echo .read 'setWidth.sql'>> report.sql echo .output rpt.txt>>report.sql echo select * from tst;>> report.sql sqlite3 tst.db < report.sql > which in many cases would needlessly make the report tediously wide, > to accomodate some less common cases where the reports generate very > wide columns. Avoiding this is sort of the point of databases doing > this for you... ;-) > > It would be swell if sqlite3 had a -notruncate option or some such, > though adding one now wouldn't really solve my immediate problem, > since that wouldn't be available on our desktop image. With its > current behavior, column mode seems fairly worthless to me. I guess > I'll need to convince folks that HTML output is suitable, or that they > need to install some other database thingy to run this stuff. Using > sqlite3 for this seemed perfect, since it's already available to > everyone. > > Thanks > Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column output truncating
On 23 Apr 2010, at 4:42pm, Derek Martin wrote: > It would be swell if sqlite3 had a -notruncate option or some such, > though adding one now wouldn't really solve my immediate problem, > since that wouldn't be available on our desktop image. With its > current behavior, column mode seems fairly worthless to me. I guess > I'll need to convince folks that HTML output is suitable, or that they > need to install some other database thingy to run this stuff. Using > sqlite3 for this seemed perfect, since it's already available to > everyone. sqlite3 is meant more as a sort of programmer's admin utility than for use by non-programmers. And anyone who knows how to use sqlite3 should know how to use programs like awk which were designed explicitly to massage output formats in the way you describe. If you are intending to produce reports suitable for non-techies, you need to capture a programmer and bribe them with pizza to write software for you. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column output truncating
On Fri, Apr 23, 2010 at 10:42 AM, Derek Martin wrote: > On Fri, Apr 23, 2010 at 08:52:37AM -0500, Jay A. Kreibich wrote: >> On Thu, Apr 22, 2010 at 06:31:07PM -0400, Derek Martin scratched on the wall: >> > Hi, >> > >> > I have a query that produces about 10 columns, some of which are very >> > wide. When I run sqlite3 with -column -header, it truncates all >> > fields to 10 characters. This makes the query absolutely useless. >> > How can I turn off this truncation? >> > >> > I already tried explicitly setting the width to 0, that did not help. >> >> Pick large enough values, or don't use column mode. > > Sadly that was the conclusion I had reached before asking here. > Needless to say, that was not the answer I was hoping for. > > Column mode (without any truncation) is the format desired for these > reports... Picking large enough values is bad, because it means that > I'd need to specify very large column widths for most of the columns, > which in many cases would needlessly make the report tediously wide, > to accomodate some less common cases where the reports generate very > wide columns. Avoiding this is sort of the point of databases doing > this for you... ;-) Well, no... the job of a database is to store your data, and then hand it back to you as fast as possible without changing the data in anyway. A perfect and most minimal database will preserve your data integrity 100%. It will give you back whatever you put in, without any interpretation. A perfect database is like a perfect audio speaker -- the latter would not add treble or bass to your audio... it will reproduce the audio exactly as it was meant to be. On top of the above, sqlite is a very minimalist db library. sqlite3 shell is a c program that allows you to access your data stored in the sqlite db file via the sqlite db library. Think of the sqlite3 shell as a client program that the sqlite developer wrote to get to the sqlite db. All the formatting that you want to do, you can and should do via your client program. You can have a choice of many different languages to program one for yourself. Or you can probably even pay and purchase a few client programs that others have written. > > It would be swell if sqlite3 had a -notruncate option or some such, What version of sqlite are you using? Funnily, I have a table with lots of running text, and even though I specify .mode column, I don't see any truncation at all. Of course, the text wraps around in my terminal window and makes everything look like nonsense, but that is another story. > though adding one now wouldn't really solve my immediate problem, > since that wouldn't be available on our desktop image. With its > current behavior, column mode seems fairly worthless to me. I guess > I'll need to convince folks that HTML output is suitable, or that they > need to install some other database thingy to run this stuff. Using > sqlite3 for this seemed perfect, since it's already available to > everyone. >d The "database thingy" you need is a client program. You could try the one written in XUL, the Firefox add-on, which, while not very attractive, actually works quite well. It is also free as in both speech and beer, so you could probably modify it to suit your needs. > Thanks > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column output truncating
On Fri, Apr 23, 2010 at 08:52:37AM -0500, Jay A. Kreibich wrote: > On Thu, Apr 22, 2010 at 06:31:07PM -0400, Derek Martin scratched on the wall: > > Hi, > > > > I have a query that produces about 10 columns, some of which are very > > wide. When I run sqlite3 with -column -header, it truncates all > > fields to 10 characters. This makes the query absolutely useless. > > How can I turn off this truncation? > > > > I already tried explicitly setting the width to 0, that did not help. > > Pick large enough values, or don't use column mode. Sadly that was the conclusion I had reached before asking here. Needless to say, that was not the answer I was hoping for. Column mode (without any truncation) is the format desired for these reports... Picking large enough values is bad, because it means that I'd need to specify very large column widths for most of the columns, which in many cases would needlessly make the report tediously wide, to accomodate some less common cases where the reports generate very wide columns. Avoiding this is sort of the point of databases doing this for you... ;-) It would be swell if sqlite3 had a -notruncate option or some such, though adding one now wouldn't really solve my immediate problem, since that wouldn't be available on our desktop image. With its current behavior, column mode seems fairly worthless to me. I guess I'll need to convince folks that HTML output is suitable, or that they need to install some other database thingy to run this stuff. Using sqlite3 for this seemed perfect, since it's already available to everyone. Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column output truncating
On Thu, Apr 22, 2010 at 06:31:07PM -0400, Derek Martin scratched on the wall: > Hi, > > I have a query that produces about 10 columns, some of which are very > wide. When I run sqlite3 with -column -header, it truncates all > fields to 10 characters. This makes the query absolutely useless. > How can I turn off this truncation? > > I already tried explicitly setting the width to 0, that did not help. Pick large enough values, or don't use column mode. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column output truncating
On Fri, Apr 23, 2010 at 8:39 AM, Adam DeVita wrote: > Could you include a bit more information about your post? (Version number, > operating system etc.) > > I'm unsure if you have compiled something or are using the command line > tool. > > There are lots of very knowledgeable and helpful people on the list. > > On Thu, Apr 22, 2010 at 6:31 PM, Derek Martin wrote: > >> Hi, >> >> I have a query that produces about 10 columns, some of which are very >> wide. When I run sqlite3 with -column -header, it truncates all >> fields to 10 characters. This makes the query absolutely useless. >> How can I turn off this truncation? >> >> I already tried explicitly setting the width to 0, that did not help. >> You have to set the width of every column with .width. I don't know the exact syntax of .width and how to effectively turn .width constraint off. You might try .mode line, which will print one value per line. Better yet, ditch the command line tool for these kind of things and use something like Perl, Python or Tcl to work with your db. If you are good with bash, you can also do a lot of magic by querying a sqlite db via shell scripts. >> Thanks. >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > VerifEye Technologies Inc. > 905-948-0015x245 > 7100 Warden Ave, Unit 3 > Markham ON, L3R 8B5 > Canada > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column output truncating
Could you include a bit more information about your post? (Version number, operating system etc.) I'm unsure if you have compiled something or are using the command line tool. There are lots of very knowledgeable and helpful people on the list. On Thu, Apr 22, 2010 at 6:31 PM, Derek Martin wrote: > Hi, > > I have a query that produces about 10 columns, some of which are very > wide. When I run sqlite3 with -column -header, it truncates all > fields to 10 characters. This makes the query absolutely useless. > How can I turn off this truncation? > > I already tried explicitly setting the width to 0, that did not help. > > Thanks. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] column output truncating
Hi, I have a query that produces about 10 columns, some of which are very wide. When I run sqlite3 with -column -header, it truncates all fields to 10 characters. This makes the query absolutely useless. How can I turn off this truncation? I already tried explicitly setting the width to 0, that did not help. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
