[sqlite] Handle multiple results using sqlite3_step
Neo Anderson
Is it possible to handle multiple results using sqlite3_step or any other API calls?
I want to execute the following SQL in one statement and want to get the two resultsets.
select 1 a; select 2 b;
_______________________________________________
sqlite-users mailing list
sqlite...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Agrawal, Manish
I am getting this error when trying to update a database row in a C# application. The data set ds has been created using the standard Visual Studio/ System.Data.SQLite tools.
The table definition is:
CREATE TABLE "Test" (
"test_Id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE ,
"Points" INTEGER NOT NULL,
"File_Location" VARCHAR NOT NULL
)
The C# code is:
int rowNumber = tBox.rowNumber; // TextBox
ds.Test[rowNumber].Points = (int)points; // from tbox
testAdapter.Update(ds.Test[rowNumber]); // ERROR THROWN HERE
Any help in resolving would be greatly appreciated.
Using Visual Studio ultimate 2010 and sqlite-1.0.79
Thanks
Manish
Igor Tandetnik
> Is it possible to handle multiple results using sqlite3_step or any other API calls?
>
> I want to execute the following SQL in one statement and want to get the two resultsets.
>
> select 1 a; select 2 b;
No. In SQLite, each sqlite3_stmt* handle represents one resultset. If you want two separate resultsets, you need two separate statements.
For this particular example, you can produce one row with two columns instead:
select 1 a, 2 b;
or two rows with one column:
select 1 a union all select 2 a;
Why do you want two resultsets? What is the actual problem you are trying to solve?
--
Igor Tandetnik
Agrawal, Manish
http://www.ismlab.usf.edu/sqlite_errors.jpg
1. Database connection not valid for getting number of changes
At System.Data.SQLite.SQLiteConnection.get_Changes()
2. Database connection not valid for getting last insert rowid
At System.Data.SQLite.SQLiteConnection.get_LastInsertRowId()
3. Database connection not valid for getting maximum memory used
At System.Data.SQLite.SQLiteConnection.get_MemoryHighwater()
4. Database connection not valid for getting memory used
At System.Data.SQLite.SQLiteConnection.get_MemoryUsed()
Workarounds or solutions would be greatly appreciated.
Thanks
Manish
Jay A. Kreibich
>
> Is it possible to handle multiple results using sqlite3_step or any
> other API calls?
sqlite3_exec() is the only API call that will process multiple SQL
statements in a single call. Its use is somewhat discouraged,
however. All output values are converted to strings, losing any type
information. In the case of multiple statements, there is no
deliminator between the result sets. Also, since it does not support
statement parameters *and* processes multiple statements in one pass,
its use is much more prone to significant SQL injection attacks.
As I understand it, the call mainly exists for backwards compatibility
with SQLite2. While the call is not actually deprecated, new
development is encouraged to use the prepare/step/finalize APIs.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
Neo Anderson
Do not be confused by the sample SQL statement. What I really wanted to do is return two different resultsets in one statement and wanted an API to handle the two resultsets. For example:
select * form table1;
select * from table2;
And I do not want to use big union.
By reading other replies I think it's actually not practical to do this.
> To: sqlite...@sqlite.org
> From: itand...@mvps.org
> Date: Sun, 25 Mar 2012 08:42:11 -0400
> Subject: Re: [sqlite] Handle multiple results using sqlite3_step
Petite Abeille
On Mar 25, 2012, at 9:36 PM, Neo Anderson wrote:
> And I do not want to use big union.
Out of curiosity, why is that? SQLite goes to great length to support relational operations. Why not use them?
Neo Anderson
It's just that the two resultsets are not related. What I thought was that I could gain some performance by combining two SELECT into one statement and execute it via one prepare/step.
> From: petite....@gmail.com
> Date: Sun, 25 Mar 2012 21:40:40 +0200
> To: sqlite...@sqlite.org
> Subject: Re: [sqlite] Handle multiple results using sqlite3_step
>
>
Simon Slavin
On 25 Mar 2012, at 8:44pm, Neo Anderson <neo_in...@msn.com> wrote:
> It's just that the two resultsets are not related. What I thought was that I could gain some performance by combining two SELECT into one statement and execute it via one prepare/step.
Ah. Now we understand. No, you can't. Almost all the work that SQLite puts into executing a SELECT is related to the data it finds. There's very little overhead generated just because you're executing a command. So you don't save much by combining those two commands into one. Execute them as two separate commands, either using _prepare() or _exec() as you see fit.
Similarly, you're not saving much time using such a compact representation as
>> select 1 a
Measure the amount of time saved: on a recent computer it's mere microseconds. You might as well use normal full syntax, and be able to understand your code when you read it in a few years' time.
Simon.
Joe Mistachkin
Agrawal, Manish wrote:
>
> 1. Database connection not valid for getting number of changes
> At System.Data.SQLite.SQLiteConnection.get_Changes()
>
> 2. Database connection not valid for getting last insert rowid
> At System.Data.SQLite.SQLiteConnection.get_LastInsertRowId()
>
> 3. Database connection not valid for getting maximum memory used
> At System.Data.SQLite.SQLiteConnection.get_MemoryHighwater()
>
> 4. Database connection not valid for getting memory used
> At System.Data.SQLite.SQLiteConnection.get_MemoryUsed()
>
Those exceptions indicate that the connection has been closed somehow.
--
Joe Mistachkin