HOW TO connect the designer to Oracle cloud?

Posts   
 
    
PeterVD
User
Posts: 15
Joined: 16-Jan-2007
# Posted on: 06-Jun-2022 11:22:10   

Hi,

FYI: using LLBLGen Pro version 5.7. Build 5.7.3.

Is it possible to connect to an Oracle Cloud database? I was trying to get the designer to connect to an Always Free database.

In the designer, using the ODP.NET Oracle Driver v21.6.1 , I can enter credentials, server and service name. When I try to connect I get this error:

-----[Core exception]--------------------
   at OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, CriteriaCtx criteriaCtx, String affinityInstanceName, Boolean bForceMatch)
   at OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, CriteriaCtx criteriaCtx, String affinityInstanceName, Boolean bForceMatch)
   at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword, CriteriaCtx criteriaCtx)
   at Oracle.ManagedDataAccess.Client.OracleConnection.Open()
   at SD.LLBLGen.Pro.Gui.Controls.WizardPages.MetaDataRetrievalWizard_Step_ConnectionData.TestConnectionData(Boolean showSuccess)
-----[InnerException]--------------------
   at OracleInternal.Network.OracleCommunication.DoConnect(String tnsDescriptor)
   at OracleInternal.ServiceObjects.OracleConnectionImpl.Connect(ConnectionString cs, Boolean bOpenEndUserSession, CriteriaCtx criteriaCtx, String instanceName)

Looking at the proposed connectionstrings on the DB Connections tab of my Oracle Cloud database, I see there is an ssl-section:

(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.xxxxxx.oraclecloud.com))
(connect_data=(service_name=xxxxxxx.adb.oraclecloud.com))(security=(ssl_server_cert_dn=
"CN=adwc.xxxxxxx.oraclecloud.com, OU=Oracle BMCS US, O=Oracle Corporation, L=Redwood City, ST=California, C=US")))

So probably I should be able to enter this data somewhere too.

Extra info:

  • I can connect to it with Oracle SQL Developer, but that one uses a wallet-file, containing all sorts of settings and data like certificates and so.
  • Trying to open a connection from code using the ODP.NET Nuget package also fails on a "Connection string is not well-formed" exception.
  • I am a SQL Server developer and not used to Oracle, but this Oracle cloud DB is free, so I wanted to give this a shot.

Kind regards,

Peter

PeterVD
User
Posts: 15
Joined: 16-Jan-2007
# Posted on: 06-Jun-2022 13:53:50   

In the meantime I was able to connect to the database from code with the ODP.NET Oracle driver.

Set these values at startup:

//Enter directory where you unzipped your cloud credentials 
//REMARK: you can set this as long as you didn't open a connection yet. It will throw an exception
OracleConfiguration.TnsAdmin = @"<path to unzipped wallet>";
OracleConfiguration.WalletLocation = OracleConfiguration.TnsAdmin;

and then open the connection like this:

//Enter your ADB's user id, password, and net service name (a TNS name from the list of connectionstrings)
string conString = "User Id=<ADMIN>;Password=<PWD>;Data Source=<TNS_NAME>;Connection Timeout=30;";

using (var cn = new OracleConnection(conString))
{
    cn.Open();
    Console.WriteLine("Successfully connected to Oracle Autonomous Database");
    cn.Close();
}

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39160
Joined: 17-Aug-2003
# Posted on: 07-Jun-2022 09:47:58   

The connection string you specify, you can add that information to the tnsnames.ora file in app\client<user name>\product<version number>\client_1\Network\Admin, did you try that too?

so this connection string:

(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.xxxxxx.oraclecloud.com))
(connect_data=(service_name=xxxxxxx.adb.oraclecloud.com))(security=(ssl_server_cert_dn=
"CN=adwc.xxxxxxx.oraclecloud.com, OU=Oracle BMCS US, O=Oracle Corporation, L=Redwood City, ST=California, C=US")))

You then give it a name after 'description', like MyOracleCloudDB, and specify that as server name

We haven't tested our system on the Oracle cloud so we don't have any data regarding whether it will work (so consider it as 'not supported'). The designer doesn't have transient error recovery implemented for Oracle cloud too in the Oracle driver (something which is likely required if you want to work with a connection from on-prem to cloud databases)

Frans Bouma | Lead developer LLBLGen Pro
PeterVD
User
Posts: 15
Joined: 16-Jan-2007
# Posted on: 13-Jun-2022 21:23:28   

Hi Frans,

I think I came very close, using the method you suggested, but I still received an error saying the connection was actively refused by the server. Adding the mentioned IP-address to the list of allowed IP's didn't fix it. But I think this was a server configuration issue, rather than an LLBLGen issue.

Anyway, in the meantime I switched to a PostgreSQL cloud database. Easy to setup, easy to connect to. :-) Unfortunately not as free as the Oracle Always Free services. :-D

Thanks anyway for the help.

Kind regards,

Peter

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39160
Joined: 17-Aug-2003
# Posted on: 14-Jun-2022 08:12:55   

An alternative (if you want to go for Oracle) can be to install the free Oracle dev db locally, develop your schema/app there, and then deploy it on the cloud DB. This is usually a better option anyway as reverse engineering a relational model from a cloud database can be difficult as they're often not offering access to meta-data schemas.

Frans Bouma | Lead developer LLBLGen Pro