Friday, July 12, 2024
HomeHow To4 Ways to Test an ODBC Connection

4 Ways to Test an ODBC Connection

Are you looking for different ways to test an ODBC connection? In this article, we will discuss 4 easy ways to test the connectivity and show how to do it with examples.

In our case, the test environment is a laptop with a Windows 11. MySQL 8, PostgreSQL 14, and also ODBC drivers for these databases are installed in advance.

We will test the connection to different data sources using the following instruments:

So, let’s dive in.

Using ODBC Data Source Administrator (64-bit)

You may already know this method. Here, we are going to access the Sakila MySQL sample database. But first, let’s create the MySQL user permissions to this database. Here’s the code:

USE sakila;
CREATE USER 'edwin'@'localhost' IDENTIFIED BY 'e@10-4ward';    
GRANT ALL ON sakila TO 'edwin'@'localhost';

To run this code, you can use any MySQL database administration tool.

Then, you have to create the data source name (DSN). For this, run the ODBC Data Source Administrator (64-bit) and create a DSN using the MySQL Connector/ODBC or the Devart ODBC Driver for MySQL. Configure the DSN with the credentials we created above.

ODBC DSN configuration for the MySQL Sakila database.
Figure 1. The ODBC DSN configuration for the Sakila database.

Now, let’s test this DSN. Click a Test Connection button as shown in Figure 2. You should see a success message.

ODBC test connection successful for Sakila database.
Figure 2. Successful connection to Sakila database message.

But is there a way to test it outside the ODBC Data Source Administrator? Let’s move on to the following method.

ODBC Test Connection Using a DQY File

This method is not just a connection test. A simple query will also run here. The result of the query will be displayed in Excel. This is quite handy if you have Excel installed on your computer.

Before we proceed, you should install the following on your machine:

Creating the DQY File and Testing a Connection 

We have a PostgreSQL sample database called sample. We added a table called a person with the following structure:

    "ID" bigint NOT NULL,
    "LastName" character varying(20) COLLATE pg_catalog."default" NOT NULL,
    "FirstName" character varying(20) COLLATE pg_catalog."default" NOT NULL,
    "MiddleName" character varying(20) COLLATE pg_catalog."default",
    "birthDate" date,
    CONSTRAINT person_pkey PRIMARY KEY ("ID")

Then, fill in the table with the names of 4 actors from Marvel’s Avengers. We also added user permissions to the database for ourselves. The User ID is edwin and the password is e@10-4ward. Now, let’s test it with a DQY file.

First, create an Excel ODBC Query File (DQY). Do this by running any text editor. Then, paste the code from below.

DRIVER=Devart ODBC Driver for PostgreSQL;Data Source=localhost;Database=sample;User ID=edwin;Password=e@10-4ward;Schema=public
SELECT * FROM person;

Save it to a file named test-postgres-connectivity.dqy. Note the DQY file extension.

Before we let Excel open this file, let’s describe what’s in this file.

  • Line 1: The file header. It should be XLODBC or Excel will see this file as invalid or damaged.
  • Line 2: We can’t find any documentation on this. We tried typing 1, 100, X, and a blank space and Excel didn’t have any problem with it. But when removing this line, Excel can’t open the file. So, for simplicity, use 1.
  • Line 3: A connection string without a DSN (because we didn’t set it up in the ODBC Data Source Administrator (64-bit). In our example above, this uses the driver, database, and permissions we needed earlier.
  • Line 4: Request. You can use any query that is valid to access the database.

Now, let’s run it. 

So, double-click the DQY file. Excel will open it and block the connection. But if you continue by clicking Yes, the 4 records from the person table will display. You will see the successful output as shown below.

Output of running the DQY file in Excel. The 4 records of the Person table is displayed.
Figure 3. The result of the successful ODBC test connection using a DQY file.

ODBC Test Connection Using PowerShell

Another way to test an ODBC connection is to use PowerShell.

What you need:

  • PowerShell
  • The PowerShell script (see below).
  • The Sakila-Connectivity-DSN we created earlier

Testing the Connection Using the PowerShell Script

There is a simple PowerShell script below. This is the same MySQL DSN we used in Figure 1 earlier.

$conn = New-Object System.Data.Odbc.OdbcConnection("DSN=Sakila-Connectivity-DSN")
$cmd = $conn.CreateCommand()
$cmd.CommandText = "SELECT COUNT(*) AS RecordCount FROM actor"
$reader = $cmd.ExecuteReader()

The script above uses the .Net ODBC connection object and the Sakila-Connectivity-DSN. It will connect to the Sakila database and count the number of rows in the actor table. Using a DataReader, it will display the number of rows.

See the console output in Figure 4.

Using a PowerShell script to do ODBC test connection and run a query.
Figure 4. Using PowerShell to test ODBC DSN and MySQL connectivity.

If there is any problem, it will show up in the console. Figure 4 shows 201 rows from the actor table. So we’re good here.

You can also make this script a function. This function can take a DSN and a table as parameters. But we leave it up to you in PowerShell.

ODBC Test Connection Using .Net and C#

Finally, the fourth method uses code. This could be cumbersome but eventually, you may use the DSN in code.

To give you more value, the next code will try to test any DSN from any SQL database and will also count the rows in the table you specify. You can paste it later in your Visual Studio project.

What you will need:

  • Visual Studio 2022 with Windows Forms project template
  • System.Data.Odbc NuGet package (you need to add this dependency to the project).
  • A couple of DSNs you want to test.

The Visual Studio Project and Code

Look at the screenshot of the app below.

The ODBC Test connection app done in C#.Net
Figure 5. The ODBC DSN Test app.

The lines of code are in the Test button click event. So, check it out below.

private void btnTest_Click(object sender, EventArgs e)
    // DSN and table are required
    if(txtDSN.Text != "" & txtTable.Text != "")   
        // create an ODBC connection
        var odbcConnection = new OdbcConnection("DSN=" + txtDSN.Text);
            odbcConnection.Open();  // open the connection

            using (var cmd = odbcConnection.CreateCommand())
                cmd.CommandText = @"SELECT COUNT(*) 
                                   as RecordCount FROM " 
                                  + txtTable.Text;
                // execute the query
                var reader = cmd.ExecuteReader();  

                if (reader.HasRows)
                    // show the result
                    MessageBox.Show("Table " 
                                  + txtTable.Text 
                                  + " has " + reader[0].ToString() 
                                  + " records","Result",
                // close the DataReader and Connection
        catch (Exception err)
            // display any runtime error
                          , MessageBoxButtons.OK
                          , MessageBoxIcon.Error);
            // dispose the connection object
        // show validation message
        MessageBox.Show("DSN and Table to Test are required!"
                       , "Validation"
                       , MessageBoxButtons.OK
                       , MessageBoxIcon.Exclamation);

The code above also uses a try-catch-finally block. This will catch errors in case you tried a 32-bit DSN or a non-existent DSN, or entered a non-existent table or view.

But why do we need to do a query? Isn’t a connection test enough? Let’s dive in.

Testing with Limited Permissions

What if you have limited access to a database and you don’t know it yet?

Consider this permission for another database. We will be using the same MySQL account we used earlier (see Figure 1). Check out Figure 6.

Granting SELECT only permission to a MySQL database table.
Figure 6. Granting SELECT permission on 1 table and showing the results.

As you can see in Figure 6, only the client table has SELECT permission. The SHOW GRANTS command shows the result of the GRANT SELECT.

Now, we prepare the DSN as shown in Figure 7.

Setting up the DSN to a database with limited permissions.
Figure 7. DSN configuration to a database with limited permissions.

Then, we test the connectivity. We start with the table with SELECT permissions. Check out Figure 8.

Connection successful on an ODBC test connection on a database with limited permissions.
Figure 8. The app successfully read and displayed the number of rows in the client table.

As expected, this should go well.

Then, test the billing table where there is no permission. See the result in Figure 9.

Error occurs in an ODBC test connection with limited database permissions.
Figure 9. Permission denied on the billing table.

As expected, this is not allowed.

So, when you see an error like in Figure 9, it’s time to talk to your database administrator and ask for more permissions.

You can try this out with another 64-bit DSN.

So, what is the point? Sometimes it pays to test the connection with a query. You can see if you have table permissions before charging into coding.