MySqlLoader and temporary tables
MySqlLoader and temporary tables
Does MySqlLoader work with temporary tables? I created one using MySqlCommand, reused the same connection with MySqlLoader, but get an exception from MySqlLoader.Close() that the table doesn't exist.
Please try the code below. It works with dotConnect for MySQL v 6.50.228 in our environment.
Code: Select all
using (MySqlConnection conn = new MySqlConnection()) {
conn.ConnectionString = "server=***;port=3308;uid=***;pwd=***;database=test;";
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TEMPORARY TABLE my_temp_table (" +
" column1 VARCHAR(50) NOT NULL" +
")";
cmd.ExecuteNonQuery();
MySqlLoader loader = new MySqlLoader();
// Specify connection that MySqlLoader will use for loading
loader.Connection = conn;
// Set table name that will be loaded into
loader.TableName = "my_temp_table";
// Populate Columns collection from table description
loader.CreateColumns();
// Prepare MySqlLoader for loading
loader.Open();
for (int i = 0; i < 10; i++) {
loader.SetValue("column1", i);
loader.NextRow();
}
// Flush buffer and dispose internal MySqlLoader structures
loader.Close();
cmd.CommandText = "select * from my_temp_table";
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) {
Console.WriteLine(reader[0]);
}
Console.ReadKey();
}
Thanks for the reply. Unfortunately, the problem reproduces with your code as well.
Unhandled Exception: Devart.Data.MySql.MySqlException: Table 'test.my_temp_table' doesn't exist
at Devart.Data.MySql.bk.t()
at Devart.Data.MySql.bk.d()
at Devart.Data.MySql.u.a(af[]& A_0, Int32& A_1)
at Devart.Data.MySql.u.a(Byte[] A_0, Int32 A_1, Boolean A_2)
at Devart.Data.MySql.MySqlLoader.d()
at Devart.Data.MySql.MySqlLoader.Dispose(Boolean disposing)
at Devart.Data.MySql.MySqlLoader.Close()
...
Devart.Data is on version 5.0.327.0. Devart.Data.MySql is on version 6.50.228.0. I'm running MySQL 5.5.16 on 64-bit Windows 7. The application is targeting .NET 4.
Unhandled Exception: Devart.Data.MySql.MySqlException: Table 'test.my_temp_table' doesn't exist
at Devart.Data.MySql.bk.t()
at Devart.Data.MySql.bk.d()
at Devart.Data.MySql.u.a(af[]& A_0, Int32& A_1)
at Devart.Data.MySql.u.a(Byte[] A_0, Int32 A_1, Boolean A_2)
at Devart.Data.MySql.MySqlLoader.d()
at Devart.Data.MySql.MySqlLoader.Dispose(Boolean disposing)
at Devart.Data.MySql.MySqlLoader.Close()
...
Devart.Data is on version 5.0.327.0. Devart.Data.MySql is on version 6.50.228.0. I'm running MySQL 5.5.16 on 64-bit Windows 7. The application is targeting .NET 4.
Please add the line "loader.Delayed = false;" before "loader.Open();" in my sample in this thread. This should work with MySQL server v 5.5.16 as well.
The MySqlLoader.Delayed property determines whether to use INSERT DELAYED statement syntax. If Delayed is set to True (by default), there is the following situation: temporary table is created in one thread, but INSERTs are executed in another thread (in which temporary table is not visible).
The MySqlLoader.Delayed property determines whether to use INSERT DELAYED statement syntax. If Delayed is set to True (by default), there is the following situation: temporary table is created in one thread, but INSERTs are executed in another thread (in which temporary table is not visible).