Ruby Tutorial

Ruby CGI

Ruby Advanced

Ruby Database Access – DBI Tutorial

The Ruby DBI (Database Interface) module provides a database-independent interface for Ruby scripts. This means you can switch your database system without having to change your code, provided the new database system has a DBI driver.

The Ruby DBI module has been deprecated and is no longer maintained. It's recommended to use database-specific gems like pg for PostgreSQL, mysql2 for MySQL, or sqlite3 for SQLite. For an ORM-like interface, ActiveRecord or Sequel are commonly used.

However, for educational purposes, here's a basic example of how you could use Ruby DBI:

require 'dbi'

begin
   # Connect to a MySQL database 'test' on localhost
   dbh = DBI.connect('DBI:Mysql:test:localhost', 'username', 'password')

   # Prepare and execute a SQL query
   sth = dbh.prepare('SELECT * FROM people')
   sth.execute

   # Print out each row
   while row = sth.fetch do
       p row
   end

   # Close the statement handle when done
   sth.finish
rescue DBI::DatabaseError => e
   puts "An error occurred"
   puts "Error code: #{e.err}"
   puts "Error message: #{e.errstr}"
ensure
   # Disconnect from the database
   dbh.disconnect if dbh
end

In the script above, we:

  1. Connect to a MySQL database named 'test' on localhost using DBI.connect.
  2. Prepare a SQL statement with dbh.prepare, then execute it with sth.execute.
  3. Fetch each row of the results with sth.fetch, then print it out.
  4. When done with the statement handle, we close it with sth.finish.
  5. If an error occurs during this process, we rescue it and print out the error code and message.
  6. Finally, whether an error occurred or not, we ensure the database connection is closed with dbh.disconnect.

Again, the dbi gem is deprecated and not maintained. If you're working with databases in Ruby, consider using the pg, mysql2, or sqlite3 gems for direct database access, and ActiveRecord or Sequel for an ORM-like interface.

  1. Using DBI for database access in Ruby:

    • Description: DBI is a database-independent interface for Ruby that provides a consistent API for interacting with various database systems.
    • Code example:
      require 'dbi'

# Connect to a database (example for MySQL)
dbh = DBI.connect('DBI:Mysql:database=testdb;host=localhost', 'username', 'password')

# Perform database operations here

# Disconnect from the database
dbh.disconnect

  2. Ruby DBI example:

    • Description: A simple example demonstrating how to use DBI to connect to a database and execute a query.
    • Code example:
      require 'dbi'

# Connect to a SQLite database
dbh = DBI.connect('DBI:SQLite3:test.db')

# Execute a query
result = dbh.execute('SELECT * FROM users')

# Fetch and print results
result.fetch do |row|
  puts "User ID: #{row['user_id']}, Name: #{row['name']}"
end

# Disconnect from the database
dbh.disconnect

  3. Connect to a database using Ruby DBI:

    • Description: The DBI.connect method is used to establish a connection to a database.
    • Code example:
      require 'dbi'

# Connect to a PostgreSQL database
dbh = DBI.connect('DBI:Pg:dbname=mydatabase;host=localhost;port=5432', 'username', 'password')

# Perform database operations here

# Disconnect from the database
dbh.disconnect

  4. Ruby DBI MySQL example:

    • Description: Connecting to a MySQL database using Ruby DBI.
    • Code example:
      require 'dbi'

# Connect to a MySQL database
dbh = DBI.connect('DBI:Mysql:database=mydatabase;host=localhost', 'username', 'password')

# Perform MySQL database operations here

# Disconnect from the database
dbh.disconnect

  5. Ruby DBI SQLite example:

    • Description: Connecting to an SQLite database using Ruby DBI.
    • Code example:
      require 'dbi'

# Connect to an SQLite database
dbh = DBI.connect('DBI:SQLite3:test.db')

# Perform SQLite database operations here

# Disconnect from the database
dbh.disconnect

  6. Ruby DBI PostgreSQL example:

    • Description: Connecting to a PostgreSQL database using Ruby DBI.
    • Code example:
      require 'dbi'

# Connect to a PostgreSQL database
dbh = DBI.connect('DBI:Pg:dbname=mydatabase;host=localhost;port=5432', 'username', 'password')

# Perform PostgreSQL database operations here

# Disconnect from the database
dbh.disconnect

  7. Ruby DBI Oracle example:

    • Description: Connecting to an Oracle database using Ruby DBI.
    • Code example:
      require 'dbi'

# Connect to an Oracle database
dbh = DBI.connect('DBI:OCI8:dbname=ORCL', 'username', 'password')

# Perform Oracle database operations here

# Disconnect from the database
dbh.disconnect

  8. Ruby DBI connection string:

    • Description: The connection string specifies the details needed to connect to a specific database.
    • Code example:
      require 'dbi'

# Example connection string for MySQL
connection_string = 'DBI:Mysql:database=mydatabase;host=localhost'

# Connect to the database
dbh = DBI.connect(connection_string, 'username', 'password')

# Perform database operations here

# Disconnect from the database
dbh.disconnect

  9. Working with transactions in Ruby DBI:

    • Description: Use transactions to group multiple SQL statements into an atomic unit.
    • Code example:
      require 'dbi'

# Connect to a database
dbh = DBI.connect('DBI:SQLite3:test.db')

# Begin a transaction
dbh['AutoCommit'] = false

begin
  # Perform multiple database operations
  dbh.execute('INSERT INTO users (name) VALUES (?)', 'John Doe')
  dbh.execute('UPDATE accounts SET balance = balance - 100 WHERE user_id = 1')

  # Commit the transaction
  dbh.commit
rescue DBI::DatabaseError => e
  # Handle errors and rollback on failure
  puts "Transaction failed: #{e.message}"
  dbh.rollback
ensure
  # Revert to auto-commit mode
  dbh['AutoCommit'] = true
end

# Disconnect from the database
dbh.disconnect

  10. Error handling in Ruby DBI:

    • Description: Use exception handling to catch and handle database-related errors.
    • Code example:
      require 'dbi'

begin
  # Connect to a database
  dbh = DBI.connect('DBI:Mysql:database=mydatabase;host=localhost', 'username', 'password')

  # Perform database operations here

rescue DBI::DatabaseError => e
  # Handle database errors
  puts "Database Error: #{e.message}"
ensure
  # Disconnect from the database
  dbh.disconnect if dbh
end

  11. Ruby DBI prepared statements:

    • Description: Use prepared statements for improved performance and security.
    • Code example:
      require 'dbi'

# Connect to a database
dbh = DBI.connect('DBI:Mysql:database=mydatabase;host=localhost', 'username', 'password')

# Create a prepared statement
statement = dbh.prepare('INSERT INTO users (name) VALUES (?)')

# Execute the prepared statement with parameters
statement.execute('John Doe')

# Disconnect from the database
dbh.disconnect

  12. Ruby DBI query execution:

    • Description: Execute SQL queries using the execute method.
    • Code example:
      require 'dbi'

# Connect to a database
dbh = DBI.connect('DBI:SQLite3:test.db')

# Execute a query
result = dbh.execute('SELECT * FROM users')

# Process the query results
result.fetch do |row|
  puts "User ID: #{row['user_id']}, Name: #{row['name']}"
end

# Disconnect from the database
dbh.disconnect

  13. Ruby DBI fetch results:

    • Description: Use the fetch method to retrieve results from a query.
    • Code example:
      require 'dbi'

# Connect to a database
dbh = DBI.connect('DBI:Mysql:database=mydatabase;host=localhost', 'username', 'password')

# Execute a query
result = dbh.execute('SELECT * FROM users')

# Fetch and print results
result.fetch do |row|
  puts "User ID: #{row['user_id']}, Name: #{row['name']}"
end

# Disconnect from the database
dbh.disconnect
Copyright © devanddep.com | All Rights Reserved