Article

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

« Go Back

Information

 
Article Number000074768
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/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.6 Driver};HOST=localhost;PORT=12345;DB=sports2000;UID=sysprogress;PWD=sysprogress;DIL=0"
$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
References to Other Documentation:

Progress Article(s):
000022406, How to setup the connection string for an ODBC DSN-less connection to a Progress database?
Attachment
Last Modified Date2/27/2018 4:13 PM


Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025