public Query(Connection conn, SqlStringTemplate sql, CommandParams cmdParams)
{
//*** query use conn resource such as parser,writer
//so 1 query 1 connection
Query bindingQuery = conn.BindingQuery;
if (bindingQuery != null)
{
//check if binding query can be close
if (!bindingQuery.LateClose())
{
//can't use this conn
throw new Exception("connection is in used");
}
}
//--------------------------------------------------------------
conn.BindingQuery = this;
//--------------------------------------------------------------
if (sql == null)
{
throw new Exception("Sql command can not null.");
}
//--------------------------------------------------------------
this._conn = conn;
this._cmdParams = cmdParams;
//--------------------------------------------------------------
nestTables = false;
_sqlParserMx = conn.MySqlParserMx;
_writer = conn.PacketWriter;
//_receiveBuffer = null;
_sqlStrTemplate = sql;
}
static void InsertLargeData(MySqlConnection conn2)
{
int size = 1024 * 1000 * 22;//ok
//int size = 1024 * 1000 * 10;//ok
//int size = 1024;
byte[] buffer = new byte[size];
byte dd = 0;
for (int i = 0; i < size; ++i)
{
buffer[i] = dd;
dd++;
if (dd > 200)
{
dd = 0;//reset;
}
}
SqlStringTemplate sql_template = new SqlStringTemplate("insert into table2(data) values(?d)");
var cmd2 = new MySqlCommand(sql_template, conn2);
cmd2.Parameters.AddWithValue("?d", buffer);
cmd2.ExecuteNonQuery();//
if (!cmd2.HasError)
{
uint inser_id = cmd2.LastInsertedId;
}
else
{
//error
}
}
public static void T_LongData()
{
var connStr = GetMySqlConnString();
{
var conn2 = new MySqlConnection(connStr);
conn2.Open();
conn2.ChangeDB("test");
SqlStringTemplate sql_template = new SqlStringTemplate("insert into table1(name) values(?i)");
for (int i = 0; i < 100000; ++i)
{
var cmd2 = new MySqlCommand(sql_template, conn2);
cmd2.Parameters.AddWithValue("?i", i);
cmd2.ExecuteNonQuery();//
}
conn2.Close();
}
//--------------
var conn = new MySqlConnection(connStr);
conn.Open();
conn.ChangeDB("test");
{
var cmd = new MySqlCommand("select * from table1", conn);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
}
reader.Close();
}
conn.Close();
}
static void TestJson4(MySqlConnection conn)
{
//------------
// mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
//mysql > SET @j2 = '1';
//mysql > SELECT JSON_CONTAINS(@j, @j2, '$.a');
//+-------------------------------+
//| JSON_CONTAINS(@j, @j2, '$.a') |
//+-------------------------------+
//| 1 |
//+-------------------------------+
//,
// "SET @j2 = '1'",
{
string[] test_some_json_func = new string[] {
SwapQuote("SET @j = ''{'a': 1, 'b': 2, 'c': {'d': 4}}'';"),
"SET @j2 = '1';"
};
for (int i = 0; i < test_some_json_func.Length; ++i)
{
SqlStringTemplate sqlTemplate = new SqlStringTemplate(test_some_json_func[i], false);
new MySqlCommand(sqlTemplate, conn).ExecuteNonQuery();
}
}
{
SqlStringTemplate sqlTemplate = new SqlStringTemplate("SELECT JSON_CONTAINS(@j, @j2, '$.a')", false);
var cmd = new MySqlCommand(sqlTemplate, conn);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string data = reader.GetString(0);
System.Diagnostics.Debug.WriteLine(data);
}
reader.Close();
}
}
public PreparedContext(uint statementId, SqlStringTemplate sqlStringTemplate, TableHeader tableHeader)
{
this.statementId = statementId;
_sqlStringTemplate = sqlStringTemplate;
_keys = _sqlStringTemplate.GetValueKeys();
//----------------------------------------------
_tableHeader = tableHeader;
int serverFieldCount = tableHeader.ColumnCount;
//add field information to _keys
List<FieldPacket> fields = tableHeader.GetFields();
//----------------------------------------------
int bindingCount = 0;
for (int i = 0; i < serverFieldCount; ++i)
{
FieldPacket f = fields[i];
if (f.name == "?") //
{
//this is binding field
_keys[bindingCount].fieldInfo = f;
bindingCount++;
}
}
//some field from server is not binding field
//so we select only binding field
if (bindingCount != _keys.Count)
{
throw new Exception("key num not matched!");
}
//-------------------------------------------------
_preparedValues = new MyStructData[bindingCount]; //***
}
static void TestJson5(MySqlConnection conn)
{
//from https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html
{
//mysql > SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
//mysql > SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
//+---------------------------------------------+
//| JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |
//+---------------------------------------------+
//| 1 |
//+---------------------------------------------+
//mysql > SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
//+---------------------------------------------+
//| JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |
//+---------------------------------------------+
//| 0 |
//+---------------------------------------------+
//mysql > SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');
//+----------------------------------------+
//| JSON_CONTAINS_PATH(@j, 'one', '$.c.d') |
//+----------------------------------------+
//| 1 |
//+----------------------------------------+
//mysql > SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');
//+----------------------------------------+
//| JSON_CONTAINS_PATH(@j, 'one', '$.a.d') |
//+----------------------------------------+
//| 0 |
//+----------------------------------------+
string[] test_some_json_func = new string[] {
SwapQuote("SET @j = ''{'a': 1, 'b': 2, 'c': {'d': 4}}'';")
};
for (int i = 0; i < test_some_json_func.Length; ++i)
{
SqlStringTemplate sqlTemplate = new SqlStringTemplate(test_some_json_func[i], false);
new MySqlCommand(sqlTemplate, conn).ExecuteNonQuery();
}
}
{
string[] select_sqls = new string[]
{
"SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');",
"SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');",
"SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');",
"SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');"
};
for (int i = 0; i < select_sqls.Length; ++i)
{
SqlStringTemplate sqlTemplate = new SqlStringTemplate(select_sqls[i], false);
var cmd = new MySqlCommand(sqlTemplate, conn);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string data = reader.GetString(0);
System.Diagnostics.Debug.WriteLine(data);
}
reader.Close();
}
}
}
