Geneos
Recommended Reads

SQL Toolkit Plug-In TECHNICAL REFERENCE

Introduction

The SQL Toolkit plug-in executes database queries that can run and display the output of the query in the dataview.

This plug-in can connect to and extract data from the following supported databases:

  • MySQL or MariaDB
  • Microsoft SQL Server
  • Oracle
  • SAP ASE, formerly known as Sybase ASE.
  • PostgreSQL
  • IBM DB2

For more information, see SQL Toolkit Plug-in User Guide.

This is a sample query that is used in SQL Toolkit plug-in:

select distinct
        from_unixtime(timestamp) as 'Date',
        varname as 'Managed Variable',
        description
        from event_table
        where unix_timestamp(now()) - timestamp <= 3600
        and severity >= 2
        order by Date asc

Note: There is no need for a semicolon at the end of the query.

The data returned by the query is converted into a set of regular Geneos Managed Variables. All the standard Geneos Enterprise Management Framework functionality (e.g. setting rules, logging to a database) applies.

Note: The order of the rows published in the dataview may not always adhere to the order returned by the query (for example, if "ORDER BY" is used in the query). This is due to performance considerations.

Plug-in Configuration

queries

Holds a set of queries which user wants the plug-in to use to query the database.

To view the list of data types that SQL Toolkit plugin does not support, see Manage unsupported data types in SQL Toolkit Plug-In User Guide.

Mandatory: Yes

queries > query

Holds a query name and query sql code for a specific query

Mandatory: Yes

queries > query > name

Describes the name by which this query is known.

For example,

Query1

Mandatory: Yes

queries > query > sql

Holds the sql used to run the query.

For example,

select * from my_table

Mandatory: Yes

queries > query > rowLimit

Limits the amount of rows to be returned by a query.

Note: Netprobe is capable of handling multiple rows. However, this can cause system failure for the Netprobe to provide correct sample.

Mandatory: No
Default: 200 rows

connection

Connection defines all of the settings that need to be passed to the database.

Note: The Netprobe has a default of 10 maximum database connections. This number is configurable in the GSE under the Advanced tab of Probes with the field Max Database Connections.

connection > database

The specific database that should be connected to. The supported databases are currently:

  • DB2
  • MySQL
  • Oracle
  • Sybase
  • MS SQL Server
  • PostgreSQL

connection > database > mysql > var-serverName

The name of the MySQL server.

Mandatory: Yes

connection > database > mysql > var-port

The port number that MySQL is listening on.

Mandatory: No
Default: 3306

connection > database > mysql > var-databaseName

The name of the database to use when running the query.

Mandatory: Yes

connection > database > oracle > var-databaseName

The name of the database to use when running the query. This is specified in the tnsnames.ora file.

Note: The full TNS definition in tnsnames.ora may be specified in place of the database name in any of these cases:

  • tnsnames.ora is available
  • tnsnames.ora is available
  • tnsnames.ora is available

If tnsnames.ora is available, doing so will override the details of the file in your Oracle directory.

For example, if you have these configuration details:

MYALIAS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MYHOST)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = MYDATABASE)
      (SERVICE_NAME = MYDATABASE)
    )
  )

In the database name field, you may specify as follows:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MYHOST)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SID=MYDATABASE)(SERVICE_NAME=MYDATABASE)))

Ensure that the alias is not included and the syntax does not have spaces. All necessary environment variables must also be set in the start-up environment of the Netprobe and adjusted for the operating system and start-up script in use. E.g. for UNIX/Linux components started from a bash script:

export ORACLE_HOME=/usr/local/oracle/instantclient_11_2
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib64:${LD_LIBRARY_PATH}

Remember to adjust the path to the Oracle installation directory and choose the correct library subdirectory based on the architecture of the Netprobe in use.

Mandatory: Yes

connection > database > oracle > var-applicationName

The application name to be set to the connection created from the sampler to the Database Server.

Mandatory: No
Default: NetProbe (listen-port <Netprobe's port>)

connection > database > sybase > var-interfaceEntry

The name of the Sybase interface. This is specified in the sql.ini or interfaces file.

Mandatory: Yes

connection > database > sybase > var-databaseName

The name of the database to use when running the query.

Mandatory: Yes

connection > database > sybase > var-applicationName

The application name to be set to the connection created from the sampler to the Database Server.

Mandatory: No
Default: NetProbe (listen-port <Netprobe's port>)

connection > database > sqlServer > var-serverName

The name of the SQL Server.

Note: When connecting to named instances the format should be "ServerNameInstanceName". On connection Geneos will verify the named instance actually matches the one listening on the specified port.

Mandatory: Yes

connection > database > sqlServer > var-port

The port number that MySQL is listening on.

Mandatory: No
Default: 3306

connection > database > sqlServer > var-applicationName

The application name to be set to the connection created from the sampler to the Database Server.

Mandatory: No
Default: NetProbe (listen-port <Netprobe's port>)

Note: This option is only valid for Windows platforms.

connection > database > sqlServer > var-databaseName

The name of the database to use when running the query if the database name is not specified in the query. If the query contains a valid database name, this value will be ignored.

Mandatory: No

connection > database > sqlServer > useWindowsAuthentication

Login using Windows authentication instead of SQL authentication.

Credentials used for Windows authentication is the username and password supplied by the user. If the username and password fields are left blank, then the currently logged-on user executing the probe will be used. Users can specify a domain by entering the username using the UPN format or the Down-level Logon format. If invalid Windows credentials are supplied, sqlServer will fallback to SQL authentication.

Mandatory: No
Default: false

Note: This option is only valid for Windows platforms.

connection > database > db2 > var-databaseName

The alias of the database to use when running the query.

Mandatory: Yes

connection > database > PostgreSQL > var-serverName

The name of the PostgreSQL server.

Mandatory: Yes

connection > database > PostgreSQL > var-databaseName

The name of the PostgreSQL database to use when running the query.

Mandatory: Yes

connection > database > PostgreSQL > var-port

The port number of the PostgreSQL server.

Mandatory: Yes

connection > database > PostgreSQL > var-applicationName

The application name to be set to the connection created from the sampler to the Database Server.

Mandatory: No
Default: NetProbe (listen-port <Netprobe's port>)

connection > var-userName

The name of the user with which to connect to the database.

Mandatory: No

connection > password

The password with which to connect to the database. To set an encrypted password, click on the "Set Password" button, then enter and confirm the password to be used.

Mandatory: No

connection > closeConnectionAfterQuery

If set to true the sampler will close the connection to the database after each query. This frees resources for other processes that may connect to the database but makes the sampler a little less efficient. If false the connection remains open but is returned to a pool of connections for reuse within the netprobe.

When using a large sample interval (e.g. running queries every hour), setting this parameter to true will reduce database resources without significantly impacting Netprobe performance.

Mandatory: No
Default: false

userDetails

This setting has been replaced by Technical Reference: connection.

Deprecated: Yes

showHeadlines

Headlines showing the rows returned, query status and query time taken should be displayed

Setting Description
true Headlines should be displayed
false Headlines should not be displayed

Mandatory: No

It is generally not useful to set the plug-in sample interval to a very small value, as most queries take some time to evaluate (if you are interested in real-time plots of data, the Active Chart feature of ActiveConsole may be more suitable). If the system attempts to sample before the current query has returned, the plug-in will ignore the sample request.

showRowline

Each row should be numbered to indicate its place relative to the other rows.

Setting Description
true Row numbers should be displayed in the dataview
false Row numbers should not be displayed in the dataview

Mandatory: No

Default: false

Enabling diagnostics

To help track down problems, the printing of debug statements can be enabled by adding a debug setting to the sampler set-up called SQL-TOOLKIT. This results in the plug-in printing statements each time it attempts something of significance e.g. attempting to open a connection to a database, being unable to connect to a database, etc.

Configuring the Netprobe environment

Depending on what database your Netprobe needs to connect to, you may have to amend your environment to expose the appropriate libraries to the Netprobe. The Netprobe indirectly loads these libraries depending on what database the plug-in has been configured to connect to.

For more information on supported MySQL libraries for each platform, see Required client libraries in Databases.

MySQL

Linux

When MySQL is installed via packaged installation, it automatically adds /user/lib64/mysql to the list of directories (/etc/ld.so.conf.d) where the dynamic linker searches for libraries. This way, even if you do not set LD_LIBRARY_PATH, the Netprobe can find the library in /usr/lib64/mysql.

Windows

set PATH=C:\Program Files\MySQL\MySQL Server 5.0\bin;%PATH%

IBM AIX

setenv LD_LIBRARY_PATH /usr/local/mysql

Caution: Some IBM AIX hosts still use the LIBPATH variable when loading shared libraries. For IBM AIX 5.3, it uses LD_LIBRARY_PATH. For backward compatibility, the path to the required libraries must be added to the LIBPATH variable.

Sybase

The plug-in behaviour depends on the Sybase client library referenced in these environment variables. If wide table and columns are used, Sybase client library must met the version supported by Geneos.

Please see Database Support section in Geneos Compatibility Matrix.

Windows

Needs an assortment of libraries usually found in the lib directory of the Sybase installation directory.

set PATH=C:\Sybase\OCS_0\dll;%PATH%

Solaris

Needs an assortment of libraries usually found in the lib directory of the Sybase installation directory.

setenv LD_LIBRARY_PATH /export/share/dev/SYBASE/OCS/lib

Linux

Needs an assortment of libraries usually found in the lib directory of the Sybase installation directory.

setenv LD_LIBRARY_PATH /export/share/dev/SYBASE/OCS/lib

AIX

Needs an assortment of libraries usually found in the lib directory of the Sybase installation directory.

Needs an assortment of libraries usually found in the lib directory of the Sybase installation directory.

setenv SYBASE /opt/sybase
setenv LD_LIBRARY_PATH /opt/sybase/OCS/lib

Oracle

Windows

Needs an assortment of libraries usually found in the bin directory of the Oracle installation directory.

set PATH=C:\oracle\product\client_1\bin;%PATH%

Solaris

Needs an assortment of libraries usually found in the bin directory of the Oracle installation directory.

setenv ORACLE_HOME /usr/oracle/oracle/product/client
setenv LD_LIBRARY_PATH /usr/oracle/oracle/product/client/lib32

Linux

Needs an assortment of libraries usually found in the bin directory of the Oracle installation directory.

setenv ORACLE_HOME /usr/oracle/oracle/product/client
setenv LD_LIBRARY_PATH /usr/oracle/oracle/product/client/lib32

AIX

Needs an assortment of libraries usually found in the bin directory of the Oracle installation directory.

setenv ORACLE_HOME /opt/oracle/product/
setenv LD_LIBRARY_PATH /opt/oracle/product/lib32

MS SQL Server

Windows

The machine running the netprobe needs to have the SQL Server OLEDB client installed on it. This is installed as part of the SQL Server Native Client which can be downloaded from Microsoft's website.

Solaris

Microsoft does not currently provide Solaris drivers which allow connecting to MS SQL Server running on Windows.

Linux

For MS SQL Server database connection from a Linux host, you need to acquire separate packages for 32-bit or 64-bit based probes. Please refer to Appendix A.

AIX

Microsoft does not currently provide AIX drivers which allow connecting to MS SQL Server running on Windows.

IBM DB2

Windows

If you wish to run the netprobe on a different machine to that on which the DB2 server is running, then you would need to download and install the DB2 Client package from IBM's website. You should then follow the instructions in the "Quick Beginnings for DB2 Clients" document to set-up communications between the client and the server. This document can be found on IBM's website. Finally, you would need to configure the netprobe environment, so that the client libraries are available to it, e.g.

set PATH=C:\Program Files\IBM\SQLLIB\BIN;%PATH%

Solaris

If you wish to run the netprobe on a different machine to that on which the DB2 server is running, then you would need to download and install the DB2 Client package from IBM's website. You should then follow the instructions in the "Quick Beginnings for DB2 Clients" document to set-up communications between the client and the server. This document can be found on IBM's website. Finally, you would need to configure the netprobe environment, so that the client libraries are available to it, e.g.

setenv LD_LIBRARY_PATH /opt/IBM/db2/V9.7/lib32

Linux

If you wish to run the netprobe on a different machine to that on which the DB2 server is running, then you would need to download and install the DB2 Client package from IBM's website. You should then follow the instructions in the "Quick Beginnings for DB2 Clients" document to set-up communications between the client and the server. This document can be found on IBM's website. Finally, you would need to configure the netprobe environment, so that the client libraries are available to it.

[32-bit netprobe]       setenv LD_LIBRARY_PATH /opt/ibm/db2/V9.7/lib32
[64-bit netprobe]       setenv LD_LIBRARY_PATH /opt/ibm/db2/V9.7/lib64

AIX

If you wish to run the netprobe on a different machine to that on which the DB2 server is running, then you would need to download and install the DB2 Client package from IBM's website. You should then follow the instructions in the "Quick Beginnings for DB2 Clients" document to set-up communications between the client and the server. This document can be found on IBM's website. Finally, you would need to configure the netprobe environment, so that the client libraries are available to it, e.g.

setenv LD_LIBRARY_PATH /opt/ibm/db2/V9.7/lib32

PostgreSQL

Netprobe requires the PostgreSQL client libraries to communicate with the PostgreSQL database server. The path to these client libraries should be included in the machine's path environment variable. Path shown below are just sample paths and may vary depending on the installation.

Windows

Needs the libpq.dll and intl.dll libraries.

set PATH=C:\Program Files (x86)\PostgreSQL\9.4\bin;%PATH%

Linux

Needs the libpq.so library.

setenv LD_LIBRARY_PATH /opt/PostgreSQL/9.4/lib

Solaris

Needs the libpq.so library.

setenv LD_LIBRARY_PATH /usr/postgres/9.4-pgdg/lib

AIX

Needs the libpq.so library.

setenv LD_LIBRARY_PATH /usr/local/pgsql/lib

Others

Microsoft.VC80.CRT redistributables are required when using AES-256 passwords in Windows machines.

Appendix A - MS SQL Server Database Setup

Required Client Libraries

The Linux netprobe requires an Open Database Connectivity (ODBC) driver for accessing an SQL Server database. This driver serves as a translation layer between the application and the database management system (DBMS) that allows the netprobe to access data via queries sent by the driver to the database.

Below is a list of the libraries that can be used by the netprobe to connect to an SQL Server database:

Library Filename Description Source
FreeTDS (with UnixODBC) sqlServerFreeTDS-0.91-unixODBC-2.3.2.tgz Preferred library for 32-bit Linux probes ITRS Group Download Site
Microsoft ODBC Driver for SQL Server on Linux Microsoft ODBC Driver 13 for SQL Server Native library used for 64-bit Linux probes https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server

Note: Microsoft ODBC Driver 17 for SQL Server is also supported.

Installation of ODBC Drivers

There are two main drivers used by a Linux netprobe to connect to an SQL Server database: FreeTDS and MS ODBC.

32-bit netprobes use FreeTDS, while 64-bit netprobes use MS ODBC.

Installing FreeTDS

FreeTDS is an open-source implementation of the Tabular Data Stream (TDS) protocol used by SQL Server that allows the netprobe to natively communicate with an SQL Server database.

Currently, the 32-bit Linux netprobe supports MS SQL Sever via the FreeTDS library.

Below are the installation steps for the FreeTDS driver:

  1. Download the FreeTDS driver from the ITRS group download site (Please see Appendix A). It is highly recommended to use the former due to the latter's stability issues.

Note: This package is common for database logging of both gateway and netprobe; hence you need to download it only once.

  1. After downloading the package, extract it to a suitable location (e.g. /opt/geneos/SQL-DB). You can use the following command for unpacking:
tar xvzf <package-name>.tgz
  1. This will result in a folder <package-name> with all required libraries present inside it. Edit 'odbcinst.ini' to set the current path of libtdsodbc.so. For example:
[FreeTDS]
Driver = /opt/geneos/SQL-DB/libtdsodbc.so

Installing Microsoft ODBC Driver for SQL Server

The MS ODBC driver allows native applications running on Linux to connect to SQL Server databases.

Currently, the 64-bit Linux netprobe supports MS SQL Sever via the MS ODBC driver.

  1. Install MS SQL Server from the Microsoft site on Windows or Linux. For fresh installations, select MS SQL Server as the default installation (i.e. do not give it any instance name). In case you are using MS SQL Server 2008 Express, there is a bug in it where selecting the Default Instance box in the installation does not work. By entering MSSQLSERVER as the instance name the installer will install SQL Server with the default (i.e. none) instance name.
  2. Download and install the latest version of gateway on your Linux box from the ITRS Download website as usual.
  3. For MS SQL Server database connection from a Linux host, you need a separate database library. You need the unixODBC driver manager and the MS SQL ODBC Driver for SQL Server. At the time of writing the SQL Server driver is "MS SQL ODBC Driver 13 for SQL Server".

Installing the Microsoft ODBC DRIVER For SQL SERVER

With the release of Microsoft's ODBC Driver 13.0 for Linux, Microsoft have made the installation and configuration of their driver much easier by supporting Linux package management tools.

  1. First follow the instructions for your platform from Microsoft's documentation.

  2. To verify that the ODBC Driver on Linux was registered successfully, execute the following command:

    odbcinst -q -d -n "ODBC Driver 13 for SQL Server"

Change the quoted string to match your driver version. It should show you output such as:

[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.1.0
UsageCount=1
  1. Copy or symlink /etc/odbcinst.ini (having above contents) to /opt/microsoft/msodbcsql/lib64 folder.

    sudo ln -s /etc/odbcinst.ini /opt/microsoft/msodbcsql/lib64/odbcinst.ini

Setting up the environment before starting Gateway/Netprobe

  1. Create gateway startup scripts from the templates provided.
  2. Set the environment variable values pertaining to MS SQL Server database in the gateway start up script (where /opt/microsoft/msodbcsql/lib64 is replaced by the path which contains the MS ODBC Driver 13 for SQL Server.

   setenv SQLSERVER_LIB /opt/microsoft/msodbcsql/lib64
   setenv LD_LIBRARY_PATH ${SQLSERVER_LIB}:.
   setenv ODBCHOME ${SQLSERVER_LIB}
   setenv ODBCSYSINI ${SQLSERVER_LIB}
   setenv ODBCINSTINI odbcinst.ini
   setenv GENEOS_MSSQL_DRIVER 'ODBC Driver 13 for SQL Server'

:: note::
   The environment variable $GENEOS_MSSQL_DRIVER is used to determine which entry in your odbcinst.ini refers to
   your MS SQL driver.  If this isn't set it defaults to driver 13 as above.

   When MS SQL server is configured Geneos will ensure that by default the library 'libodbc.so' is used.
   This will be found in the ${SQLSERVER_LIB} directory so you should ensure that this is first in your LD_LIBRARY_PATH.

   Older installations may have the $GENEOS_ODBCLIB environment variable set.  If this is the case it will be overriden
   and a warning written to the log file.  This is to prevent iODBC being used by default wheere it is installed.
   Pre (GA3.0.16) Gateways and Netproves have no concept of this.  The work-around is to place a symbolic link from libiodbc.so to libodbc.so in the
   same directory and ensure it comes first on the LD_LIBRARY_PATH.
  1. Configure MS SQL Server database connection in the SQL-Toolkit Plug-In and run the Netprobe. You should be able to connect to an MS SQL Server database. You should see the following line in log file "Connecting to DB using Free TDS Driver" or "Connecting to DB using ODBC Driver 13 for SQL Server".

Note: When running SQL-Toolkit on Windows and connecting to a SQL Server instance using the Windows Authentication mode, you should not need to provide any explicit credentials in the database setup. If run as a service the netprobe must be either running as a user with permission to connect to the SQL Server instance, or credentials are provided in the sql-toolkit sampler set-up.

Note: Connecting to a dynamic MS SQL instance is not supported by the Microsoft ODBC driver. A named instance must be referenced by a port number. See the Microsoft documentation for more information.

Sample Queries

Oracle

QUERY SQL*PLUS Netprobe
Simple SELECT query
SELECT * FROM tbl_name;
SELECT * FROM tbl_name
Function call without parameters
SELECT func_name FROM dual;
SELECT func_name FROM dual
Function call with input parameters
SELECT func_name('str', 1)
FROM dual;
SELECT func_name('str', 1) FROM dual
SELECT func_name(col1, col2)
FROM tbl_name;
SELECT func_name(col1, col2)
FROM tbl_name
Procedure call without parameters
exec proc_name;
proc_name
BEGIN proc_name; END;
Procedure call with input parameters
exec proc_name('str', 1);

BEGIN proc_name ('str', 1); END;
BEGIN proc_name ('str', 1); END;
Procedure call with output parameter
DECLARE num NUMBER;
BEGIN
proc1(num);  -- as output
proc2(num);  -- as input
END;
DECLARE num NUMBER;
BEGIN
proc1(num);
proc2(num);
END;

Note: Cursor return type is only supported in Oracle.

Sybase

QUERY ISQL Netprobe
Simple SELECT query
SELECT * FROM tbl_name
GO
SELECT * FROM tbl_name
Function call without parameters
SELECT dbo.func_name()
GO
SELECT dbo.func_name()
Function call with input parameters
SELECT dbo.func_name('str',1)
GO
SELECT dbo.func_name('str',1)
SELECT dbo.func_name(col1, col2)
FROM tbl_name
GO
SELECT dbo.func_name(col1, col2)
FROM tbl_name
Procedure call without parameters
EXECUTE proc_name
GO
EXECUTE proc_name

Note

If a procudure is returning multiple resultsets, only the first resultset will be displayed in the dataview.

Procedure call with input parameters
EXECUTE proc_name 'str',1
GO
EXECUTE proc_name 'str',1
BEGIN proc_name 'str', 1 END
GO
BEGIN proc_name 'str', 1 END
Procedure call with output parameter
DECLARE @num TINYINT
EXECUTE proc1 @num OUTPUT
EXECUTE proc2 @num IN
DECLARE @num TINYINT
EXECUTE proc1 @num OUTPUT
EXECUTE proc2 @num IN

MySQL

QUERY MYSQL Netprobe
Simple SELECT query
SELECT * FROM tbl_name;
SELECT * FROM tbl_name
Function call without parameters
SELECT func_name FROM dual;
SELECT func_name FROM dual
SELECT func_name;
SELECT func_name

Note

Function calls without table reference can be called with or without 'FROM dual'

Function call with input parameters
SELECT func_name('str', 1);
SELECT func_name('str', 1)
SELECT func_name(col1, col2)
FROM tbl_name;
SELECT func_name(col1, col2)
FROM tbl_name
Procedure call without parameters
CALL proc_name;
CALL proc_name

Note

If a procudure is returning multiple resultsets, only the first resultset will be displayed in the dataview.

Procedure call with input parameters
CALL proc_name('str',1);
CALL proc_name('str',1)
Procedure call with output parameter
EXECUTE proc1(@num);  --as output
EXECUTE proc2(@num);  --as input
EXECUTE proc1(@num);
EXECUTE proc2(@num)

DB2

QUERY DB2 Netprobe
Simple SELECT query
SELECT * FROM tbl_name
SELECT * FROM tbl_name
Function call without parameters
SELECT func_name
FROM SYSIBM.SYSDUMMY1
SELECT func_name
FROM SYSIBM.SYSDUMMY1
SELECT *
FROM TABLE(func_name)
SELECT *
FROM TABLE(func_name)
Function call with input parameters
SELECT func_name('str', 1)
FROM SYSIBM.SYSDUMMY1
SELECT func_name('str', 1)
FROM SYSIBM.SYSDUMMY1
SELECT func_name(col1, col2)
FROM tbl_name
SELECT func_name(col1, col2)
FROM tbl_name
SELECT *
FROM TABLE(func_name('str', 1))
SELECT *
FROM TABLE(func_name('str', 1))
Procedure call without parameters
CALL proc_name
CALL proc_name
Procedure call with input parameters
CALL proc_name('str',1)
CALL proc_name('str',1)
Procedure call with output parameter
BEGIN
DECLARE num SMALLINT;
CALL proc1(num); -- as output
CALL proc2(num); -- as input
END
BEGIN
DECLARE num SMALLINT;
CALL proc1(num);
CALL proc2(num);
END

MS SQL Server

QUERY ISQL Netprobe
Simple SELECT query
SELECT * FROM tbl_name;
SELECT * FROM tbl_name
Function call without parameters
SELECT func_name;
SELECT func_name
SELECT *
FROM func_name;
SELECT *
FROM func_name
Function call with input parameters
SELECT func_name('str', 1);
SELECT func_name('str', 1)
SELECT *
FROM func_name('str', 1);
SELECT *
FROM func_name('str', 1)
SELECT func_name(col1, col2)
FROM tbl_name;
SELECT func_name(col1, col2)
FROM tbl_name
Procedure call without parameters
EXEC proc_name;
EXEC proc_name
Procedure call with input parameters
EXEC proc_name 'str',1;
EXEC proc_name 'str',1
Procedure call with output parameter
DECLARE @lastRw TINYINT;
EXEC get_max_ @lastRw OUTPUT;
EXEC insertNextRow @lastRw;
DECLARE @lastRw TINYINT
EXEC get_max_ @lastRw OUTPUT
EXEC insertNextRow @lastRw

Note: When calling functions/procedures in MS SQL Server, [EXEC ] can be omitted or replaced with [EXECECUTE ].

PostgreSQL

QUERY ISQL Netprobe
Simple SELECT query
SELECT * FROM tbl_name;
SELECT * FROM tbl_name
Function call without parameters
SELECT func_name();
SELECT func_name()
SELECT *
FROM func_name();
SELECT *
FROM func_name()
Function call with input parameters
SELECT func_name('str', 1);
SELECT func_name('str', 1)
SELECT *
FROM func_name('str', 1);
SELECT *
FROM func_name('str', 1)
SELECT func_name(col1, col2)
FROM tbl_name;
SELECT func_name(col1, col2)
FROM tbl_name
Function call with output parameter

Note: Output parameters are not included in the function call.

SELECT func_name('str', 1);
SELECT func_name('str', 1)
SELECT *
FROM func_name('str', 1);
SELECT *
FROM func_name('str', 1)