Auto Create Spire ODBC

Posted inBlog

If you are using ODBC to connect applications like Excel or Access to Spire, it can be convenient to create an ODBC datasource on your computer. This makes connecting easier and saves the password but does not make that password available to users.

We can automate the creation of this datasource making it easier to deploy\change across multiple computers.

Here is a sample batch file that does the trick. As a bonus, it also tests the datasource to ensure your settings are correct.

Most users will only need to edit the DSN_NAME, DB_NAME, DB_SERVER, DB_USER and DB_PWD entries.

  • DSN_NAME: Can be anything you choose. I prefer to match it with the DB_NAME
  • DB_NAME: The short name of your Spire company database shown in the Spire Administrator web page.
  • DB_SERVER: The name or IP address of your Spire server.
  • DB_USER: This is normally “spireapp” but sometimes “postgres”.
  • DB_PWD: This is on your server in the file: C:\ProgramData\Spire\spire.ini. Here is the entry in the spire.ini that corresponds to this example password (bold text): dburl =postgresql://spireapp:dFy7U1iis7FO4Y8L@localhost/spire
@echo off
REM ============================================
REM  SETTINGS – EDIT THESE VALUES
REM ============================================

REM DSN name (what shows in ODBC Data Sources)
set "DSN_NAME=INSPIRE"

REM Database connection
set "DB_NAME=INSPIRE"
set "DB_SERVER=localhost"
set "DB_USER=spireapp"
set "DB_PWD=dFy7U1iis7FO4Y8L"

REM These settings are for 64 bit Excel\Access
set "ODBC_CONF=%SystemRoot%\System32\odbcconf.exe"
set "PG_DRIVER=PostgreSQL Unicode(x64)"

REM If using 32 bit Excel\Access, REM out the above settings and unREM these
REM set "ODBC_CONF=%SystemRoot%\SysWOW64\odbcconf.exe"
REM set "PG_DRIVER=PostgreSQL Unicode"

REM ============================================
REM  NO NEED TO EDIT BELOW THIS LINE
REM ============================================

echo.
echo Creating ODBC DSN "%DSN_NAME%"...

"%ODBC_CONF%" CONFIGDSN "%PG_DRIVER%" ^
  "DSN=%DSN_NAME%|DATABASE=%DB_NAME%|SERVER=%DB_SERVER%|UID=%DB_USER%|PWD=%DB_PWD%"

echo.
echo Datasource Created.

echo.
echo Testing DSN "%DSN_NAME%"...

powershell -NoLogo -NoProfile -Command ^
  "$cs = 'DSN=%DSN_NAME%;UID=%DB_USER%;PWD=%DB_PWD%';" ^
  "$cn = New-Object System.Data.Odbc.OdbcConnection $cs;" ^
  "try { $cn.Open(); Write-Host 'DSN test PASSED.'; exit 0 } catch { Write-Host 'DSN test FAILED:' $_.Exception.Message; exit 1 } finally { $cn.Close() }"

if errorlevel 1 (
  echo *** DSN test FAILED ***
) else (
  echo DSN test OK.
)

echo.
pause

Note: Once completed, this file contains a password that should not be shared with all users. Do not make this file generally available.

You can download this batch file here: CreateSpireODBC.bat

64-Bit vs 32-Bit Excel

Most Spire users have 64-bit Excel \ Access on their computers. If so you may need to install the 64-bit PostgreSQL drivers. Spire only ships with 32-bit PostgreSQL drivers. You can download those drivers here: 64-bit PostgreSQL ODBC Drivers

If you ARE using 32-Bit Excel, then you need to change the driver name specified in the batch file. Once modified, that section should look like this:

REM These settings are for 64 bit Excel\Access
REM set "ODBC_CONF=%SystemRoot%\System32\odbcconf.exe"
REM set "PG_DRIVER=PostgreSQL Unicode(x64)"

REM If using 32 bit Excel\Access, REM out the above settings and unREM these
set "ODBC_CONF=%SystemRoot%\SysWOW64\odbcconf.exe"
set "PG_DRIVER=PostgreSQL Unicode"