Unit Testing - Shared connection

How does connection sharing works in Unit Testing? I had some presumptions but none of them seem to be entirely correct.

I believed it would share the connection with the Tab that was active when the Unit Testing was invoked/started. But it seems they only share the connection settings, not the actual connection.
The Tab I invoked UT from used connection1, which by default connects to database1 but was now connected to database2. The UT window does not show the connection status, so I thought it would also connect to database2 but it did not, it connected to the default database1.

Also, it does not display the used connection and the current connection status anywhere in the UT window, therefore I can only guess (or use something equivalent to SELECT DB() if it exists) which database/schema is it connected to currently. This connection sharing is a bit confusing. Could the Unit Testing window get its own connection (or at least an indicator for which connection is being used and what database it is connected to)?

The Unit Test project doesn't save connection settings in the project file. In interactive move the project is run using saved settings of the current connection. If you run it from the command line, then you need to specify the connection details explicitly.

Does this help?

Last edited by SysOp on Sat Jun 13, 2020 4:35 pm; edited 1 time in total

That works splendidly when the tests are deployed for automated use. But while developing the tests it would be nice to have greater control over that. It's inconvenient to change the database and impossible to change the server. I have to close the Unit Testing window, open a Tab in SE that uses the correct connection, restart Unit Testing, and only then can I reopen the Unit Testing project. It's rather difficult compared to the Connection dialog of SE and even that one in SE could be optimized further. The Connection dialog is absolutely necessary when adding new connections or modifying existing ones. For the quite common task of switching between existing connections, a simple dropdown in front of the one for choosing the current database would suffice. It would be much faster too.

I can also understand if you don't want to touch that. These nuances only come out after intensive use so most users probably don't even notice.

That connection handling is generalized, there is a generic framework, kind of a foundation for for all so called tools that run on top of it. The Unit Testing is just happens to be one of them. That framework handles connections for the tools.

There is a way around that, not sure if it's simple or not for you. You can develop your own plugin using the preinstalled Plugin IDE. And in the plugin create a simple form showing a list of connections, which would enable you to select the required connection and run the command line for the unit testing framework for the selected connection. And then you can make it wait for the run to complete and display the completion status, or don't wait, and perhaps run in parallel the same for another database. The plugin can be added to SA menus. A bit more complex version may include a second list for the unit testing projects, maybe add some other options too, to enable to automate the processing exactly as you want. If you choose to give it a try and have any how-to questions, please don't hesitate to ask. It shouldn't take long to develop something like that, and perhaps only a few dozen lines of code is required for all of that. It doesn't require any special programming skills. It took me about 30 seconds and just a few mouse clicks and keystrokes to create the mockup form pictured below. Of course I'm familiar with the IDE. For a novice it would take longer to find where different menus and controls are. But once you master one plugin, you may want to use that tool to automate more things.

Are you implying that might be able to add some connection-dialog-like to Unit Testing (and possibly replace or extend the one SQL Editor uses)?

No, sorry this is not what I meant. It would be your own dialog with named connections. The idea is to customize the menu in the Options dialog and next to Unit Testing menu add your own menu command, for example, Run Test Projects. You would set that menu type to Execute Plugin and choose the plugin you developed. In the plugin you can show your own list of connections by their prefered names, whatever you like, which could be the same as in the SQL Assistant connection dialog or different. For simplicity, let's assume use the same names. With a bit more coding you can make your plugin read connection details from the SAS file, which is certainly doable but requires more work. This would enable you to avoid updating the plugin every time you add new connection. In the code behind the Run button you would set the command line for the selected connection and execute it. If names are the same, it literally requires just 2 lines of code, or 3 ( I was lazy to copy path to sacmd.exe to the component's properties). The code can be written in Pascal, C++ (simplified), VBScript, JavaScript. In my example below I selected Pascal.

It's as simple as that.

Sorry, in my example I forgot to add closing quote to the line with command line parameters. I wanted to demonstrate the concept, and didn't pay attention to the accuracy.

The documentation describes how to deploy plugins. Basically you just copy plugin files to a named folder under C:\Program Files (x86)\SQL Assistant 11\plugins
To verify, open Options dialogs, on the first tab in the Plugins section you should see your plugin. Go to Common section below, expand Menu option, expand Code and Data Generators menu branch, click Unit Tests, and then click "+" toolbar icon to the right of the menu. Add new command like My Custom Unit Tests Runner, choose Execute Plugin for the Action and then select your plugin name. That should be it.

I thought you might be interested in the enhancements made in v12 in the unit testing framework. You can now write your custom scripted checks for multiple resultsets returned, and you can have all results saved to a table. This solution is platform independent. You can now query results using SQL, or you can access them directly using scripting functions, which is actually pretty simple. Examples can be found here https://www.softtreetech.com/sqlassist/SqlAssist12.pdf, see pages 294-295

This is brilliant! Where shall I put my comments, remarks, and complaints? Here, or shall I open a new topic?

EDIT: I'll start a new one.