I tried your query and it seems to work for me.
From Mono.Data.SqliteClient/SqliteCommand.cs:
public SqliteDataReader ExecuteReader ()
{
return ExecuteReader (CommandBehavior.Default);
}
public SqliteDataReader ExecuteReader (CommandBehavior behavior)
{
int r;
return ExecuteReader (behavior, true, out r);
}
public SqliteDataReader ExecuteReader (CommandBehavior behavior,
bool want_results, out int rows_affected)
{
Prepare ();
// The SQL string may contain multiple sql commands, so the
main
// thing to do is have Sqlite iterate through the commands.
// If want_results, only the last command is returned as a
// DataReader. Otherwise, no command is returned as a
// DataReader.
As you can see, the default behavior supports multiple commands.
I attached a simple test case, that contains your query.
On Mac OS 10.4.8, mono 1.2.2.1 intel 32bit and the latest sqlite
version my test case works as expected.
Both commands get executed, and ExecuteReader() returns a DataReader
containing the results.
--Thomas
Chris Seaton wrote:
Does anyone have experience using Mono.Data.SqliteClient?
It works fine for me apart from when I have multiple Sql statements
in a single command. If I run something like
command = database.CreateCommand();
command.CommandText = "update films set year = year + 10; select
year, title from films where year >= 1980;";
reader = command.ExecuteReader();
I expect both statements to be executed and to read results from the
second statement. This is what the source code seems to imply is what
should happen. When I try it, only the first statement is executed.
Any ideas?
Thanks
Chris Seaton
_______________________________________________
Mono-list maillist - [email protected]
http://lists.ximian.com/mailman/listinfo/mono-list
using System;
using System.Data;
using System.IO;
using System.Text;
using Mono.Data.SqliteClient;
namespace MonoTests.Mono.Data.SqliteClient
{
public class SqliteCommandUnitTests
{
readonly static string uri = "SqliteTest.db";
readonly static string connectionString = "URI=file://" + uri + ", version=3";
static SqliteConnection conn = new SqliteConnection (connectionString);
[STAThread]
static void Main(string[] args)
{
Create();
InsertWithTransaction();
Select();
}
public static void Create()
{
try
{
if(File.Exists(uri))
{
conn.Dispose();
File.Delete(uri);
}
}
catch(Exception e)
{
throw e;
}
SqliteCommand createCommand = new SqliteCommand("CREATE TABLE FILMS(TITLE TEXT, YEAR INTEGER)",conn);
SqliteCommand insertCommand = new SqliteCommand("INSERT INTO FILMS (TITLE, YEAR) VALUES('A Clockwork Orange', 1971)",conn);
try
{
conn.Open();
createCommand.ExecuteNonQuery();
insertCommand.ExecuteNonQuery();
}
catch(Exception e)
{
throw new Exception("Sqlite CREATE failed",e);
}
finally
{
conn.Close();
}
}
public static void Select()
{
SqliteCommand simpleSelect = new SqliteCommand("UPDATE FILMS SET YEAR = YEAR + 10; SELECT YEAR, TITLE FROM FILMS WHERE YEAR >= 1980;", conn);
using(conn)
{
conn.Open();
SqliteDataReader dr = simpleSelect.ExecuteReader();
while(dr.Read())
{
string test = dr[0].ToString();
Console.WriteLine(test);
}
}
}
public static void InsertWithTransaction()
{
conn.Open();
SqliteTransaction t = conn.BeginTransaction() as SqliteTransaction;
SqliteCommand c1 = new SqliteCommand("INSERT INTO FILMS VALUES ('The Departed',2006)",conn,t);
SqliteCommand c2 = new SqliteCommand("INSERT INTO FILMS VALUES ('City of God',2002)",conn,t);
SqliteCommand c3 = new SqliteCommand("INSERT INTO FILMS VALUES ('The usual Suspects',1995)",conn,t);
SqliteCommand c4 = new SqliteCommand("INSERT INTO FILMS VALUES ('Rear Window',1954)",conn,t);
using(conn)
{
try
{
c1.ExecuteNonQuery();
c2.ExecuteNonQuery();
c3.ExecuteNonQuery();
c4.ExecuteNonQuery();
t.Commit();
}
catch(Exception e)
{
t.Rollback();
throw new Exception("Sqlite INSERT (with transaction) failed", e);
}
}
}
}
}_______________________________________________
Mono-list maillist - [email protected]
http://lists.ximian.com/mailman/listinfo/mono-list
