1. Dan Harrel
  2. SnapObjects
  3. Wednesday, 7 April 2021 20:23 PM UTC

I am having an interesting problem writing a .NET Core 3.1 API using SnapObjects to connect to SQL Anywhere via ODBC.  The problem has to do with when database connections are made and released. 

The SQL Anywhere database I am using has long been in use for a classic PowerBuilder application, and now we are writing a Web API to use it.  The API will be consumed by .NET applications, and not a PowerBuilder application.

The Web API Controller invokes a Service, and the Service receives a DataContext object in its constructor by Dependency injection.  The DataContext class is a subclass of OdbcSqlAnywhereDataContext.  

The Service retrieves a .NET Datastore with the following code.  The exception catching code will be explained momentarily...

var ds_result = new DataStore<ABC>(_dataContext);

try
{
    await ds_result.RetrieveAsync(...);
}
catch (OdbcException oex)
{

    string sql = "select user_error_no, user_error_msg, connection_property ('number') as connection_id from dummy";
    var list = _dataContext.SqlExecutor.Select<DynamicModel>(sql);

    ...

 }

The SQL behind the .NET Datastore's Datawindow is of the form:

select ... from prc_my_proc ();

The stored procedure "prc_my_proc" returns a result set, and in some cases signals a "user exception".  The signaled exception is caught in the above C# code as an OdbcException.  The problem is, the OdbcException object "oex" will not have the user exception message.  oex.Message will be generically: "[SAP][ODBC Driver][SQL Anywhere]User-defined exception signaled".  

Our SQL Anywhere code that signals the user exception parks the user exception message in a SQL Anywhere connection variable, which is a variable whose value lives for the life of the connection.  In our PowerBuilder application, we now go to fetch this message.  I'm trying to do the same in our .NET Web API as shown in the exception code above.

Our problem is - the retrieved user_error_no and user_error_msg values are null, and we have determined this is because the retrieval happens on a DIFFERENT connection than the stored procedure call that signaled the exception. 

However both the datastore being retrieved and the subsequent SqlExecutor invocation both reference the same DataContext object.

My questions now are as follows:

  • How can I get both database calls to use the same database connection?  (conceivably I could write several database calls that I want to form a transaction, and certainly those would have to share a connection)
  • Should I be doing things differently?

Thanks for your consideration!

 

Dan Harrel Accepted Answer Pending Moderation
  1. Friday, 9 April 2021 19:37 PM UTC
  2. SnapObjects
  3. # 1

I have created a simpler example that illustrates my problem:

        public static void TestSqlExecutor()
        {
            var options = new OdbcSqlAnywhereDataContextOptions(connectionString);
            using (var context = new OdbcSqlAnywhereDataContext(options))
            {
                string sql = "select connection_property ('number') as connection_id from dummy";
                string connectionId1 = string.Empty;
                string connectionId2 = string.Empty;

                var list1 = context.SqlExecutor.Select(sql);
                if (list1.Count > 0)
                {
                    connectionId1 = list1.FirstOrDefault().GetValue("connection_id");
                }

                var list2 = context.SqlExecutor.Select(sql);
                if (list2.Count > 0)
                {
                    connectionId2 = list2.FirstOrDefault().GetValue("connection_id");
                }

                Console.WriteLine($"connectionId1={connectionId1}, connectionId2={connectionId2}.");
            }
        }

The two ConnectionId values written to the console are different and sequential numbers.  

I do not have this problem if I use System.Data.Odbc classes.  In this case, subsequent SQL operations using the same OdbcConnection object use the same database connection. 

I'm really hoping I can find a solution as I would rather stick with SnapObjects.Data and SnapObjects.Data.Odbc because (1) I like the functionality and the .NET Datastore, and (2) it supports async operation.

Thanks!

Comment
There are no comments made yet.
David Xiong @Appeon Accepted Answer Pending Moderation
  1. Saturday, 10 April 2021 07:12 AM UTC
  2. SnapObjects
  3. # 2

Hi Dan,

 

SnapObjects uses connection pooling to minimize the cost of repeatedly opening and closing connections (https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/connection-pooling). By default, each database call will automatically open a connection, and will automatically close the connection after the call is completed.

 

To make multiple database calls use the same database connection, you can manually control it. You can try to use the CurrentConnection of the DataContext to manage the database connection through "open" and "close", which will enable multiple consecutive database operations to use the same connection. After manually opening the connection, you also need to manually close it, otherwise the connection will always exist and will not be closed automatically.

 

Please try the following example and let us know if there are any problems. Thank you!

 

      public static void TestSqlExecutor()

        {

            var options = new OdbcSqlAnywhereDataContextOptions(connectionString);

           

            using (var context = new OdbcSqlAnywhereDataContext(options))

            {

               

                context.CurrentConnection.Open();

                

                string sql = "select connection_property ('number') as connection_id from dummy";

                string connectionId1 = string.Empty;

                string connectionId2 = string.Empty;

               

                

                var list1 = context.SqlExecutor.Select<DynamicModel>(sql);

                if (list1.Count> 0)

                {

                    connectionId1 = list1.FirstOrDefault().GetValue("connection_id").ToString();

                }

               

                var list2 = context.SqlExecutor.Select<DynamicModel>(sql);

                if (list2.Count> 0)

                {

                    connectionId2 = list2.FirstOrDefault().GetValue("connection_id").ToString();

                }

                

                context.CurrentConnection.Close();

               

                Console.WriteLine($"connectionId1={connectionId1}, connectionId2={connectionId2}.");

            }

        }

 

Regards,

David

Comment
There are no comments made yet.
Dan Harrel Accepted Answer Pending Moderation
  1. Saturday, 10 April 2021 16:10 PM UTC
  2. SnapObjects
  3. # 3

Thanks, David. Your suggested code additions solved the problem!

By the way, I determined that Connection Pooling was not enabled for the ODBC driver. I changed that.

A few quick follow-ups, if you don't mind:

Q1) If I manually control the connection as you suggest, does the connection return to the pool after "context.CurrentConnection.Close();".

Q2) If I had not manually closed the connection, would it have been closed when the context object is destroyed upon exiting the "using" block?

Q3) Is there any way to tell whether an opened connection came from the pool or is fresh? I have a reason to ask this: SQL Anywhere database server Authenticated Edition requires the client to specify an authentication key for new connections.

Thanks again!

Comment
  1. David Xiong @Appeon
  2. Monday, 12 April 2021 10:41 AM UTC
Hi Dan,

Please see my replies below:

Q1) If I manually control the connection as you suggest, does the connection return to the pool after "context.CurrentConnection.Close();".

David: Yes, the connection will return to the pool after it is close.

Q2) If I had not manually closed the connection, would it have been closed when the context object is destroyed upon exiting the "using" block?

David: When the context object is destroyed, the connection will be close.

Q3) Is there any way to tell whether an opened connection came from the pool or is fresh? I have a reason to ask this: SQL Anywhere database server Authenticated Edition requires the client to specify an authentication key for new connections.

David: As for this case, could you please provide a demo for us to further analyze?



  1. Helpful
  1. Dan Harrel
  2. Monday, 12 April 2021 14:21 PM UTC
Thank you very much, David, and for offering to look further into Q3. However I'm pretty confident we can figure something out. I asked just in case you or your colleagues had any knowledge off hand. If I run into trouble on this, I know where to turn. Thanks, again!
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.