Monitoreo Conexion a Base de DATOS.

Before Connect Model Property

Scope

Languages: Java, .NET
Environments: Web, Win
Access Technologies: JNDI (Java), JDBC (Java), ADO.NET (.NET)

Introduction

The Before Connect property has been implemented to change connection properties dynamically at execution time.

In order to use this feature, a GeneXus procedure is required. This procedure should state the connection properties at execution time; it will be executed immediately before any connection request is executed by the application to interact with the database.

A new model property called 'Before Connect' has been added.

Description

A GeneXus procedure should be stated in the new 'Before Connect' property because it will be called before the connection is established. This procedure must be programmed in GeneXus and should fulfill some requirements as well.

In this procedure you may state/change the properties of the Datastore to which the application should connect to.

This procedure will be called every time that a database request is executed. For instance, if we have a work panel that loads data from a database, this procedure will be executed in each Refresh (F5).

Procedure requirements
It must have at least one in/out DBConnection type parameter.

parm(INOUT: &dbconn); //&dbconn is a DBConnection type variable

In 3 Tier models, the "Execute in new LUW" object property must be set to yes.

The procedure receives the &dbconn with the Datastore details, and you can modify those details depending on any logic that you may need to apply. For example, you can change the Datastore details depending on the User that is actually executing the application.

Example - Multi company application

Suppose that the login web panel sets the user that has logged in a web session:

&Session.Set('UserID', str(&UserID)) 

Also, we want to connect to different databases depending on the user logged in.
For this purpose, let's define a procedure named CONNECT, which must be stated in the 'Before Connect' Model property:

JAVA Example

Rules

Parm(inout: &dbconn); 

Source

&UserID = val(&Session.Get('UserID')) 

Do Case
   Case &UserID = 1 
   &DataBase  = "companyone" 
   Case &UserID = 2 
   &DataBase  = "companytwo" 
   Otherwise 
   &DataBase  = "companyone" //default database 
Endcase 

// Get Default connection details 
&dbconn = GetDatastore("Default") 
&dbconn.JDBCDriverName = "com.microsoft.jdbc.sqlserver.SQLServerDriver" 
&dbconn.JDBCDriverURL = "jdbc:microsoft:sqlserver://fabian:1433;databaseName=" + trim(&DataBase) + ";SelectMethod=cursor" 
&dbConn.UserName = 'username' 
&dbConn.UserPassword = 'userpassword' 

Tip: Para XEV2, con driver jtds, configurar así:
&dbconn.JDBCDriverName = "net.sourceforge.jtds.jdbc.Driver"
&dbconn.JDBCDriverURL = "jdbc:jtds:sqlserver://MyServer:1433/"+ &database.Trim()

Tip: check the client.cfg configuration file located under the classes folder if you want to get the complete JDBC Driver URL.

.NET Example
Rules

Parm(inout: &dbconn); 

Source

&UserID = val(&Session.Get('UserID')) 

//select the Database depending on UserID 
Do Case
   Case &UserID = 1 
   &DataBase  = "companyone" 
   Case &UserID = 2 
   &DataBase  = "companytwo" 
   Otherwise 
   &DataBase  = "companyone" //default database 
Endcase 

//Change connection properties 
&dbconn = GetDatastore("Default") 
&dbconn.UserName = 'username' 
&dbconn.UserPassword = 'userpassword' 
&dbconn.ConnectionData = "DATABASE=" + &DataBase.Trim()

Considerations for Java

Two new properties have been implemented in the DBConnection type for using an external data source to connect to a database. These new properties are: UseExternalDatasource and ExternalDatasourceName.

Example for Java

For example, the procedure may look as follows:

parm(&dbconn); 

&dbcon.UseExternalDatasource = 1 
&dbcon.ExternalDatasourceName = "jdbc/orabarba1" 

Compatibility with Java 8.0

In GeneXus 8.0 you could change the connection information at runtime. This behavior could be implemented by changing a few lines in the CLIENT.CFG (read more information in SAC #16659).

Considerations that should be taken into account when using this feature in GeneXus 8.0:

Will this stop working after converting to GeneXus 9.0?
YES

What should we do in order to keep this functionality in the new version?
As per the above mentioned SAC, this functionality required a few more lines in the CLIENT.CFG and a procedure that returned a string with the JNDI.

Now, it is not necessary to change the CLIENT.CFG, and we suggest deleting those lines. However, a few changes should be done in the model.

The old procedure should now receive a DBConnection type variable instead of a Character one. In this procedure, the DBConnection properties should be stated rather than returning only a character variable:

&dbcon.UseExternalDatasource = 1 
&dbcon.ExternalDatasourceName = "jdbc/orabarba1" 

&dbconn should be inout instead of OUT as before.

Fuente: Articulo Original