The online racing simulator
[OT] SQLite + C#
(23 posts, started )
#1 - JamesF1
[OT] SQLite + C#
I've been searching for the answer to this problem for a day or two now, I've read hundreds of pages from Google searches, and I just must be overlooking something.

I'm using managed-sqlite to access an SQLite v3 database (not that that's relevant, as I'm just obviously doing something dumb). SELECT works fine, using .ExecuteQuery() on the database. However, trying to use .ExecuteDML() to do anything, like a DELETE or an INSERT just doesn't work. (Edit: I suppose I should mention it *appears* to change the database in memory/temporary store, but doesn't save the database changes across launches of the program.)

For example:
db.BeginTransaction();

int q = db.ExecuteDML("DELETE FROM conditions WHERE id=" + rowData[0]);

db.CommitTransaction();

As you can see, I've tried BEGIN'ing/COMMIT'ing the transaction at relevant positions, but I get a "cannot commit transaction - SQL statements in progress" error at the COMMIT.

I'm pulling my hair out - please point out my entirely obvious errors

Thanks in advance!
#2 - NotAnIllusion
No idea, but.. it claims there's at least one SQL statement in progress, so I guess recheck that you've ended all the previous ones correctly. From what I understood from the non-existent docs on the Google code page is that you have to commit each transaction before starting a new one. Also check that you have write access and you opened the file as write (if there's any distinction).
#3 - JamesF1
I've tried doing begin/commit transaction around the other two SQL statements in the program (both SELECT statements), but to no avail. The error just ends up being whenever the second commit gets executed.
#4 - amp88
You shouldn't use transactions to perform select statements (they don't change the state of the database). Do you have 2 commit transaction statements in one query?

Could you post the part of your code that's causing the problem?
#5 - JamesF1
I was 99% certain that was the case, I only tried it to see if there was some form of solution.

Here's some code...

Database setup (done on program load):
Database db = new Database();
db.Open("conditions.s3db");

Then I have two select statements, which work perfectly.

Delete DML (this is the bit that doesn't work):
(Just to note, I *know* I don't need a transaction for this single DML - but it was to illustrate... as it doesn't work without the transaction, either - it simply returns 1 affected row [as it should], but on next program load, it actually doesn't change the database itself)
try
{
db.BeginTransaction();

int q = db.ExecuteDML("DELETE FROM conditions WHERE id=" + rowData[0]);

[color=red]db.CommitTransaction();[/color]

}
catch (ManagedSQLite.Exception msqle) {
//Debug... actually handle this properly (when it works)
MessageBox.Show(msqle.ToString());
}

I'm constantly getting thrown into the catch with the exception:
ManagedSQLite.Exception: cannot commit transaction - SQL statements in progress
at ManagedSQLite.Database.ExecuteQuery(String szSQL)
at ManagedSQLite.Database.CommitTransaction()
at MMES.MMES.but_removeCondition_Click(Object sender, EventArgs e) in <path>:line 61

Line 61 is the line I've highlighted in red above.
#6 - amp88
Have you tried printing out the value of q before the commit statement? I haven't used SQLite so I'm sort of poking in the darkness with a blunt stick, but if you try printing the value of q and it doesn't print as you expect (should be the number of rows the delete statement effected) then it could be possible the commit is being performed before the delete statement has completed (not sure exactly how that could happen but it's the only thing I can think of at the moment...).
#7 - JamesF1
I have tried, yes... it returns as 1 (as it should, as I'm deleting a single record each time).
#8 - JamesF1
Sorry to push this, but has anyone else got any ideas? I've tried all sorts of ways but it just won't perform the delete properly
#9 - mcgas001
hmmm...out of intrest, why do you do .ExecuteDML()? When ever ive worked with database's (MySql/MsSql) ive always done .ExecuteQuery() and that seemed to work fine.(unless of course i was using .Net 3.5 in which case id use LinQ). Seems strange to me why you use that. Unless im totally missing something.....
#10 - JamesF1
Well, in this particular wrapper, ExecuteDML() returns the number of rows affected as an integer, rather than returning a Query object (as ExecuteQuery() does). So, I simply use ExecuteDML() to get back the number of affected rows more easily

Obviously, for SELECT, you need to use ExecuteQuery() so you can iterate through the returned rows.
#11 - mcgas001
OK, I see. Well, Is there anything forcing you to use that wrapper? If not maybe get another one. Like this?
#12 - JamesF1
There's nothing forcing me, no... but this doesn't seem like a problem with library... that's why I'm puzzled. I may convert it to SQLite.NET later... as that's what I've used on previous projects

Regardless, I'm still interested to work out why this doesn't work *shrug*
#13 - mcgas001
Quote from JamesF1 :Regardless, I'm still interested to work out why this doesn't work *shrug*

Sorry i couldnt help . I cant even find the SQLite server to test it. So i was just looking at the wrapper and trying to relate it back to MySql -Bad idea
#14 - JamesF1
SQLite doesn't use a server. SQLite uses a single database file, to enable it to be entirely portable and embeddable
#15 - mcgas001
So, its like MsSql Compact edition then?
#16 - JamesF1
Similar, yes.
#17 - mcgas001
Quote from JamesF1 :Similar, yes.

LOL, Sorry for spamming your thread, and sorry i couldnt help.

E: Idiot moment :\
#18 - NotAnIllusion
Quote :void CommitTransaction() Ends and commits the transaction to the database file.
void EndTransaction() Ends and commits the transaction to the database file.

They're the same thing, afaik.
#19 - mcgas001
Acutally, slightly off topic here. Im amazed at this and the way it works. Its like a little .dll file and thats the db. Is this totally portable? Like i wont EVER need to install anything on another pc to run it? Just move the file with the program. If so ill use this a LOT more!


Also, Just tried the following...

Db.Open("C:\\Documents and Settings\\Sean\\Desktop\\test.db");

Db.BeginTransaction();

Db.ExecuteDML("create table test(one varchar(24), two varchar(24))");

Db.EndTransaction();

...and that worked fine.
#20 - JamesF1
Well yes, that's exactly how it works. Take the dll (or include the source in your project), and that's all you need to access the database.

As for the query... peculiar that it works... maybe the wrapper is buggy.
#21 - the_angry_angel
Quote from JamesF1 :As for the query... peculiar that it works... maybe the wrapper is buggy.

tbh, I'd be leaning towards that as well. I've used sqlite in the past (granted not a .NET library, which is my point I guess) and not had this issue
#22 - NotAnIllusion
I did find various mentions of [open db file] cursor related problems, so might well be.
#23 - JamesF1
Works perfectly with SQLite.NET. The reason I didn't use that to begin with was I'm not really a fan of how it wraps SQLite... it's a bit feature-barren in lots of ways

[OT] SQLite + C#
(23 posts, started )
FGED GREDG RDFGDR GSFDG