select()

Sends a SQL statement to the server via ado.ExecuteSQL and returns the parsed ADO Recordset XML as an ECMDbResult. Positional parameters are safely bound into the query before it is sent — never use string formatting for user input.

Only SELECT queries are supported without additional server configuration. Data manipulation statements (INSERT, UPDATE, DELETE) must be explicitly enabled via a registry setting on the application server.

Leading whitespace in the statement (e.g. a newline from a multi-line string literal) is stripped before the command is sent. ado.ExecuteSQL otherwise returns an empty result set without an error when the command does not start with the statement keyword, which previously surfaced as an opaque IndexError. A trailing newline is harmless.

1. Signature

  • Sync

  • Async

ecm.db.select(sql: str, *params: int | str | float, flags: int = 0) -> ECMDbResult
await ecm.db.select(sql: str, *params: int | str | float, flags: int = 0) -> ECMDbResult

2. Parameters

Parameter Type Default Description

sql

str

—

SQL template string with optional placeholders.

*params

int | str | float

—

Values substituted into the placeholders in order. The number of values must match the number of non-%% placeholders.

flags

int

0

Bitmask passed as Flags to ado.ExecuteSQL. 0 = automatic N-prefix for string literals on Unicode servers (default). 2 = disable automatic N-prefixing.

3. Placeholders

Placeholder Type Behaviour

%s

String

Single quotes are escaped (' → ''); value is enclosed in '…​'.

%w

Word string

Like %s, but additionally rejects whitespace and SQL comment markers (--, /, /).

%d

Integer

Value is cast to int; non-numeric values raise ValueError.

%f

Float

Value is cast to float; non-numeric values raise ValueError.

%u

Unquoted identifier

Only letters, digits, _, @, $, # are allowed. Value is inserted without quotes. Use for table or column names.

%%

Literal %

No parameter is consumed.

4. Return value

An ECMDbResult instance containing columns and rows.

4.1. ECMDbResult

Attribute / Method Type Description

.columns

list[ECMDbColumn]

Ordered list of column definitions, sorted by 1-based column index.

.rows

list[ECMDbRow]

List of result rows, one per <z:row> element.

len(result)

int

Number of rows.

for row in result

—

Iterates over .rows directly.

4.2. ECMDbColumn fields

Field Type Description

name

str

Column name as returned by the server.

number

int

1-based column index.

data_type

str

ADO data type string, e.g. "string", "int", "dateTime".

max_length

int | None

Maximum byte length declared in the schema, or None if absent.

nullable

bool

Whether the column allows NULL values.

base_table

str | None

Source table name, or None if not provided by the schema.

base_column

str | None

Source column name, or None if not provided by the schema.

4.3. ECMDbRow access

Method / Syntax Return type Description

row["column_name"]

str | None

Raw string value, or None for NULL.

row.get("column_name")

str | None

Same as getitem — raw string or None for missing / NULL.

row.typed("column_name", str)

str | None

Value coerced to the given type. Supported: str, int, float, bool, datetime.

NULL values are always returned as None, regardless of access method.

5. Examples

5.1. Simple query

  • Sync

  • Async

result = ecm.db.select("SELECT benutzer, osemail FROM benutzer")
print(f"{len(result)} rows")
for row in result:
    print(row["benutzer"], row["osemail"])
result = await ecm.db.select("SELECT benutzer, osemail FROM benutzer")
print(f"{len(result)} rows")
for row in result:
    print(row["benutzer"], row["osemail"])

5.2. Query with parameters

  • Sync

  • Async

result = ecm.db.select(
    "SELECT id, benutzer FROM benutzer WHERE benutzer = %s AND aktiv = %d",
    "john",
    1,
)
for row in result:
    print(row["id"], row["benutzer"])
result = await ecm.db.select(
    "SELECT id, benutzer FROM benutzer WHERE benutzer = %s AND aktiv = %d",
    "john",
    1,
)
for row in result:
    print(row["id"], row["benutzer"])

5.3. Typed value access

  • Sync

  • Async

result = ecm.db.select("SELECT benutzer, aktiv, logincount FROM benutzer")
for row in result:
    username: str | None = row.typed("benutzer", str)
    active: bool | None = row.typed("aktiv", bool)
    login_count: int | None = row.typed("logincount", int)
    print(username, active, login_count)
result = await ecm.db.select("SELECT benutzer, aktiv, logincount FROM benutzer")
for row in result:
    username: str | None = row.typed("benutzer", str)
    active: bool | None = row.typed("aktiv", bool)
    login_count: int | None = row.typed("logincount", int)
    print(username, active, login_count)

5.4. Dynamic table name

  • Sync

  • Async

# %u inserts an unquoted identifier — only safe characters allowed
table = "benutzer"
result = ecm.db.select("SELECT * FROM %u WHERE id = %d", table, 42)
table = "benutzer"
result = await ecm.db.select("SELECT * FROM %u WHERE id = %d", table, 42)

5.5. Inspect column metadata

  • Sync

  • Async

result = ecm.db.select("SELECT * FROM benutzer WHERE 1 = 0")
for col in result.columns:
    print(col.name, col.data_type, col.max_length, col.nullable)
result = await ecm.db.select("SELECT * FROM benutzer WHERE 1 = 0")
for col in result.columns:
    print(col.name, col.data_type, col.max_length, col.nullable)

6. See also

  • ecm.db — Namespace overview with SQL injection protection notes