Tutorial: Multiple Database Connection Example

In this tutorial we will review a practical example of use for the macros sc_connection_edit and sc_connection_new on Scriptcase. On that example, Scriptcase will identify the company of the user who is logging in, changing the connection data dynamically for that user to access his company’s database. In a second step, Scriptcase will create a new connection dynamically to fetch data that will be used by a Grid application.

sc_connection_edit Macro

That macro can be used by any Scriptcase application, the changes in the connection will take effect the next time you connect to the database.

How to use sc_connection_edit:

sc_connection_edit("conn_name", $arr_conn);

1st parameter: connection name

2nd parameter: array of items you want to change the current connection.

The array can have the following indexes:

• $arr_conn[‘server’]
• $arr_conn[‘user’]
• $arr_conn[‘password’]
• $arr_conn[‘database’]
• $arr_conn[‘persistent’] – The value “Y” or “N”
• $arr_conn[‘encoding’]

You can only use the index you want to change, but it is not required to give all of them.

Macro sc_connection_new

The macro can be used on any Scriptcase event and application.

Forma de usar sc_connection_new

sc_connection_new("new_conn_mysql", $arr_conn);

1st parameter: the connection name. If a connection physically exists with the same name, this macro will have no effect. Physically created connections are stronger, sc_connection_edit should be used to change an existing connection.

2nd parameter: array of items. Mandatory to have all indexes except persistent and encoding that are optional

The array can have the following indexes:

• arr_conn[‘drive’] – See the full list of drives ​​below. .
• $arr_conn[‘user’]
• $arr_conn[‘password’]
• $arr_conn[‘database’]
• $arr_conn[‘persistent’] – Valor “Y” ou “N”
• $arr_conn[‘encoding’]

List of drivers (see descriptions in the connection interface according to the database): access, ado_access, odbc, db2, db2_odbc, odbc_db2, odbc_db2v6, firebird, borland_ibase, ibase, firebird, pdo_informix, informix, informix72, maxsql, ado_mssql, pdo_sqlsrv, mssqlnative, odbc_mssql, mssql, pdo_mysql, mysqlt, mysql, oci805, odbc_oracle, oci8, oci8po, oracle, postgres7, pdo_pgsql, postgres64, postgres, pdosqlite, sqlite, sybase

Tutorial example

1 – onValidate Event

The login system is a control application Scriptcase (app_Login), with the following code snippet in your ONVALIDATE event:

2 – onRecord Event

Below we see the code used in the query of employees in onRecord event:

Reminding that onRecord event will be executed once for each row in the query load.