This class enables you to connect to a database and execute SQL statements. More...

Public Member Functions
boolean	close ()
	Close the database connection and free the server ressources. More...

DBConnection	DBConnection ()
	Connection to DOCUMENTS Database. More...

DBConnection	DBConnection (String connType, String connString, String user="", String password="")
	Connection to External Database. More...

DBResultSet	executeQuery (String sqlStatement)
	Execute a SELECT statement and retrieve a DBResultSet containing the result rows. More...

DBResultSet	executeQueryUC (String sqlStatement)
	Execute a SELECT statement using a x64/UTF-8 DOCUMENTS and retrieve a DBResultSet containing the result rows. More...

boolean	executeStatement (String sqlStatement)
	Execute any SQL statement on the external database. More...

boolean	executeStatementUC (String sqlStatement)
	Execute any SQL statement using a x64/UTF-8 DOCUMENTS on the external database. More...

String	getLastError ()
	Function to get the description of the last error that occurred. More...

Detailed Description

This class enables you to connect to a database and execute SQL statements.

You can connect to the DOCUMENTS database (see constructor without parameters) or to any external database (see constructor with parameters). For reading the result of a SELECT statement, the class DBResultSet is used. A DBConnection object can only be used for one DBResultSet object. Meaning, if you want to create another DBResultSet object you must also create an additional DBConnection object. Please consider the example in the constructor description.

Constructor & Destructor Documentation

◆ DBConnection() [1/2]

DBConnection DBConnection::DBConnection ( )

Connection to DOCUMENTS Database.

The constructor without parameters can be used to create a connection to the database currently used in DOCUMENTS. For connecting an external database, see the documentation of the constructor with parameters.

Since: DOCUMENTS 4.0

See also: DBResultSet

◆ DBConnection() [2/2]

DBConnection DBConnection::DBConnection	(	String	connType,
		String	connString,
		String	user = `""`,
		String	password = `""`
	)

Connection to External Database.

The constructor with parameters can be used to create a connection to an external database. Meaning to a database, that is not connected to DOCUMENTS. For connecting the DOCUMENTS database, we recommend to use the constructor without parameters.

You can connect to any database system of arbitrary type. But depending on your DOCUMENTS database, you can use different values for parameter connType. The difference is described in the following two cases.

Case 1: Parameter connType = "sqlserver" | "oracle" | "mysql"

This connection types allow you to connect to an external database of the given type. But the type must be identical to the database system that is connected to the DOCUMENTS server. DOCUMENTS supports the following types (also see system requirements).

Windows: SQL Server, Oracle Database, MySQL, MariaDB
Linux: MySQL, MariaDB

So this parameter values can be used with the following database systems:

"mysql": MySQL and MariaDB
"sqlserver": SQL Server
"oracle": Oracle Database

Case 2: Parameter connType = "odbc"

This connection type allows the connection to an external database of almost every type. In this case the database will be accessed using the standard API for databases, called Open Database Connectivity (ODBC). The only restriction is, that an ODBC driver for the database must be available and this driver must be installed on the DOCUMENTS host. Additional, an ODBC Driver Manager can or sometimes must be used to specify a Data Source Name (DSN). The DSN allows you to define the required connection attributes all in one place. Sometimes, the credentials can also be specified at the DSN. If not, they can be set as parameters in the DBConnection constructor.

On DOCUMENTS 5.0e and lower the DSN must be used, if the DOCUMENTS database is not SQL Server.

ODBC

A short and good description about the common ODBC concept is available in the documentation of turbodbc (the ODBC documentation is independent of turbodbc). Additional there is a Microsoft documentation about ODBC.

ODBC on Windows

If your DOCUMENTS is running on Windows with SQL Server, you will probably have an ODBC Driver for SQL Server. Additional you should have the ODBC Data Source Administrator App for configuring Data Sources. To connect to a remote SQL Server, you may have to enable the TCP/IP protocol in SQL Server Configuration Manager and to open the port in the firewall on the remote SQL Server host.

ODBC on Linux

You can find installation instructions for the ODBC Driver for SQL Server in the Microsoft Documentation. The ODBC Driver Manager package unixODBC is available in the package archives of Ubuntu (e.g. 16.04 and 18.04) and can be installed using the package manager APT. For testing your database connection independent of PortalScripting, you can use isql.

Note: ODBC is not JDBC, JDBC is not supported!

Parameters

connType	The type of the database connection (`"odbc" \| "sqlserver" \| "oracle" \| "mysql"`), see two cases in constructor description.
connString	The complete connection string or the data source name (DSN).
user	The login name used to authenticate against the external database.
password	The (plaintext) password of the user utilized to connect to the database.

Returns: DBConnection object representing the active database connection or null in case of any error.

Since: ELC 3.50 / otrisPORTAL 5.0

See also: DBResultSet

Example:: // Create DB-Connection to an external database (DOCUMENTS is connected to SQL Server)
var connectionString = "driver={ODBC Driver 11 for SQL Server};server=localhost;database=myDB;trusted_connection=yes;";
var myDB = new DBConnection("sqlserver", connectionString);

// Create DB-Connection to any DB using the DSN
var dataSourceName = "myDSN";
var user = "test";
var password = "test";
var myDB = new DBConnection("odbc", dataSourceName, user, password);

Example:: var myDB = null;
var myRS = null;
try {
// create DB-Connection using the Data Source Name "Nordwind"
myDB = new DBConnection("odbc", "Nordwind");
if (myDB.getLastError() != null)
throw "Error connection the database: " + myDB.getLastError();

// executeQuery returns a DBResultSet
myRS = myDB.executeQuery("SELECT Nachname, Vorname FROM Personal");
if (!myRS)
throw "Error in SELECT statement: " + myDB.getLastError();

// read all persons from the result set
while (myRS.next()) {
var fullName = myRS.getString(0) + ", " + myRS.getString(1);

// Fails, because you must read the columns in the correct order!
//var fullName = myRS.getString(1) + ", " + myRS.getString(0);

// Fails, because it is not allowed to read a value twice!
//var fullName = myRS.getString(0) + ", " + myRS.getString(0);

util.out(fullName);
}
} catch (err) {
util.out(err);
} finally {
// Important: free resources!
if (myRS)
myRS.close();
if (myDB)
myDB.close();
}

Member Function Documentation

◆ close()

boolean DBConnection::close ( )

Close the database connection and free the server ressources.

Note: It is strongly recommanded to close each DBConnection object you have created, since database connections are so-called expensive ressources and should be used carefully.

Returns: true if successful, false in case of any error

Since: ELC 3.50 / otrisPORTAL 5.0

See also: DBResultSet.close()

◆ executeQuery()

DBResultSet DBConnection::executeQuery ( String sqlStatement )

Execute a SELECT statement and retrieve a DBResultSet containing the result rows.

Note: This instruction should only be used to SELECT on the external database, since the method always tries to create a DBResultSet. If you need to execute different SQL statements, refer to the DBConnection.executeStatement() method.

Parameters

sqlStatement String containing the SELECT statement you want to execute in the database

Returns: DBResultSet containing the result rows generated by the SELECT instruction

Since: ELC 3.50 / otrisPORTAL 5.0

See also: DBConnection.executeStatement(String sqlStatement)

Note: x64/UTF-8 DOCUMENTS version: since DOCUMENTS 4.0a HF2 the method handles the statement as UTF-8-String

◆ executeQueryUC()

DBResultSet DBConnection::executeQueryUC ( String sqlStatement )

Execute a SELECT statement using a x64/UTF-8 DOCUMENTS and retrieve a DBResultSet containing the result rows.

Note: This instruction should only be used to SELECT on the external database, since the method always tries to create a DBResultSet. If you need to execute different SQL statements, refer to the DBConnection.executeStatement() method.

Parameters

sqlStatement String containing the SELECT statement you want to execute in the database

Returns: DBResultSet containing the result rows generated by the SELECT instruction

Since: DOCUMENTS 4.0

See also: DBConnection.executeStatementUC()

Deprecated:: since DOCUMENTS 4.0a HF2, use DBConnection.executeQuery() instead

◆ executeStatement()

boolean DBConnection::executeStatement ( String sqlStatement )

Execute any SQL statement on the external database.

You can execute any SQL statement, as long as the database driver used for the connection supports the type of instruction. Use this method especially if you want to INSERT or UPDATE or DELETE data rows in tables of the external database. If you need to SELECT table rows, refer to the DBConnection.executeQuery() method.

Returns: true if successful, false in case of any error

Since: ELC 3.50 / otrisPORTAL 5.0

See also: DBConnection.executeQuery(String sqlStatement)

Note: x64/UTF-8 DOCUMENTS version: since DOCUMENTS 4.0a HF2 the method handles the statement as UTF-8-String

◆ executeStatementUC()

boolean DBConnection::executeStatementUC ( String sqlStatement )

Execute any SQL statement using a x64/UTF-8 DOCUMENTS on the external database.

You can execute any SQL statement, as long as the database driver used for the connection supports the type of instruction. Use this method especially if you want to INSERT or UPDATE or DELETE data rows in tables of the external database. If you need to SELECT table rows, refer to the DBConnection.executeQueryUC() method.

Returns: true if successful, false in case of any error

Since: DOCUMENTS 4.0

See also: DBConnection.executeQueryUC()

Deprecated:: since DOCUMENTS 4.0a HF2, use DBConnection.executeStatement() instead

◆ getLastError()

String DBConnection::getLastError ( )

Function to get the description of the last error that occurred.

Returns: Text of the last error as String

Since: ELC 3.50 / otrisPORTAL 5.0

See also: DocFile.getLastError()

Public Member Functions

Detailed Description

Constructor & Destructor Documentation

◆ DBConnection() [1/2]

◆ DBConnection() [2/2]

Member Function Documentation

◆ close()

◆ executeQuery()

◆ executeQueryUC()

◆ executeStatement()

◆ executeStatementUC()

◆ getLastError()