Sclera can work in a server mode that implements the PostgreSQL backend protocol 3.0, which is compatible with PostgreSQL 7.4+.

Though this server, Sclera can interface with the latest PostgreSQL ODBC and JDBC drivers, PostgreSQL’s shell (psql), and anything else that uses PostgreSQL’s native protocol (libpq).

This means that you can now connect to Sclera using Microsoft Excel, Tableau, QlikView, or any other tool of your choice. Configure the tools as you would to connect to PostgreSQL, but point the connection to the Sclera server instead.

This feature was introduced in Sclera 2.2 and is not supported in earlier versions. At the moment, this is an experimental (beta) implementation.

Starting and Stopping the Server

The server is started from the Sclera shell.

> START SERVER [PORT server_port] [DAEMON] [PUBLIC];

This starts a thread that accepts connections at the specified server_port (default 5435). Specifying daemon specifies that the thread should be started as a daemon (default: not a daemon), and public specifies that remote connections should be accepted (default: only local connections accepted).

The following starts a server listening at port 9191, the server thread is not a daemon, and only local connections are accepted.

> START SERVER PORT 9191;

The server starts in the background, but the shell needs to keep running, as exiting the shell will stop the server.

The server can also be stopped manually as follows:

> STOP SERVER;

Connecting to the server

As mentioned earlier, you can connect to the server using PostgreSQL ODBC and JDBC drivers, PostgreSQL’s shell (psql), and anything else that uses PostgreSQL’s native protocol (libpq).

The server can support multiple simultaneous connections to the server by multiple users. All these connections share the same schema and locations, have their separate workspaces (aka cache store). This implies that the locations and persistent objects (tables, views, classifiers, etc.) added by one connection will be visible to all connections, but the temporary objects created by a connection will not be visible to other connections.

Connecting with the Server using ODBC

Applications can interface with the server using the PostgreSQL ODBC driver (available for download at the PostgreSQL ODBC website. The server provides the same interface as a PostgreSQL 7.4+ server.

The ODBC DSN should be configured exactly as for PostgreSQL 7.4+:

DRIVER={PostgreSQL Unicode};DATABASE=database_name;SERVER=server_host;PORT=server_port;UID=user;PWD=password;SSLmode=disable;ReadOnly=0;Protocol=7.4

In the above:

  • database_name is the name of the database to be used as the cache store. This should be unique per connection.
  • server_host is the address of the host where the Sclera server is running.
  • server_port is the port of the host where the Sclera server is accepting connections (specified while starting the server, default 5435).
  • user is the user name. To connect as the admin, please use your registered email id. (See user management for alternatives.) If you do not want to specify the user, please do not specify the UID.
  • password is the password associated with the user, if any. If you do not want to specify the password, please do not specify the PWD.

The server does not work with secure ssl connections at the moment, so the DSN must have SSLmode=disable. The DRIVER and Protocol should also be as specified.

For example, the following DSN connects to the server running on myhost.example.com, port 9191 as user sclerauser@example.com with password as secret.

DRIVER={PostgreSQL Unicode};DATABASE=testodbc;SERVER=myhost.example.com;PORT=9191;UID=sclerauser@example.com;PWD=secret;SSLmode=disable;ReadOnly=0;Protocol=7.4

Connecting with Sclera Server using JDBC

Applications can interface with the server using the PostgreSQL JDBC driver (available for download at the PostgreSQL JDBC website. The server provides the same interface as a PostgreSQL 7.4+ server.

The JDBC connection should be configured exactly as for PostgreSQL 7.4+. The JDBC URL is as follows:

jdbc:postgresql://server_host:server_port/database_name

Here:

  • database_name is the name of the database to be used as the cache store. This should be unique per connection.
  • server_host is the address of the host where the Sclera server is running.
  • server_port is the port of the host where the Sclera server is accepting connections (specified while starting the server, default 5435).

The user and password can be specified in the property and passed to DriverManager.getConnection along with the above URL.

For example, the following connects to the server running on myhost.example.com, port 9191 as user sclerauser@example.com.

String url = "jdbc:postgresql://myhost.example.com:9191/testjdbc";
Properties props = new Properties();
props.setProperty("user","sclerauser@example.com");
props.setProperty("password","secret");
props.setProperty("ssl","false");
Connection conn = DriverManager.getConnection(url, props);

The server does not work with secure ssl connections at the moment, so the “ssl” property must be set to “false” (this is the default, so the props.setProperty("ssl","false"); is actually redundant).

The “user” is the user name. To connect as the admin, please use your registered email id. (See user management for alternatives.) If you do not want to specify the user and/or the password, do not set the associated properties. All other properties will be ignored.

Embedded Mode

In addition to the server mode connection, Sclera also provides JDBC interface in an embedded mode – which means that your application can start Sclera in the same JVM, and interface natively without any network communication overheads. Please see the documentation for additional details.

Connecting with Sclera Server using PostgreSQL Shell (psql)

The PostgreSQL interactive shell psql can be used to interface with the server.

For example, the following connects to the server running on myhost.example.com, port 9191 as user sclerauser@example.com, after prompting for the password, if needed.

$ psql "sslmode=disable" -p 9191 -h myhost.example.com -U "sclerauser@example.com"

The sslmode=disable is needed in case psql is built with ssl enabled. For further details on psql, please refer to the PostgreSQL documentation.

Note that the postgresql information schema is not supported (yet) and so metacommands such as “\d” will not work. Please use Sclera’s commands instead.


Related Documentation