SQLite Forum

SQL-Query is always empty
Login

SQL-Query is always empty

(1) By anonymous on 2020-04-16 11:37:34 [link] [source]

Hello guys,
while programming a database I ran into a problem with some SQL-Queries. SqliteConnection conn = new SqliteConnection("data source = " + pathtoDB);

            SqliteCommand selectMaxValue = new SqliteCommand();
            selectMaxValue.Connection = conn;
            selectMaxValue.CommandText = "SELECT myValue FROM myTable;";

            conn.Open();

            SqliteDataReader reader = selectMaxValue.ExecuteReader();
            while (reader.Read())
            {
                int maxValue = int.Parse(reader["myValue"].ToString());
                Console.WriteLine(maxValue);
            }

            conn.Close();

This code gives me the values I want. But if I change the Query to "SELECT MAX(myValue) FROM myTable;" or "SELECT myValue FROM myTable WHERE 'something'" the result in reader is empty.
If I try the queries in a shell it works just fine (so nothing wrong with my database).
Why is it that I can't run other queries?
Thanks in advance

(2) By Keith Medcalf (kmedcalf) on 2020-04-16 13:52:36 in reply to 1 [link] [source]

Because you are asking for the value of something called "myValue" and the other query's do not return anything called "myValue". Try something like:

select max(myValue) AS myValue from myTable;

If you want to access the result using the name "myValue" ...

(5) By anonymous on 2020-04-16 16:09:27 in reply to 2 [link] [source]

This makes perfect sense and if not for now I would have run into that problem later, but my error occures in this SqliteDataReader reader = selectMaxValue.ExecuteReader(); line. While in Debug-Mode I read the value of "reader" after the statement above.
- reader {Microsoft.Data.Sqlite.SqliteDataReader} Microsoft.Data.Sqlite.SqliteDataReader Depth 0 int FieldCount 1 int + Handle {SQLitePCL.sqlite3_stmt} SQLitePCL.sqlite3_stmt HasRows true bool Identity null object IsClosed false bool RecordsAffected -1 int VisibleFieldCount 1 int __identity null object _closeConnection false bool _closed false bool + _command {Microsoft.Data.Sqlite.SqliteCommand} Microsoft.Data.Sqlite.SqliteCommand + _record {Microsoft.Data.Sqlite.SqliteDataRecord} Microsoft.Data.Sqlite.SqliteDataRecord _recordsAffected -1 int + _stmtEnumerator {Microsoft.Data.Sqlite.SqliteCommand.<GetStatements>d__54} System.Collections.Generic.IEnumerator<SQLitePCL.sqlite3_stmt> {Microsoft.Data.Sqlite.SqliteCommand.<GetStatements>d__54} + _timer {System.Diagnostics.Stopwatch} System.Diagnostics.Stopwatch - Ergebnisansicht Beim Aufklappen der Ergebnisansicht wird IEnumerable aufgeführt. Empty "Bei der Auflistung wurden keine Ergebnisse zurückgegeben." string In the last line you can see the value of "reader". It is "empty". Normaly there are as many values in it as in the result of my query. I tried with the first example and I got my 18 results I should get from my db.

(6) By Keith Medcalf (kmedcalf) on 2020-04-16 17:25:26 in reply to 5 [link] [source]

Since the default column name is "max(myValue)" perhaps the wrapper pukes on it. Did you try explicitly naming the column to something using an AS clause?

(7) By anonymous on 2020-04-16 19:27:32 in reply to 6 [source]

Yes, I tried. Still empty.
What do you mean by "pukes on it"? In which way could the wrapper affect my situation?

(8) By Keith Medcalf (kmedcalf) on 2020-04-16 19:46:49 in reply to 7 [link] [source]

For example, I have a wrapper that uses the returned column names to form object attribute names. Attribute names must start with an alphabetic character or underscore, and may contain only alphanumerics and underscores. So a SQL statement such as select max(y) from x will return a single column of results with the name max(y). If you attempted to use this as an attribute name, explosions would occur.

I don't know what you are using to access the returned data so perhaps it does not "like" the parenthesis and just drops the data silently rather than failing quickly and loudly.

(9) By anonymous on 2020-04-18 18:55:39 in reply to 8 [link] [source]

I realy don't know what was wrong.
Since I wanted to use my results in an "INSERT"-Statement I use the problematic part as a subquery in it. It works just fine now.
In it I also added more paranthesis and I also added some numbers in my statement. But the other one still wouldn't work.

(3) By Ryan Smith (cuz) on 2020-04-16 14:22:57 in reply to 1 [link] [source]

When you run your queries outside of your program, in any database viewer or the CLI, note the column name at the top of the list of values - THAT is the value name that sqlite (or any SQL engine) returns, and THAT is the name your (reader["somevalue"]) will be reading.

To prove this, change the query to:

  SELECT myValue AS Cowabunga FROM myTable;

Your original code will now also not work, but then change your code to:

  ....parse(reader["Cowabunga"])

et voila, it will work again because now the returned data column name and the request name match again.

When you request data by name, make sure you use (SELECT ... AS xxx FROM ...) where you read data like (reader["xxx"]).

(4) By Gunter Hick (gunter_hick) on 2020-04-16 14:25:55 in reply to 1 [link] [source]

If a Statement works in the SQLite Shell but not in the program, then the program is at fault.

Parsing a result set for a specific column name without naming the column in the query is a common error. It works most of the time, until the generated name is different from the one expected.

You did not include a terminating semicolon in your second example, so SQLite may have a problem parsng the query. This should show up if you were to check for errors. Or possibly 'something' is never TRUE, leading to an empty result set.