This is a mixed bag of nuts, all that I have found so far with the mono.data.sqliteclient in all subfolders under the editor are libraries that only support v2 of sqlite, BUT they all also require sqlite3.dll when used, all of them expect a “URI=file:somefile.db”, but when you try to do an insert, they expect UNICODE, but when I try to supply that in the connection string for encoding=unicode, I still get errors, what is the correct connection string with the new U3 to use the Sqliteclient that is provided?
What version of mono is in use? Apparently there is a bug in mono.data.sqliteclient and mono.data.sqlite in certain version of mono which explains this:
System.Security.VerificationException: Error verifying Mono.Data.SqliteClient.Sqlite:HeapToString (intptr,System.Text.Encoding): Incompatible parameter value with constructor signature: sbyte* X void* (Native Pointer) at 0x003f
at Mono.Data.SqliteClient.SqliteCommand.BindParameters3 (IntPtr pStmt) [0x00000] in <filename unknown>:0
at Mono.Data.SqliteClient.SqliteCommand.ExecuteReader (CommandBehavior behavior, Boolean want_results, System.Int32 rows_affected) [0x00000] in <filename unknown>:0
at Mono.Data.SqliteClient.SqliteCommand.ExecuteNonQuery () [0x00000] in <filename unknown>:0
Which is discussed in article:
http://www.mail-archive.com/mono-patches@lists.ximian.com/msg10794.html
The file is getting created but the mono bug doesn’t see the file, the connection string I am using is as follows:
string constr = "URI=file:gamedata.db";
This statement creates the database as it should
string sql= "CREATE TABLE IF NOT EXISTS gamedata (guid varchar(64),lastIP varchar(32)); ";
But this statement failed with the above error and is part of the mono bug
string sqlInsert = "INSERT INTO gamedata (guid, lastip) Values (?, ?); ";
Combined code that creates the database, the table, and should insert the data is as follows
// game table
string sql= "CREATE TABLE IF NOT EXISTS gamedata (guid varchar(64),lastIP varchar(32)); ";
string sqlInsert = "INSERT INTO gamedata (guid, lastip) Values (?, ?); ";
Mono.Data.SqliteClient.SqliteConnection con = new SqliteConnection(constr);
Mono.Data.SqliteClient.SqliteCommand cmd = new SqliteCommand(sql,con);
Mono.Data.SqliteClient.SqliteCommand cmdInsert = new SqliteCommand(sqlInsert, con);
Debug.Log(con.Encoding);
SqliteParameter p0 = new SqliteParameter();
p0.ParameterName = "@pguid";
p0.DbType = DbType.Object;
p0.Value = p.guid;
SqliteParameter p1 = new SqliteParameter();
p1.ParameterName = "@plastip";
p1.DbType = DbType.Object;
p1.Value = p.ipAddress;
cmdInsert.Parameters.Add(p0);
cmdInsert.Parameters.Add(p1);
try
{
Debug.Log("opening con");
con.Open();
Debug.Log("executing create");
cmd.ExecuteNonQuery();
Debug.Log("executing insert");
cmdInsert.ExecuteNonQuery();
Debug.Log("closing connection");
con.Close();
Debug.Log("Table created");
}
catch(Exception ex)
{
Debug.Log(ex.ToString());
}
Can someone please verify this problem?
Its mono 2.6.x thats in use in U3 since b2 or b3, as a consequence code that worked before might not needfully work directly anymore.
So sqlite is dead at the moment?
Zumwalt did you link to the correct thing in your post? Was interested in reading it but when I click on your url, it brings me to a .diff from back in 2006 that appears to add unicode support.
Good question, I had gone through a ton of threads in their lists to track this thing down, I will go back through them when I get back on that machine tomorrow to make sure it was the right thread post. Doesn’t matter what I do with sqlite or sqliteclient at the moment, I can not do any inserts at all without errors.
Gah, I can’t find the other thread talking about the bug that was dated january of this year :(, well doing more research based on knowing that Unity b2/b3 is using 2.6x, I have found out that 2.8 removed Mono.Data.SqliteClient
http://www.mono-project.com/Release_Notes_Mono_2.8
Now, this doesn’t bode well even if I switch to just using the Mono.Data.Sqlite, I am still required to use the System.Data that is included which now that Mono.Data is deprecated and removed based on the System.Data, the System.Data.DbType is a required element for the SqliteParameter, this is the hangup, no matter if I use System.String aka DbType.String, the Mono.Data.Sqllite command parameter for varchar through what Unity has in place, is expecting unicode to the database, however, what ever version of sqlite is in Unity is not passing unicode, Sqlite3 by default is unicode characterset.
Furthermore:
http://www.mono-project.com/Release_Notes_Mono_2.6
According to release notes for 2.6, a new connector is in place for sqlite, which is the DbLinqProvider, which I have not gotten to work this morning, not as of yet, in U3, I have filed a bug report on the sqlite issue, going to try to see if I can get DbLinqProvider to work today so I can get moving a little further with this.
No good, here is the error:
System.Security.VerificationException: Error verifying Mono.Data.Sqlite.SqliteConvert:UTF8ToString (intptr): Incompatible parameter value with constructor signature: sbyte* X void* (Native Pointer) at 0x0032
at Mono.Data.Sqlite.SqliteConvert.ToString (IntPtr nativestring) [0x00000] in <filename unknown>:0
at Mono.Data.Sqlite.Sqlite3.SqliteLastError () [0x00000] in <filename unknown>:0
at Mono.Data.Sqlite.Sqlite3.Close () [0x00000] in <filename unknown>:0
at Mono.Data.Sqlite.SqliteConnection.Close () [0x00000] in <filename unknown>:0
at DbLinq.Data.Linq.Database.Implementation.DatabaseConnection.Dispose () [0x00000] in <filename unknown>:0
at DbLinq.Data.Linq.Database.Implementation.TransactionalCommand.Dispose () [0x00000] in <filename unknown>:0
at DbLinq.Data.Linq.Sugar.Implementation.QueryRunner.Execute (DbLinq.Data.Linq.Sugar.DirectQuery directQuery, System.Object[] parameters) [0x00000] in <filename unknown>:0
at System.Data.Linq.DataContext.ExecuteCommand (System.String command, System.Object[] parameters) [0x00000] in <filename unknown>:0
at RakConnector.dbLINQ () [0x00042] in RakConnector.cs:83
UnityEngine.Debug:Log(Object)
RakConnector:dbLINQ() (at Assets/CSScripts/RakConnector.cs:88)
RakConnector:Start() (at Assets/CSScripts/RakConnector.cs:26)
To test, add the System.Data.Linq to your project asset folder, add it to the Unity Editor folder, you can grab it from:
C:\Program Files\Unity\Editor\Data\Mono\lib\mono\2.0
Also, you will probably need to grab System.dll and System.Data.dll, you will also need to download sqlite3.dll and place it both in your editor root folder at:
C:\Program Files\Unity\Editor
So you will be adding 3 files to the editor folder from the mono\2.0 folder
System.dll
System.Data.dll
System.Data.Linq.dll
You also have to have the sqlite3.dll from
http://www.sqlite.org/download.html
Once you have that file and placed it in your Editor folder, also place all 4 files in your Asset folder, otherwise Unity will not know what System is, what System.Data is, what System.Data.Linq is and would throw errors about missing sqlite3 library, if you build to a folder, copy all 4 files to your game folder where your game executable is or the game will blow up.
Unfortunately, Inserts / Updates and Deletes do not work at the moment, only the create table works.
Here is the code for using sqlite or linq, call either method from the Start() routine in Unity.
using UnityEngine;
using System;
using System.Collections;
using Mono.Data.Sqlite;
using System.Data.Linq;
using System.Data;
public class RakConnector : MonoBehaviour {
// HERE we have a list of our server ports
// this just shows a cascade drop and reconnect
// using custom raknet server to Unity client
int[] iplist = { 55000, 50000, 45000 };
int lastconnect = 0;
string constr = "URI=file:gamedata.db,Version=3";
//string constr = "Data Source=|DataDirectory|gamedata.db;Version=3;";
NetworkPlayer p;
void Start () {
Network.Connect("71.20.75.212", iplist[0], "Rumpelstiltskin");
p = Network.player;
Debug.Log("Your Network Connection: " + p.ipAddress);
Debug.Log("Your GUID: " + p.guid);
lastconnect++;
dbLINQ();
dbSqlite();
}
void Update() { }
void OnDisconnectedFromServer(NetworkDisconnection info)
{
Debug.Log("We have disconnected from server:"+ info.ToString());
// try to connect to next server
Network.Connect("71.20.75.212", iplist[lastconnect], "Rumpelstiltskin");
switch (lastconnect)
{
case 0:
lastconnect = 1;
break;
case 1:
lastconnect = 2;
break;
case 2:
lastconnect = 0;
break;
}
}
void OnConnectedToServer()
{
Debug.Log("We have connected to server");
}
[RPC]
void clientRPC(string value)
{
Debug.Log("RPC MESSAGE RECEIVED: " + value);
}
// fails on the parameters
void dbLINQ()
{
var conn = new SqliteConnection("DbLinqProvider=Sqlite;Data Source=gamedata.db3");
var db = new DataContext(conn);
// game table
string sql = "CREATE TABLE IF NOT EXISTS gamedata (guid varchar(64),lastIP varchar(32)); ";
string sqlInsert = "INSERT INTO gamedata (guid, lastip) Values (?, ?); ";
object[] iParams = { p.guid, p.ipAddress };
try
{
db.ExecuteCommand(sql);
db.ExecuteCommand(sqlInsert, iParams);
}
catch (Exception ex)
{
Debug.Log(ex.ToString());
}
finally
{
db.Connection.Close();
db.Connection.Dispose();
}
}
// fails on parameters, no good
void dbSqlite()
{
// game table
string sql = "CREATE TABLE IF NOT EXISTS gamedata (guid varchar(64),lastIP varchar(32)); ";
string sqlInsert = "INSERT INTO gamedata (guid, lastip) Values (?, ?); ";
SqliteConnection con = new SqliteConnection(constr);
SqliteCommand cmd = new SqliteCommand(sql, con);
SqliteCommand cmdInsert = new SqliteCommand(sqlInsert, con);
SqliteParameter p0 = new SqliteParameter();
p0.ParameterName = "@pguid";
p0.DbType = DbType.String;
p0.Value = p.guid;
SqliteParameter p1 = new SqliteParameter();
p1.ParameterName = "@plastip";
p1.DbType = DbType.String;
p1.Value = p.ipAddress;
cmdInsert.Parameters.Add(p0);
cmdInsert.Parameters.Add(p1);
try
{
Debug.Log("opening con");
con.Open();
Debug.Log("executing create");
cmd.ExecuteNonQuery();
Debug.Log("executing insert");
cmdInsert.ExecuteNonQuery();
Debug.Log("closing connection");
con.Close();
Debug.Log("Table created");
}
catch (Exception ex)
{
if (con != null)
con.Close();
Debug.Log(ex.ToString());
}
finally
{
con.Close();
con.Dispose();
}
}
}
EDIT:
Just so there is no confusion, that is my current full test code, NOTE, the IP is dynamic and will change as I reboot my device and you probably can’t use my test server, not for this project, so change the IP to your own Unity RakNet server, this is behind a firewall and is a sandbox setup.
Actually LINQ won’t even create the table, apparently providing or not providing a parameter list doesn’t matter using LINQ, the call assume no parameters but the result on the back end is blowing up with parameter errors, which is testable via the code above.
I know older thread, but curious if sqllite with U3 is still an issue?
3.1 didn’t touch these things, so all that held before still holds
Darn, thought I might have missed it. What are people using for single player or ios databases then?
on iOS databases, but I primarily work through objc for such stuff as it is easier anyway
Any clue if on their known issue list to look at? With Torque always saw the guys confirming the issue, haven’t seen that in the forums here.
Thats cause Torque had no bug reporting system, their only way was confirming it (and then ignoring it or taking 6 - 12 months to fix trivialities by my experience).
Here you need to use the bug reporter, posting on the boards is worthless.
And nope, there is no publically accessable known issue list I fear.
…i know this topic OLD … really old,but does this problem still exists?
please, check this → SQLiteKit | Integration | Unity Asset Store
That is 100% managed code, full SQLite3 support, all platforms.
No native dependencies.
There is example scene with test/demo code for platforms: WebPlayer, PC, Mac, Android, IPhone