Dump example retrieves error data

Dump example retrieves error data

Postby Zen » Sat 15 Oct 2005 05:15

Testing Environment : VS2003 and CoreLab.MySql(version 3.0.2 Trial Version)

When using C# dump example come with CoreLab installer, I tried to backup an MySQL DB(ServerVersion: 4.1.1-alpha-standard-log) in Traditional Chinese and save the file. The DB table charset is utf8 and InnoDB. The file gives text like:

Code: Select all
SET FOREIGN_KEY_CHECKS = 0;
--
-- Table structure for table  acc_account
--
DROP TABLE IF EXISTS acc_account;
CREATE TABLE `acc_account` (
  `account_num` int(10) unsigned NOT NULL default '0',
  `account_name` varchar(20) default '',
  `client_id` int(10) unsigned NOT NULL default '0',
  `account_balance` decimal(8,2) NOT NULL default '0.00',
  `account_prepay_start_day` int(10) unsigned NOT NULL default '1',
  `account_open_date` datetime NOT NULL default '1900-05-16 00:00:00',
  `account_type_id` int(10) unsigned NOT NULL default '0',
  `account_enabled` enum('Y','N') NOT NULL default 'Y',
  `account_remark` varchar(255) default '',
  `account_close_date` datetime default '1900-05-16 00:00:00',
  PRIMARY KEY  (`account_num`)
) TYPE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table acc_account
--
INSERT INTO acc_account VALUES (100001, '綜合帳戶', 100001, -3000.50, 1, '2004-12-01 00:00:00', NULL, NULL, NULL, NULL);
INSERT INTO acc_account VALUES (100002, '綜合帳戶', 100002, 3914.56, 2, '2004-12-02 00:00:00', NULL, NULL, NULL, NULL);
INSERT INTO acc_account VALUES (100005, '特殊帳戶', 100005, 0.00, 5, '2004-12-05 00:00:00', NULL, NULL, NULL, NULL);
INSERT INTO acc_account VALUES (100006, '尿片帳戶', 100001, 0.00, 15, '2004-12-12 00:00:00', NULL, NULL, NULL, NULL);
INSERT INTO acc_account VALUES (100007, '"', 100001, 9920.00, 1, '2004-12-01 00:00:00', NULL, NULL, NULL, NULL);
INSERT INTO acc_account VALUES (100008, '"', 100002, 500.00, 2, '2004-12-02 00:00:00', NULL, NULL, NULL, NULL);
INSERT INTO acc_account VALUES (100009, '"', 100003, 12070.00, 3, '2004-12-03 00:00:00', NULL, NULL, NULL, NULL);
INSERT INTO acc_account VALUES (100010, '"', 100004, -97450.00, 4, '2004-12-04 00:00:00', NULL, NULL, NULL, NULL);
INSERT INTO acc_account VALUES (100011, '"', 100005, 0.00, 5, '2004-12-05 00:00:00', NULL, NULL, NULL, NULL);
INSERT INTO acc_account VALUES (100012, '綜合', 100006, 0.00, 1, '2005-01-01 00:00:00', NULL, NULL, NULL, NULL);
INSERT INTO acc_account VALUES (100015, '綜合', 100009, 0.00, 1, '2005-01-01 00:00:00', NULL, NULL, NULL, NULL);
INSERT INTO acc_account VALUES (100536, '綜合戶口', 100329, 0.00, 1, '2005-08-02 11:35:42', NULL, NULL, NULL, NULL);
INSERT INTO acc_account VALUES (100537, '綜合戶口', 100339, 3000.00, 16, '2005-06-16 00:00:00', NULL, NULL, NULL, NULL);
INSERT INTO acc_account VALUES (100538, '\t?', 100364, 0.00, 1, '2005-08-02 14:43:19', NULL, NULL, NULL, NULL);


It seems like some words cannot be showed. I have tried to use MySQL administrator which developed by MySQL.
The output file gives like this:

Code: Select all
INSERT INTO `acc_account` (`account_num`,`account_name`,`client_id`,`account_balance`,`account_prepay_start_day`,`account_open_date`,`account_type_id`,`account_enabled`,`account_remark`,`account_close_date`) VALUES
(100001,'綜合帳戶',100001,'-3000.50',1,'2004-12-01 00:00:00',1,'Y','','1900-05-16 00:00:00'),
(100002,'綜合帳戶',100002,'3914.56',2,'2004-12-02 00:00:00',1,'Y','','1900-05-16 00:00:00'),
(100005,'特殊帳戶',100005,'0.00',5,'2004-12-05 00:00:00',3,'N','','2005-07-18 19:55:23'),
(100006,'尿片帳戶',100001,'0.00',15,'2004-12-12 00:00:00',1,'Y','','1900-05-16 00:00:00'),
(100007,'我的錢包',100001,'9920.00',1,'2004-12-01 00:00:00',2,'Y','','1900-05-16 00:00:00'),
(100008,'我的錢包',100002,'500.00',2,'2004-12-02 00:00:00',2,'Y','','1900-05-16 00:00:00'),
(100009,'我的錢包',100003,'12070.00',3,'2004-12-03 00:00:00',2,'Y','','1900-05-16 00:00:00'),
(100010,'我的錢包',100004,'-97450.00',4,'2004-12-04 00:00:00',2,'Y','','1900-05-16 00:00:00');
INSERT INTO `acc_account` (`account_num`,`account_name`,`client_id`,`account_balance`,`account_prepay_start_day`,`account_open_date`,`account_type_id`,`account_enabled`,`account_remark`,`account_close_date`) VALUES
(100011,'我的錢包',100005,'0.00',5,'2004-12-05 00:00:00',2,'N','','2005-07-18 19:55:23'),
(100012,'綜合',100006,'0.00',1,'2005-01-01 00:00:00',1,'N','','2005-04-15 18:44:09'),
(100015,'綜合',100009,'0.00',1,'2005-01-01 00:00:00',1,'N','','2005-04-21 11:50:21'),
(100016,'綜合',100010,'-2400.00',1,'2005-01-01 00:00:00',1,'Y','','1900-05-16 00:00:00');
INSERT INTO `acc_account` (`account_num`,`account_name`,`client_id`,`account_balance`,`account_prepay_start_day`,`account_open_date`,`account_type_id`,`account_enabled`,`account_remark`,`account_close_date`) VALUES
(100536,'綜合戶口',100329,'0.00',1,'2005-08-02 11:35:42',1,'Y','','1900-05-16 00:00:00'),
(100537,'綜合戶口',100339,'3000.00',16,'2005-06-16 00:00:00',1,'Y','','1900-05-16 00:00:00'),
(100538,'按金戶口',100339,'5000.00',16,'2005-06-16 00:00:00',2,'Y','','1900-05-16 00:00:00');


This can get the backup script I need. How can I do to use MySQLDirect component to output like this.

I will enclose the code I use to output the script from MySqlDump component in CoreLab.

This function connects the database.
Code: Select all
private void btConnect_Click(object sender, System.EventArgs e) {

      connection.Close();

      connection.UserId = edUser.Text;
     connection.Unicode = true;
      connection.Password = edPassword.Text;
      connection.Host = edHost.Text;
      connection.Port = Convert.ToInt32(edPort.Text);
      connection.Database = edDatabase.Text;

      try {
        Cursor.Current = Cursors.WaitCursor;

        connection.Open();

        Cursor.Current = Cursors.Default;

        DialogResult = DialogResult.OK;
      }
      catch (MySqlException exception) {
        Cursor.Current = Cursors.Default;

        retries--;
        if (retries == 0)
          DialogResult = DialogResult.Cancel;

        switch (exception.Code) {
          case 1045:
            ActiveControl = edUser;
            break;
          case 2003:
          case 2005:
            ActiveControl = edHost;
            break;
        }
       
        throw;
      }
    }


This function stores the script to the text file.
Code: Select all
   
private void btSave_Click(object sender, System.EventArgs e) {
      if (saveFileDialog.ShowDialog() == DialogResult.OK) {
        Stream stream = saveFileDialog.OpenFile();
        if (stream != null) {
          statusBar.Panels[0].Text = "Save in progress";
          Encoding unicodeString = Encoding.UTF8 ;
          stream.Write( unicodeString.GetBytes(mySqlDump.DumpText), 0, unicodeString.GetBytes(mySqlDump.DumpText).Length);
          stream.Close();
          statusBar.Panels[0].Text = "Save complete";
        }
      }
    }


This function backup the script to the mySqlDump.DumpText
Code: Select all
    private void btBackup_Click(object sender, System.EventArgs e) {
     
      mySqlDump.Tables = "acc_account";
      statusBar.Panels[0].Text = "Backup in progress";
      mySqlDump.Backup();
      statusBar.Panels[0].Text = "Backup complete";
    }


P.S. I have already set the MySqlConnection.Unicode = true as well. And I cannot use the output which generated from MySqlDump component to restore the database.
Zen
 

Postby Serious » Tue 18 Oct 2005 06:47

I think you have some errors in your btSave_Click function.
First of all, strings in .NET Framework are stored using UCS-2 charset, not UTF-8. Using UFT-8 with MySQLDirect .NET does not affect on this.
Secondly, in my opinion calling GetBytes method twice may affect performance of your application.
Try this code
Code: Select all
private void btSave_Click(object sender, System.EventArgs e) {
      if (saveFileDialog.ShowDialog() == DialogResult.OK) {
        Stream stream = saveFileDialog.OpenFile();
        if (stream != null) {
          statusBar.Panels[0].Text = "Save in progress";
          byte [] bytes = Encoding.Unicode.GetBytes(mySqlDump.DumpText);
          stream.Write(bytes, 0, bytes.Length);
          stream.Close();
          statusBar.Panels[0].Text = "Save complete";
        }
      }
    }
If it works in your situation, let us know.
Serious
 


Return to dotConnect for MySQL