Hi

We face an issue related to ADO database connection and are seeking help resolving it.

PowerBuilder: 2022 R3 Build 3391

Visual Studio: 2022

DBMS: Microsoft Azure

In short, the issue is that any SQL command fails with SQLCode -1 and no SQL error text.

A datastore retrieve terminates the application.

1. We tried OLE and SQL connections, with the same result.
2. We also tried using the PowerBuilder "MSOLEDBSQL SQL Server" connection to the same database, which works.
3. We tried the DLL from a C# console application, which works.
4. We ran the SQL commands below to check the connection and get the last SQL that was executed.
   1. We can see that all the database connections are alive.
   2. We can only see the SQL performed using the PowerBuilder connection.
   3. Any SQL command performed by PowerBuilder using ADO is not listed.

select max (spid) from sys.sysprocesses
WHERE DB_NAME(dbid) = '****'
AND loginame <> 'sa'
AND Trim (hostname) = '******'
AND Trim (program_name) <> 'azdata';

SELECT deqs.last_execution_time AS [Time], dest.text AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC;

Here is the PowerBuilder code. Based upon the “template application” with MDI.

On the main window, we have:

Instance Variables

CONSTANT long TEST_TYPE_OLE = 1
CONSTANT long TEST_TYPE_SQL = 2
CONSTANT long TEST_TYPE_PB  = 3
CONSTANT String PATH = "C:\Temp\PowerBuilder.ADO.NET\net8.0\publish\"

// **** to be replaced with real values
CONSTANT String is_Password = "*****"
CONSTANT String is_ServerName = "******.database.windows.net,1433"
CONSTANT String is_Database = "Database='****'"
CONSTANT String is_LogId = "*****"

A command button with this clicked event code:

wf_testado (TEST_TYPE_OLE)
wf_testado (TEST_TYPE_SQL)
wf_testado (TEST_TYPE_PB)

Windows function: integer wf_testado (long al_testtype)

DotNetAssembly lcs_ass
DotNetObject   lcs_obj
long           ll_return
boolean        lb_return
Long           ll_version
String         ls_Name
Transaction    lu_Trans
datastore      lds

ChangeDirectory (PATH)

lu_Trans = CREATE Transaction

IF al_TestType <> TEST_TYPE_PB THEN
   lcs_ass = Create DotNetAssembly
   lcs_obj = Create DotNetObject

   //Load the .NET assembly
   ll_return = lcs_ass.LoadWithDotNet(PATH + "SafranAdoDatabaseConnection.dll")
   if ll_return < 0 then
       messagebox("The DLL Load Failed " + String (ll_return), lcs_ass.errortext)
       return -1
   end if

   ll_return = lcs_ass.createinstance ("SafranAdoDatabaseConnection.AdoConnection", lcs_obj)
   if ll_return < 0 then
       messagebox("The Create Instance failed " + string (ll_return),lcs_ass.errortext)
       return -1;
   end if
END IF

CHOOSE CASE al_TestType
CASE TEST_TYPE_SQL
   // --------------------------------------------------------------------------------
   // ADO Connection, SQL connection
   // --------------------------------------------------------------------------------
   lu_Trans.DBMS = "ADO.NET"
   lu_Trans.LogPass = is_Password
   lu_Trans.LogId = is_LogId
   lu_Trans.AutoCommit = False
   lu_Trans.ServerName = "tcp:" + is_ServerName
   lu_Trans.Database = is_Database
   lu_Trans.DBParm = "Provider='MSOLEDBSQL19';DataSource=tcp:" + is_ServerName +";"+is_Database+";Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

   DISCONNECT USING lu_Trans;

   //Get the C# database connection information
   lb_return = lu_Trans.SetAdoConnection(lcs_obj.AdoConnectionProxySQL)

CASE TEST_TYPE_OLE
   // --------------------------------------------------------------------------------
   // ADO Connection, OLE connection
   // --------------------------------------------------------------------------------
   lu_Trans.DBMS = "ADO.NET"
   lu_Trans.LogPass = is_Password
   lu_Trans.LogId = is_LogId
   lu_Trans.AutoCommit = False
   lu_Trans.ServerName = "tcp:" + is_ServerName
   lu_Trans.Database = is_Database
   lu_Trans.DBParm = "Provider='MSOLEDBSQL19',DataSource='"+is_ServerName+"',"+is_Database+";"

   DISCONNECT USING lu_Trans;

   //Get the C# database connection information
   lb_return = lu_Trans.SetAdoConnection(lcs_obj.AdoConnectionProxyOLE)

CASE TEST_TYPE_PB
   // --------------------------------------------------------------------------------
   // Direct none ADO connection
   // --------------------------------------------------------------------------------
   lu_Trans.DBMS = "MSOLEDBSQL SQL Server"
   lu_Trans.LogPass = is_Password
   lu_Trans.LogId = is_LogId
   lu_Trans.AutoCommit = False
   lu_Trans.ServerName = "tcp:" + is_ServerName
   lu_Trans.DBParm = ""+is_Database+",Provider='MSOLEDBSQL19'"

   DISCONNECT USING lu_Trans;

   lb_return = TRUE
END CHOOSE

IF lb_return THEN
    CONNECT USING lu_Trans;
    IF lu_Trans.sqlcode <> 0 THEN
      messagebox("Error",lu_Trans.sqlerrtext)
    END IF

   //Use the C# connection to execute ESQL in
   SELECT TOP 1 1 into :ll_version FROM sys.objects USING lu_trans;
   ls_name = String (ll_version)

   IF lu_Trans.SQLCode = 0 THEN
      MessageBox("name", ls_name)
   ELSE
      MessageBox("Error, Test type " + String (al_TestType), lu_Trans.SQLErrText + "~r~nSQLDBCode:" + String (lu_Trans.SQLDBCode) + "~r~nSQLCode:" + String (lu_Trans.SQLCode))
   END IF

   DISCONNECT USING lu_Trans;
ELSE
   MessageBox ("Error, Test type " + String (al_TestType), "Could not SetAdoConnection")
END IF

DISCONNECT USING lu_Trans;
DESTROY lu_Trans

Return 1

Our C# code, compiled using .NET 8.0

Configuration = Release | x64

Target framework = net8.0-windows7.0

Deployment mode = Self-contained

Target runtime = Win.x64

using Appeon.DB.Sharing;
using Microsoft.Identity.Client;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace SafranAdoDatabaseConnection
{
    public class AdoConnection
    {
        public IAdoConnectionProxy AdoConnectionProxySQL { get; set; }
        public IAdoConnectionProxy AdoConnectionProxyOLE { get; set; }

        private readonly string ClientId = "*********";
        private readonly string ClientSecret = "*******";
        private readonly string TenantId = "******";
        private readonly string[] Scopes = new[] { "https://database.windows.net/.default" };
        private readonly string ConnectionStringOLE = "Provider=MSOLEDBSQL19;Data Source=tcp:******.database.windows.net,1433;Initial Catalog=*****;";
        private readonly string ConnectionStringSQL = "Server=tcp:*****.database.windows.net,1433;Initial Catalog=****;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";

        public AdoConnection ()
        {
            var task = GetAccessToken ();
            string accesstoken = task.GetAwaiter ().GetResult ();

            SetupSQL (accesstoken);
            SetupOLE (accesstoken);
        }

        // called from a console application, to test the connection
        public void TestSQL ()
        {
            SqlConnection connection = (SqlConnection)AdoConnectionProxySQL.Connection;
            string query = "SELECT TOP 1 * FROM networks"; // Just to test if we can get some data
            using (SqlCommand command = new SqlCommand (query, connection))
            {
                using (SqlDataReader reader = command.ExecuteReader ())
                {
                    while (reader.Read ())
                    {
                        // Replace with actual column index or column name
                        Console.WriteLine ("Project name: " + reader["name"].ToString ());
                    }
                }
            }
        }

        // called from a console application, to test the connection
        public void TestOLE ()
        {
            OleDbConnection connection = (OleDbConnection)AdoConnectionProxyOLE.Connection;
            string query = "SELECT TOP 1 * FROM networks"; // Just to test if we can get some data
            using (OleDbCommand command = new OleDbCommand (query, connection))
            {
                using (OleDbDataReader reader = command.ExecuteReader ())
                {
                    while (reader.Read ())
                    {
                        // Replace with actual column index or column name
                        Console.WriteLine ("Project name: " + reader["name"].ToString ());
                    }
                }
            }
        }

        private void SetupOLE (string accessToken)
        {
            // First get the access token
            string oleConnectionString = ConnectionStringOLE;
            oleConnectionString += $"Access Token={accessToken};";
            OleDbConnection connection = new (oleConnectionString);
            connection.Open ();
            AdoConnectionProxyOLE = new AdoConnectionProxy
            {
                Connection = connection
            };
        }

        private void SetupSQL (string accessToken)
        {
            string connectionString = ConnectionStringSQL;
            SqlConnection connection = new (connectionString);
            connection.AccessToken = accessToken;
            connection.Open ();
            AdoConnectionProxySQL = new AdoConnectionProxy
            {
                Connection = connection
            };
        }

        /// 
        /// Gets the access token
        /// 
        /// 
        private async Task GetAccessToken ()
        {
            var app = ConfidentialClientApplicationBuilder.Create(ClientId)
                .WithClientSecret(ClientSecret)
                .WithAuthority(new Uri($"https://login.microsoftonline.com/{TenantId}"))
                .Build();
            var result = await app.AcquireTokenForClient(Scopes).ExecuteAsync();
            var accessToken = result.AccessToken;
            return accessToken;
        }
    }
}