How to connect Ruby with Google Drive using ODBC

[daniel]

Introduction

In this article, we will show how to connect to the Google Drive API with Ruby using the ODBC interface.

Connecting Ruby to Google Drive via ODBC allows seamless interaction with cloud storage, enabling you to automate file management, retrieve metadata, and integrate Google Drive with other applications.

By leveraging the ruby-odbc gem, you can efficiently access and manipulate files stored in Google Drive, enhancing automation and workflow efficiency. This guide will walk you through the installation, configuration, and troubleshooting process to establish a reliable connection between Ruby and Google Drive using ODBC

Prerequisites

• ODBC PowerPack: Download and install the ZappySys ODBC here.
• Google Drive Account

Steps

Step 1. Installing Ruby and the ruby-odbc Gem

Some versions of Ruby may not be fully compatible with the ruby-odbc gem. To ensure proper functionality, using Ruby 3.1.xis recommended, as newer versions may introduce compatibility issues.

1. Download the installer from Ruby installer
2. Install Ruby and DevKit.
3. Open Command Prompt as an administrator
4. Verify the version you are using with this command ruby -v
5. install the ruby-odbc gem with the following command: gem install ruby-odbc
6. Verify the gem installed with this command: gem list ruby-odbc
   Note: If you encounter installation issues, you may need administrative permissions

Step 2: Create/Select a Project in the Google API Console

1. Navigate to the Google API Console.

2. Click on the Project Dropdown at the top bar and either select an existing project or create a new one by clicking CREATE PROJECT.
   

   

3. Once the project is set, click ENABLE APIS AND SERVICES.

4. Enable both Sheets API and Drive API by searching for them and clicking ENABLE.
   

   

5. Return to the main screen and click on the OAuth Consent Screen tab. Provide the necessary details and save.

6. Move to the Credentials tab.

7. Click CREATE CREDENTIALS in the top bar, choose OAuth Client ID, select Desktop App as the Application Type, and click Create to obtain your Client ID and Secret.
   

   

Step 3: Create a New Driver

1. Open the ODBC Data Source by typing “ODBC” in the search box and launching the ODBC Data Source.
   

   

2. To gain access for yourself or other users, go to User DSN or System DSN. Go to the System tab for SQL Server Integration and add a new System DSN. Click the Add button.
   

   

3. From the driver list, select ZappySys API Driver, then select Google Drive in the connector list and press Continue.
   

   

Step 4: Google Drive Connector Configuration

1. Fill in the connector fields, including Client ID, Client Secret, and Scopes.

2. Generate the token and test the connection.
   

   

3. Go to the Preview Tab, select any table, and preview the result. Press OK to save the configuration.
   

   

Step 5. Connecting Ruby to ODBC

Once the DSN is set up, you can use ruby-odbc to establish the connection and run queries. Create a Ruby file, e.g. GoogleDriveRuby.rb

require 'odbc'

# DSN name configured in ODBC
# DSN name configured in ODBC
dsn = "API driver Google Drive"  # Replace with the correct name

begin
  # Connect to the database using DSN
  db = ODBC.connect(dsn)

  # Verify connection
  puts "✅ Successfully connected to ODBC"

  # Execute a test query
  query = "SELECT * FROM FilesList"  # Adjust according to your database
  stmt = db.run(query)

  # Check if there is data
  rows = stmt.fetch_all
  if rows.empty?
    puts "⚠️ No data found in the database."
  else
    # Display results
    rows.each do |row|
      puts row.inspect
    end
  end

  # Close connection
  stmt.close
  db.disconnect

rescue ODBC::Error => e
  puts "❌ Connection error: #{e.message}"
end

When you run the code, it will call the API and retrieve data.

Common errors and solutions

Error: cannot load such file -- dbi (LoadError)

Cause
You are trying to use the dbi gem, but it is not installed.

Solution
In the command line, run these commands to install the ODBC connectors.

gem install dbi
gem install dbd-odbc

---

Error: Failed to build gem native extension.

Cause
A compiler or necessary libraries are missing when compiling the gem.

Solution
In the command line, install MSYS2 and ensure gcc and make are available.

---

Error: Data source name not found, and no default driver specified.

Cause
The DSN is not configured, or the driver is not installed.

Solution

• Verify the configuration in odbcad32.exe
• Ensure the DSN name in the code exactly matches the one configured in ODBC.

---

Error: Issues with Ruby Version

Cause
Some Ruby versions may not be compatible with the ruby-odbc gem, especially if they are too new or old.

Solution

• Check your Ruby version with:

  ruby -v
  

• If your version is 3.2.x and you experience issues, try using an earlier version of Ruby (e.g., 2.7.x) or use RVM to switch versions:

  rvm install 2.7.8
  rvm use 2.7.8
  

---

Conclusion

Integrating Ruby with Google Drive through ODBC provides a powerful way to manage cloud storage, automate file operations, and enhance data accessibility. By following this guide, you have learned how to set up the connection, troubleshoot potential issues, and streamline interactions with Google Drive.

To get started, download and install the ZappySys ODBC PowerPack to enable ODBC connectivity with Google Drive.

If you experience any challenges, review the error logs and verify your DSN configuration. If you still need assistance, feel free to reach out to ZappySys Support.

Related Links:

• Google DriveConnector for ODBC
• Ruby installer

Contact Us

If you encounter any challenges or have specific use cases, please contact our support team via chat or ticket.