Database Connections

-- Test value (used in Studio Pro's Execute Query dialog)
Parameters: ( empName: string default 'Smith' )

-- Test with NULL value
Parameters: ( optionalDate: datetime null )

mdl 1;
-- Step 1: Create module
create module OracleDemo;

-- Step 2: Create constants for connection
create constant OracleDemo.OracleConnectionString ( Type: string, DefaultValue: 'jdbc:oracle:thin:@//10.211.55.2:1522/ORCLPDB1' );

create constant OracleDemo.OracleUser ( Type: string, DefaultValue: 'scott' );

create constant OracleDemo.OraclePassword ( Type: string, DefaultValue: 'tiger' );

-- Step 3: Create non-persistent entity for results
create non-persistent entity OracleDemo.EmpRecord (
  EMPNO: decimal,
  ENAME: string(10),
  JOB: string(9),
  SAL: decimal,
  DEPTNO: decimal
);

-- Step 4: Create database connection
create database connection OracleDemo.HRDatabase (
  Type: 'Oracle',
  ConnectionString: @OracleDemo.OracleConnectionString,
  Username: @OracleDemo.OracleUser,
  Password: @OracleDemo.OraclePassword
) {
  query GetAllEmployees (
    Sql: 'SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP ORDER BY EMPNO',
    Returns: OracleDemo.EmpRecord
  )

  query GetEmployeeByName (
    Sql: 'SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP WHERE ENAME = {empName}',
    Parameters: ( empName: string ),
    Returns: OracleDemo.EmpRecord
  )

  query GetHighEarners (
    Sql: 'SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP WHERE SAL >= {minSalary}',
    Parameters: ( minSalary: decimal ),
    Returns: OracleDemo.EmpRecord
  )
};

mdl 1;
create constant Inventory.PgConnectionString ( Type: string, DefaultValue: 'jdbc:postgresql://localhost:5432/inventory_db' );

create constant Inventory.PgUser ( Type: string, DefaultValue: 'inventory_app' );
create constant Inventory.PgPassword ( Type: string, DefaultValue: '' );

create non-persistent entity Inventory.ProductRecord (
  ProductId: integer,
  ProductName: string(100),
  Quantity: integer,
  Price: decimal
);

create database connection Inventory.ProductDatabase (
  Type: 'PostgreSQL',
  ConnectionString: @Inventory.PgConnectionString,
  Username: @Inventory.PgUser,
  Password: @Inventory.PgPassword
) {
  query GetAllProducts (
    Sql: 'SELECT product_id, product_name, quantity, price FROM products',
    Returns: Inventory.ProductRecord,
    Map: (
      ProductId = product_id,
      ProductName = product_name,
      Quantity = quantity,
      Price = price
    )
  )

  query SearchProducts (
    Sql: 'SELECT product_id, product_name, quantity, price FROM products WHERE product_name ILIKE {searchPattern}',
    Parameters: ( searchPattern: string ),
    Returns: Inventory.ProductRecord,
    Map: (
      ProductId = product_id,
      ProductName = product_name,
      Quantity = quantity,
      Price = price
    )
  )
};

-- List all database connections
list database connections;

-- List connections in a specific module
list database connections in MyModule;

-- View connection source code
describe database connection MyModule.MyDatabase;

-- Remove one
drop database connection [if exists] MyModule.MyDatabase;

-- Constants for configuration
create constant Module.Name ( Type: string, DefaultValue: 'value' );
list constants in module;

-- Non-persistent entities for results
create non-persistent entity Module.Name (...);
list entities in module;

-- Execute a query and store results
$ResultList = execute database query Module.Connection.QueryName;

-- Fire-and-forget (no output variable)
execute database query Module.Connection.QueryName;

$ResultList = execute database query Module.Connection.QueryName
  dynamic 'SELECT id, name FROM employees WHERE active = true LIMIT 10';

-- The definition declares $driverId; this SQL ignores it, and the call still
-- has to supply it.
$Count = execute database query F1.DuckDB.CountAllDrivers
  dynamic 'SELECT count(*) AS n FROM read_csv(''/data/f1db-drivers.csv'')'
  ( driverId = 'unused' );

--   read_csv({dataDir} || '/f1db-drivers.csv')

-- Query definition (in the database connection's { } block):
--   query GetDriversByNationality (
--     Sql: 'SELECT * FROM drivers WHERE nationality = {nation}',
--     Parameters: ( nation: String ),
--     Returns: Module.DriverRecord
--   )

-- Microflow execution:
$Drivers = execute database query Module.Connection.GetDriversByNationality
  (nation = $NationalityVar);

$Results = execute database query Module.Connection.QueryName
  connection (DBSource = $url, DBUsername = $user, DBPassword = $Pass);

mdl 1;
-- Set up non-persistent entity, constants, and connection
create non-persistent entity HR.EmployeeRecord (
  EmpId: integer,
  Name: string(100),
  Department: string(50)
);

create constant HR.DbUrl ( Type: string, DefaultValue: 'jdbc:postgresql://localhost:5432/hrdb' );
create constant HR.DbUser ( Type: string, DefaultValue: 'app' );
create constant HR.DbPass ( Type: string, DefaultValue: '' );

create database connection HR.MainDB (
  Type: 'PostgreSQL',
  ConnectionString: @HR.DbUrl,
  Username: @HR.DbUser,
  Password: @HR.DbPass
) {
  query GetAllEmployees (
    Sql: 'SELECT emp_id, name, department FROM employees',
    Returns: HR.EmployeeRecord,
    Map: (EmpId = emp_id, Name = name, Department = department)
  )

  query GetByDepartment (
    Sql: 'SELECT emp_id, name, department FROM employees WHERE department = {dept}',
    Parameters: ( dept: string ),
    Returns: HR.EmployeeRecord,
    Map: (EmpId = emp_id, Name = name, Department = department)
  )
};

-- Microflow that executes the query
create microflow HR.ACT_LoadEmployees($Department: string)
returns list of HR.EmployeeRecord as $Employees
begin
  $Employees = execute database query HR.MainDB.GetByDepartment
    (dept = $Department);
  return $Employees;
end;

mdl 1;
sql connect postgres 'postgres://user:pass@host:5432/legacydb' as source;
import from source query 'SELECT name, email FROM employees'
  into HRModule.Employee
  map (name as Name, email as Email);