Working with Databases in IIB

Can we access multiple databases from a single IIB or WMB compute node?

1. Ensure that all of the databases are of the same type (since they will all be called through the same driver)
2. In the compute node, you can use ESQL like -
    
   Database.{Datasource}.{SchemaName}.{Table} in the FROM clause

Note - sometimes (when single DB interaction, and DSN property is set) we do not need to mention the Datasource name like in below - 

INSERT INTO Database.Table1(Id, Name, Description) VALUES(Id, Name, Description);

        UPDATE Database.Table1 AS PD SET Id=Id, Name=Name, Description=Description

WHERE PD.Id = Id;

----------------------------------------------

Using SELECT statement :


SET PartsTable.Part[] = SELECT
  P.PartNumber,
  P.Description,
  P.Price

 FROM Database.DSN1.Shop.Parts AS P;



Using PASSTHRU function:



SET OutputRoot.XML.Data.SelectResult.Row[] =
  PASSTHRU('SELECT R.* FROM Schema1.Table1 AS R WHERE R.Name = ? OR R.Name = 
           ? ORDER BY Name'
   TO Database.DSN1
   VALUES ('Name1', 'Name4'));


Using Java - JDBC driver :

 Connection conn1 = getJDBCType4Connection("DSN1",JDBC_TransactionType.MB_TRANSACTION_AUTO);
 Connection conn2 = getJDBCType4Connection("DSN2",JDBC_TransactionType.MB_TRANSACTION_AUTO); 



Using PASSTHRU statement -

Used for DDL (administration queries)

PASSTHRU 'DROP TABLE Shop.Customers' TO Database.DSN1;