ReadTableFromDataStore - functions are not working correctly in the editor

[smalers]

While investigating some diversion record issues, I tried to use the ReadTableFroMDataStore command to run a stored procedure to read diversion comments and the command editor would not allow me to enter function parameters.

[smalers]

The following exception message is printed when trying to list the functions:

Receiver class com.microsoft.sqlserver.jdbc.SQLServerConnection does not define or inherit an implementation of the resolved method 'abstract java.lang.String getSchema()' of interface java.sql.Connection

ChatGPT says that since Java 11 is being used, a new version of the SQL Server JDBC driver is needed. I updated to version 13.2.1, which is the latest version. Then, the following exception occurs:

Warning[3](DWR.DMI.HydroBaseDMI.SelectHydroBaseJDialog.checkServerForDatabaseNames): The connection to the host localhost, named instance cdss failed. Error: "java.net.SocketTimeoutException: Receive timed out". Verify the server and instance names and check that no firewall is blocking UDP traffic to port 1434. For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host.
com.microsoft.sqlserver.jdbc.SQLServerException: The connection to the host localhost, named instance cdss failed. Error: "java.net.SocketTimeoutException: Receive timed out". Verify the server and instance names and check that no firewall is blocking UDP traffic to port 1434. For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host.

ChatGPT says to update the connection string with trustServerCertificate=true;. It may be necessary to have more sophisticated handling of connection string properties in the future, but try including by default to see if it works. The base DMI class already has a setAdditionalConnectionProperties method to set additional connection string properties.

Now I get the following. Note that I am running TSTool on one computer in my development environment and connecting to a database on a different computer. I'll check to see if the SQL Server Browser is running, but this has not been an issue before. I'm going to save the comment before continuing.

com.microsoft.sqlserver.jdbc.SQLServerException: The connection to the host localhost, named instance cdss failed. Error: "java.net.SocketTimeoutException: Receive timed out". Verify the server and instance names and check that no firewall is blocking UDP traffic to port 1434. For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host.

[smalers]

I confirmed that the SQL Server Browser service is running on the remote computer. This service is needed any time a named instance is used, even for a local instance, so this must have been used before.

I figured out that the code is using a GenericDMI instances for the HydroBase connection dialog and I was not passing the additional connection string to it. I added and it works. I'm going to add a separate issue to get feedback from the State on how certificates should be handled.

[smalers]

It looks like the latest JDBC driver has some differences from the previous version that impacted the editing of functions. Specifically, the metadata for functions includes FUNCTION_NAME and SPECIFIC_NAME. For SQL Server, the former usually ends with ;1 indicating the "first" function with the name, even though SQL Server does not allow overloaded functions. The SPECIFIC_NAME is the human-facing function. The SPECIFIC_NAME metadata values for function and function columns is apparently not being returned for SQL Server so I had to handle that. Other databases such as PostgreSQL and Oracle allow overloaded functions, in which case the SPECIFIC_NAME is actually more specific and indicates the function parameter types. I got things working to confirm that the basic problem is fixed but need to do some more cleanup, including:

1. If a function is not overloaded, just show the user the simple name.
2. If a function is overloaded, show the user the overloaded function signature, with parameters and return value.
3. Update the editor to always show parameter types so they user can enter the needed information.
4. Do the same cleanup for procedures (they are handled differently in databases and the JDBC driver).
5. Do the same cleanup for the RunSql command.
6. Do some basic testing on HydroBase, PostgreSQL (TriLynx NovaStar database), and Oracle (Reclamation HDB database).