Tag Archives: SQL Multi Select

Selecting databases to run scripts on in SQL Multi Select

When you add a server to a query group in SQL Multi Select, you can choose whether your scripts need to be ran on all the databases on that server, or just the database you’re connected to, or a specific set of databases.

The first option is an easy way to always run your queries on all your databases on a server.  Even when you add new databases to a server, your queries will still run on those databases.  When you need finer control over which databases to run your queries on, the third option comes in handy.

Let’s say you have a PostgreSQL server, and you set up the connection settings like this:

When you add this server to a group, you will need to specify on which databases your queries should run on.  If you want to run your queries on all databases on that server, just select the run scripts on all databases option.

Likewise, If you want to run your scripts only on the connected database, select the run scripts on connected database only option.

What if you need to specify a set of database names explicitly?  Just select the regular expressions filter option and list down the databases you want to include e.g. say you want to run your scripts only for the databases starting with the prefix database followed by the digits 0 to 6, and also databases starting with the prefix prod.

Or if you need to exclude a specific database, use the exclude filter e.g. say you want to exclude databases with the prefix dev.

This being a regular expression filter, you can easily include or exclude databases matching a string pattern e.g. say you want to include databases with the prefix prod, but not if the prefix is followed by the numbers 0 to 9.

 

Linux and Wine compatibility

Here at Yohz Software, we receive the occasional email asking if our PostgreSQL-centric products run on Linux.  Being Windows applications, our products will run on Linux but requires Wine (the software, not the drink).  DB Doc has been able to run on Ubuntu since 2014, and we recently installed and ran PgComment and SQL Multi Select on Ubuntu 17.04 and Fedora 25.

SQL Multi Select on Fedora 25 and PlayOnLinux:

DB Doc on Ubuntu and Wine.

There are the occasional GUI quirks, which we try to fix as soon as possible, which is why you sometimes see different text and/or images if you run the same application on Windows.  If you discover any quirks/inconsistencies we have overlooked, please do drop us a line at support@yohz.com.

SQL Multi Select on Ubuntu 17.04 and Wine 2.0.1

Well, SQL Multi Select mostly runs on Ubuntu 17.04 with Wine 2.0.1.  Instructions for installing Wine 2.0.1 on Ubuntu 17.04 can be found here.

What works:

  • running multiple statements in a script
  • using scripts located on Linux file system
  • displaying images in result set
  • using the command line interface to run projects
  • connecting to PostgreSQL databases via SSL connections
  • exporting results to text files and SQLite database

What doesn’t work:

  • clicking on the summary file to open it – you’ll need to manually locate and open the file
  • help file won’t open because it’s in a chm format – use the online help instead (http://www.yohz.com/help/dbdoc/index.html)

If you come across any other functionality that doesn’t work on Ubuntu, please do drop us a line at support@yohz.com.

Why use SQL Multi Select in addition to pgAdmin 4

Let me start by saying pgAdmin 4 is a great tool for PostgreSQL.  It makes a lot of the admin and monitoring work easier.  It’s also cross-platform, and it’s free.  On the other hand, SQL Multi Select is just a query tool, that runs only on Windows.  Well, it runs on Ubuntu using Wine, and on Fedora via PlayOnLinux.    The Basic Edition is free, and here are some reasons you may want to use SQL Multi Select as your PostgreSQL query tool instead of pgAdmin.  Note that pgAdmin 4 (1.4) was used for this comparison.

Multiple server targets

You can run queries on multiple PostgreSQL databases simultaneously with SQL Multi Select.  For example, say you want to run a script that returns every databases’ size.  Just set up a group containing the databases you want to run the query on:

Set up the scripts on run on each of those databases:

Run the script, and you’ll see the results for each of those databases, across all servers in the server group.

Multiple result sets

When you run the following in pgAdmin:

only the last result set is returned.

In SQL Multi Select, the same script returns multiple result sets.

To make things clearer, you can place comments before each individual script:

and SQL Multi Select will use those comments to identify the result sets instead of arbitrary numbers.

Image display

In bytea columns storing images, pgAdmin displays the hex values.

SQL Multi Select displays a thumbnail of the image (gif, jpeg, png, bmp, dicom), and some image properties.  Note that SQL Multi Select inspects the bytea values and identifies the image format automatically.  There is no need to provide another column that tells SQL Multi Select the image format.

 

Blob data identification

In bytea columns containing files, pgAdmin displays the hex values.

SQL Multi Select displays details of the data contained in those columns.  Note that SQL Multi Select inspects the bytea values and identifies the data type accordingly.

Result sets containing bytea columns return very slow

If your query returns bytea columns a few MBs in size, pgAdmin takes an extremely long time to return the result set when compared to SQL Multi Select.  For e.g. this query returns a single bytea column that contains a 13 Mb zip file.  pgAdmin takes 7 minutes to display the single row:

SQL Multi Select returns the same result set in 1 second.

 

Search function

In SQL Multi Select, you can search for a value in the result sets.  You can also use a regular expression to search for values.

pgAdmin is still great for administrative tasks, but as a query tool, SQL Multi Select may be better in some cases.  Download SQL Multi Select and give it a try now.  The 14-day trial will be automatically converted to the FREE Basic Edition once the trial period has elapsed.

Related blogs:

SQL Multi Select use case

If you have manage multiple databases in your work, there would have been occasions where you needed to run the same set of queries against multiple databases or database servers.  The regular and tedious way to do this would have been to connect to each database/server, run your query, log the results, and move on to the next database/server.

SQL Multi Select removes the tedium of the above process.  Basically, you set up a list of all the servers you manage in your environment.  You then group these servers by function (each server can exist in multiple groups).  For example, you may have a group containing just your development servers, another group for your production servers etc.

Now, you can run a query, or a set of queries, against these server groups.  The results of these queries are consolidated in a single result set, so you can easily compare the results across different servers.  For ad-hoc queries, this makes it easy to run the same query on multiple database/servers.

You may also have queries you run repeatedly e.g. queries to check database sizes, server statuses etc.  You can use the SQL Multi Select command line interface (available only in the Standard and Professional editions) to run these queries in a scheduled job, and have the results saved in text files or in a SQLite database.

SQL Multi Select supports PostgreSQL, MySQL, and Oracle databases.  All server groups will contain servers of the same type, so for example, you cannot mix MySQL and Oracle servers in the same query group.