20. DB access part 1: MySQL

iFun Engine’s ORM automates DB processing to handle game objects, but direct access to the DB server may be required in some cases to read or write data in the DB regardless of the ORM. In these cases, you can use MySQL Connector, explained below, for SQL processing in all DBs compatible with MySQL.

Important

This feature is meant to operate concurrently with ORM, but is used to handle parts ORM cannot when separate tables such as operating tools must be accessed. Please refer to ORM if your goal is to handle game objects.

Simultaneously accessing objects handled by ORM through MySQL Connector is very risky and can result in data loss and malfunctions. Please refer to Accessing the database the iFun Engine ORM uses.

Note

You can directly import MySQL libraries, but it is better to use classes provided by iFun Engine as you then do not need to worry about thread interference.

Tip

Not all functions are contained in the following explanation. For more details, please see MariaDB class.

20.1. Resetting DB connections

20.1.1. Setting values

Ptr<Mariadb> Mariadb::Create(host, user, password, schema,
                             num_connections [, charset] [, auto_retry_on_deadlock])

Mariadb Mariadb.Create(host, user, password, schema,
                       num_connections [, charset] [, auto_retry_on_deadlock])

Set the values for the DB connection

host

Enter the DB server address. E.g. tcp://127.0.0.1:3306

user

Enter the DB account ID.

password

Enter the DB account password.

schema

Enter the DB name.

num_connections

Enter the number of connections in the connection pool.

charset

Enter the connection character set. The DB’s default value is used if omitted.

auto_retry_on_deadlock

Please read the explanation at http://dev.mysql.com/doc/refman/5.5/en/innodb-deadlocks.html. False if omitted.

client_flags

mysql_com.h 와 https://mariadb.com/kb/en/mysql_real_connect 에 나열된 flag 를 입력합니다. CLIENT_MULTI_STATEMENTS, CLIENT_REMEMBER_OPTIONS 는 자동으로 입력됩니다.

slow_query_logging_time_in_ms

로그로 남기려는 느린 쿼리의 기준 시간을 밀리초 단위로 입력합니다. 기본 값은 5 초 입니다.

connection_failure_handler

연결이 실패하거나 연결이 끊길 때 불릴 콜백 함수를 입력합니다. 이 함수는 인자로 연결돼야할 개수와 연결된 개수를 받을 수 있습니다.

Note

Text strings may break if the character set is different from the generated DB/table/column.

Note

If there are more than 2 connections, they are executed in parallel, as the query order is not guaranteed otherwise.

Note

If the num_connections is large, some may fail. A log of timeouts after a set period of time may be output. Increase max_connections, backlog, and max_allowed_packet values in MySQL settings.

20.1.2. Initializing the thread pool

void Mariadb::Initialize()

void Mariadb.Start()

Reset the connection pool to match the number of connections set using Create(). You can run queries once this function has been invoked.

20.2. Running queries

Use one of the following functions. Functions with Sync at the end run synchronously.

void Mariadb::ExecuteQuery(query, callback)
Ptr<ResultSets> Mariadb::ExecuteQuerySync(query, Mariadb::Error *error)

public void Mariadb.ExecuteQuery(query, callback)
public ResultSets ExecuteQuerySync(string query, out Error error)

query

This is the SQL Query text string to run.

callback

This is the callback function to handle execution results.

error

This is the output variable to send the error value when there is an error.

Note

The callback must be in the form void(const Ptr<ResultSets> &result_sets, const Mariadb::Error &error).

Note

Prepared statements are not supported in the current version.

20.3. MySQL Connector example

The example below assumes the following table and procedure.

Member table:

===================
No.  Name.  Weight.
-------------------
0    dog    12.5
1    cat    2.1
2    bird   0.5
3    tiger  290.0

ExampleTable table:

CREATE TABLE ExampleTable (date DATETIME)

ExampleProc procedure:

CREATE PROCEDURE ExampleProc ()
BEGIN
  SELECT * FROM Member;
END

ExampleProc2 procedure:

CREATE PROCEDURE ExampleProc2 (OUT param1 INT, OUT param2 CHAR(50))
BEGIN
  SET param1 = 1234;
  SET param2 = "test";
END

20.3.1. Example - SELECT

The example below outputs the following results:

====================
No: 0, Name: dog, Weight: 12.5
No: 3, Name: tiger, Weight: 290
====================
No: 2, Name: bird, Weight: 0.5

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Ptr<Mariadb> connections;

void Initialize() {
  connections = Mariadb::Create("tcp://127.0.0.1:3306", "myid", "mypw",
                                "mydb", 5, "utf8", false);
  connections->Initialize();
}

void ExecuteQuery() {
  std::stringstream query;
  query << "SELECT * FROM Member WHERE Weight > 10.0;"
        << "SELECT * FROM Member WHERE Weight < 1.0;";

  connections->ExecuteQuery(query.str(), OnQueryExecuted);
}

void OnQueryExecuted(const Ptr<ResultSets> &result_sets,
                     const Mariadb::Error &error) {
  if (error.code != 0) {
    LOG(ERROR) << "Error: code=" << error.code << ", desc=" << error.desc;
    return;
  }

  // As many as SELECT queries.
  while (result_sets->SeekNextResultSet()) {
    LOG(INFO) << "=====================";

    // As many as result rows of each query.
    while (result_sets->SeekNextRow()) {
      LOG(INFO) << "No: " << result_sets->GetInt(0)
                << ", Name: " << result_sets->GetString(1)
                << ", Weight: " << result_sets->GetDouble(2);
    }
  }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Mariadb connections;

void Initialize() {
  Mariadb connections = Mariadb.Create (
      "tcp://127.0.0.1:3306", "funapi", "funapi", "funapi", 8, "utf8");

  connections.Start();
}

void ExecuteQuery() {
  StringBuilder strbuilder = new StringBuilder ();
  strbuilder.Append ("SELECT * FROM Member WHERE Weight > 10.0;");
  strbuilder.Append ("SELECT * FROM Member WHERE Weight < 1.0;");

  connections.ExecuteQuery (query.ToString(), OnQueryExecuted);
}

public void OnQueryExecuted(ResultSets result_sets, Mariadb.Error error)
{
  if (error.Code != 0)
  {
    Log.Info ("Error: code={0}, desc={1}", error.Code, error.Desc);
  }

  // As many as SELECT queries.
  while (result_sets.SeekNextResultSet ()) {
    Log.Info ("=====================");

    // As many as result rows of each query.
    while (result_sets.SeekNextRow ()) {
      Log.Info ("No: {0}", result_sets.GetString (0));  // 또는 GetInt("No")
      Log.Info (", Name: {0}", result_sets.GetString (1));
      Log.Info (", Weight: {0}", result_sets.GetDouble (2));
    }
  }
}

20.3.2. Example - INSERT

The following are sample results output using SELECT after INSERT. The following results are output:

2016-06-14 16:23:19

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
Ptr<Mariadb> connections;

void Initialize() {
  connections = Mariadb::Create("tcp://127.0.0.1:3306", "myid", "mypw",
                                "mydb", 5, "utf8", false);
  connections->Initialize();
}

void ExecuteQuery() {
  string now = boost::posix_time::to_iso_extended_string(WallClock::Now());

  std::stringstream query;
  query << "INSERT INTO ExampleTable VALUES('" << now << "');";

  Mariadb::Error error;
  connections->ExecuteQuerySync(query.str(), &error);
  if (error.code != 0) {
    LOG(ERROR) << "Error: code=" << error.code << ", desc=" << error.desc;
    return;
  }

  string query2 = "SELECT date FROM ExampleTable;";
  connections->ExecuteQuery(query2, OnQueryExecuted);
}

void OnQueryExecuted(const Ptr<ResultSets> &result_sets,
                     const Mariadb::Error &error) {
  if (error.code != 0) {
    LOG(ERROR) << "Error: code=" << error.code << ", desc=" << error.desc;
    return;
  }
  if (not result_sets->SeekNextResultSet()) {
    LOG(ERROR) << "no result set.";
    return;
  }

  while (result_sets->SeekNextRow()) {
    WallClock::Value datetime = result_sets->GetDateTime(0);
    LOG(INFO) << "datetime: " << datetime;
  }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Mariadb connections;

void Initialize() {
  Mariadb connections = Mariadb.Create (
      "tcp://127.0.0.1:3306", "funapi", "funapi", "funapi", 8, "utf8");

  connections.Start();
}

// 쿼리 실행
void ExecuteQuery()
{
  string query = "INSERT INTO ExampleTable VALUES('{0}');";
        string now = WallClock.GetTimestring(WallClock.Now);

  Mariadb.Error error;
  connections.ExecuteQuerySync (String.Format (query, now), out error);

  if (error.Code != 0)
  {
    Log.Info ("Error: code={0}, desc={1}", error.Code, error.Desc);
  }

  string query2 = "SELECT date FROM ExampleTable;";

  connections.ExecuteQuery(query2, OnQueryExecuted);
}

public void OnQueryExecuted(ResultSets result_sets, Mariadb.Error error)
{
  if (error.Code != 0)
  {
    Log.Info ("Error: code={0}, desc={1}", error.Code, error.Desc);
  }

  if (!result_sets.SeekNextResultSet ()) {
    Log.Error ("no result set.");
    return;
  }

  while (result_sets.SeekNextRow ()) {
    System.DateTime dateTime = result_sets.GetDateTime (0);
    Log.Info ("Datetime : {0}", dateTime);
  }
}

20.3.3. Example - SELECT using a procedure

In the example below, the following results are generated and obtained by invoking a procedure instead of directly through SELECT.

No: 0, Name: dog, Weight: 12.5
No: 1, Name: cat, Weight: 2.1
No: 2, Name: bird, Weight: 0.5
No: 3, Name: tiger, Weight: 290

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Ptr<Mariadb> connections;

void Initialize() {
  connections = Mariadb::Create("tcp://127.0.0.1:3306", "myid", "mypw",
                                "mydb", 5, "utf8", false);
  connections->Initialize();
}

void ExecuteQuery() {
  std::stringstream query;
  query << "CALL ExampleProc();";

  connections->ExecuteQuery(query.str(), OnQueryExecuted);
}

void OnQueryExecuted(const Ptr<ResultSets> &result_sets,
                     const Mariadb::Error &error) {
  if (error.code != 0) {
    LOG(ERROR) << "Error: code=" << error.code << ", desc=" << error.desc;
    return;
  }

  if (not result_sets->SeekNextResultSet()) {
    LOG(ERROR) << "no result set.";
    return;
  }

  while (result_sets->SeekNextRow()) {
    LOG(INFO) << "No: " << result_sets->GetInt(0)
              << ", Name: " << result_sets->GetString(1)
              << ", Weight: " << result_sets->GetDouble(2);
  }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Mariadb connections;

void Initialize() {
  Mariadb connections = Mariadb.Create (
      "tcp://127.0.0.1:3306", "funapi", "funapi", "funapi", 8, "utf8");

  connections.Start();
}

public void ExecuteQuery(Mariadb connections)
{
  connections.ExecuteQuery ("CALL ExampleProc();", OnQueryExecuted);
}

public void OnQueryExecuted(ResultSets result_sets, Mariadb.Error error)
{
  if (error.Code != 0)
  {
    Log.Info ("Error: code={0}, desc={1}", error.Code, error.Desc);
  }

  while (result_sets.SeekNextResultSet ()) {
    while (result_sets.SeekNextRow ()) {
      Log.Info ("No: {0}", result_sets.GetInt (0));  // 또는 GetInt("No")
      Log.Info (", Name: {0}", result_sets.GetString (1));
      Log.Info (", Weight: {0}", result_sets.GetDoubl e(2));
    }
  }
}

20.3.4. Example - Getting procedure OUTPUT

Procedure OUT parameters are used in the example below.

Note

The current version does not support directly receiving the OUT parameter. Substitute in the SQL session variable and SELECT.

p1: 1234, p2: test

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Ptr<Mariadb> connections;

void Initialize() {
  connections = Mariadb::Create("tcp://127.0.0.1:3306", "myid", "mypw",
                                "mydb", 5, "utf8", false);
  connections->Initialize();
}

void ExecuteQuery() {
  std::stringstream query;
  query << "CALL ExampleProc2(@p1, @p2);"
        << "SELECT @p1, @p2;";

  connections->ExecuteQuery(query.str(), OnQueryExecuted);
}

void OnQueryExecuted(const Ptr<ResultSets> &result_sets,
                     const Mariadb::Error &error) {
  if (error.code != 0) {
    LOG(ERROR) << "Error: code=" << error.code << ", desc=" << error.desc;
    return;
  }

  if (not result_sets->SeekNextResultSet()) {
    LOG(ERROR) << "no result set.";
    return;
  }

  while (result_sets->SeekNextRow()) {
    LOG(INFO) << "p1: " << result_sets->GetInt(0)
              << ", p2: " << result_sets->GetString(1);
  }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Mariadb connections;

void Initialize() {
  Mariadb connections = Mariadb.Create (
      "tcp://127.0.0.1:3306", "funapi", "funapi", "funapi", 8, "utf8");

  connections.Start();
}

void ExecuteQuery()
{
  StringBuilder query = new StringBuilder ();
  query.Append ("CALL ExampleProc2(@p1, @p2);");
  query.Append ("SELECT @p1, @p2;");
  connections.ExecuteQuery (query.ToString(), OnQueryExecuted);
}

public void OnQueryExecuted(ResultSets result_sets, Mariadb.Error error)
{
  if (error.Code != 0)
  {
    Log.Info ("Error: code={0}, desc={1}", error.Code, error.Desc);
  }

  if (!result_sets.SeekNextResultSet ()) {
    Log.Error ("no result set.");
    return;
  }

  while (result_sets.SeekNextRow ()) {
    Log.Info ("P1 : {0}", result_sets.GetInt (0));
    Log.Info (", P2 : {0}", result_sets.GetString (1));
  }
}