Connection to Oracle ODBC Datasource via Visual Studio asp.net

Question

I would like to connect to a oracle db in our network, but I do not know how. In visual code, we use this .env for our database connection:

# db credentials
ORACLEDB_USER=x
ORACLEDB_PASSWORD=x
ORACLEDB_HOSTNAME=xxxx
ORACLEDB_PORT=1521
ORACLEDB_SERVICE_NAME=xxxx
IF_INV_PATH=
inv_target_path=
TIFF_PATH_ROOT=
inv_path=

which works. When I now try to use the Visual Studio ODBC Server connection I have to use a connection string. I tried something like this (found via google): (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xxxx)));uid=xxxx;pwd=xxxx;

where I get this error:

(which loosely translates to: Datasource not found, and no default driver given)

I'm pretty confused and have no experience with setting up a connection to a oracle db. I have installed the x64 driver from this site: https://docs.microsoft.com/de-de/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15 But it doesn't change anything.

The Microsoft ODBC driver is 32-bit (and depreciated) Install the 32-bit Oracle Client or use the 64-bit ODBC Driver from Oracle. — Wernfried Domscheit
The link points to an ODBC driver to SQL Server - not Oracle Database. — Wernfried Domscheit
And here a link where you can find the ODBC driver from Oracle: oracle.com/database/technologies/instant-client/downloads.html — Wernfried Domscheit
Thank you, i'll try it monday. Btw I know that my connection string is correct, because its the same that visual code returns if I print the python connection object. Hopefully this will solve my Problem — user3793935
It's not a matter of your connection string. The ODBC Driver, the installed Oracle client and your application have to match (i.e. all three must be either 32-bit or 64-bit, you cannot mix it) — Wernfried Domscheit

user3793935 user3793935 · Accepted Answer · 2021-07-12T08:40:08

Thanks to Wernfried Domscheit I finally got it to work. Thanks man! Here is how I did it:

Install Visual Studio
Download Version 18.5 Basic Package / ODBC Package

https://www.oracle.com/de/database/technologies/instant-client/microsoft-windows-32-downloads.html

https://www.oracle.com/de/database/technologies/instant-client/winx64-64-downloads.html#ic_winx64_inst
unzip both in the same directory (I choose C:\oracle\odbc\x86(x64)\instantclient_18_5) and install the driver via cmd as admin: C:\oracle\odbc\x86\instantclient_18_5>odbc_install.exe

create C:\oracle\admin and put tnsnames.ora in

dev =
  (DESCRIPTION =
    (FAILOVER = ON)
    (LOAD_BALANCE = OFF)
    (CONNECT_TIMEOUT=3)
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxx)(PORT = 1521))
    )
    (CONNECT_DATA = 
      (SERVER = DEDICATED)
      (SERVICE_NAME = xxxxxx)
      (SID = ORCL)
    )
  )

Systemvariables → Add TSN_ADMIN : C:\oracle\admin to the environment variables and C:\oracle\odbc to the path
Open ODBC-Datenquelle-Adminsitrator 32 / 64 bit→ System-DSN and add
Open Visual Studio → Server Explorer → Connect to Database → Microsoft ODBC Data Source (ODBC) and choose your user / system data source name

Connection to Oracle ODBC Datasource via Visual Studio asp.net

1 Answers