How to connect to 2 databases in Powerbuilder

How to connect to more than 1 database in Powerbuilder

Some times, you need to connect to more than 1 database in your application. In Powerbuilder, there's 1 object for database connection that you can create programmatically. The object call: TRANSACTION.

In this example, I will explain how to connect to 2 databases, which mean 1 is with default database connection object (named SQLCA), and the other is the object that we create mannualy.


I'm using Microsoft SQL Server 2000 as a database engine.

First of all, we need to create a transaction object. It's depending on what the purpose of the transaction. You can declare with Instance, Global or even Local variable for this object.

Let's say we create Instant Variable, called: SQLCAID

1:  transaction SQLCAID;  

On Open Event, we must create first the transaction object, then setup the properties of the object.

Note: You can try first on the Database painter, to get the right property values, and to make sure that you can connect to the database. Then copy and paste the script.

1:  SQLCAID = CREATE transaction;  
2:  SQLCAID.DBMS = "MSS Microsoft SQL Server"  
3:  SQLCAID.Database = "database1"  
4:  SQLCAID.LogPass = "password"  
5:  SQLCAID.ServerName = "localhost"  
6:  SQLCAID.LogId = "sa"  
7:  SQLCAID.AutoCommit = False  
8:  SQLCAID.DBParm = ""  
9:    
10:  // Try to connect the database  
11:  // REMEMBER, you must add USING SQLCAID statement at the end of your SQL Statement, every times you want to execute your database that related with SQLCAID transaction  
12:    
13:  CONNECT USING SQLCAID;  
14:    
15:  // Trap the error if the object can't connect to database  
16:    
17:  IF SQLCAID.SQLCode <> 0 THEN  
18:    MessageBox("Connection Error to "+SQLCAID.Database, &  
19:      "Unable to connect to database. Make sure you type the correct password." + &  
20:      "~r~nTry Again. If the problem persists, contact IT Officer" + &  
21:      "~r~nSQLDBCode = " + String(SQLCAID.SQLDBCode) + &  
22:      "~r~nSQLErrText = " + SQLCAID.SQLErrText)  
23:    HALT  
24:  END IF  

At the other side, you still must declare the DEFAULT SQLCA transaction object.

1:  SQLCA.DBMS = "MSS Microsoft SQL Server"  
2:  SQLCA.Database = "databasedefault"  
3:  SQLCA.LogPass = "password"  
4:  SQLCA.ServerName = "localhost"  
5:  SQLCA.LogId = "sa"  
6:  SQLCA.AutoCommit = False  
7:  SQLCA.DBParm = ""  
8:    
9:  // Just for make sure that you connect with SQLCA transaction  
10:    
11:  CONNECT USING SQLCA;  
12:    
13:  // Trap the error if the object can't connect to database  
14:  IF SQLCA.SQLCode <> 0 THEN  
15:       MessageBox("Connection Error to "+SQLCA.Database, &  
16:            "Unable to connect to database. Make sure you type the correct password." + &  
17:            "~r~nTry Again. If the problem persists, contact IT Officer" + &  
18:            "~r~nSQLDBCode = " + String(SQLCA.SQLDBCode) + &  
19:            "~r~nSQLErrText = " + SQLCA.SQLErrText)  
20:       HALT  
21:  END IF  

Now you already connected with 2 databases.

Remember, always put USING SQLCA or USING SQLCAID statement at the end of your SQL Statement to make sure that the SQL Statement will execute into the database that you purpose

Example:

1:  string sProductID, sProductName  
2:    
3:  // will retrieve the product_id and product_name column from SQLCAID transaction  
4:  SELECT product_id, product_name INTO :sProductID, :sProductName FROM product_master USING SQLCAID;  
5:    
6:  // will retrieve the product_id and product_name column from SQLCA (Default) transaction  
7:  SELECT product_id, product_name INTO :sProductID, :sProductName FROM product_master USING SQLCA;