ECSQL Code Examples

The code examples do not explicitly show examples using the ECDb class. However, the code examples work likewise for ECDb. Just replace IModelDb.withPreparedStatement in the code examples with ECDb.withPreparedStatement.

Also see frequently used ECSQL queries for the specific ECSQL queries that app backends and services often run.

Parameter Bindings

Binding per parameter

Positional parameters

iModel.withPreparedStatement("SELECT ECInstanceId,ECClassId,Parent,LastMod FROM bis.Element WHERE CodeValue=? AND LastMod>=?", (stmt: ECSqlStatement) => {
  stmt.bindString(1, "MyCode");
  stmt.bindDateTime(2, "2018-01-01T12:00:00");

  while (stmt.step() === DbResult.BE_SQLITE_ROW) {
    // do something with the query result
  }
});

Named parameters

iModel.withPreparedStatement("SELECT ECInstanceId,ECClassId,Parent,LastMod FROM bis.Element WHERE CodeValue=:code AND LastMod>=:lastmod", (stmt: ECSqlStatement) => {
  stmt.bindString("code", "MyCode");
  stmt.bindDateTime("lastmod", "2018-01-01T12:00:00Z");

  while (stmt.step() === DbResult.BE_SQLITE_ROW) {
    // do something with the query result
  }
});

Binding to all parameters at once

See section "ECSQL parameter types in iTwin.js" to learn which types to use for the parameters when binding all parameters at once.

Positional parameters

iModel.withPreparedStatement("SELECT ECInstanceId,ECClassId,Parent,LastMod FROM bis.Element WHERE CodeValue=? AND LastMod>=?",
  (stmt: ECSqlStatement) => {
    stmt.bindValues(["MyCode", "2018-01-01T12:00:00Z"]);

    while (stmt.step() === DbResult.BE_SQLITE_ROW) {
      // do something with the query result
    }
  });

Named parameters

iModel.withPreparedStatement("SELECT ECInstanceId,ECClassId,Parent,LastMod FROM bis.Element WHERE CodeValue=:code AND LastMod>=:lastmod",
  (stmt: ECSqlStatement) => {
    stmt.bindValues({ code: "MyCode", lastmod: "2018-01-01T12:00:00Z" });

    while (stmt.step() === DbResult.BE_SQLITE_ROW) {
      // do something with the query result
    }
  });

Navigation properties

Navigation properties are structs made up of the Id of the related instance and the backing ECRelationshipClass. The NavigationBindingValue interface is used to bind values to navigation property parameters.

iModel.withPreparedStatement("SELECT ECInstanceId FROM bis.Element WHERE Parent=?", (stmt: ECSqlStatement) => {
  stmt.bindNavigation(1, { id: "0x132" });
  // ...
});

iModel.withPreparedStatement("SELECT ECInstanceId FROM bis.Element WHERE Parent=?", (stmt: ECSqlStatement) => {
  stmt.bindValues([{ id: "0x132" }]);
  // ...
});

Because of the struct nature of navigation properties, you can also use its members in the ECSQL. The two following examples illustrate this by specifying the Id member of a navigation property.

iModel.withPreparedStatement("SELECT ECInstanceId FROM bis.Element WHERE Parent.Id=?", (stmt: ECSqlStatement) => {
  stmt.bindId(1, "0x132");
  // ...
});

iModel.withPreparedStatement("SELECT ECInstanceId FROM bis.Element WHERE Parent.Id=?", (stmt: ECSqlStatement) => {
  stmt.bindValues(["0x132"]);
  // ...
});

Struct properties

You can either parameterize a struct property as a whole or parameterize individual members of the struct. See "Struct properties in ECSQL" for the ECSQL background.

The ECSQL examples used in this section refer to the sample ECSchema in "Struct properties in ECSQL".

Binding structs as a whole

iModel.withPreparedStatement("SELECT Name FROM myschema.Company WHERE Location=?", (stmt: ECSqlStatement) => {
  stmt.bindStruct(1, { street: "7123 Main Street", zip: 30211 });
  // ...
});

iModel.withPreparedStatement("SELECT Name FROM myschema.Company WHERE Location=?", (stmt: ECSqlStatement) => {
  stmt.bindValues([{ street: "7123 Main Street", zip: 30211 }]);
  // ...
});

Binding to individual struct members

iModel.withPreparedStatement("SELECT Name FROM myschema.Company WHERE Location.Street=? AND Location.Zip=?", (stmt: ECSqlStatement) => {
  stmt.bindString(1, "7123 Main Street");
  stmt.bindInteger(2, 32443);
  // ...
});

iModel.withPreparedStatement("SELECT Name FROM myschema.Company WHERE Location.Street=? AND Location.Zip=?", (stmt: ECSqlStatement) => {
  stmt.bindValues(["7123 Main Street", 32443]);
  // ...
});

The two ECSQL examples used in this section amount to the same results.

Array properties

See "Array properties in ECSQL" for the ECSQL background.

The ECSQL examples used in this section refer to the sample ECSchema in "Array properties in ECSQL".

iModel.withPreparedStatement("SELECT Name FROM myschema.Company WHERE PhoneNumbers=?", (stmt: ECSqlStatement) => {
  stmt.bindArray(1, ["+16134584201", "+16134584202", "+16134584222"]);
  // ...
});

iModel.withPreparedStatement("SELECT Name FROM myschema.Company WHERE PhoneNumbers=?", (stmt: ECSqlStatement) => {
  stmt.bindValues([["+16134584201", "+16134584202", "+16134584222"]]);
  // ...
});

Working with the query result

The current row of the query result can be retrieved in two ways:

• as a whole as JavaScript literal (adhering to the ECSQL row format)
• column by column (using the ECSqlValue API as returned from ECSqlStatement.getValue)

The column by column approach is more low-level, but gives you more flexible access to the data in the row. For example, ECClassIds are turned into class names in the ECSQL row format. Using the ECSqlValue API allows you to retrieve ECClassIds as Id64s.

Rows as a whole

The following example is intended to illustrate the ECSQL row format:

iModel.withPreparedStatement("SELECT ECInstanceId,ECClassId,Parent,LastMod FROM bis.Element WHERE Model.Id=?", (stmt: ECSqlStatement) => {

  stmt.bindId(1, "0x113");

  while (stmt.step() === DbResult.BE_SQLITE_ROW) {
    const row: any = stmt.getRow();
    console.log(JSON.stringify(row));
  }
});

Output

{id: "0x312", className: "StructuralPhysical.Slab", parent: {id: "0x433", relClassName: "BisCore.PhysicalElementAssemblesElements"}, lastMod: "2018-02-03T13:43:22Z"}

{id: "0x313", className: "StructuralPhysical.Slab", parent: {id: "0x5873", relClassName: "BisCore.PhysicalElementAssemblesElements"}, lastMod: "2017-11-24T08:21:01Z"}

...

Note how the ECProperties used in the ECSQL are converted to members of the JavaScript literal and how their names are transformed according to the rules described in the ECSQL row format.

The following example illustrates how to work with the ECSQL row JavaScript literal:

iModel.withPreparedStatement("SELECT ECInstanceId,ECClassId,Parent,LastMod FROM bis.Element WHERE Model.Id=?", (stmt: ECSqlStatement) => {

  stmt.bindId(1, "0x113");

  console.log("ECInstanceId | ClassName | Parent Id | Parent RelClassName | LastMod");

  while (stmt.step() === DbResult.BE_SQLITE_ROW) {
    const row: any = stmt.getRow();
    const id: Id64String = row.id;
    const className: string = row.className;
    const parent: NavigationValue = row.parent;
    const lastMod: string = row.lastMod;

    console.log(`${id}|${className}|${parent.id}|${parent.relClassName}|${lastMod}`);
  }
});

Output

Column by column

iModel.withPreparedStatement("SELECT ECInstanceId,ECClassId,Parent,LastMod FROM bis.Element WHERE Model.Id=?", (stmt: ECSqlStatement) => {
  stmt.bindId(1, "0x113");

  console.log("ECInstanceId | ClassName | Parent Id | Parent RelClassName | LastMod");

  while (stmt.step() === DbResult.BE_SQLITE_ROW) {
    const idValue: ECSqlValue = stmt.getValue(0);
    const classIdValue: ECSqlValue = stmt.getValue(1);
    const parentValue: ECSqlValue = stmt.getValue(2);
    const lastModValue: ECSqlValue = stmt.getValue(3);

    const id: Id64String = idValue.getId();
    const className: string = classIdValue.getClassNameForClassId();
    const parent: NavigationValue = parentValue.getNavigation();
    const lastMod: string = lastModValue.getDateTime();

    console.log(`${id}|${className}|${parent.id}|${parent.relClassName}|${lastMod}`);
  }
});

Output

The sample is code is intentionally verbose to better illustrate the semantics of the API.

The following example illustrates the flexibility of the column by column approach by preserving the ECClassId as id instead of having it converted to a class name.

iModel.withPreparedStatement("SELECT ECClassId,Parent.RelECClassId FROM bis.Element WHERE Model.Id=?", (stmt: ECSqlStatement) => {
  stmt.bindId(1, "0x113");

  console.log("ECClassId | Parent RelECClassId");
  while (stmt.step() === DbResult.BE_SQLITE_ROW) {
    const classIdValue: ECSqlValue = stmt.getValue(0);
    const parentRelClassIdValue: ECSqlValue = stmt.getValue(1);

    const classId: string = classIdValue.getId();
    const parentRelClassId: string = parentRelClassIdValue.getId();

    console.log(`${classId}|${parentRelClassId}`);
  }
});

Output

Last Updated: 30 November, 2023