Sets the Procedure object that dynamically changes the connection properties just before the application accesses the database at runtime.
Generators: .NET, Java, .NET Framework
To use this feature, it is necessary to define a Procedure object that specifies the desired connection properties. This Procedure is executed just before the application establishes a connection to the database, ensuring that the connection parameters are adjusted according to the specific needs of each run.
In this Procedure, you may state/change the properties of the Datastore to which the application must connect.
The BeforeConnect method in Java is invoked with a frequency that depends on whether the type of connection pool used is the server pool or the GeneXus pool.
-
If the server pool is used (similar to .NET): The BeforeConnect procedure is called before executing any database access in each Logical Unit of Work (LUW). This means that during a request to the server, which can be an action in a Web Panel object, a Transaction object, a service, etc. that operates on the database (such as “for each”, “commit”, “update”, “delete”, among others.), BeforeConnect will be executed once. However, if during execution there is a Procedure object that starts a new LUW, a new connection to the database will be opened and BeforeConnect will be executed again.
- If the GeneXus pool is used: BeforeConnect is called before each operation on the database. This happens because, when using the GeneXus pool, a connection to the pool is requested before each operation. Although the pool may return the same connection previously used, BeforeConnect will be executed in each operation that interacts with the database.
In .NET, the connection pool uses the connection string as a key that is automatically managed by the framework. If a different username is assigned in the datastore for each user, connections will not be reused between them.
In Java, for the connection pool to be shared between users, the username must be the same. Otherwise, each user will use an independent connection without taking advantage of the shared pool.
It is essential to evaluate the use of different connection strings for each user. If this strategy is chosen, a viable alternative is to use a common connection string, modifying only the database for each tenant. Auditing of the logged-in user can be managed through an auxiliary table or the web session.
The use of BeforeConnect should not have a significant impact, as long as the operations performed within the procedure are lightweight. It is crucial that these operations, such as modifying connection data in the datastore, are not expensive to avoid performance problems.
The Procedure configured in the 'Before Connect' property must be defined in the Knowledge Base and comply with the requirements mentioned below.
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 property must be set to Yes; in Web environments, it must be set to No.
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 executing the application.
Furthermore, it must not access the database or use any functionality that does it. Some functionalities that access the database (and must not be used, because if they were used, the program would enter an infinite loop) are as follows:
- For Each command
- Business Components
- GAMSession.Get() or any method that internally accesses the database
- Call to a procedure that accesses the database
This property applies only at runtime.
Suppose that the Login Web Panel sets the end user that has logged into a web session:
&Session.Set('UserID', str(&UserID))
Also, you want to connect to different databases depending on the logged in user.
For this purpose, define a Procedure named CONNECT, which must be stated in the 'Before Connect' Model property:
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
// Changing Default connection
&dbconn = GetDatastore("Default")
&dbconn.JDBCDriverName = "com.microsoft.jdbc.sqlserver.SQLServerDriver"
&dbconn.JDBCDriverURL = "jdbc:microsoft:sqlserver://MyServer:1433;databaseName=" + trim(&DataBase) + ";SelectMethod=cursor"
&dbConn.UserName = 'username'
&dbConn.UserPassword = 'userpassword'
Below is the same case using JDBC driver:
&dbconn.JDBCDriverName = "net.sourceforge.jtds.jdbc.Driver"
&dbconn.JDBCDriverURL = "jdbc:jtds:sqlserver://MyServer:1433/"+ &database.Trim()
Alternatives to connect to an external datasource and GeneXus connection pool at runtime.
Rules
Parm(inout: &dbconn);
Source
&dbconn = GetDatastore("Default")
if &Connection = 'JNDI'
&dbconn.UseExternalDatasource = 1
&dbconn.ExternalDatasourceName = 'java:/comp/env/jdbc/myoracle'
else
&dbconn.UseExternalDatasource = 0
&dbconn.JDBCDriverName = "oracle.jdbc.driver.OracleDriver"
&dbconn.JDBCDriverURL = "jdbc:oracle:thin:@Testorcl:1521:testorcl"
&dbConn.UserName = &UserId.Trim()
&dbConn.UserPassword = &UserPwd.Trim()
endif
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() //SQLServer
//&dbconn.ConnectionData = "Data Source=" + &DataBase.Trim() //MySQL
To apply the corresponding changes when the property value is configured, execute Build any object with the purpose of generating the *.config files.
DBConnection Data Type
After connect property