Connecting to Oracle database in a Unity script

[Rob_Biddle]

Are there any guidelines for how to connect to an external Oracle database in a Unity script?

I'm familiar with using SqlClient objects and System.DataSets to connect to SQL Server databases, but this is the first time I'm trying to work with an Oracle database.  I know I don't want to use System.Data.OracleClient since it's deprecated.

My ultimate goal is to have a Unity script that will run in a workflow timer.  Do I need to have an Oracle client installed on the AppServer and/or on the server running the Timer Service?  I'm trying to come up with a solution that would require a minimal amount of Oracle software installed on our various AppServers and Timer Service servers.  This is a new project which needs to retrieve data from (and update data to) an Oracle database.  Other than that, our OnBase installation doesn't use Oracle at all.

I installed the 'Oracle Developer Tools for Visual Studio' on my machine, and created a Windows Form which successfully connects to an Oracle database (with hard-coded user id, password, and datasource port, service name, and host, so as to not use the tnsnames.ora file) and retrieves data from a table.  So that was one small success.  Next step was to try to duplicate this in a Unity script.  Our OnBase Configuration module is on a server, so, instead of installing 'Oracle Developer Tools for Visual Studio' on the server, I copied the 'Oracle.DataAccess.dll' to the server and used the Unity 'Import Assembly Wizard' to import it.  I can now reference 'Oracle.DataAccess' in my Unity project, my code compiles.  I basically copied and pasted my VS code into my Unity project, with its OracleConnection, OracleCommand, and OracleDataReader.  But I get a runtime exception "The type initializer for 'Oracle.DataAccess.Client.OracleConnection' threw an exception." with an InnerException "Oracle.DataAccess.Client.OracleException The provider is not compatible with the version of Oracle client".

As I'm wading through various Google search results, it appear I may need to have an Oracle client installed on the server, or perhaps some additional Oracle dll's.  But before continuing I thought I'd ask the advice of anyone who may have done this before.

Thank you in advance for any help!

Rob

[Hank_Thomas]

Follow-up question for you on those Connection Strings. I have setup my connection string successfully, but am not aware of how to use it.I am a beginner trying to write my first Unity Script to run a query against the OB Database. I don't see the functionality that I need in any existing features in Studio. Is there any MRG or document detailing with Unity Scripting? ie( SqlConnection myConn = new ??? )Thanks

[Ulrik_Petersen]

Hank,

If I understand your question correctly, it sounds like you need to add a reference to ODP.NET to your project.  I would recommend checking out the documentation at http://www.oracle.com/technetwork/topics/dotnet/index-085163.html. ; Specifically the "Oracle By Example for Visual Studio" for your version of VS.  There are also code samples you may find useful.

Also, since this isn't strictly speaking a Unity API issue, you might want to check out Stack Overflow.  You will find a lot of information on there about ODP.NET.

Thanks,

Ulrik

[Hank_Thomas]

Hello Ulrik, I probably was not very clear. I was editing a Unity Script in Studio and I was referring to the Ribbon button entitled “Connection Strings” I had set up a connection string and named it OnBase ( original I am not ) with user “hsi”. But I did not see how to reference the string I just created. My question would be the same for Oracle or SQL Server.Am I making more sense?ThanksHank

[Ulrik_Petersen]

Hank,

Most of my experience is with using the Unity API in custom .NET applications, so I may not be the best person to address your question.

That being said, I believe the OP's solution was to use the Oracle library instead of the functionality you are trying to leverage.  The resources I mentioned will help you with using that library.

You might want to start a separate thread if you want to try to avoid that approach.  Sorry that I couldn't provide a more direct answer to your question. 

Thanks,

Ulrik

[#6]

This is a sample C# Unity script snippet for a Connection String named "OracleSample"

   if(connection == null)    

{     connection = ConfigurationHelper.GetConnection(

     app.Configuration, "OracleSample");  

   connection.Open();    

}