iSql

class iSql()

Note

Deprecated: Will be removed in the next major version.

Creates a new iSql object.

iSql.addQuery(stmt, options)

Note

Deprecated: Will be removed in the next major version.

Specifies an sql query.

Arguments
  • stmt (string) – The sql staetment.

  • options (iSqlOptions) –

iSql.columnPriv(params, options)

Note

Deprecated: Will be removed in the next major version.

Retrieves column privledge metadata.

Arguments
  • params (Array.<string>) – The first element is the qualifer or catalog for the table. This may be an empty string. The second element is the schema name for the column. The third element is the name of the table. The fourth element is the name of the column.

  • options (iSqlOptions) –

iSql.columns(params, options)

Note

Deprecated: Will be removed in the next major version.

Retrieves column metadata.

Arguments
  • params (Array.<string>) – The first element is the qualifer or catalog for the table. This may be an empty string. The second element is the schema name for the column. The third element is the name of the table. The fourth element is the name of the column.

  • options (iSqlOptions) –

iSql.commit(options)

Note

Deprecated: Will be removed in the next major version.

Commits or rolls back a transaction.

Arguments
iSql.count(options)

Note

Deprecated: Will be removed in the next major version.

Retireves the number of column results, parameters, or both.

Arguments
iSql.describe(options)

Note

Deprecated: Will be removed in the next major version.

Retireves the description of a column, parameter, or both.

Arguments
iSql.execute(params, options)

Note

Deprecated: Will be removed in the next major version.

Executes a prepared statement.

Arguments
iSql.fetch(options)

Note

Deprecated: Will be removed in the next major version.

Frees Resources.

Arguments
iSql.foreignKeys(params, options)

Note

Deprecated: Will be removed in the next major version.

Retrieves foreign key metadata.

Arguments
  • params (Array.<string>) – The first element is the qualifer or catalog for the primary key table. This may be an empty string. The second element is the schema name for the primary key table. The third element is the name of the primary key table. The fourth element is the qualifer or catalog for the foreign key table. This may be an empty string. The fifth element is the schema name for the foreign key table. The sixth element is the name of the foreign key table.

  • options (iSqlOptions) –

iSql.free()

Note

Deprecated: Will be removed in the next major version.

Adds sql free XML.

iSql.pColumns(params, options)

Note

Deprecated: Will be removed in the next major version.

Retrieves procedure columns metadata.

Arguments
  • params (Array.<string>) – The first element is the qualifer or catalog for the procedure. This may be an empty string. The second element is the schema name for the procedure. The third element is the name of the procedure.

  • options (iSqlOptions) –

iSql.prepare(stmt, options)

Note

Deprecated: Will be removed in the next major version.

Prepares a sql statement.

Arguments
  • stmt (string) – The sql statement.

  • options (iSqlOptions) –

iSql.primaryKeys(params, options)

Note

Deprecated: Will be removed in the next major version.

Retrieves primary key metadata.

Arguments
  • params (Array.<string>) – The first element is the qualifer or catalog for the table. This may be an empty string. The second element is the schema name for the table. The third element is the name of the table.

  • options (iSqlOptions) –

iSql.procedures(params, options)

Note

Deprecated: Will be removed in the next major version.

Retrieves procedure metadata.

Arguments
  • params (Array.<string>) – The first element is the qualifer or catalog for the procedure. This may be an empty string. The second element is the schema name for the procedure. The third element is the name of the procedure.

  • options (iSqlOptions) –

iSql.rowCount(options)

Note

Deprecated: Will be removed in the next major version.

Retrieves the number of rows affected by a change.

Arguments
iSql.special(params, options)

Note

Deprecated: Will be removed in the next major version.

Retrieves special column metadata.

Arguments
  • params (Array.<string>) – The first element is the qualifer or catalog for the table. This may be an empty string. The second element is the schema name for the column. The third element is ‘row’, ‘transaction’, or ‘session’. The fourth element is ‘no’ or ‘nullable’.

  • options (iSqlOptions) –

iSql.statistics(params, options)

Note

Deprecated: Will be removed in the next major version.

Retrieves metadata statistics for a table.

Arguments
  • params (Array.<string>) – The first element is the qualifer or catalog for the table. This may be an empty string. The second element is the schema name for the table. The third element is the name of the table. The fourth element is ‘all’ or ‘unique’ to specify the level of stats returned.

  • options (iSqlOptions) –

iSql.tablePriv(params, options)

Note

Deprecated: Will be removed in the next major version.

Retrieves table priviledge metadata.

Arguments
  • params (Array.<string>) – The first element is the qualifer or catalog for the table. This may be an empty string. The second element is the schema name for the table. The third element is the name of the table.

  • options (iSqlOptions) –

iSql.tables(params, options)

Note

Deprecated: Will be removed in the next major version.

Retrieves table metadata.

Arguments
  • params (Array.<string>) – The first element is the qualifer or catalog for the table. This may be an empty string. The second element is the schema name for the table. The third element is the name of the table. The fourth element is the type of the table. This may be an empty string.

  • options (iSqlOptions) –

iSql.toXML()

Note

Deprecated: Will be removed in the next major version.

Returns

string – - The generted sql XML.

iSqlOptions()

iSql Options

Arguments
  • error (string) – Determines action when an error is encountered. Valid options are on, off, or fast. Default is fast. Using on will cause the script execution to stop and log a full error report. Using off or fast continues executing the script. The Difference is that fast will log a brief error report and off will not.

commitOptions()

commit options

Arguments
  • action (string) – The commit action either commit or rollback. Default is commit.

  • error (string) – Determines action when an error is encountered. Valid options are on, off, or fast. Default is fast. Using on will cause the script execution to stop and log a full error report. Using off or fast continues executing the script. The Difference is that fast will log a brief error report and off will not.

describeOptions()

describe options

Arguments
  • desc (string) – The desc option can be col, parm, or both. Default is both.

  • error (string) – Determines action when an error is encountered. Valid options are on, off, or fast. Default is fast. Using on will cause the script execution to stop and log a full error report. Using off or fast continues executing the script. The Difference is that fast will log a brief error report and off will not.

countOptions()

count options

Arguments
  • desc (string) – The desc can be col, parm, or both. Default is both.

  • error (string) – Determines action when an error is encountered. Valid options are on, off, or fast. Default is fast. Using on will cause the script execution to stop and log a full error report. Using off or fast continues executing the script. The Difference is that fast will log a brief error report and off will not.

executeParams()

execute parameters

Arguments
  • subArray (Array.<string>) – Each sub element is [value, io] of the parameter.

fetchOptions()

fetch options

Arguments
  • block (string) – The block option can be all or number of rows to fetch n. Default is ‘all’.

  • desc (string) – The desc option can be ‘on’ or ‘off’. Default is on.

  • error (string) – Determines action when an error is encountered. Valid options are on, off, or fast. Default is fast. Using on will cause the script execution to stop and log a full error report. Using off or fast continues executing the script. The Difference is that fast will log a brief error report and off will not.

Examples

Run a query

const { Connection, iSql } = require('itoolkit');
const { parseString } = require('xml2js');

const connection = new Connection({
  transport: 'ssh',
  transportOptions: { host: 'myhost', username: 'myuser', password: 'mypassword' },
});

const sql = new iSql();

sql.addQuery('SELECT LSTNAM, STATE FROM QIWS.QCUSTCDT');
sql.fetch();
sql.free();

connection.add(sql);

connection.run((error, xmlOutput) => {
  if (error) {
    throw error;
  }
  parseString(xmlOutput, (parseError, result) => {
    if (parseError) {
      throw parseError;
    }
    console.log(JSON.stringify(result));
  });
});

Call a procedure

const { Connection, iSql } = require('itoolkit');
const { parseString } = require('xml2js');

const connection = new Connection({
  transport: 'ssh',
  transportOptions: { host: 'myhost', username: 'myuser', password: 'mypassword' },
});

const sql = new iSql();

sql.prepare('call qsys2.tcpip_info()');
sql.execute();
sql.fetch();
sql.free();

connection.add(sql);

connection.run((error, xmlOutput) => {
  if (error) {
    throw error;
  }
  parseString(xmlOutput, (parseError, result) => {
    if (parseError) {
      throw parseError;
    }
    console.log(JSON.stringify(result));
  });
});