Zum Hauptinhalt springen

SelectBuilder

Class for building ANSI-SQL Query.

context.enableModules();
const { SelectBuilder } = require("ou.sp.SelectBuilder");

try {

    const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
    const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
    const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);

    const rows = SelectBuilder
        .from("costcenter", db)
        .select("costcenterName", "string")
        .select("id", "number")
        .execute();

    context.returnValue = JSON.stringify(rows);
} catch (error) {
    context.errorMessage = error.message;
    context.returnValue = -1;
} finally {
    db.close();
}

Type Parameters​

SelectedColumns​

SelectedColumns = never

SelectedColumnNames​

SelectedColumnNames = never

KnownTableName​

KnownTableName extends keyof KnownTables = never

Implements​

Constructors​

Constructor​

new SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>(): SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

Returns​

SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

Methods​

select()​

Call Signature​

select<ColumnName, ColumnType>(column): SelectBuilder<SelectedColumns | { name: ColumnName; type: ColumnType; }, SelectedColumnNames | ColumnName, KnownTableName>

Adds a single column to the current select.

const query = new SelectBuilder
query.select({
    name: "column1",
    type: "string",
});
Type Parameters​
ColumnName​

ColumnName extends string | number | symbol

ColumnType​

ColumnType extends any

Parameters​
column​

SelectColumnDefinition<Extract<ColumnName, string>, never, ColumnType>

The column to select. Can be a single ColumnDefinition or an array of ColumnDefinitions

Returns​

SelectBuilder<SelectedColumns | { name: ColumnName; type: ColumnType; }, SelectedColumnNames | ColumnName, KnownTableName>

the current instance

Call Signature​

select<ColumnName, ColumnAlias, ColumnType>(column): SelectBuilder<SelectedColumns | { name: ColumnName; alias: ColumnAlias; type: ColumnType; }, SelectedColumnNames | ColumnName, KnownTableName>

Adds a single column to the current select.

const query = new SelectBuilder
query.select({
    name: "column1",
    type: "string",
});
Type Parameters​
ColumnName​

ColumnName extends string | number | symbol

ColumnAlias​

ColumnAlias extends string

ColumnType​

ColumnType extends any

Parameters​
column​

SelectColumnDefinition<Extract<ColumnName, string>, ColumnAlias, ColumnType>

The column to select. Can be a single ColumnDefinition or an array of ColumnDefinitions

Returns​

SelectBuilder<SelectedColumns | { name: ColumnName; alias: ColumnAlias; type: ColumnType; }, SelectedColumnNames | ColumnName, KnownTableName>

the current instance

Call Signature​

select<ColumnName, ColumnAlias, ColumnType, TArray>(columns): SelectBuilder<SelectedColumns | ArrayToUnion<TArray>, SelectedColumnNames | ArrayToUnion<TArray>["name"], KnownTableName>

Adds a range of columns to the current select.

const query = new SelectBuilder
query.select([{
         name: "column1",
         type: "string",
     },{
         name: "column2",
         type: "number",
     },
]);
Type Parameters​
ColumnName​

ColumnName extends string | number | symbol

ColumnAlias​

ColumnAlias extends string

ColumnType​

ColumnType extends any

TArray​

TArray extends SelectColumnDefinition<Extract<ColumnName, string>, ColumnAlias, ColumnType>[]

Parameters​
columns​

TArray

The columns name to select.

Returns​

SelectBuilder<SelectedColumns | ArrayToUnion<TArray>, SelectedColumnNames | ArrayToUnion<TArray>["name"], KnownTableName>

the current instance

Call Signature​

select<ColumnName, ColumnType>(column, type): SelectBuilder<SelectedColumns | SelectColumnDefinition<Extract<ColumnName, string>, never, ColumnType>, SelectedColumnNames | ColumnName, KnownTableName>

Adds a single column to the current select.

const query = new SelectBuilder
query.select("column1", "string");
Type Parameters​
ColumnName​

ColumnName extends string | number | symbol

ColumnType​

ColumnType extends any

Parameters​
column​

ColumnName

The column name to select.

type​

ColumnType

The data type

Returns​

SelectBuilder<SelectedColumns | SelectColumnDefinition<Extract<ColumnName, string>, never, ColumnType>, SelectedColumnNames | ColumnName, KnownTableName>

the current instance

from()​

Call Signature​

from(table, dbOrBundle): SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

Specifies the table and db connection to use.

tipp

Use the static SelectBuilder.from function.

Using the static function:

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const select = SelectBuilder.from("table", db);

Using the instance function:

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const instance = new SelectBuilder();
instance.from("table", db);
Parameters​
table​

keyof KnownTables

The table you want to select data

dbOrBundle​

DbWithProvider

Either a DBConnection instance or an object containing both the DBConnection and its DBProvider

Returns​

SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

the current instance

Call Signature​

from(table, dbOrBundle, provider?): SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

Parameters​
table​

keyof KnownTables

dbOrBundle​

DBConnection

provider?​

DBProvider

Returns​

SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

Deprecated​

please use the newer syntax with DbWithProvider

where()​

where(condition): SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

Adds a raw where condition.

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const select = SelectBuilder
    .from("customer", db)
    .where("firstName like 'chuck%'")
    .where("AND lastName like 'norris%'");

Parameters​

condition​

string

the raw where condition

Returns​

SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

the current instance

andWhere()​

andWhere(condition): SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

Adds a raw AND where condition.

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const output = SelectBuilder
    .from("customer", db)
    .andWhere("recipient = 'K10000'")
    .andWhere("costcenter like '%Einkauf%'")
    .toSQL();
// SELECT * FROM costcenter WHERE recipient = 'K10000' AND (costcenter like '%Einkauf%')

Parameters​

condition​

string

the raw where condition

Returns​

SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

the current instance

orWhere()​

orWhere(condition): SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

Adds a raw OR where condition.

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const output = SelectBuilder
    .from("customer", db)
    .orWhere("costcenter like '%Einkauf%'")
    .orWhere("costcenterName like '%Einkauf%'")
    .toSQL();
// SELECT * FROM costcenter WHERE costcenter like '%Einkauf%' OR (costcenterName like '%Einkauf%')

Parameters​

condition​

string

the raw where condition

Returns​

SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

the current instance

limit()​

limit(limitRows): SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

Indicate a maximum number of rows that are returned.

warnung

If you want to use the limit function, you must provide a DBProvider in from() or in the constructor (see example).

Example using MSSQL

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const output = SelectBuilder.from("customer", db, "mssql").limit(10).toSQL();
// SELECT TOP 10 * FROM customer

Example using MariaDB

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const output = SelectBuilder.from("customer", db, "mariadb").limit(10).toSQL();
// SELECT * FROM customer LIMIT 10

Parameters​

limitRows​

Maxmimal numbers of rows to fetch

number | bigint

Returns​

SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

the current instance

offset()​

offset(offsetRows): SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

Indicate a offset to start

warnung

If you want to use the offset function, you must provide a DBProvider in from() or in the constructor (see example).

Example using MSSQL

vorsicht

Watch out, offset must be followed by orderBy!

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const output = SelectBuilder.from("costcenter", db, "mssql").orderBy("id").offset(10).toSQL();
// SELECT * FROM costcenter ORDER BY id OFFSET 10 ROWS
const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const output = SelectBuilder.from("costcenter", db, "mssql").orderBy("id").limit(10).offset(10).toSQL();
// SELECT * FROM costcenter ORDER BY id OFFSET 10 ROWS FETCH FIRST 10 ROWS ONLY

Example using MariaDB

vorsicht

Watch out, offset can only be used with limit. If no limit was provided, the (nearly) largest number is provided as limit.

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const output = SelectBuilder.from("costcenter", db, "mariadb").offset(10).toSQL();
// SELECT * FROM costcenter LIMIT 9007199254740991 OFFSET 10
const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const output = SelectBuilder.from("costcenter", db, "mariadb").limit(10).offset(10).toSQL();
// SELECT * FROM costcenter LIMIT 10 OFFSET 10

Parameters​

offsetRows​

number

Maxmimal numbers of rows to fetch

Returns​

SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

the current instance

distinct()​

distinct(): SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

Adds the DISTINCT flag to the query.

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const output = SelectBuilder.from("costcenter", db).distinct().toSQL();
// SELECT DISTINTCT * FROM costcenter

Returns​

SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

the current instance

aggregate()​

Call Signature​

aggregate<TColumnName>(command, column): SelectBuilder<SelectedColumns | { name: TColumnName; type: "number"; }, SelectedColumnNames | TColumnName, KnownTableName>

Add a special command for a column like MAX, MIN, SUM, AVG.

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const select = SelectBuilder.from("customer", db).aggregate("SUM", "column1");
Type Parameters​
TColumnName​

TColumnName extends string | number | symbol

Parameters​
command​

AggregateCommands

SQL commands like MAX, MIN, SUM, AVG

column​

TColumnName

The column name to aggregate

Returns​

SelectBuilder<SelectedColumns | { name: TColumnName; type: "number"; }, SelectedColumnNames | TColumnName, KnownTableName>

the current instance

Call Signature​

aggregate<TColumnName, TColumnAlias>(command, column, alias): SelectBuilder<SelectedColumns | { name: TColumnName; alias: TColumnAlias; type: "number"; }, SelectedColumnNames | TColumnAlias, KnownTableName>

Add a special command for a column like MAX, MIN, SUM, AVG.

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const select = SelectBuilder.from("customer", db).aggregate("SUM", "column1", "alias");
Type Parameters​
TColumnName​

TColumnName extends string | number | symbol

TColumnAlias​

TColumnAlias extends string

Parameters​
command​

AggregateCommands

SQL commands like MAX, MIN, SUM, AVG

column​

TColumnName

The column name to aggregate

alias​

TColumnAlias

An alias for the column name

Returns​

SelectBuilder<SelectedColumns | { name: TColumnName; alias: TColumnAlias; type: "number"; }, SelectedColumnNames | TColumnAlias, KnownTableName>

the current instance

count()​

Call Signature​

count<TColumnName>(column): SelectBuilder<SelectedColumns | { name: "count"; type: "number"; }, SelectedColumnNames | TColumnName, KnownTableName>

Adds a COUNT aggregate to the select.

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const select = SelectBuilder.from("customer", db).count("column1");
Type Parameters​
TColumnName​

TColumnName extends string | number | symbol

Parameters​
column​

TColumnName

The column name to aggregate

Returns​

SelectBuilder<SelectedColumns | { name: "count"; type: "number"; }, SelectedColumnNames | TColumnName, KnownTableName>

the current instance

Call Signature​

count<TColumnName, TColumnAlias>(column, alias): SelectBuilder<SelectedColumns | { name: TColumnAlias; type: "number"; }, SelectedColumnNames | TColumnAlias, KnownTableName>

Adds a COUNT aggregate to the select.

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const select = SelectBuilder.from("customer", db).count("column1", "alias");
Type Parameters​
TColumnName​

TColumnName extends string | number | symbol

TColumnAlias​

TColumnAlias extends string

Parameters​
column​

TColumnName

The column name to aggregate

alias​

TColumnAlias

An alias for column name

Returns​

SelectBuilder<SelectedColumns | { name: TColumnAlias; type: "number"; }, SelectedColumnNames | TColumnAlias, KnownTableName>

the current instance

join()​

Call Signature​

join(condition): SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

Adds a join condition to the query.

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const select = SelectBuilder.from("customer", db).join("INNER JOIN address ON customer.customerId = address.customerId");
Parameters​
condition​

string

the join condition like "INNER JOIN address ON customer.customerId = address.customerId"

Returns​

SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

the current instance

Call Signature​

join<TKnownTableName>(joinType, table, condition): SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName | TKnownTableName>

Adds a join condition to the query.

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const select = SelectBuilder.from("customer", db).join("INNER", "address", "customer.customerId = address.customerId");
Type Parameters​
TKnownTableName​

TKnownTableName extends keyof KnownTables

Parameters​
joinType​

INNER, LEFT, RIGHT, FULL

"INNER" | "LEFT" | "RIGHT" | "FULL"

table​

TKnownTableName

KnownTableName or the table name

condition​

string

the join condition like "ON customer.customerId = address.customerId"

Returns​

SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName | TKnownTableName>

orderBy()​

orderBy<TColumnName>(column, ascending?): SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

Adds a order by condition to the query

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const select = SelectBuilder.from("customer", db).orderBy("created", false);

Type Parameters​

TColumnName​

TColumnName

Parameters​

column​

TColumnName

the column you want to sort

ascending?​

boolean

true if sort ascending otherwise false (default true)

Returns​

SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

the current instance

groupBy()​

groupBy<TColumnName>(columns): SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

Adds a group by condition to the query

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const select = SelectBuilder
     .from("customer", db)
     .select({
              name: "column1",
              type: "string",
     })
     .groupBy("column1");
const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const select = SelectBuilder
    .from("customer", db)
    .select([{
             name: "column1",
             type: "string",
         },{
             name: "column2",
             type: "number",
         },
    ])
    .groupBy(["column1", "column2"]);

Type Parameters​

TColumnName​

TColumnName

Parameters​

columns​

the column or columns you want to group by

TColumnName | TColumnName[]

Returns​

SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

the current instance

having()​

having(condition): SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

Adds a having condition to the query. The column should wrapped with brackets ([column] not column).

warnung

Attention, you must provide a group by condition

Parameters​

condition​

string

The having condition statement like "count(column1) > 10"

Returns​

SelectBuilder<SelectedColumns, SelectedColumnNames, KnownTableName>

the current instance

See​

groupBy!

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const select = SelectBuilder
     .from("customer", db)
     .select({
              name: "column1",
              type: "string",
     })
     .groupBy("column1")
     .having("COUNT(column1) > 0");

toSQL()​

toSQL(): string

Returns the sql string

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const output = SelectBuilder.from("table", db).toSQL();
// SELECT * FROM table

Returns​

string

Returns the generated SQL string

Implementation of​

StatementBuilder.toSQL

toString()​

toString(): string

Returns the sql string

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const output = SelectBuilder.from("table", db).toString();
// SELECT * FROM table

Returns​

string

Returns the generated SQL string

Implementation of​

StatementBuilder.toString

toDBResultSet()​

toDBResultSet(): DBResultSet

Executes the current statement (executeQuery) and returns a DBResultSet.

Returns​

DBResultSet

a DBResultSet

use()​

Call Signature​

use(dbOrBundle): this

Use a active DB connection. You can either pass a database connection together with its provider as a single object (recommended), or pass the connection and provider as separate parameters

Recommended usage:

select.use({ db, provider });

Alternative usage (legacy):

select.use(db, provider);
Parameters​
dbOrBundle​

DbWithProvider

Either a DBConnection instance or an object containing both the DBConnection and its DBProvider

Returns​

this

the current instance

Call Signature​

use(dbOrBundle, provider?): this

Parameters​
dbOrBundle​

DBConnection

provider?​

DBProvider

Returns​

this

Deprecated​

please use the newer syntax with DbWithProvider

execute()​

execute<TRow>(callback?): TRow[]

Executes the current query.

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const rows = SelectBuilder.from("costcenter", db).execute();

Type Parameters​

TRow​

TRow extends unknown

Parameters​

callback?​

QueryIteratorCallback<TRow>

Returns​

TRow[]

Returns the mapped rows

Throws​

Throws error when DBConnection.getLastError() is filled with information.

getColumns()​

getColumns(): SelectColumnDefinition<string, string, DBResultType>[]

Returns the selected columns of this instance.

Returns​

SelectColumnDefinition<string, string, DBResultType>[]

the selected columns

replace()​

replace<ColumnName>(columnName, value, replacementValue?): this

Replaces each occurrence of a value in a specific column with a new value.

(...)
const orderItems = SelectBuilder
    .from("orderItem", db)
    .select("orderNumber", "string")
    .select("itemNumber", "string")
    .select("quantity", "number")
    .select("articleName", "string")
    .replace("quantity", null, 0)
    .replace("articleName", null, "")
    .execute();

Note that the replacing happens after the value has been transformed and mapped, meaning that if a column has an alias, the alias needs to be used as the column name in the replace method:

(...)
const vendors = SelectBuilder
    .from("vendor", db)
    .select("vendorId", "string")
    .select({ name: "city", alias: "location", type: "string" })
    .replace("location", null, "")
    .execute();

If the replacement parameter is omitted and the search value is null, null values will be replaced with the default value depending on the selected type:

  • string: "" (empty string)
  • number/float: 0
  • boolean: false
  • date/timestamp: new Date(0)

Type Parameters​

ColumnName​

ColumnName extends string | number | symbol

Parameters​

columnName​

ColumnName

The column name to replace values

value​

ReplaceableColumns<KnownTableName, SelectedColumns>[ColumnName]

The value to search for

replacementValue?​

unknown

The replacement value

Returns​

this

replaceEvery()​

replaceEvery(value, replacementValue?): this

Replaces each occurrence of a value in all columns with a new value.

(...)
const orderItems = SelectBuilder
    .from("orderItem", db)
    .select("orderNumber", "string")
    .select("itemNumber", "string")
    .select("quantity", "number")
    .select("articleName", "string")
    .replaceEvery(null, 0)
    .execute();

If the replacement parameter is omitted and the search value is null, null values will be replaced with the default value depending on the selected type:

  • string: "" (empty string)
  • number/float: 0
  • boolean: false
  • date/timestamp: new Date(0)

Parameters​

value​

unknown

The value to search for

replacementValue?​

unknown

The replacement value

Returns​

this

replaceEveryNull()​

replaceEveryNull(replacementValue?): this

Replaces each occurrence of null in all columns with a new value.

(...)
const orderItems = SelectBuilder
    .from("orderItem", db)
    .select("orderNumber", "string")
    .select("itemNumber", "string")
    .select("quantity", "number")
    .select("articleName", "string")
    .replaceEveryNull()
    .execute();

If the replacement parameter is omitted, null values will be replaced with the default value depending on the selected type:

  • string: "" (empty string)
  • number/float: 0
  • boolean: false
  • date/timestamp: new Date(0)

Parameters​

replacementValue?​

unknown

The replacement value

Returns​

this

from()​

Call Signature​

static from<TKnownTableName>(table, dbOrBundle): SelectBuilder<never, never, TKnownTableName>

Creates a new SelectBuilder instance by using the specific table and db connection.

You can either pass a database connection together with its provider as a single object (recommended), or pass the connection and provider as separate parameters

Recommended usage:

const { db, provider } = getDatabaseConnectionAndProvider("ousp");
const select = SelectBuilder.from("table", { db, provider });

Alternative usage (legacy):

const dbUser = util.getEnvironment("OUSP_DATABASE_USER");
const dbPassword = util.getEnvironment("OUSP_DATABASE_PASSWORD");
const db = new DBConnection("odbc", "ousp", dbUser, dbPassword);
const select = SelectBuilder.from("table", db, "mssql");
Type Parameters​
TKnownTableName​

TKnownTableName extends keyof KnownTables

Parameters​
table​

TKnownTableName

The table you want to select data

dbOrBundle​

DbWithProvider

Either a DBConnection instance or an object containing both the DBConnection and its DBProvider

Returns​

SelectBuilder<never, never, TKnownTableName>

a new instance of SelectBuilder class

Call Signature​

static from<TKnownTableName>(table, dbOrBundle, provider?): SelectBuilder<never, never, TKnownTableName>

Type Parameters​
TKnownTableName​

TKnownTableName extends keyof KnownTables

Parameters​
table​

TKnownTableName

dbOrBundle​

DBConnection

provider?​

DBProvider

Returns​

SelectBuilder<never, never, TKnownTableName>

Deprecated​

please use the newer syntax with DbWithProvider