-
Dan Harrel
- SnapObjects
- Wednesday, 7 April 2021 08: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!
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.