Question Authors' Forum

Problem with results in SQL Questions: output "incomplete"

 
Picture of Sergi García
Problem with results in SQL Questions: output "incomplete"
by Sergi García - Thursday, 9 November 2017, 8:09 AM
 
I have a problem.

I'm creating SQL questions and when the result is too large, it is not appears complete.

For example. In the screen capture appears "San Franci" and it should be "San francisco".

Any idea to solve this?


Picture of Trent Lewis
Re: Problem with results in SQL Questions: output "incomplete"
by Trent Lewis - Thursday, 9 November 2017, 2:58 PM
 

Hi,

Could be an issue with SQLite not giving you the width you want (although it should be the same for the expected and got answers if you are using the sample SQLite commands and version).  You can modified the width of the columns, see below

Trent

https://sqlite.org/cli.html

By default, each column is between 1 and 10 characters wide, depending on the column header name and the width of the first column of data. Data that is too wide to fit in a column is truncated. Use the ".width" dot-command to adjust column widths, like this:

sqlite> .width 12 6
sqlite> select * from tbl1;
one           two   
------------  ------
hello         10    
goodbye       20    
sqlite>

The ".width" command in the example above sets the width of the first column to 12 and the width of the second column to 6. All other column widths were unaltered. You can gives as many arguments to ".width" as necessary to specify the widths of as many columns as are in your query results.

If you specify a column a width of 0, then the column width is automatically adjusted to be the maximum of three numbers: 10, the width of the header, and the width of the first row of data. This makes the column width self-adjusting. The default width setting for every column is this auto-adjusting 0 value.


Picture of Sergi García
Re: Problem with results in SQL Questions: output "incomplete"
by Sergi García - Thursday, 9 November 2017, 9:26 PM
 

It worked!!!

I customized the code, and I changed


prelude = ".mode column\n.headers on\n"

to

prelude = ".mode column\n.headers on\n.width 50 50\n"

Thanks a lot!

Is there any way to fit automatically to the size of data instead writing 50 50 by hand?


Regards, Sergi

Picture of Richard Lobb
Re: Problem with results in SQL Questions: output "incomplete"
by Richard Lobb - Friday, 10 November 2017, 9:09 AM
 

Thanks Trent and Sergi. I've changed the line in the prototype to

prelude = ".mode column\n.headers on\n.width 50 50\n"
as you suggest, Sergi. I'll include this prototype in the next push to github.

Let me know of any other issues.

Cheers

Richard


Picture of Trent Lewis
Re: Problem with results in SQL Questions: output "incomplete"
by Trent Lewis - Friday, 10 November 2017, 12:16 PM
 

...although this will only affect the first two columns.  Do you think it is wise to specify the width of only the first two, when more columns can be returned and those extra columns will be "different"?  Might this cause some confusion?  What if I have a column the is greater than 50?  If I have 5 columns returned the first two will be 50 (which is very wide) and then the others will be the default.

I'd prefer to if it was left at the default column width for the default prelude...but I'll just hack it back to how I want any way ;)

Trent

Picture of Richard Lobb
Re: Problem with results in SQL Questions: output "incomplete"
by Richard Lobb - Friday, 10 November 2017, 1:15 PM
 

Thanks Trent. Indeed, making that the default was downright silly - that's what happens when you copy something in a hurry, without thinking, before heading off for an appointment.

Change reverted.

Ricahrd

Picture of Richard Lobb
Re: Problem with results in SQL Questions: output "incomplete"
by Richard Lobb - Friday, 10 November 2017, 4:58 PM
 

I've added a "columnwidths" template parameter to the SQL question type so you don't need to customise the question. I prefer to avoid customising questions where possible because customised questions no longer inherit from the prototype, so if you want to change that, you usually then have to update all the customised versions as well.

You can now set the template parameter in any question to, say,

{"columnwidths":[10, 20, 15, 5]}

to get nice-looking output.

I attach the updated question type and the examples which use it. Let me know if you find any problems. I've updated the experimental SQL quiz to use this improved prototype, too.

Richard

Picture of Sergi García
Re: Problem with results in SQL Questions: output "incomplete"
by Sergi García - Friday, 10 November 2017, 11:58 PM
 

It is a good improvement, thanks a lot :) :) :)