This is in list format, what happens if you try to select item from list/index 1 on it, do you get all of it, an error or a single item, 5, returned?
You may need to encode/decode the string to json list structure/object if it is not coming back as a list object (you can find these blocks in the web component)
Maybe you forced each row into text format by assigning the result table into a ListView Elements list?
Show the blocks you use on the results list.
Yes, thanks for both your answers.
The string with brackets I'm receiving is a list. Maybe my problem is where I read the result rather than when I'm saving the rows returned by the Select.
I'm going to build a small test app so I can easily try different things. I think both answers gave me lots to try.
Barry.
Thanks. Using your suggestions, I wrote a small test program and I think I have come up with a solution to how I would like it to work.
In my select statement above each row result from the database is in the form:
[5,Adam at Home,www.comics.com/adam_at_home,0,640]
I'm not quite sure why the brackets are there, but I then execute a For loop to remove the brackets and convert the remaining CSV string into a list. I replace the comiclist Global variable string element with the list.
I can then use a procedure like the above to return the third element (URL) from one of the rows of comiclist.
Barry.
I don't see any evidence that you have learned how to detect and manipulate tables (lists of lists).
Here is a sample doc and app to read:
I have a feeling you are correct – there is something I just don't understand about what I'm seeing.
The database I have is in SQLite. That's not going to change
It's hard to tell from the SQLite documentation, but what I receive from the Select statement is a CSV string surrounded by brackets. The goal is to have a five element list with each element representing one of the returned columns from the Select statement.
The brackets mean something (maybe this is how a list is represented in JSON?). I would like to treat this bracketed string as a list or convert this bracketed string into a list. One way of doing that is the brute force, Rube Goldberg, For loop code I am using. Is there a better way?
Barry.
The result from the database is a table in csv Format
To convert that table into a list of lists, usually you just use the list from csv table block
Taifun
Here is a useful utility (Tidy) to show the structure of a given value:
tidy.aia (3.6 KB)
This works best in conjunction with the AI2 Do It debugger.
P.S. These blocks can be dragged directly into your Blocks Editor workspace.
Thanks. Here is a little block I'm using in my test program:
The local variable csl contains 27 rows in the list (maybe the terminology is 27 Elements). Each row is a string similar to the row shown below as a result of the Select command.
[5, Adam at Home,www.comics.com/adam_at_home,0,640]
Using the "list from table" block, comiclist ends up being a 27 row list with each row in a format similar to:
([5, Adam at Home,www.comics.com/adam_at_home,0,640])
It seems that parentheses are just added around the string contained in each row of the list.
My current solution is to, brute force, remove the brackets from the beginning and ending of each row and treat the remaining string as a CSV string. I then use "replace list item" and "list from CSV row" to split each five item CSV string into a five element list.
This works. It's a little bit clunky. I'm trying to learn if there is a better way.
Thanks,
Barry.
I'm not familiar with that extension... I was assuming, that it is returning a table in csv format as my own sqlite extension...
It seems not the case? you might want to check the documentation or get some support from the author of that extension how to simplify your blocks...
Taifun
Thanks again. I'm hoping the developer of this component is on this forum. I don't see any obvious "contact support" links on his github page.
His documentation states for the Select method:
These methods execute a SQL SELECT statement, with optional bind parameters, that returns a list with zero or more rows of data. See the section below about bind parameters for more information.
Although not shown here, the AfterSelect event is fired from the SelectSQLAsync method when the query is complete.
The list returned by these methods has a row element list for each row matched by the query. The elements in the row element list depend on the ReturnColumnNames property. When this property is true, the elements in the row element list are themselves lists, each with two elements; a column name and a column value. When the property is false, the elements in the row element list are the column values in the same order as the requested columns in the SELECT query.
As can be determined from the last sentence in his description, yes, that is what I'm receiving. But the values are enclosed in square brackets.
It's not really a problem since by brute force method of parsing the result works. Although, it's like a song you can't get out of your head – it's bugging me. Those square brackets must mean something and they must be there for a reason, but I don't know what it is 
Barry.
Could you share your aia project / database file, you can do it via provate message if you do not want to put it on the forum. I can then then take a look at how the extension is outputting your data.
I may have uncovered your issue. The data returned from a query doesn't return a list of lists, it returns an outer list with inner strings. In order to handle this you need to bring in a web component in order to use the JSON DecodeText block, which will convert the output to a list of lists. In the blocks below i have, for simplicity, added the decode block directly to the list.
Try this, and see how you get on.
Thanks.
I probably shouldn't upload the database since a few years ago Google scolded me when I uploaded the database in conjunction with a different app. They said the database may contain links to copyright protected material (it doesn't) – but anyway.
I have written a small test app that simulates two lines as returned from the Select statement.
CSTst2.aia (199.5 KB)
There are two buttons.
The first button performs the calculations that I'm currently doing and shows the results – five values from the five columns of each result row from the Select statement.
The second button, I believe, performs the equivalent of your example above using JsonTextDecode. When the button is pressed it crashes on my test device with the error message:
Bad arguments to length of list
The operation length of list cannot accept the arguments: [(5]
The block quotes must be part of the error statement rather than the value. Just 5 would be the correct answer. I'm not sure why it adds the open parenthesis.
Hopefully this test program will make it easy to check different possibilities.
Barry.
Well, before I even run anything:
You should really make them like this (should be draggable):
or
which both return a list of lists:
[
["5", "Adam at Home", "www.comics.com/adam_at_home", "0", "640"],
["9", "Ants in Pants", "www.comics.com/ants_in_pants", "0", "520"]
]
Your sqlite database appears to be empty (even though it's size is 416kb), so I can't test...
OK, got your sqlite db loaded up.
Seems your output data needs to be encoded then decoded in order to work with it as a list.
These blocks return all the data, then get the first record, encode/decode, then get the second item in the first record (which is the url)
Do It Result: "http://www.gocomics.com/2cowsandachicken"
---
Thanks. "Pretending to be lists" I think is the key.
I see a CSV string enclosed in brackets and I immediately think "ooh, JSON list". But, maybe it's an incorrectly formatted JSON list.
My two strings represent the output of the SQL Select statement. That's not going to change. It's what I have to work with.
My "brute force" method removes the two square brackets and treats the remaining string as a CSV string. I then use the List from CSV row method. From that I get a five item list, as expected.
But since I'm learning I wondered what the correct way to handle this was. The documentation for JsonTextDecode says it takes a Json string input (showing a CSV string enclosed in square brackets) and returns list output. The string I have must not be a valid Json string.
The question is: "what is the best way to decode this string". Maybe my brute force method is as good as I will get.
Barry.
Did you not see what I did to get lists and data out using the encode/decode?
I didn't realize I had forgotten to remove the database from assets.
This is a good exercise for me. I played around with your example more closely and produced another small test app. The disabled blocks show many of the different things I tried. Yours looks like a possibility.
CSTst3.aia (198.0 KB)
Barry.
Remember to set "Lists as JSON" in Screen1 properties.