Using EDB*Plus v41

To open an EDB*Plus command line, navigate through the Applications or Start menu to the EDB Postgres Advanced Server menu. Select Run SQL Command Line > EDB*Plus. You can also invoke EDB*Plus from the operating system command line with the following command:

edbplus [ -S[ILENT ] ] [ <login> | /NOLOG ] [ @<scriptfile>[.<ext> ] ]

SILENT

If specified, the EDB*Plus sign-on banner is suppressed along with all prompts.

login

Login information for connecting to the database server and database. login takes the following form. Don't use any white space in the login information.

<username>[/<password>][@{<connectstring> | <variable> } ]

username is a database username with which to connect to the database.

password is the password associated with the specified username. If you don't provide a password but a password is required for authentication, a password file is used if available. If there's no password file or no entry in the password file with the matching connection parameters, then EDB*Plus prompts for the password.

connectstring is the database connection string with the following format:

<host1>[:<port1>],<host2>[:<port2>],<host3>[:<port3>],..[/<dbname>][?ssl={true | false}][&targetServerType={primary}]

host is the hostname or IP address on which the database server resides. If you don't specify @connectstring, @variable, or /NOLOG, the default host is assumed to be the localhost.

port is the port number receiving connections on the database server. The default is 5444.

dbname is the name of the database to connect to. The default is edb.

If Internet Protocol version 6 (IPv6) is used for the connection instead of IPv4, then the IP address must be enclosed in square brackets (that is, [ipv6_address]). The following is an example using an IPv6 connection:

edbplus.sh enterprisedb/password@[fe80::20c:29ff:fe7c:78b2]:5444/edb

The pg_hba.conf file for the database server must contain an appropriate entry for the IPv6 connection. This example shows an entry that allows all addresses:

# TYPE  DATABASE        USER         ADDRESS           METHOD
host    all             all          ::0/0             md5

For more information about the pg_hba.conf file, see the PostgreSQL core documentation.

If you want an SSL connection, then include the ?ssl=true parameter in the connection string. In such a case, the connection string must minimally include host:port, with or without /dbname. The default for the ssl parameter is false. See Using a secure sockets layer (SSL) connection for instructions on setting up an SSL connection.

variable is a variable defined in the login.sql file that contains a database connection string. The login.sql file can be found in the edbplus subdirectory of the EDB Postgres Advanced Server home directory.

/NOLOG

Specify /NOLOG to start EDB*Plus without establishing a database connection. In this mode, you can't use SQL commands and EDB*Plus commands that require a database connection. You can later give the CONNECT command to connect to a database after starting EDB*Plus with the /NOLOG option.

scriptfile[.ext ]

scriptfile is the name of a file residing in the current working directory, containing SQL and/or EDB*Plus commands that execute after startup of EDB*Plus. ext is the filename extension. If the filename extension is sql, then you can omit the .sql extension. When creating a script file, always name the file with an extension. Otherwise EDB*Plus can't access it. EDB*Plus assumes a .sql extension on filenames that are specified with no extension.

The following example shows user enterprisedb with password password connecting to database edb running on a database server on the localhost at port 5444.

C:\Program Files\edb\edbplus>edbplus enterprisedb/password
Connected to EnterpriseDB 16.4.1 (localhost:5444/edb) AS enterprisedb

EDB*Plus: (Build 41.3.0)
Copyright (c) 2008-2021, EnterpriseDB Corporation. All rights reserved.

SQL>

The following example shows user enterprisedb with password password connecting to database edb running on a database server on the localhost at port 5445.

C:\Program Files\edb\edbplus>edbplus enterprisedb/password@localhost:5445/edb
Connected to EnterpriseDB 16.4.1 (localhost:5445/edb) AS enterprisedb

EDB*Plus: (Build 41.3.0)
Copyright (c) 2008-2021, EnterpriseDB Corporation. All rights reserved.

SQL>

Using variable hr_5445 in the login.sql file, the following shows how it is used to connect to database hr on localhost at port 5445.

C:\Program Files\edb\edbplus>edbplus enterprisedb/password@hr_5445
Connected to EnterpriseDB 16.4.1 (localhost:5445/hr) AS enterprisedb

EDB*Plus: (Build 41.3.0)
Copyright (c) 2008-2021, EnterpriseDB Corporation. All rights reserved.

SQL>

The following is the content of the login.sql file used in this example.

define edb="localhost:5445/edb"
define hr_5445="localhost:5445/hr"

You can also define multi-host database connection strings in the login.sql file with the ?targetServerType=primary parameter included in the connection string. The following shows how you can define a multi-host connection string in login.sql:

define edb="192.168.2.24:5444,192.168.2.25:5445,192.168.2.26:5446/edb"

The following example executes a script file, dept_query.sql, after connecting to database edb on server localhost at port 5444.

C:\Program Files\edb\edbplus>edbplus enterprisedb/password @dept_query
Connected to EnterpriseDB 16.4.1 (localhost:5444/edb) AS enterprisedb

SQL> SELECT * FROM dept;

DEPTNO DNAME          LOC
------ -------------- -------------
10     ACCOUNTING     NEW YORK
20     RESEARCH       DALLAS
30     SALES          CHICAGO
40     OPERATIONS     BOSTON

SQL> EXIT
Disconnected from EnterpriseDB Database.

The following is the content of file dept_query.sql used in this example.

SET PAGESIZE 9999
SET ECHO ON
SELECT * FROM dept;
EXIT