Feedback
Did this article resolve your question/issue?

   

Article

OpenEdge How to test an ODBC connection on Windows using PowerShell?

« Go Back

Information

 
TitleOpenEdge How to test an ODBC connection on Windows using PowerShell?
URL Namehow-to-test-an-odbc-connection-dsn-on-windows-using-powershell
Article Number000180105
EnvironmentProduct: OpenEdge
Version: 10.x, 11.x
OS: Windows 7 and later
Question/Problem Description
How to use Windows Powershell to test an ODBC connection?
How to verify ODBC connection string using DSN?
How to check if DSN less ODBC connect string works?
 
Steps to Reproduce
Clarifying Information
Error Message
Defect Number
Enhancement Number
Cause
Resolution
On Windows 8 and higher and Windows server 2012 and higher
The following commands are used to get more information about the ODBC drivers installed and DSN's already defined:

powershell Get-OdbcDriver
(to list all installed ODBC drivers, both 32-bit and 64-bit)

powershell Get-OdbcDsn
(to list all ODBC User DSN(s) and System DSN(s) that are using either 32-bit or 64-bit ODBC drivers)

On Windows 7
T
he following commands must be used instead:

powershell Get-ItemProperty 'HKLM:\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers'
(list all 64 bit ODBC drivers on a 64 bit Windows machine or list all 32 bit ODBC drivers on a 32 bit Windows machine)
 
powershell Get-ItemProperty 'HKLM:\SOFTWARE\Wow6432Node\ODBC\ODBCINST.INI\ODBC Drivers'
(list all 32 bit ODBC drivers on a 64 bit Windows machine)

powershell Get-ItemProperty 'HKLM:\SOFTWARE\ODBC\ODBC.INI\*'
(to list all 64 bit ODBC DSN's)

powershell Get-ItemProperty 'HKLM:\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\*'
(to list all 32 bit ODBC DSN's)

To test the ODBC connection with a powershell script the command:
powershell .\QueryTest.ps1
for testing a 64 bit ODBC connection on a 64 bit Windows or a 32 bit ODBC connection on a 32 bit Windows or

C:\Windows\syswow64\WindowsPowerShell\v1.0\powershell .\QueryTest.ps1
for testing a 32 bit ODBC connection on a 64 bit Windows. 

Where QueryTest.ps1 is a text file containing script similar to: 
 
#Connection string can be either via DSN or DSN less - any properly formatted string works
#Sample connect via DSN
#$connectstring = "DSN=sports2000;Uid=sysprogress;Pwd=" + 'sysprogress' + ";"
#Sample connect using DSN less connection
$connectstring = "DRIVER={Progress OpenEdge 11.7 Driver};HOST=localhost;PORT=12345;DB=sports2000;UID=sysprogress;PWD=" + 'syprogress' + ";DIL=READ UNCOMMITTED"
​​​$sql = @'
select TOP 1 * from PUB.customer
'@
$conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)
$conn.open()
$cmd = New-Object system.Data.Odbc.OdbcCommand($sql,$conn)
$da = New-Object system.Data.Odbc.OdbcDataAdapter($cmd)
$dt = New-Object system.Data.datatable
$null = $da.fill($dt)
$conn.close()
$dt

- Replace the $connectstring with the connection to be tested.
- Replace the $sql string with any valid SQL statement from the database.

All the commands above have 'powershell ' added in front of the powershell commands to execute, when the commands are executed inside a powershell command prompt instead of a regular Windows command prompt then the 'powershell ' part in front of the powershell commands need to be removed.

Note: By default powershell may not have permissions to run scripts
To resolve that, run the following powershell commands first:
powershell -Command "set-executionpolicy remotesigned"
C:\Windows\syswow64\WindowsPowerShell\v1.0\powershell -Command "set-executionpolicy remotesigned"
Workaround
Notes
Last Modified Date5/16/2022 9:59 AM
Attachment 
Files 1. QueryTest.ps1
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.