Retrieving the last column in empty SQLite database results in runtime error

I'm using Taifun's SQLite extension. I have made an SQLite database with 15 columns and default values of NULL. I am retrieving all 15 columns from the SQLite database and converting it to a list using the "list from csv table" block. But when I select the 15th element in the list, I get a runtime error message that the list length is only 14.

The following is the runtime error message:

Runtime Error
Select list item: Attempt to get item number 15 of a list of length 14: ["1", "", "", "", "", "", "", "", "", "", "", "", "", ""]

The following are the blocks:

Please download and post each of those event block(s)/procedures here ...
(sample video)

By using the right click option, we can pull the blocks into out Blocks Editor and examine the contents of that overly long text block.

Alternatively, duplicate that text block with the SELECT statement and separate it into front and back halves for visibility, then JOIN them together at run time.

The blocks still don't show the entire text. I've screen captured the first and last half of the long text. I've also pasted the text as follows:

SELECT ID, C, E, S, M, EFN, EFM, EFPC, EFPEF, TR, MaxS, x, y , z, L

as test set the property IgnoreHeader to false and display the result from the database in a label to find out, what's going on


Trying to push the limits! Snippets, Tutorials and Extensions from Pura Vida Apps by Taifun.

I've set the IgnoreHeader to false to display the result from the database at the 15th (last) column and it shows the proper header of "L".

probably a column in between is missing?
you also could try a SELECT * FROM Main to see, if this makes a difference

it does not seem to be an issue with a reserved word

would you mind posting the result of the select statement i.e. the header row and a data row here in the community so we could take a look to find out, what could be the issue?

btw. you also can use Do it to debug your blocks, see also tip 4 here App Inventor: How to Learn | Pura Vida Apps
see also Live Development, Testing, and Debugging Tools

Trying to push the limits! Snippets, Tutorials and Extensions from Pura Vida Apps by Taifun.

I tried SELECT * FROM Main but I'm still having the same problem.

This is the result of the select statement:
[["ID", "C", "E", "S", "M", "EFN", "EFM", "EFPC", "EFPEF", "TR", "MaxS", "x", "y", "z", "L"], ["1", "", "", "", "", "", "", "", "", "", "", "", "", ""]]

The problem seems to be intermittent at times. Usually at the start of the program and after inserting a couple of new rows. It seems to occur especially when the values are null. If I populate the "L" (last column) with a value, it seems to resolve the issue by having the last column suddenly show up so the list length changes from 14 to 15. Afterwards, it often seems to be okay when I insert another row but the problem would often recur when I insert a second row.

@Taifun, Could this result row termination problem be linked to the code changes for Carriage Return in SELECT results, combined with AI2's treatment of Carriage Return in CSV table parsing? (wild guess)

SQlite Extension

See the App Inventor Extensions document about how to use an App Inventor Extension.

For questions about this extension or bug reports please start a new thread in the App Inventor Extensions forum. Thank you.

For feature requests please contact me by email. To be a sponsor of a new method already is possible starting from only 10 USD! With your contribution you will help the complete App Inventor community. Thank you.

Aug 11th, 2016 : Version 1d: avoid DX execution failed error: build each extension separately

Jul 6th, 2017 : Version 1e: bugfix: remove last Carriage Return for SELECT statement results
Note: using the extension version 1e together with another Pura Vida Apps extension built between July 1st and July 20th might result in error message Unable to find TaifunTools component while opening a project. Use extension version 1f instead.

Jul 21th, 2017 : Version 1f: new build using extension-specific directory in aix file, "More Information" link updated in extension description

Sep 3rd, 2017 : Version 1g: minor bugfix to get correct length of the result after doing a SELECT query

Jan 26th, 2018 : Version 1h: bugfix: double values in SELECT statements will not be truncated anymore

@ABG it looks like you are correct...
I now did a quick test like this

and as you can see, the last empty string is missing after using the list from csv table block

more simplified

the list from csv table block should return ((A B C) (row1 *empty string* *empty string*))
but does return only ((A B C) (row1 *empty string*)), i.e. one empty string is missing

this seems to be a bug in the list from csv table block... @ewpatton what is your opinion?


I'm not an extension writer by any means, but I would
guess that an AI2 extension should adapt to the foibles of its environment,
in this case expecting a \n at the end of the last row of a csv table?

it seems to be there was a change in the logic...

of course I adjust to the system, but up to now a csv table did not need a \n at the end, on the contrary, adding an \n would have resulted in another empty row after converting...
EDIT: see also Q4 here


For lack of a good standard on the format of a CSV table, I would fall back on Postel's Law

follow a general principle of robustness: be conservative in what you do, be liberal in what you accept from others.

Perhaps in the case of constructing a CSV table, it is more conservative to wrap empty strings with "", avoiding the tricky case of a trailing ','?

P.S. This might be connected to AI2's lack of a concept of a NULL value, as opposed to an empty string.

in September 2017 I fixed a bug in the sqlite extension and removed the last carriage return from the result of Select statements... why? see the following example

after selecting something from the database you like to get the result without getting something additional (like a carriage return)... because for example you like to be able to compare a result with something...

the extension is not able to know, what will be done after providing the result...

  1. will the result be used directly (like in the example above), i.e. a carriage return is not desired, or
  2. will the list from csv table block be used afterwards to convert the result into a list of lists (i.e. a carriage return is required for the list from csv table block to work correctly, but only if the last item in the result is an empty string, else the list from csv table block would work correctly...)

Therefore let me suggest @ewpatton to fix the bug in the list from csv row and the list from csv table blocks

list from csv row (a,) should return (a *empty string*)
list from csv table (a,b\nc,) should return ((a b)(c *empty string*))

as workaround for @Jimmy meanwhile let me suggest to make sure, that there are no null values in the last column, if you want to use the list from csv table block after getting the result from the database... just use a SELECT A,B,C, 'something' FROM myTable statement, so the last column is always the text 'something'

I'm open for other suggestions...


This is one of those cases where double quotes come to the rescue.
double quote handling works fine

If you double quote all empty results, there is no need to add any extra terminator to the csv table.

Unquoted non-empty strings pass through the csv table conversion just fine.

thank you for the suggestion, I will think about this tomorrow, if this suggestion will not break things for the other case


One question is whether an empty cell is really the empty string or null? If the latter, replacing empty cells with double quotes potentially changes the meaning.

Meanwhile, I poked around the CsvParser code and figured out a solution that should fix this:

1 Like