# coding: utf-8
# ODBC->MySQL
require "./odbc"

class MySqlOdbc < Odbc

   def initialize

      super(__ENCODING__)
   end

   def execQuery(sSQL)
   
      ir = execdirect(sSQL)
      if ir != 0
         sa = "ODBC MySQL SQL error¥r¥n" + getErr + "¥r¥n[" + sSQL + "]¥r¥n"
         print sa
         disconnect
         exit
      end
      return ir
   end

end

mysqlodbc = MySqlOdbc.new

#ir = mysqlodbc.connect("db0","root","root")

ir = mysqlodbc.driverConnect("DSN=db0;UID=root;PWD=root")

# sconstr = "DRIVER={MySQL ODBC 5.3 Unicode Driver};" +
#           "SERVER=localhost;PORT=3306;OPTION=0;STMT=SET utf-8;" +
#           "DATABASE=db0;UID=root;PWD=root"
# ir = mysqlodbc.driverConnect(sconstr)

if ir != 0
   print "接続エラー " + ir.to_s + "¥r¥n"
   exit
end
print mysqlodbc.sconstr + "¥r¥n"

sSQL = "CREATE TABLE IF NOT EXISTS tbl1 (" +
       "id int," +
       "tda datetime," +
       "cnote char(20)," +
       "PRIMARY KEY (id)," +
       "KEY key1 (tda))" +
       "DEFAULT CHARACTER SET utf8," +
       "ENGINE=MYISAM"

# MariaDBでAriaを使用する場合
#sSQL = "CREATE TABLE IF NOT EXISTS tbl1 (" +
#       "id int," +
#       "tda datetime," +
#       "cnote char(20)," +
#       "PRIMARY KEY (id)," +
#       "KEY key1 (tda))" +
#       "DEFAULT CHARACTER SET utf8," +
#       "ENGINE=Aria PAGE_CHECKSUM=1 TRANSACTIONAL=1"
mysqlodbc.execQuery(sSQL)

print "テーブル一覧¥r¥n"
ir,ardt = mysqlodbc.tableList
ardt.each{|cat, schem, name, type, rem|
   schem = "" if schem == nil
   rem  = "" if rem == nil
   print cat + "," + schem + "," + name +  "," + type + "," + rem + "¥r¥n"
}
print "¥r¥n"

print ardt[0][2] + "テーブルのカラム¥r¥n"
ir,ardt = mysqlodbc.tableColumns(ardt[0][0],ardt[0][1],ardt[0][2])
ardt.each{|arz|
   sa = ""
   arz.each{|sb|
      sb = "" if sb == nil
      sa += sb + ","
   }
print sa + "¥r¥n"
}

sSQL = "SELECT max(id) FROM tbl1"
mysqlodbc.execQuery(sSQL)
ir,ardt = mysqlodbc.getData
id = 0
if ardt.length != 0
   id = ardt[0].to_i
   id += 1
end

sSQL = "INSERT INTO tbl1 VALUES (" +
       id.to_s + ",'" +
       Time.now.strftime("%Y-%m-%d %H:%M:%S") + "'," +
       "'日本☀☃ﾆﾎﾝ123abc')"
mysqlodbc.execQuery(sSQL)              #データ書き込み

print "データ表示¥r¥n"
sSQL = "SELECT * FROM tbl1 ORDER BY id DESC LIMIT 5"
mysqlodbc.execQuery(sSQL)
ir,ardt = mysqlodbc.describeCol
print ardt[0] + "," + ardt[1] + "," + ardt[2] + "¥r¥n"
while true do
   ir,ardt = mysqlodbc.getData
   if ir == -1
      break
   end
   print ardt[0] + "," + ardt[1] + "," + ardt[2] + "¥r¥n"
end

mysqlodbc.disconnect
print "終了¥r¥n"