How can I create an ODBC connection from .bat file in Windows?

Question

I need a batch script to create an ODBC/DSN connection from a .bat file in Windows. How can I do this?

Answer 1

Sure.

Manually create an ODBC connection on your PC using the "Data Sources (ODBC)" applet in Admin tools.

Launch regedit and go to HKLM\Software\ODBC and export the whole lot from ODBC down to a .reg file. This is a right click option on the folder.

Open the exported file in Notepad now you need to delete everything that doesn't refer to your particular ODBC connection.

You should end up with a registry file looking something like this

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
"TEST"="SQL Server"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\TEST]
"Driver"="C:\\Windows\\system32\\SQLSRV32.dll"
"Server"="SERVERNAME"
"LastUser"="USERNAME"
"Trusted_Connection"="Yes"

It may be slightly different depending on connection settings and such like. You will need the two sections, the data sources defines the type of connection, in this instance a SQL server one, then the bit that tells you what it is called, which driver to use and what sort of authentication to use. this will all be done when you set up the connection, you just need the .reg file with it in.

You can then import it on any pc by either double clicking the reg file or if you have the facility run a script to import it using regedit. You can call this import from a batch file:

regedit /s FileToImport.reg

Answer 2

@Echo Off
cd %systemroot%\system32
call :IsAdmin

SET /P psqlname=PostgreSQL Link datasource ismini  Giriniz : 
reg.exe add "HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources" /v "%psqlname%" /t REG_SZ /d "PostgreSQL Unicode(x64)" /f
reg.exe add "HKLM\SOFTWARE\ODBC\ODBC.INI\%psqlname%" /v  "Driver" /t REG_SZ /d "C:\\Program Files\\psqlODBC\\1301\\bin\\psqlodbc35w.dll" /f
reg.exe add "HKLM\SOFTWARE\ODBC\ODBC.INI\%psqlname%" /v  "CommLog" /t REG_SZ /d "0" /f

reg.exe add "HKLM\SOFTWARE\ODBC\ODBC.INI\%psqlname%" /v  "Debug" /t REG_SZ /d "0" /f
reg.exe add "HKLM\SOFTWARE\ODBC\ODBC.INI\%psqlname%" /v  "Fetch" /t REG_SZ /d "100" /f
reg.exe add "HKLM\SOFTWARE\ODBC\ODBC.INI\%psqlname%" /v  "UniqueIndex" /t REG_SZ /d "1" /f
reg.exe add "HKLM\SOFTWARE\ODBC\ODBC.INI\%psqlname%" /v  "UseDeclareFetch" /t REG_SZ /d "0" /f
reg.exe add "HKLM\SOFTWARE\ODBC\ODBC.INI\%psqlname%" /v  "UnknownSizes" /t REG_SZ /d "0" /f
reg.exe add "HKLM\SOFTWARE\ODBC\ODBC.INI\%psqlname%" /v  "TextAsLongVarchar" /t REG_SZ /d "1" /f
reg.exe add "HKLM\SOFTWARE\ODBC\ODBC.INI\%psqlname%" /v  "UnknownsAsLongVarchar" /t REG_SZ /d "0" /f
reg.exe add "HKLM\SOFTWARE\ODBC\ODBC.INI\%psqlname%" /v  "BoolsAsChar" /t REG_SZ /d "1" /f
reg.exe add "HKLM\SOFTWARE\ODBC\ODBC.INI\%psqlname%" /v  "Parse" /t REG_SZ /d "0" /f
reg.exe add "HKLM\SOFTWARE\ODBC\ODBC.INI\%psqlname%" /v  "MaxVarcharSize" /t REG_SZ /d "255" /f
reg.exe add "HKLM\SOFTWARE\ODBC\ODBC.INI\%psqlname%" /v  "MaxLongVarcharSize" /t REG_SZ /d "8190" /f
reg.exe add "HKLM\SOFTWARE\ODBC\ODBC.INI\%psqlname%" /v  "ExtraSysTablePrefixes" /t REG_SZ /d "" /f
reg.exe add "HKLM\SOFTWARE\ODBC\ODBC.INI\%psqlname%" /v  "Description" /t REG_SZ /d "" /f
reg.exe add "HKLM\SOFTWARE\ODBC\ODBC.INI\%psqlname%" /v   "UID"        /t REG_SZ /d "postgres" /f
SET /P dbname=Baglanmak istediginiz Postgresql Database name  Giriniz : 
reg.exe add "HKLM\SOFTWARE\ODBC\ODBC.INI\%psqlname%" /v "Database" /t REG_SZ /d "%dbname%" /f
SET /P Username=Baglanmak istediginiz Database Kullanici ismini giriniz : 
reg.exe add "HKLM\SOFTWARE\ODBC\ODBC.INI\%psqlname%" /v "Username"  /t REG_SZ /d "%Username%" /f
SET /P  Password=Baglanmak istediginiz Database Kullanici password bilgisini  giriniz  : 
reg.exe add "HKLM\SOFTWARE\ODBC\ODBC.INI\%psqlname%" /v "Password"  /t  REG_SZ /d "%Password%" /f
SET /P  Servername=Baglanmak istediginiz Database Servername adresini giriniz : 
reg.exe add "HKLM\SOFTWARE\ODBC\ODBC.INI\%psqlname%" /v "Servername" /t REG_SZ /d  "%Servername%" /f
SET /P Port=Portu numarasini giriniz : 
reg.exe add "HKLM\SOFTWARE\ODBC\ODBC.INI\%psqlname%" /v  "Port"  /t  REG_SZ /d      "%Port%"    /f

echo lutfen odbc baglantilarini test edin
Pause & Exit

Answer 3

We can make some change to previous script of Gerald Schneider:

@echo Off
cd %systemroot%\system32
set  psqlname=Dsn_name
set  dbname=database
set  Username=Username
set  Password=password
set  Servername=servername
set  Port=5432
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\\%psqlname%\\\\"
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources" /v "%psqlname%" /t REG_SZ /d "PostgreSQL Unicode(x64)" /f
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\\%psqlname%\\\\" /v  "Driver" /t REG_SZ /d "C:\Program Files\psqlODBC\0905\bin\psqlodbc35w.dll" /f
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\\%psqlname%\\\\" /v  "CommLog" /t REG_SZ /d "0" /f
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\\%psqlname%\\\\" /v  "Debug" /t REG_SZ /d "0" /f
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\\%psqlname%\\\\" /v  "Fetch" /t REG_SZ /d "100" /f
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\\%psqlname%\\\\" /v  "UniqueIndex" /t REG_SZ /d "1" /f
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\\%psqlname%\\\\" /v  "UseDeclareFetch" /t REG_SZ /d "0" /f
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\\%psqlname%\\\\" /v  "UnknownSizes" /t REG_SZ /d "0" /f
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\\%psqlname%\\\\" /v  "TextAsLongVarchar" /t REG_SZ /d "1" /f
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\\%psqlname%\\\\" /v  "UnknownsAsLongVarchar" /t REG_SZ /d "0" /f
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\\%psqlname%\\\\" /v  "BoolsAsChar" /t REG_SZ /d "1" /f
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\\%psqlname%\\\\" /v  "Parse" /t REG_SZ /d "0" /f
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\\%psqlname%\\\\" /v  "MaxVarcharSize" /t REG_SZ /d "255" /f
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\\%psqlname%\\\\" /v  "MaxLongVarcharSize" /t REG_SZ /d "8190" /f
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\\%psqlname%\\\\" /v  "ExtraSysTablePrefixes" /t REG_SZ /d  /f
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\\%psqlname%\\\\" /v  "Description" /t REG_SZ /d  /f
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\\%psqlname%\\\\" /v   "UID"        /t REG_SZ /d "postgres" /f
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\\%psqlname%\\\\" /v "Database" /t REG_SZ /d "%dbname%" /f
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\\%psqlname%\\\\" /v "Username"  /t REG_SZ /d "%Username%" /f
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\\%psqlname%\\\\" /v "Password"  /t  REG_SZ /d "%Password%" /f
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\\%psqlname%\\\\" /v "Servername" /t REG_SZ /d  "%Servername%" /f
reg.exe add "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\\%psqlname%\\\\" /v  "Port"  /t  REG_SZ /d      "%Port%"    /f
exit 0