MySqlLoader and temporary tables

MySqlLoader and temporary tables

Postby daverod » Sun 02 Oct 2011 21:26

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.
daverod
 
Posts: 3
Joined: Sun 02 Oct 2011 21:03

Postby Shalex » Wed 05 Oct 2011 12:30

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();
    }
Shalex
Devart Team
 
Posts: 7341
Joined: Thu 14 Aug 2008 12:44

Postby daverod » Wed 05 Oct 2011 14:56

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.
daverod
 
Posts: 3
Joined: Sun 02 Oct 2011 21:03

Postby Shalex » Mon 10 Oct 2011 14:45

We have reproduced the problem with the 5.5.16 version of MySQL server. We will investigate it and notify you about the results.
Shalex
Devart Team
 
Posts: 7341
Joined: Thu 14 Aug 2008 12:44

Postby daverod » Tue 18 Oct 2011 23:48

Thanks Shalex -- any progress on this issue? Thanks!
daverod
 
Posts: 3
Joined: Sun 02 Oct 2011 21:03

Postby Shalex » Thu 20 Oct 2011 13:10

We are working on this issue. We will post here when it is fixed.
Shalex
Devart Team
 
Posts: 7341
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Mon 14 Nov 2011 14:31

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).
Shalex
Devart Team
 
Posts: 7341
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for MySQL