sos

sails-oracle-sp

A sails adapter specialized for invoking Oracle stored procedures

Showing:

Popularity

Downloads/wk

3

GitHub Stars

2

Maintenance

Last Commit

5yrs ago

Contributors

1

Package

Dependencies

1

Size (min+gzip)

6.1KB

License

MIT

Type Definitions

Tree-Shakeable

No?

Categories

Readme

sails-oracle-sp

About

The sails-oracle-sp adapter provides easy access to Oracle stored procedures for sails models

Installation

To install this adapter, run:

$ npm install sails-oracle-sp

Usage

The input parameters are provided by the controller to the model and then to this adapter.

Sails connection

Specify the credential details needed to connect your Oracle instance in config/connections.js

Example connection named oraclehr
  oraclehr: {
    adapter: 'sails-oracle-sp',
    user: 'hr',
    password: 'welcome',
    package: 'HR',
    cursorName: 'DETAILS',
    connectString: 'localhost/xe'
  }
  • package is the name of the PL/SQL Package
  • cursorName is the name of the PL/SQL ref cursor to return results

Sails model

Sails' model is designed to support interacting with database tables. Consequently Sail's model includes table-orientated attributes such as table name and column attributes. Put another way, details pertaining to a specific database table (e.g., table name, column details) are stored in a Sails' model. Sails uses the information in a table's model to conduct SQL against said table.

Sails-oracle-sp is using Sails' model to specific details pertaining to a set of stored procedures instead of a table. The fit between using Sails' model for accessing tables and stored procedures is not exact. When used to access database tables Sail's model corresponds to exactly one table. In contrast, generally a Sails model is used to access a "family" of four stored procedures: one for each REST verb (POST, GET, PUT and DELETE.) Every stored procedure in this "family" has the same prefix. As an example, consider a package named hr, with stored procedures that operate on a table named employees. Table 1 shows the stored procedure names, their affect and the REST verb to which they are mapped.

Package namestored procedure nameREST Verbdescription
foobar_cPOSTcreate a bar record
foobar_rGETfetches a bar record
foobar_uPUTupdates a bar record
foobar_dDELETEdestroys a bar record

Table 1: REST verbs with respect to the specific stored procedure they will invoke.

Note that each stored procedure name is prefixed with "family name" "bar", followed by an underscore, which is then appended by one of the following letters: C,R,U,D.

You specify the details that cause Sails to generate the correct stored procedure calls in api/models/YOUR_MODEL.js. Sails-oracle-sp repurposes the table name attribute such that it becomes the stored procedures' "family name". So for this example the tableName attribute is "bar".

Sails-oracle-sp repurposes the column name attribute so as to specify store procedure parameters.

Sails adapters, including Sails-oracle-sp, expose model-contextualized CRUD methods. These adapter methods are named create(), find(), update(), and destroy(). Table 2 shows how these adapter methods map to stored procedure names.

adapter methodsstored procedure nameREST Verbdescription
create()bar_cPOSTcreate a bar record
find()bar_rGETfetches a bar record
update()bar_uPUTupdates a bar record
destroy()bar_dDELETEdestroys a bar record

Table 2: Sails-oracle-sp adapter methods with respect to the specific stored procedure they will invoke.

When generating the stored procedure call the adapter method appends the necessary letter to the "family name."

Example model named Employees using the oraclehr connection
module.exports = {

  tableName: 'EMPLOYEES',
  schema: true,
  connection: 'oraclehr',
  autoCreatedAt: 'false',
  autoUpdatedAt: 'false',

  attributes: {
    EMPLOYEE_ID: {
      type: 'integer',
      autoIncrement: true,
      primaryKey: true,
      unique: true
    },
    FIRST_NAME: {
      type: 'string',
      required: false
    },
    LAST_NAME: {
      type: 'string',
      required: true
    },
    EMAIL: {
      type: 'string',
      required: true
    },
    PHONE_NUMBER: {
      type: 'string',
      required: false
    },
    HIRE_DATE: {
      type: 'string',
      required: true
    },
    JOB_ID: {
      type: 'string',
      required: true
    },
    SALARY: {
      type: 'float',
      required: false
    },
    COMMISSION_PCT: {
      type: 'float',
      required: false
    },
    MANAGER_ID: {
      type: 'integer',
      required: false
    },
    DEPARTMENT_ID: {
      type: 'integer',
      required: false
    }
  }
};

Example

See sails-oracledb-sp-example

License

MIT © 2014

Rate & Review

Great Documentation0
Easy to Use0
Performant0
Highly Customizable0
Bleeding Edge0
Responsive Maintainers0
Poor Documentation0
Hard to Use0
Slow0
Buggy0
Abandoned0
Unwelcoming Community0
100