English
Chinese (Simplified)
French
German
Italian
Japanese
Russian
Spanish
Vietnamese
execute an SQL statement
-
I needed to extract information from the SG Server, and I wonder, is it possible to execute an SQL statement to retrieve information? For example, all messages with the date, sender, recipient, and subject. Is this possible?
-
Arron Staff
Yes.
SGDBTool.exe utility found in the SecurityGatway\app directory is probably the best tool to use. You'll want to make sure you run it from an Administrator command prompt or it won't work.
The command to use is sgdbtool.exe execute c:\temp\sqlscript.sql
Where c:\temp\sqlscript.sql contains the query you'd like to run.
If you are able to wait for the beta, which we hope to release in the near future, it will allow you to export data from the message log.
-
Yes! It's a great idea. I will try the option you mentioned and test that beta. I like that idea.
-
Matthew Staffsgdbtool.exe can be used to execute an SQL query that modifies the database. However, it can not be used to query information. It offers the same ability as Setup | Database | Advanced | Execute SQL Statement. There are two options, the first is to use the Firebird isql.exe tool included in the SG installation. However, as the embedded version of Firebird only allows connections from a single process, you would need to stop the SecurityGateway system service or work against a copy of the database. Below is an example of how to execute a query.
"C:\Program Files\MDaemon Technologies\SecurityGateway\Firebird3_x64\isql.exe" -u SYSDBA -p masterkey "C:\Program Files\MDaemon Technologies\SecurityGateway\App\SECURITYGATEWAY.FBD"
Database: C:\Program Files\MDaemon Technologies\SecurityGateway\App\SECURITYGATEWAY.FBD, User: SYSDBA
SQL>
SQL> select val from settings where name='Templates';VAL
===============================================================================
C:\Source\EmailShield\Devel\Templates\SQL>
SQL> exit;The second approach would be to use the third-party IBExpert (https://www.ibexpert.net/ibe/) application. IBExpert offers a GUI to view and modify a Firebird database. There is a free "personal edition" available. This would be my recommended approach, however, the same limitation applies to the embedded version of Firebird. If you plan to access the database often using IBExpert, I would consider installing the stand-alone Firebird database server and then using sgdbtool.exe -setdbconnect to configure SG to use it.
As Arron mentioned SG 9.5.0 which will be available as a beta in the relatively near future will offer the ability to export the results of the message log as a .CSV file.
-
Arron Staff
Sorry Javier, I forgot the SGDBTool can only be used to execute queries that modify the DB.
-
So if I understood correctly, I can install a standalone Firebird server, import the database, and configure the SG server to work with that server instead of the embedded database.
Thank for everything.
-
Matthew Staff
@Javier, that's correct. You don't need to import the existing database into the Firebird server if you are installing the Firebird server on the SG server. If the Firebird server will be installed on another server, then you just need to copy the database file to that server. I typically create a "database" folder and then copy the SG database into that. When connecting to a Firebird server, the connection string contains the path of the database relative to the Firebird server.
These steps are copied from the instructions to setup clustering, but are the same for just a single SG server connecting to the Firebird database server.Setting up the Firebird 3 Database Server
In order to use Clustering, you must install the Firebird 3 database server at a network location accessible to each node in the cluster.
To set up your Firebird 3 server:
- Download the Firebird 3 Database Server.
- Run the installer on a machine that will be accessible to all nodes.
- Accept the License Agreement, and click Next.
- Read the information, and click Next.
- Choose a folder, and click Next.
- On Select Components, click Next.
- Choose a name for the Start Menu folder (or click Don't create a Start Menu folder), and click Next.
- Leave the Select Additional Tasks options set to the default values (i.e. SuperServer mode, Run as a Service, and Copy client library), and click Next.
- Type and Retype a SYSDBA password (the "SYSDBA" username and this password will be needed later), and click Next.
- Click Install
- Click Next
- Click Finish
Configuring SecurityGateway to Use the External Database Server
In a SecurityGateway cluster, each node must be configured to connect to the same database file, which must be located on the Firebird 3 database server you set up in the previous section. To set up SecurityGateway to use the external database server:
- On the Firebird server, create a folder for your database file (e.g. C:\Databases).
- Copy your primary SecurityGateway database file (i.e. \SecurityGateway\App\SECURITYGATEWAY.FBD) to that location.
- On your SecurityGateway server, open the Windows Command Prompt.
- Switch to your \SecurityGateway\App\ folder.
- Type: sgdbtool.exe -setdbconnect, and press Enter.
- For "Use embedded Firebird database Y/N?" type N, and Enter
- For "Enter Firebird Server IP", type the IP address of the Firebird server (e.g. 10.10.0.1), and press Enter.
- Press Enter for "Enter Firebird Server Port (default 3050)".
- For "Enter Firebird Database Path or Alias", type the full path to the database file that you copied to the Firebird server (e.g. C:\Databases\SECURITYGATEWAY.FBD), and press Enter.
- Press Enter for "Enter Firebird Database Username (default SYSDBA)".
- For "Enter Firebird Database Password (default masterkey)", type the password you created when installing the Firebird 3 server, and press Enter.
Your Primary SecurityGateway node should now be connected to the external database server.
-
@Matthew McDermott @Arron Caruth Thank you, I will do it this way, and I will be able to generate the reports they ask for much faster.
