Direct database access to databases defined in the config file. Inside a server thread class instances are available using App.dbConnections

  
      const mainConn = App.dbConnections['main']
 mainConn.exec('insert into table1(a, b) values(:a:, :b:)', {a: 1, b: 2})
 mainConn.commit()

# new DBConnection ()

Members

# inTransaction instance

Is database in transaction

Methods

# commit () → boolean instance

Commit transaction. If transaction is not started return false

# exec (sql:  string, params:  Array) → boolean instance

Execute SQL what do not expect a result and contains named :paramName: and/or inline :(inlineValue): parameters

Arguments:

# execParsed (sqlStatement:  string, params:  Array) → boolean | Object instance

Execute parsed (without inline parameters) sql statement what do not expect a result. Can be used to call a stored procedure with both IN and OUT parameters (ODBC driver, UB 5.24.11)

Input parameters should be passed as primitives, output - as object { value: 0, output: true }, where value MUST be of the same type as output parameter type, in case of string - must have enough chars to fit a result

WARNING for SQL Server, the called procedure should NOT produce additional output:

  • SET NOCOUNT ON MUST be added (including possible nested procedures calls)
  • any print statement MUST be removed (including possible nested procedures calls)

Arguments:
  • sqlStatement: string

    Statement with parameters as ?

  • params: Array

    Parameters values

  
      const conn = App.dbConnections.main
if (conn.config.driver === 'MSSQLODBC') { // see https://documentation.help/odbcsql/od_6_035_5dnp.htm
  // call stored with one parameter of type INT and what returns an INT
  const funcResultRes = conn.execParsed(
    '{? = call inParamTest(?)}', // statement wrapped by {} - so called "ODBC CALL escape sequence"
    [{ value: 0, output: true }, 1]
  )
  console.log('funcResultRes', funcResultRes) // { out0: 101 }

  // call stored proc what do not return a result (ignore result)
  const funcWOResultRes = conn.execParsed('{call inParamTest(?)}', [1])
  console.log('funcWOResultRes', funcWOResultRes) // true

  // call stored proc declared as
  // CREATE PROCEDURE outParamTest
  //   @employeeID INT,
  //   @managerID nvarchar(100) OUTPUT
  const resOut = conn.execParsed(
    '{call outParamTest(?, ?)}',
    [1, {value: ' '.repeat(100), output: true}] // use ' '.repeat(100) to reserve a buffer for output result of length 100
  )
  console.log('resOut=', resOut) // { out1: 'string result' }
  // NOTE - in case procedure do not modify an output parameter it will remain a string of 100 spaces
}

# genID (entity:  string) → number instance

Generate ID. If entity is specified - generate for specified entity (it cah have hos own sequence generator) if entity not specified (UB 5.18.17+) - generate ID for this connection

Arguments:

# rollback () → boolean instance

Rollback transaction. If transaction is not started return false

# run (sql:  string, params:  Object) → string instance

Run SQL what expect a result and contains named :paramName: and/or inline :(inlineValue): parameters

Arguments:

# runParsed (sql:  string, paramsopt:  Array) → string instance

Run parsed (all parameters are ?) SQL what expects result (select statement for example). Returns result as JSON string. To get result as JSON better to use selectParsedAsObject

Arguments:

# savepointWrap (func:  function) → * instance

For Postgres wrap a func call into temporary savepoint. In case func throws savepoint is rollback'ed and error is re-trowed, otherwise checkpoint is released. For other RDBMS execute func as is. Return a func result

Arguments:
  
      function insertionWhatMayFail () {
  let eStore = UB.DataStore('ubm_enum')
  eStore.run('insert', { execParams: { eGroup: 'tst', code: '1', name: 'test1' } })
}

let db = App.dbConnections[App.domainInfo.entities.ubm_enum.connectionName]
try {
  db.savepointWrap(insertionWhatMayFail)
} catch (e) {
  console.log('insertion failure inside savepoint')
}

# selectParsedAsObject (parsedSql:  string, paramsValues:  Array) → Array.<Object> instance

Run parsed (all parameters are ?) SQL what expects result (select statement for example). Returns result as parsed JSON

WARNING Oracle return all field name in UPPER case if AS "normalName" is not specified, Postgres - in lower case so better to write a query as such select ID as "ID", modelName AS "modelName" from .. instead of select ID, modelName from ..

Arguments:

# startTransaction () → boolean instance

Start transaction. If transaction is already started return false