mysql2 connection to seekdb sample program

This topic describes how to build an application using mysql2 and seekdb to perform basic operations such as creating tables, inserting data, and querying data.

Prerequisites

• You have installed Ruby and RubyGems.
• You have installed seekdb.

Procedure

1. Check the versions of Ruby and RubyGems.
2. Install the required gem.
3. Obtain the seekdb connection information.
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 command to check the versions of Ruby and RubyGems:

ruby -v
gem -v

Step 2: Install the required gem

Run the following command to install the mysql2 gem:

gem install mysql2

Step 3: Obtain the seekdb connection information

Contact the seekdb deployment personnel or administrator to obtain the database connection string.

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

Parameters:

• $host: the IP address for connecting to seekdb.
• $port: the port for connecting to seekdb.
• $database_name: the name of the database to be accessed.
• $user_name: the account for connecting to seekdb.
• $password: the password for 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 program:

   ruby main.rb

   Expected output:

   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 the following:

   • Whether the database address and port are correct
   • Whether the username and password are correct
   • Whether the network connection is normal

2. Permission error: If you encounter permission-related errors, ensure that the user has sufficient permissions to perform the required operations.

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

Performance optimization recommendations

1. Use a connection pool to manage database connections.
2. Use prepared statements.
3. Set the query timeout appropriately.
4. Use batch operations to improve performance.