-
Toan Nguyen
- PowerBuilder
- Wednesday, 29 July 2026 04:21 PM UTC
Hello,
I have a PB2022 R3 application that calls methods in .NET C# DLL to authenticate user to implement MFA.
As far as I can tell, the codes work fine on both PowerBuilder and C#. I was able to connect successfully and also able to import an ADO.NET Connection from the C# class library. In my PB application, I was able to use the imported connection to connect to the Oracle DB.
However, when I go to test the connection by issuing simple SELECT query, I got the 'ORA-50001: Connection must be open for this operation' exception.
I am attaching screenshots of my debug session in PowerBuilder to show the successful connection and exception from the SELECT statement.
Also, below are the codes for both PowerBuilder and C#.
PowerBuilder codes:
....
ldna_assembly = CREATE DotNetAssembly
ldno_authenticate = CREATE DotNetObject
ldno_tp_fetcher = CREATE DotNetObject
ls_connString = "user id=" + as_username + ";password=" + as_password + ";data source='CSBMFA.SIE'; persist security info=false"
ll_return = ldna_Assembly.LoadWithDotNet( is_cst_authentication_dll)
if ll_return = 1 then
ll_return = ldna_Assembly.CreateInstance( is_authentication_class , ldno_authenticate, ls_connString)
if ll_return = 1 then
Try
/* Trigger the dotnet function */
ls_role_information = ldno_authenticate.AuthenticateUser("INK","INK",as_username,as_password)
Catch(runtimeerror re_error)
/* Handle .NET error */
MessageBox('AuthenticateUser()',' Runtime Error: ' + re_error1.text)
lb_error = TRUE
return FALSE
End Try
END IF
end if
// Get IAdoConnectionProxy from the C# DLL to connect to Oracle DB
SQLCA.DBParm = "Provider='Oracle',DataSource='CSBMFA.SIE',SQLCache=50,StaticBind=0,DisableBind=0"
lb_return = SQLCA.SetAdoConnection( ldno_authenticate.get_adoconnproxy() )
IF lb_return THEN
CONNECT USING SQLCA;
if sqlca.sqlcode <> 0 then
f_db_message('Logon','Unable to connect to: ' + sqlca.servername, sqlca)
DESTROY ldno_authenticate
DESTROY ldno_tp_fetcher
DESTROY ldna_assembly
return FALSE
end if
// Testing the DB connection
SELECT COUNT(*) INTO :ll_result FROM INK_subject USING SQLCA ;
if sqlca.sqlcode <> 0 then
f_db_message('Logon','Unable to connect to: ' + sqlca.servername, sqlca)
DESTROY ldno_authenticate
DESTROY ldno_tp_fetcher
DESTROY ldna_assembly
return FALSE
end if
END IF
C# Codes:
public class Authenticator_PB
{
public string _connectionString;
public IAdoConnectionProxy AdoConnProxy { get; set; }
public Authenticator_PB(string connectionString) => this._connectionString = connectionString;
public string AuthenticateUser(string systemId, string appId, string userId, string password)
{
if (string.IsNullOrWhiteSpace(systemId))
throw new ArgumentException("System Id cannot be null or empty", nameof(systemId));
if (string.IsNullOrWhiteSpace(userId))
throw new ArgumentException("User Id cannot be null or empty", nameof(userId));
if (string.IsNullOrWhiteSpace(password))
throw new ArgumentException("Password cannot be null or empty", nameof(password));
string str;
try
{
using (OracleConnection oracleConnection = new OracleConnection(this._connectionString))
{
using (OracleCommand command = oracleConnection.CreateCommand())
{
command.CommandText = "E$_Login.Authenticate";
command.CommandType = CommandType.StoredProcedure;
command.BindByName = true;
OracleParameterCollection parameters1 = command.Parameters;
OracleParameter oracleParameter1 = new OracleParameter();
oracleParameter1.ParameterName = "p_system_id";
oracleParameter1.OracleDbType = OracleDbType.Varchar2;
oracleParameter1.Value = (object)systemId;
parameters1.Add(oracleParameter1);
OracleParameterCollection parameters2 = command.Parameters;
oracleConnection.Open();
// Get ADO.NET Connection Proxy
IAdoConnectionProxy ado = new AdoConnectionProxy();
ado.Connection = oracleConnection;
AdoConnProxy = ado;
command.ExecuteNonQuery();
str = $"{command.Parameters["p_set_role_str"].Value.ToString()}, {command.Parameters["p_error_num"].Value.ToString()}, {command.Parameters["p_error_msg"].Value.ToString()}";
}
}
}
catch (OracleException ex)
{
Console.Error.WriteLine("Oracle error: " + ex.Message);
throw;
}
catch (Exception ex)
{
Console.Error.WriteLine("General error: " + ex.Message);
throw;
}
return str;
}
I would appreciate anyone can tell me what am I missing and how to resolve this issue.
Thank you very much in advance.
Toan Nguyen
Find Questions by Tag
Helpful?
If a reply or comment is helpful for you, please don’t hesitate to click the Helpful button. This action is further confirmation of their invaluable contribution to the Appeon Community.