[前][次][番号順一覧][スレッド一覧]

mysql:13723

From: Tadafumi Kouzato <Tadafumi Kouzato <grego@xxxxxxxxxx>>
Date: Tue, 13 Feb 2007 17:45:29 +0900
Subject: [mysql 13723] Connector/Net でinsertしたレコードのauto_incrementとtimestamp

上里@ぴえろ です。

Connector/Net(5.0.3)を使ってMySQL(5.0.24)のテーブルに
レコードをinsertし、そのレコードのTimestampとauto_incrementカラム
を取得しようとしています。
が、Timestampとauto_incermentカラムを取得できません。


まずテーブルはこんな感じです。

mysql> desc book;
+------------+-----------+------+-----+-------------------+----------------+
| Field      | Type      | Null | Key | Default           | Extra          |
+------------+-----------+------+-----+-------------------+----------------+
| ts         | timestamp | YES  |     | CURRENT_TIMESTAMP |                |
| book_id    | int(11)   | NO   | PRI | NULL              | auto_increment |
| issuer     | int(11)   | YES  |     | NULL              |                |
| issue_date | date      | YES  |     | NULL              |                |
+------------+-----------+------+-----+-------------------+----------------+

下記のようなストアドプロシージャとC#のプログラムを書きました。

--ストアドプロシージャ-----------------------------------------------
mysql> show create procedure add_new_book\G
*************************** 1. row ***************************
       Procedure: add_new_book
        sql_mode:
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `add_new_book`(in x_issuer integer, in x_issue_date date,
out t timestamp, out bid int)
begin
insert into book (issuer,issue_date) values (x_issuer,x_issue_date);
set bid = last_insert_id();
select ts into t from book where book_id = bid;
end
1 row in set (0.00 sec)

---------------------------------------------------------------------


---- C#(Visual Studio 2005)で書いたクライアントプログラムの一部------
using System;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using MySql.Data.MySqlClient;
using NUnit.Framework;

namespace DbMySQLSecondLesson
{
    [TestFixture]
    public class SimpleBookFixture
    {
        private string connectionString;
        private MySqlConnection connection;

        [SetUp]
        public void SetUp() {
            connectionString =
                System.Configuration.ConfigurationManager.AppSettings.Get("Book.Connection");
            Assert.IsNotNull(connectionString);

            connection = new MySqlConnection(connectionString);
            connection.Open();
        }

        [TearDown]
        public void TearDown() {
            connection.Close();
        }

        public void CreateNewBook() {
            MySqlCommand clearCommand = connection.CreateCommand();
            clearCommand.CommandText = "delete from book";
            clearCommand.ExecuteNonQuery();

            MySqlCommand selectCommand = new MySqlCommand("select ts,book_id,issuer,issue_date from book", connection);
            MySqlDataAdapter da = new MySqlDataAdapter();
            Assert.IsNotNull(da);
            DataTableMapping TblMap = da.TableMappings.Add("Table", "book");
            DataColumnMapping ColMap1 = TblMap.ColumnMappings.Add("ts", "ts");
            DataColumnMapping ColMap2 = TblMap.ColumnMappings.Add("book_id", "book_id");

            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            da.SelectCommand = selectCommand;
            DataSet ds = new DataSet();
            Assert.IsNotNull(ds);

            MySqlCommand insertCommand = new MySqlCommand("add_new_book", connection);
            insertCommand.CommandType = CommandType.StoredProcedure;
            insertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;

            MySqlParameterCollection icpc = insertCommand.Parameters;
            MySqlParameter param1 = icpc.Add("?x_issuer", MySqlDbType.Int32, 0, "issuer");
            param1.Direction = ParameterDirection.Input;

            MySqlParameter param2 = icpc.Add("?x_issue_date", MySqlDbType.Date, 0, "issue_date");
            param2.Direction = ParameterDirection.Input;

            MySqlParameter param3 = icpc.Add("?t", MySqlDbType.Timestamp, 0, "ts");
            param3.Direction = ParameterDirection.Output;

            MySqlParameter param4 = icpc.Add("?bid", MySqlDbType.Int32, 0, "book_id");
            param4.Direction = ParameterDirection.Output;

            da.InsertCommand = insertCommand;

            int RowsRetrieved = da.Fill(ds);
            Assert.AreEqual(0, RowsRetrieved);
            DataTable tbl = ds.Tables["book"];

            DataRow row = tbl.NewRow();
            row["issuer"] = 7;
            row["issue_date"] = "2007-01-07";
            row["book_id"] = -1;
            row["ts"] = "2007-02-07 00:00:00";
            tbl.Rows.Add(row);

            da.Update(tbl);

            System.Console.WriteLine(tbl.Rows[0]["ts"]);
            System.Console.WriteLine(tbl.Rows[0]["book_id"]);
            System.Console.WriteLine(tbl.Rows[0]["issuer"]);
            System.Console.WriteLine(tbl.Rows[0]["issue_date"]);
        }
    }
}

--------------------------------------------------------------------------

で、実行結果(Console.Out)は下記です。
------- Console.Out -------------------------
2007/02/07 0:00:00
-1
7
2007/01/07 0:00:00
---------------------------------------------

データベースは、更新(insert)されています。
----------------------------------------------
mysql> select * from book;
+---------------------+---------+--------+------------+
| ts                  | book_id | issuer | issue_date |
+---------------------+---------+--------+------------+
| 2007-02-13 17:08:33 |      36 |      7 | 2007-01-07 |
+---------------------+---------+--------+------------+
1 row in set (0.00 sec)

----------------------------------------------

プログラム中に愛以外の何かが足りないような気がするのですが、
それが何なのかわかりません。どうすればいいのでしょうか?

-- 
=========================================================
   @)@)     PIERROT CO., LTD.       (株)ぴえろ
  (--- )    Tadafumi Kouzato         上里 直史
~~~~~~~~~~~                          grego@xxxxxxxxxx
=========================================================

[前][次][番号順一覧][スレッド一覧]