niota

Before you can use niota to edit data in your databases you have to set up connections to this databases. You have to edit the connections file manually on the server.

• logon to the server where N-PAPP is installed
• navigate to \app\conf - folder
• edit file connections.php

One connection out of the array of configured connections must be set as the metadata database connection. This connection must be a MySQL connection.

Other database connections can be set to your local database management systems and may be of this types

• MySQL Server
• MS SQL Server
• Oracle
• Teradata

You can also configure other types of connection in this file.

• Web service connection
• LDAP connection (for authentication via LDAP)

The metadata connection is assigned by this variable.

// the metadata connection, from the ET_CONNECTIONS keys
define('ET_METADATA_CONNECTION', 'niota_meta');

The assigned value must exist as a valid entry in the array of defined connections. The array of possible connections is defined in constant ET_CONNECTIONS.

You can define in theory as many connections as you like. In reality it's recommended to limit the number of connections. (max 128). To get a valid connection definition you have to set a number of entries in the constant array ET_CONNECTIONS

Every connection definition is a named array with this settings. Not every setting must be made in every case. If you do not set opitional parameters then some default values are used.

There are some pre-definied constants/variables that you can use in the configuration of a connection. A database connection must be of type CONN_TYPE_DATABASE.

PHPGRID_DB_TYPE                     => <database type of the connection>,
PHPGRID_DB_DISPLAY_NAME             => "<display name>",
PHPGRID_DB_HOSTNAME                 => "<host:port>",
PHPGRID_DB_USERNAME                 => "<user for connection>",
PHPGRID_DB_PASSWORD                 => "<password for connection>",
PHPGRID_DB_NAME                     => "<fixed datbase name for connection>",
PHPGRID_DB_CHARSET                  => "<character set of connection on db side",
PHPGRID_DISPLAY_NUMBER_DECIMALPOINT => "<decimal point character",
PHPGRID_DB_PW_ENCRYPTED             => <password encryption true/false flag>
PHPGRID_SOURCE_DATE_FORMAT          => "<date format on db side>",
PHPGRID_DISPLAY_DATE_FORMAT         => "<date format in niota",
PHPGRID_SOURCE_DATETIME_FORMAT      => "<timestamp format on db side>",
PHPGRID_DISPLAY_DATETIME_FORMAT     => "<timestamp format in niota>",
PHPGRID_CONN_TYPE                   => <connection type>,

Type of connection in case of a database connection. Here you can see allowed value for connection database type

In case you choose a type that is an ODBC connection type you need to configure a proper ODCB connection as well and reference to this connection in the hostname parameter.

Text string that is used in the UI displayname of the connection.

Name or address of the host where the database engine in runnning. The hostname may have different types of setting depending on the type of DB connection you choose.

• name of the ODBC connection in case you use a ODBC connection type
• hostname:port in case you use a native connection type
• false in case you use Oracle tnsames.ora - file

Name / ID of the user you want to connect to the database. Use a technical user or a service user here. Asure, that the password of user changes not regularly.

Password used to connect to the database. Use encrypted password if possible. If you use encrypted password, set PHPGRD_DB_PW_ENCRYPTED true.
Use php-applicaton /app/tools.php to encrypt your password string.

Name of the database the system connects to. In case of MS SQL Server the database name has to include the schema as well.

Character set Id of the database. The application itself always tries to convert to utf-8. You have to specify the dataset of the DB / the source system here.

Character to use as decimal point separator in niota.

Flag, that indicates if your password is encrypted (true) or not (false).

Formatstring, that describes how date variables are stored / formated on database side. Mainly relevant for Oracle connections.

Formatstring, that describse how date variables should be formated in niota.

Formatstring, that describes how date-time (timestamp) variables are stored / formated on database side. Mainly relevant for Oracle connections.

Formatstring, that describse how date-time (timestamp) variables should be formated in niota.

Overall type of the connection definition. Here you can see allowed value for connection type

• create the MSSQL ODBC data source.

Take care wether you create a 64 bit or 32 bit data source. 64 bit PHP does not work with 32 bit ODBC data source and vice versa.

• edit file niota/conf/connections.php according to the connection details.

The PHPGRID_DB_HOSTNAME key has got the value of the ODBC data source name.

• test the connection by loading a grid in niota

• edit file niota/app/conf/connections.php according to the connection details.

See the sample connection to modify in file “niota/app/conf/connections_sample.php”.

• test the connection by loading a grid in niota

¹⁾ Only differences are listed.

It is possible to use Windows Authentication in the case of native also in the case of ODBC connection.

requirements

• Windows Server OS
• Microsoft Active Directory (AD) installed on Windows Server OS
• SQL Server database server
  • Windows authentication must be enabled under menu item: server properties
  • AD user must exists as a „login” on the database server
  • AD user must have access right to query table meta info – needed for proper working of niota

²⁾

administrative tasks IMPORTANT! AD user used for Windows Authentication (database authentication) must be the same user who runs Apache service! (can be set in service properties)

When Apache runs under a specified user account, consider that this account may be removed by your group policies configured by your central IT department. It may occur that the user is removed / deactivated frequently. Then authentication will fail. You need a permanent right to run services with an AD account / local account.

AD user must have rights on system to copy and move files during niota activities

• Excel import
• File upload – special care to access network drive if needed
• Write log files

³⁾)

Take care on specifying the AD user as „domain\user-name”. E.g.: in the picture domain is „win19” and the user name is „test”.

⁴⁾ Only differences and user authentication settings are listed.

ODBC connection: PHPGRID_DB_HOSTNAME has the value of the ODBC datasource name (while you have to use server name/IP in the case of native connection - see next section).

native connection: PHPGRID_DB_HOSTNAME ⇒ “SQL-Server-instance-name” is the instance name of the db server. In this example the AD user and the db server are defined in the same domain. Host name can also be defined by the IP address of the database server. For further details see PHP documentation: https://www.php.net/manual/en/function.sqlsrv-connect.php

User name and password fields has to be set as empty-string in the case of both connections.

^{5) 6)}

niota uses native Oracle connection. Nevertheless Oracle Instant Client is also required. The Oracle instant client zip can be downloaded from niota download page.

If you have got the right version of Instant Client on your machine you can start with step 3 here.

Installation steps:

• create a new environment variable in Windows:
  • Go to Control Panel → System → System Properties → Environment Variables
  • In the “System variables” frame click “New”
  • Define the new variable as: ORACLE_HOME = C:\path\to\oracle-instant-client
• Add ”Oracle instant client \bin” folder path to the beginning of the environment variable: Path.

Path = %ORACLE_HOME%\bin;… Note: if your ”Oracle instant client” installation does not have a bin folder then add the “instant client” folder to the Path. Like: Path = C:\path\to\oracle-instant-client;… Or you can use the following formula (the two lines are equivalent): Path = %ORACLE_HOME%;…

• restart the computer
• edit file niota/app/conf/connections.php according to the connection details

You can find an example definition in file niota/app/conf/connections_sample.php at the following section:

Oracle-tnsnames.ora => array(	...

Read notes and comments in file “connections_sample.php”. Comments provide additional information about the configuration. You can check the two sort of Oracle connection definitions in ‘Table 2’.

• log in to niota and load a grid that uses the configured connection

⁷⁾ Only differences are listed

• create the Teradata ODBC data source

Take care wether you create a 64 bit or 32 bit data source. 64 bit PHP does not work with 32 bit ODBC data source and vice versa.

• edit file niota/app/conf/connections.php according to the connection details

The PHPGRID_DB_HOSTNAME key has set to the value of the ODBC data source name.

• test the connection by loading a grid in niota

MySQL uses native connections. You do not need any driver.

• edit file niota/app/conf/connections.php according to the connection details
• test the connection by loading a grid in niota

PHPGRID_WS_DISPLAY_NAME             => "<display name>",
PHPGRID_WS_ENDPOINT                 => "<webservice endpoint>",
PHPGRID_WSDL_LOCATION               => "<location of wsdl file",
PHPGRID_WS_USERNAME                 => "<user for connection>",
PHPGRID_WS_PASSWORD                 => "<password for connection>",
PHPGRID_CONN_TYPE                   => <connection type>,
PHPGRID_WS_PASSWORD_ENCRYPTED       => <password encryption true/false flag>

Text string that is used in the UI displayname of the connection.

URL of the endpoint of the webservice. Syntax is like this:

http://<server>:<port>/<path>

URL and name of the WSDL-file of the webservice. The filename must include the file-extension Syntax is like this:

http://<server>:<port>/<path>/<wsdl-file>

Name / ID of the user you want to connect to the webservice. Use a technical user or a service user here. Asure, that the password of user changes not regularly.

Password used to connect to the webservice. Use encrypted password if possible. If you use encrypted password, set PHPGRID_WS_PASSWORD_ENCRYPTED true.

Flag, that indicates if your web service password is encrypted (true) or not (false).

PHPGRID_DB_HOSTNAME                 => "<ldap-server>:<port>",
PHPGRID_DB_DN                       => "<ladp-servername in canonical notation>",

URL of the LDAP server and port. Syntax is like this:

ldaps://<server>:<port>
//
e.g. in case of secure LDAP
ldaps://my.hostname.com:636
//
e.g. in case of plain LDAP
ldap://my.hostname.com:389

Name of the LDAP server in canonical notation Syntax is like this:

dc=<first part>,dc=<main part>,dc=<top level>
e.g.
dc=my,dc=hostname,dc=com>