@firstfleet/ffsql

FFSQL

This is our libary that wraps database calls for MSSQL. We support both (task-based) and (promise-based) calls.

It relies on four npm packages.

• mssql - used to make promise-based calls to SQL
• data.task - used to create a task oriented work flow
• ffErrorHandler - used for error handling and logging
• md5 - used to compare versions of the db config file (if you are using one for user and password)

Installation

npm install @firstfleet/ffsql --save

Promises

ExecMsProc (procecure, params, options)

Inputs

• procedure: name of SQL stored procedure
• params: object containing key/value options that must match the parameters of the procedure
• options
  • firstOnly - if set to true, the method will only return the first record found (instead of an array)

Special notes

• If your query result is a single value, it will automatically be unpacked to a simple scaler value (instead of an array)

Examples:

const sql = require('@firstfleet/ffsql')
sql.ExecMsProc('ESig_GetFieldsToSave', {DocID: docId, SignSequence: signSequence}, {firstOnly: true})
sql.ExecMsProc('ESig_GetEmployeeDocs', {empId: empId})
sql.ExecMsProc('ESig_GetDocsMissingPDFData');

ExecMsQuery (queryText, params, options)

Inputs

• queryText: Any SQL text. Param placeholders should be in the string prefixed with @ followed by an incrementing integer (similar to .NET PetaPoco syntax)
• params: an array of parameters that will slide into the queryText placeholders
• options
  • firstOnly - if set to true, the method will only return the first record found (instead of an array)

Special notes

• If your query result is a single value, it will automatically be unpacked to a simple scaler value (instead of an array)

Examples:

 const sql = require('@firstfleet/ffsql')
 sql.ExecMsQuery(`select count(*) from ESigEmpDocs where EmpId = @0`, [empId], {firstOnly: true});
 sql.ExecMsQuery('update ESigEmpDocs set SignDate = getdate(), SignStatus = @0 where Id = @1', [signStatus, empDocId])

Tasks

Each of the exposed module methods return Task from data.task. You need to fork the task in order to get you Task.rejected(error) or your Task.of(data)

Require the module
cosnt sql = require('@firstfleet/ffsql')

ReturnSqlResults

sql.ReturnSqlResults(procedure, params)

Takes in a prodcedure name {string} and params {Object}. Returns a Task.rejected(error) || Task.of(data)

Example

sql.ReturnSqlResults("Database.dbo.getSqlRecords", {user: userId, option: 1})

ExecSql

sql.ExecSql(procedure, params)

Takes in a procedure name {string} and a params {Object}. Returns either a Task.rejected(error) || Task.of('Success') Used to execute a sql procedure when not expecting any data to return.

ExecDynamicSql

sql.ExecDynamicSql(procdedure || sql string, params)

Takes in either a sql procedure, or a string of sql text like "select * from table". Returns either a Task.rejected(error) || Task.of(data)