18. 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.

18.1. Resetting DB connections

18.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.

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.

18.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.

18.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.

18.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

18.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));
    }
  }
}

18.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);
  }
}

18.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));
    }
  }
}

18.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));
  }
}