Tuesday 18 July 2017

VB - Deploying an Application using Oracle Data Access

Introduction

It's great when you get your application to run in Visual Studio, or even from the compiled files on your PC, but at some stage you're going to want to deploy or share it. This is where I ran into problems with Oracle drivers.

Oracle  Drivers

You set up your Oracle access in VB by selecting the DLL that came with your client software and adding it to the project resources. Here's what mine looked like in Visual Studio..

Assigning the Oracle library as a project resource.
Then add the following imports into your program module..

Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types


But, although you've set your resources correctly, the DLL location and version is likely to be different on other machines you deploy to. So you'll probably to get errors.

You can start to fix this by setting the Copy Local value to be True, and then the DLL file gets bundled into the release folder when you re-compile.

Set Copy Local
But while this might get your locally compiled version working, it's likely that other machines will still have problems with driver compatibility errors.

Typically you'll see something like this..

Oracle.DataAccess.Client.OracleConnection' threw an exception. ---> Oracle.DataAccess.Client.OracleException: The provider is not compatible with the version of Oracle client

Solution

Download the Instant Client Basic Lite files from Oracle and unzip it to your PC. Copy the following library DLL files and put it into the same deploy folder as your executable:-

oci.dll
ociw32.dll
Oracle.DataAccess.dll 
orannzsbb11.dll
oraocci11.dll
oraociicus11.dll
OraOps11w.dll

Hopefully that will get you going.

Friday 7 July 2017

VB - Connecting to an Oracle Database without using a TNS Entry

Introduction

Connecting to an Oracle database isn't too much of a problem, there's examples everywhere on the web along the following lines..

Dim conn As New OracleConnection()
Dim connstr as String, dataSource as String, userId as String, password as String
dataSource = "dev10g"
userId = "jsmith"
password = "letmein"
connstr = "Data Source=" + dataSource + "; User Id=" + userId + "; Password=" + password + ";"
conn.ConnectionString = connstr
Try
  conn.Open()
Catch ex As Exception
  ' Database connection failed
  conn.Dispose() 'Dispose of the connection
  Exit Sub
End Try


This works ok, but it's not very portable unless everyone that wants to use it sets themselves a TNS entry called "dev10g" in their tnsnames.ora file.

What if we wanted to define the host and port number in the config, and then connect without using TNS?

Connecting to Oracle Directly

In practice all we need to do is alter the connection string to provide the information that the tnsnames.ora would have sent. So the code above doesnt change much.

Dim conn As New OracleConnection()
Dim connstr As String
Dim dbServer As String, dbPort As String, dbServiceName As String
Dim userId As String, password As String

dbServer = "lordv01"
dbPort = "1521"
dbServiceName = "dev10g"
userId = "jsmith"
password = "letmein"
dconnstr = "Data Source=" + dbServer + ":" + dbPort + "/" + dbServiceName + ";User ID=" + userId + ";Password=" + password
conn.ConnectionString = connstr
Try
  conn.Open()
  Catch ex As OracleException
  conn.Dispose() 'Dispose of the connection
  Exit Sub
End Try


It took me a bit of poking around to find the right syntax, so hopefully someone will find this useful.