Problems with SQLite and CREATE TABLE

[lostdreamz]

I am developing my NWN 2 PW based on NWNX4 with the Sqlite plugin while I wait for ODBC plugin. I had a few problems this weekend when trying to create the tables. I am using one table for each one of my campaign databases which works quite nicely. This is about 7 tables that I put into a script to create them.

Surprisingly some of the tables got created and others failed. I changed the order of tables and a few that failed succeeded. I thought it may have something to do with trying to do too many SQL statements all at once so I put a delay of 1 second between each create. Still the same tables failed. In the end I gave up and used SQLite3.exe to grenerate the tables which worked fine with exactly the same SQL which was failing.

Its not actually a problem for me as the tables did get created but it could cause problems for other people sending direct SQL statements via NWNX4.

Thanks

Rebecca Casidy - Owner World Of Desire PW.

[lostdreamz]

Oh here is the script that failed

void CreateAndTest(string name, int exp=0)
{
SendMessageToPC(GetLastUsedBy(), "Creating Table "+name+" for SQLite...");
SQLExecDirect("CREATE TABLE "+name+" (" +
"player varchar(64) NOT NULL default '~'," +
"tag varchar(64) NOT NULL default '~'," +
"name varchar(64) NOT NULL default '~'," +
"val text," +
"expire int(11) default NULL," +
"last timestamp NOT NULL default current_timestamp," +
"PRIMARY KEY (player,tag,name)" +
")");
SetPersistentString(GetModule(),"test","test value",exp,name);
string testRet = GetPersistentString(GetModule(),"test",name);
if (testRet=="test value")
{
SendMessageToPC(GetLastUsedBy(), name+"test passed");
DeletePersistentVariable(GetModule(),"test",name);
}
else
SendMessageToPC(GetLastUsedBy(), name+" test failed");
}

void main()
{
DelayCommand(1.0,CreateAndTest("wod_messages"));
DelayCommand(2.0,CreateAndTest("wod_accounts"));
DelayCommand(3.0,CreateAndTest("wod_bounty"));
DelayCommand(4.0,CreateAndTest("wod_houses"));
DelayCommand(5.0,CreateAndTest("wod_bank"));
DelayCommand(6.0,CreateAndTest("wod_dbclear"));
DelayCommand(7.0,CreateAndTest("wod_mulaccounts"));
DelayCommand(8.0,CreateAndTest("wod_player"));
}

[Papillon]

Thanks for reporting this. I could reproduce the problem... there will likely be a fix on Saturday.
_________________
Papillon

[Papillon]

Nah, the fix comes today, this annoyed me no end.

So, the problem was three-fold:

1) The logfile did not report the actual error, which was something like "database table locked". The interface of SQLite is goofy at this point, so I had to code around this a little. The real error shows up now.

2) The error above was caused by the automatic transaction feature of the plugin. With SQLite, you can only create tables when there is no open transaction running, so you have to COMMIT before creating the tables.

3) The COMMIT in turn did not work (sigh!), because of a remaining open resultset. Due to the change how resultsets work now, there is always one open and you simply can't commit then. I extended the SQLite sources with a new code to denote this error and the plugin now tries to close the resultset when it sees that error.

Long story short - it works now:

[lostdreamz]

Thank you. You are awesome. I tried the commit as well as I thought it might be that but as I said gave up when it didnt work either. I will download the latest version. I love NWNX4.

[Quick_silver]

This Code: