Which ODBC client to use with Uniface

Hi,
below a letter sent to a customer. I'm intrested in your opinions of which Sql Server ODBC client to use? In this scenario the database access is done directly from the client pc to the database, eg. no application-server used and Uniface9 is used. The letter isn't perhaps expressed by the best possible english (fast translation :-).

The different versions of the Sql clients are a jungle.

If Uniface isn't used as a Remote application, e.g. by Uniface Anywhere, the Sql client must be on the user's PC, as it is in your situation.

In general, we use the so-called
"SQL Server" (SQLSRV32) which is included in the "Windows Data Access Components" library and is therefore already installed on the user's PC. 
This is the version that users of your XXXXX-system now use and it usually works well. The good thing here is that there is no need to do any 
installation on the user's machine, especially if the actual ODBC source is defined as a file on the file server.

There are faster/more advanced drivers, which must be installed on the user's computer.
SQL Server Native Client (SQLNCLI)
Driver={SQL Server Native Client} (SQL Server 2005)
Driver={SQL Server Native Client 10.0} (SQL Server 2008)
Driver={SQL Server Native Client 11.0} (SQL Server 2012 and later)

 

Microsoft ODBC Driver for SQL Server (MSODBCSQL)
Driver={ODBC Driver XX for SQL Server}

The XXXXXX-system does not require the latest versions and therefore the latest functions. If there are a lot of problems, however, 
you can try to install newer, faster drivers, which could e.g. because of speed  help up the situation somehow. 
I recommend that we still wait to update the Sql clients, let's see if it has started to work better now.

Hi,
below a letter sent to a customer. I'm intrested in your opinions of which Sql Server ODBC client to use? In this scenario the database access is done directly from the client pc to the database, eg. no application-server used and Uniface9 is used. The letter isn't perhaps expressed by the best possible english (fast translation :-).

The different versions of the Sql clients are a jungle.

If Uniface isn't used as a Remote application, e.g. by Uniface Anywhere, the Sql client must be on the user's PC, as it is in your situation.

In general, we use the so-called
"SQL Server" (SQLSRV32) which is included in the "Windows Data Access Components" library and is therefore already installed on the user's PC. 
This is the version that users of your XXXXX-system now use and it usually works well. The good thing here is that there is no need to do any 
installation on the user's machine, especially if the actual ODBC source is defined as a file on the file server.

There are faster/more advanced drivers, which must be installed on the user's computer.
SQL Server Native Client (SQLNCLI)
Driver={SQL Server Native Client} (SQL Server 2005)
Driver={SQL Server Native Client 10.0} (SQL Server 2008)
Driver={SQL Server Native Client 11.0} (SQL Server 2012 and later)

 

Microsoft ODBC Driver for SQL Server (MSODBCSQL)
Driver={ODBC Driver XX for SQL Server}

The XXXXXX-system does not require the latest versions and therefore the latest functions. If there are a lot of problems, however, 
you can try to install newer, faster drivers, which could e.g. because of speed  help up the situation somehow. 
I recommend that we still wait to update the Sql clients, let's see if it has started to work better now.

Hello Roger

For the corresponding version of the Microsoft ODBC Driver for versions of the Microsoft SQL Server supported by Uniface,

consult the Microsoft documentation

I have these links:

Information from Microsoft: System Requirements, Installation, and Driver Files can be found on the web:
Microsoft ODBC Driver for SQL Server on Windows

System requirements, installation, and driver files

release notes and downloads

The ODBC driver 18.4 is the latest version for the ODBC driver.

Thanks Gianni and Peter for the info.

The customer problem is that the program suddenly stops working, probably the program loose  connection to the database. this usually happens as they have left the PC for some time and as they return and start to work, their Uniface program is no longer connected, or at least they can't control the program anymore as the hourglass is spinning. 
These problems appeared as the customer updated from Sql Server 2012 to Sql server 2019, and did a lot of other updates to their network, and actually also updated their client PCs.

As the customer still use Uniface 9.7.04, we can't do that much. We have now decided that the update to Uniface10 will be done as soon as possible.
Then we have better possibilities to work on this problem, as we then use supported versions.

As I understand all of these; SQL Server" (SQLSRV32), SQL Server Native Client (SQLNCLI) and Microsoft ODBC Driver for SQL Server (MSODBCSQL) have the needed ODBC tools. Whatever you use I think in this case you will have the same problem.
The drivers may act somehow different, "Sql Server" is spinning forever,  "Microsoft ODBC Driver for SQL Server" might exit the program as it does a limited retries etc.

So, we will try updating to Uniface10. I hope the eventual problem is a malfunctioning switch or something similar :-(, however we can't argue that much as long as we use Uniface9.

Gianni, which version of Uniface9 do you use, as you use that new MSS-driver?

Regards RogerW.

Thanks Gianni and Peter for the info.

The customer problem is that the program suddenly stops working, probably the program loose  connection to the database. this usually happens as they have left the PC for some time and as they return and start to work, their Uniface program is no longer connected, or at least they can't control the program anymore as the hourglass is spinning. 
These problems appeared as the customer updated from Sql Server 2012 to Sql server 2019, and did a lot of other updates to their network, and actually also updated their client PCs.

As the customer still use Uniface 9.7.04, we can't do that much. We have now decided that the update to Uniface10 will be done as soon as possible.
Then we have better possibilities to work on this problem, as we then use supported versions.

As I understand all of these; SQL Server" (SQLSRV32), SQL Server Native Client (SQLNCLI) and Microsoft ODBC Driver for SQL Server (MSODBCSQL) have the needed ODBC tools. Whatever you use I think in this case you will have the same problem.
The drivers may act somehow different, "Sql Server" is spinning forever,  "Microsoft ODBC Driver for SQL Server" might exit the program as it does a limited retries etc.

So, we will try updating to Uniface10. I hope the eventual problem is a malfunctioning switch or something similar :-(, however we can't argue that much as long as we use Uniface9.

Gianni, which version of Uniface9 do you use, as you use that new MSS-driver?

Regards RogerW.

Hi Roger,

Your question: which version of Uniface9 do you use, as you use that new MSS-driver? 
My Answer: Currently: U9.7.05.056, AFAIK latest U97.

Latest driver available on U97 for MSS is version U5.3 compiled against libraries of MSS 2019.

A small hint: having upgraded MS SQL Server from 2012 to 2022 the only thing changed is the database.
If you search on Google for "ms sql 2022 connection lost on idle session timeout" you could easily find MSS instances installed on Azure by default are closing an established connection after 30 minutes of idle. Could this be your problem too?

Best Regards,
Gianni

Hi Gianni,
These problems appeared as the customer updated from Sql Server 2012 to Sql server 2019, and did a lot of other updates to their network, and actually also updated their client PCs.

If you search on Google for "ms sql 2022 connection lost on idle session timeout" you could easily find MSS instances installed on Azure by default are closing an established connection after 30 minutes of idle.

Looks like my problem, however it's not an Azure server. I've to look at this.

Hi,
it would be nice to get some response from Uniface or someone else, about Sql2022 being more sensitive (default settings?) to closing connections after a while, ie does Uniface in new drivers take into consideration that the connection may be handled by connection pooling and so perhaps being closed after eg. idle 30 minutes?
At least in  old windows applications you usually open the connection at application start and close it at application exit, ie. not opening and starting the connection for every communication with the database.

Long ago I wrote AsynchInterrupt code to poll the database as the Uniface-program is idle, but could this be solved in some other way?

Regards RogerW. 

Hello Roger

There is the internal Uniface component UTIMER.

Hello Peter,
yes I have used both UTIMER, and $TIMEOUT in the asn-file to generate an asynchronous interrupt.

However it would be nice to now about settings in Sql server, Sql client or Uniface configuration to avoid getting the database connection stopped as the Uniface client program is left idle for some "X" minutes, ie. a solution to this problem, not programming database polling for idle instances of Uniface-client. 
 

Hello Roger

When the connection would stay alive you might run into locking problems for other users and also to many connections being open.

We do have the connector option lock time:

Hello,
Peter as Iain says, I think those settings handles things that happens as you execute things and are active within the program.
My problem now is inactivity, probably having to do with connection pools, connection lifespan etc, or then just some hardware going to sleep.

We will now try to update the driver in Uniface9 environment and after that updating to Uniface10, getting some hints about these problems and where to handle them.
However, it's good to know that the program works well as long as you don't do anything else, and that I probably could solve it by an easy database polling routine.