public override void Open()
{
this.InternalConnection = new mysqli(
host: __SQLiteConnectionStringBuilder.InternalConnectionString.InternalHost,
user: __SQLiteConnectionStringBuilder.InternalConnectionString.InternalUser,
password: __SQLiteConnectionStringBuilder.InternalConnectionString.Password
);
//http://php.net/manual/en/mysqli.errno.php
if (this.InternalConnection.connect_errno != 0)
{
var message = new
{
this.InternalConnection.connect_errno,
//hint = "Check your credentials!",
this.InternalConnection.connect_error,
ConnectionString = new
{
__SQLiteConnectionStringBuilder.InternalConnectionString.InternalHost,
__SQLiteConnectionStringBuilder.InternalConnectionString.InternalUser
}
};
throw new Exception(
message.ToString()
);
}
this.InternalConnection.query(
"CREATE DATABASE IF NOT EXISTS `" + __SQLiteConnectionStringBuilder.InternalConnectionString.DataSource + "`"
);
if (this.InternalConnection.errno != 0)
{
var message = new
{
this.InternalConnection.errno,
hint = "Check your credentials!",
this.InternalConnection.error,
};
throw new Exception(
message.ToString()
);
}
this.InternalConnection.query(
"use `" + __SQLiteConnectionStringBuilder.InternalConnectionString.DataSource + "`"
);
}
/// <summary>
/// This Method is a javascript callable method.
/// </summary>
/// <param name="e">A parameter from javascript.</param>
/// <param name="y">A callback to javascript.</param>
public void WebMethod2(string e, Action<string> y)
{
// https://sites.google.com/a/jsc-solutions.net/backlog/knowledge-base/2013/201301/20130101
var x = new __ConsoleToDatabaseWriter(y);
Native.API.error_reporting(-1);
Console.WriteLine("before data");
var m = new mysqli(
"127.0.0.1",
"root",
""
);
#region query
Action<string> query =
_sql =>
{
(m.query(_sql) as mysqli_result).With(
r =>
{
r.close();
}
);
if (m.errno != 0)
{
var message = new { m.errno, m.error, _sql };
Console.WriteLine(message.ToString());
}
};
#endregion
query("CREATE DATABASE IF NOT EXISTS `datasource1001`");
query("use `datasource1001`");
{
#region create table.sql
var sql = @"
create table if not exists TheGridTable
(
ContentKey INTEGER PRIMARY KEY AUTOINCREMENT
, ContentValue text not null
, ContentComment text not null
, ParentContentKey INTEGER
, FOREIGN KEY(ParentContentKey) REFERENCES TheGridTable (ContentKey)
)
";
sql = ScriptCoreLib.PHP.Data.SQLiteToMySQLConversion.Convert(sql);
#endregion
query(sql);
}
{
var sql = @"
insert into TheGridTable (ContentValue, ContentComment, ParentContentKey) values (
? /* text */
, ? /* text */
, ?
)
";
(m.prepare(sql) as mysqli_stmt).With(
stmt =>
{
Console.WriteLine("in prepare " + new { sql });
string n = null;
stmt.bind_param_array("sss",
"a ContentValue",
"a ContentComment",
n
);
stmt.execute();
var id = m.insert_id;
Console.WriteLine("new item " + new { id });
(m.prepare(sql) as mysqli_stmt).With(
cstmt =>
{
//Console.WriteLine("in prepare " + new { sql });
//string n = null;
stmt.bind_param_array("ssi",
"a ContentValue",
"a ContentComment",
id
);
stmt.execute();
var cid = m.insert_id;
Console.WriteLine("new child item " + new { cid });
}
);
}
);
}
{
var sql = @"
select
t1.ContentKey
, t1.ContentValue
, t1.ContentComment
, (select count(*) from TheGridTable t2 where t2.ParentContentKey = t1.ContentKey) as ContentChildren
from TheGridTable t1
-- best way
where (t1.ParentContentKey is null and (? is null or ? = '')) or t1.ParentContentKey = ?
";
Console.WriteLine("x before select");
var stmt = (m.prepare(sql) as mysqli_stmt);
if (m.errno != 0)
{
var message = new { m.errno, m.error, sql };
Console.WriteLine(message.ToString());
}
if (stmt != null)
{
Console.WriteLine("x in prepare " + new { sql });
var aa = new List<object>();
//aa.Add(null);
// what if android cannot handle nulls?
aa.Add("");
aa.Add("");
aa.Add("");
var aaa = aa.ToArray();
// will it work?
stmt.bind_param_array("sss",
aaa
);
stmt.execute();
stmt.store_result();
Console.WriteLine("x store_result " + new { stmt.num_rows, stmt.field_count });
for (int i = 0; i < stmt.num_rows; i++)
{
var a = stmt.__fetch_array();
var ContentKey = (int)a["ContentKey"];
var ContentValue = (string)a["ContentValue"];
var ContentComment = (string)a["ContentComment"];
var ContentChildren = (int)a["ContentChildren"];
//var message = new { i, a = Native.DumpToString(a) };
var message = new { i, a = new { ContentKey, ContentValue, ContentComment, ContentChildren } };
Console.WriteLine(message.ToString());
if (ContentChildren > 0)
{
(m.prepare(sql) as mysqli_stmt).With(
cstmt =>
{
cstmt.bind_param_array("iii",
ContentKey,
ContentKey,
ContentKey
);
cstmt.execute();
cstmt.store_result();
Console.WriteLine("x child store_result " + new { cstmt.num_rows, cstmt.field_count });
cstmt.free_result();
}
);
}
}
//reader.dispose
stmt.free_result();
}
}
Console.WriteLine("all done");
x.Dispose();
}
public override void Close()
{
if (this.InternalConnection == null)
return;
this.InternalConnection.close();
this.InternalConnection = null;
}
