Connect to OceanBase Database by using mysql2

This topic describes how to build an application by using mysql2 and OceanBase Database. It also covers the use of the application for fundamental database operations, including table creation, data insertion, data query, and other basic operations.

Prerequisites

• You have installed Ruby and RubyGems.
• You have installed OceanBase Database and created a MySQL tenant.

Procedure

1. Check the versions of Ruby and RubyGems.
2. Install the required gem.
3. Obtain the connection information of OceanBase Database.
4. Create a sample program.
5. Run the sample program.

Step 1: Check the versions of Ruby and RubyGems

Open the terminal and run the following commands to check the versions of Ruby and RubyGems:

ruby -v
gem -v

Step 2: Install the required gem

Run the following command to install mysql2 by using gem:

gem install mysql2

Step 3: Obtain the connection information of OceanBase Database

Contact the deployment personnel or administrator of OceanBase Database to obtain the connection string.

mysql -h$host -P$port -u$user_name -p$password -D$database_name

Parameter description:

• $host: the IP address for connecting to OceanBase Database.
• $port: the port for connecting to OceanBase Database.
• $database_name: the name of the database to be accessed.
• $user_name: the tenant account.
• $password: the password of the account.

Step 4: Create a sample program

1. Create a configuration file named config.rb:

   PASSWORD = ENV['OB_PASSWORD'] || 'your_password'
   
   CLIENT_CONFIG = {
     host: 'your_host',
     port: your_port,
     username: 'your_username',
     password: PASSWORD,
     all_databases: true
   }
   

2. Create a sample program named main.rb:

   require 'mysql2'
   require_relative 'config'
   
   # Create a connection.
   client = Mysql2::Client.new(CLIENT_CONFIG)
   
   # Create a database.
   db_name = "ruby_test_db"
   client.query("CREATE DATABASE IF NOT EXISTS #{db_name}")
   client.query("USE #{db_name}")
   puts "Database '#{db_name}' selected"
   
   # Create a table.
   client.query("DROP TABLE IF EXISTS users")
   client.query("CREATE TABLE users (
     id INT AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(255) NOT NULL,
     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   )")
   
   # Insert sample data.
   sample_names = ["Alice", "Bob", "Charlie", "David"]
   sample_names.each do |name|
     client.query("INSERT INTO users (name) VALUES ('#{name}')")
   end
   
   puts "Table created and sample data inserted successfully"
   puts "Inserted #{sample_names.length} sample records"
   
   # Query and display all data.
   results = client.query("SELECT * FROM users")
   puts "\nUsers:"
   results.each do |row|
     puts "#{row['id']}: #{row['name']} (created: #{row['created_at']})"
   end
   
   # Close the connection.
   client.close
   

Step 5: Run the sample program

1. Run the following command to run the program:

   ruby main.rb
   

   The expected return result is as follows:

   Database 'ruby_test_db' selected
   Table created and sample data inserted successfully
   Inserted 4 sample records
   
   Users:
   1: Alice (created: 2025-05-20 17:56:22 +0800)
   2: Bob (created: 2025-05-20 17:56:22 +0800)
   3: Charlie (created: 2025-05-20 17:56:22 +0800)
   4: David (created: 2025-05-20 17:56:22 +0800)
   

FAQ

1. Connection error: If you cannot connect to the database, check whether the following information is correct:

   • The database address and port
   • The username and password
   • The network connection

2. Privilege error: If you encounter a privilege error, make sure that the user has the required privileges to perform the operation.

3. SQL syntax error: If there is a syntax error in the SQL statement, check whether the SQL syntax is correct.

Performance optimization suggestions

1. Use a connection pool to manage database connections.
2. Use prepared statements.
3. Set a reasonable query timeout period.
4. Use batch processing to improve performance.