Using ODBC drivers to connect to Postgres database via Powershell



  • I've routinely connected to a postgres database via ODBC using a powershell script. Like this....

    $DBConnectionString = "Driver={PostgreSQL Unicode(x64)};Server=$MyServer;Port=$MyPort;Database=$MyDB;Uid=$MyUid;Pwd=$MyPass;Timeout=$timeout;CommandTimeout=$commandTimeout;"
    $script:DBConn = New-Object System.Data.Odbc.OdbcConnection;
    $DBConn.ConnectionString = $DBConnectionString;
    $DBConn.Open();
    

    I've just started getting the error....

    Exception calling "Open" with "0" argument(s): "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"
    

    Since this script is running a new build, I'm guessing a configuration error. Server is windows 11, Postgre is a fresh installation of 14.2 downloaded from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads The latest ODBC drivers came from the file psqlodbc_13_02_0000-x64.zip also downloaded from https://www.postgresql.org/ftp/odbc/versions/msi/

    I think the ODBC driver installation was successful because if I run ODBC 64 bit administrator, click on the 'drivers' tab I can see the 4 drivers I expect

    1. PostgreSQL ANSI
    2. PostgreSQL ANSI(x64)
    3. PostgreSQL Unicode
    4. PostgreSQL Unicode(x64)

    I've tried editing the script to try each of the 4 drivers I think I have - but get the same error each time. I know the database is up and working, I can connect via pgAdmin and psql.

    It's a new, clean installation of something that has worked on previous servers for several years. What have I forgotten or done wrong?

    The script is being run from a desktop shortcut with this target (I think this demonstrates I'm running the 64 bit version)

    "%SystemRoot%\syswow64\WindowsPowerShell\v1.0\powershell.exe " -NoExit -command "&c:\pwrShl\TestToDb_015.ps1
    

    I've tried setting up a DSN (just for test) and I the ODBC 'test' button says the connection worked. I've tried running the powershell script as administrator - makes no difference.



  • It should work:

    $DBConnectionString = "Driver={PostgreSQL Unicode(x64)};Server=$MyServer;Port=$MyPort;Database=$MyDB;Uid=$MyUid;Pwd=$MyPass;Timeout=$timeout;CommandTimeout=$commandTimeout;"
    $DBConn = [System.Data.Odbc.OdbcConnection]::new($DBConnectionString)
    $DBConn.Open();
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2