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:
- ODBC Data Source Administrator (64-bit)
- Excel ODBC Query (DQY) File
- PowerShell Script
- .Net Code using C#
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.
Now, let’s test this DSN. Click a Test Connection button as shown in Figure 2. You should see a success 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:
- Microsoft Excel
- PostgreSQL
- Sample database with a structure mentioned in the next subsection
- Devart ODBC Driver for PostgreSQL
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:
CREATE TABLE IF NOT EXISTS public.person
(
"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.
XLODBC
1
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.
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")
$conn.open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = "SELECT COUNT(*) AS RecordCount FROM actor"
$reader = $cmd.ExecuteReader()
$reader.Read()
$reader[0]
$reader.Close()
$conn.Close()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.
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 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);
try
{
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();
reader.Read();
if (reader.HasRows)
{
// show the result
MessageBox.Show("Table "
+ txtTable.Text
+ " has " + reader[0].ToString()
+ " records","Result",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
// close the DataReader and Connection
reader.Close();
odbcConnection.Close();
}
}
catch (Exception err)
{
// display any runtime error
MessageBox.Show(err.Message,"Error"
, MessageBoxButtons.OK
, MessageBoxIcon.Error);
}
finally
{
// dispose the connection object
odbcConnection.Dispose();
}
}
else
{
// 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.
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.
Then, we test the connectivity. We start with the table with SELECT permissions. Check out Figure 8.
As expected, this should go well.
Then, test the billing table where there is no permission. See the result in Figure 9.
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.
