npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

oledb-edgejs

v1.5.2

Published

Uses Edge.js to connect to an ADO.NET Databases.

Downloads

7

Readme

oledb.js

npm version license tips

A small promise based module which uses Edge to connect and execute queries for a OLE DB, ODBC or SQL database.

Example

const connectionString = '...';

const oledb = require('oledb');
const db = oledb.oledbConnection(connectionString);

let command = 'select * from account;';

db.query(command)
.then(result => {
    console.log(result);
},
err => {
    console.error(err);
});

The result will look like this:

{
    query: 'select count(*) from account where name = @p1',
    type: 'query',
    params: [
        {
            name: 'p1',
            value: 'Mike',
            direction: 0,
            isNullable: false,
            precision: null,
            scale: null,
            size: null
        }
    ],
    'result': [
        [
            {
                id: 1,
                name: 'Bob'
            }
        ]
    ]
}

Installation

npm install oledb --save

This module is a proxy that uses ADO.NET to call .NET code and therefore requires the .NET Framework to be installed.

Options

The module exposes three functions to initialize database connections:

  • oledb.oledbConnection(connectionString) - Initializes a connection to an OLE DB database.
  • oledb.odbcConnection(connectionString) - Initializes a connection to an ODBC database.
  • oledb.sqlConnection(connectionString) - Initializes a connection to an SQL database.

Promises

There are a number available promises that can be used to send commands and queries to a database connection:

  • .query(command, [parameters]) - Executes a query and returns an is the result set returned by the query as an Array.
  • .execute(command, [parameters]) - Executes a query command and returns an is the the number of rows affected.
  • .scalar(command, [parameters]) - Executes a query and returns an is the first column of the first row in the result set returned by the query. All other columns and rows are ignored.
  • .procedure(command, [parameters]) - Excutes a stored procedure and returns the number of rows affected.
  • .procedureScalar(command, [parameters]) - Excutes a stored procedure and returns the result.
  • .transaction(commands) - Excutes an array of commands in a single transaction and returns the result of each.

Each parameter is described below:

  • command - The string query command to be executed.
  • parameters - An Array of parameter values. This is an optional parameter.
  • commands - A parameter used for transactions, see the Transactions section below.

Query Parameters

Parameters are also supported and use positional parameters that are marked with a question mark (?) OR named parameters, i.e @parameter1. Here is an example:

let command = `
    update account
    set
        firstname = ?
    where
        id = ?;
`;

let parameters = [ 'Bob', 69 ];

db.execute(command, parameters)
.then(rowsAffected => {
    console.log(rowsAffected);
},
err => {
    console.error(err);
});

Query Parameter Options

There are a number of additional options for query parameters, a query parameter can either be a single value or an object:

let parameters = [
    'Bob',  //Declare a single parameter value. Defaults to: { name: '@p1', value: 'Bob' }
    //Or use an object to specify additional options...
    {
        name: 'myParameter',    //OPTIONAL - Parameter name. Defaults to index based parameter names, i.e @p1, @p2, @p3 ect. Note that the @ symbols are optional.
        value: 123,             //OPTIONAL - Defaults to null.
        direction: string,      //OPTIONAL - The parameter direction, (Input, Input/Output, Output, Return Value). See oledb.PARAMETER_DIRECTIONS enum.
        isNullable: bool,       //OPTIONAL - Whether to treat the paramter as non-nullable.
        precision: byte,        //OPTIONAL - The precision of the parameter value in bytes.
        scale: byte,            //OPTIONAL - The scale of the parameter value in bytes.
        size: byte              //OPTIONAL - The size of the parameter value in bytes.
    }
];

Multiple Data Sets

The .query promise has support for multiple data sets that can be returned in a single query. Here is an example:

let command = `
    select * from account;
    select * from address;
`;

db.query(command)
.then(results => {
    console.log(results[0]); //1st query result
    console.log(results[1]); //2nd query result
},
err => {
    console.error(err);
});

Stored Procedures

Stored procedures can be executed using the .procedure function with optional parameters and return value. Here is an example:

let procedureName = `addNumbers`;

let parameters = [1, 2];

db.procedure(procedureName, parameters)
.then(result => {
    console.log(result);
},
err => {
    console.error(err);
});

Stored Procedure Return Values

You can use a return value or output parameter with the .procedure function. The parameter might look like this:

{
    name: 'sum',
    direction: oledb.PARAMETER_DIRECTIONS.OUTPUT
}

for more options, see Query Parameter Options section.

Here is an example:

let procedureName = `addNumbers`;

let parameters = [
    {
        name: 'num1',
        value: 1
    },
    {
        name: 'num2',
        value: 2
    },
    {
        name: 'sum',
        direction: oledb.PARAMETER_DIRECTIONS.OUTPUT
    }
];

db.procedure(procedureName, parameters)
.then(result => {
    console.log(result);
    console.log(result.params[2].value);    //The output value returned by addNumbers stored procedure.
},
err => {
    console.error(err);
});

Transactions

The .transaction promise will execute multiple commands in a single transaction, this is useful for if you want to insert records across different tables and need to ensure that they all are inserted successfully, or not at all. All query types are supported, including procedure. Here is an example:

let commands = [
    {
        query: 'insert into account (name) values (?)',
        params: [ 'Bob' ]
    },
    {
        query: 'select * from account where name = ?',
        type: oledb.COMMAND_TYPES.QUERY,
        params: [ 'Bob' ]
    }
];

db.transaction(commands)
.then(results => {
    console.log(results); //An array of query results.
},
err => {
    console.log(err);
});

Note: The result field will contain an array of results if using a query command as multiple query results are supported by each executed query. See Multiple Data Sets above.

All commands must follow the following structure:

{
    query: string,      //REQUIRED - The query string
    params: Array,      //OPTIONAL - The query parameters
    type: string        //OPTIONAL - The query type, use one of the oledb.COMMAND_TYPES enumerations. Defaults to 'command'.
}

$prev Parameter

With transactions, you can use the special '$prev' parameter to inject the previous command's result into the next executing query. For example:

let commands = [
    //First query, executes a stored procedure and returns an account id.
    {
        query: 'insert_account (@name)',
        params: [ 
            {
                name: 'name',
                value: 'Bob'
            },
            {
                name: 'accountId',
                direction: oledb.PARAMETER_DIRECTIONS.RETURN_VALUE
            }
        ],
        type: oledb.COMMAND_TYPES.PROCEDURE
    },
    //Second query, executes a select query with the returned value from the previous query.
    {
        query: 'select * from account where id = @accountId',
        type: oledb.COMMAND_TYPES.QUERY,
        params: [
            {
                name: 'accountId',
                value: '$prev'      //Note: This value must be a string.
            }
        ]
    }
];

db.transaction(commands)
.then(results => {
    console.log(results[0]); //Insert stored procedure result. Returns the ID of the account.
    console.log(results[1]); //Select query result. Returns the account 'Bob' record.
},
err => {
    console.log(err);
});

License

This project is licensed under MIT.