Parameters for Odbc.DataSource
The Odbc.DataSource function takes two parameters—a connectionString for your driver, and an options record that lets you override various driver behaviors. Through the options record you can override capabilities and other information reported by the driver, control the navigator behavior, and affect the SQL queries generated by the M engine.
The supported options records fields fall into two categories—those that are public and always available, and those that are only available in an extensibility context.
The following table describes the public fields in the options record.
| Field | Description |
|---|---|
CommandTimeout |
A duration value that controls how long the server-side query is allowed to run before it's canceled. Default: 10 minutes |
ConnectionTimeout |
A duration value that controls how long to wait before abandoning an attempt to make a connection to the server. Default: 15 seconds |
CreateNavigationProperties |
A logical value that sets whether to generate navigation properties on the returned tables. Navigation properties are based on foreign key relationships reported by the driver. These properties show up as “virtual” columns that can be expanded in the query editor, creating the appropriate join. If calculating foreign key dependencies is an expensive operation for your driver, you may want to set this value to false. Default: true |
HierarchicalNavigation |
A logical value that sets whether to view the tables grouped by their schema names. When set to false, tables are displayed in a flat list under each database. Default: false |
SqlCompatibleWindowsAuth |
A logical value that determines whether to produce a SQL Server compatible connection string when using Windows Authentication—Trusted_Connection=Yes.If your driver supports Windows Authentication, but requires extra or alternative settings in your connection string, you should set this value to false and use the CredentialConnectionString options record field described in the next table.Default: true |
The following table describes the options record fields that are only available through extensibility. Fields that aren't simple literal values are described in later sections.
| Field | Description |
|---|---|
AstVisitor |
A record containing one or more overrides to control SQL query generation. The most common usage of this field is to provide logic to generate a LIMIT/OFFSET clause for drivers that don't support TOP. Fields include Constant and LimitClause.More information: Overriding AstVisitor |
CancelQueryExplicitly |
A logical value that instructs the M engine to explicitly cancel any running calls through the ODBC driver before terminating the connection to the ODBC server. This field is useful in situations where query execution is managed independently of the network connections to the server, for example in some Spark deployments. In most cases, this value doesn't need to be set because the query in the server is canceled when the network connection to the server is terminated. Default: false |
ClientConnectionPooling |
A logical value that enables client-side connection pooling for the ODBC driver. Most drivers will want to set this value to true. Default: false |
CredentialConnectionString |
A text or record value used to specify credential-related connection string properties. |
HideNativeQuery |
A logical value that controls whether or not the connector shows generated SQL statements in the Power Query user experience. This should only be set to true if the back end data source natively supports SQL-92. Default: false |
ImplicitTypeConversions |
A table value containing implicit type conversions supported by your driver or backend server. Values in this table are additive to the conversions reported by the driver itself. This field is typically used with the SQLGetTypeInfo field when overriding data type information reported by the driver. |
OnError |
An error handling function that receives an errorRecord parameter of type record.Common uses of this function include handling SSL connection failures, providing a download link if your driver isn't found on the system, and reporting authentication errors. |
SoftNumbers |
Allows the M engine to select a compatible data type when conversion between two specific numeric types isn't declared as supported in the SQL_CONVERT_* capabilities. Default: false |
SqlCapabilities |
A record providing various overrides of driver capabilities, and a way to specify capabilities that aren't expressed through ODBC 3.8. More information: Overriding SqlCapabilities |
SQLColumns |
A function that allows you to modify column metadata returned by the SQLColumns function.More information: Overriding SQLColumns |
SQLGetFunctions |
A record that allows you to override values returned by calls to SQLGetFunctions.A common use of this field is to disable the use of parameter binding, or to specify that generated queries should use CAST rather than CONVERT. More information: Overriding SQLGetFunctions |
SQLGetInfo |
A record that allows you to override values returned by calls to SQLGetInfo.More information: Overriding SQLGetInfo |
SQLGetTypeInfo |
A table or function that returns a table that overrides the type information returned by SQLGetTypeInfo.When the value is set to a table, the value completely replaces the type information reported by the driver. SQLGetTypeInfo won't be called.When the value is set to a function, your function will receive the result of the original call to SQLGetTypeInfo, allowing you to modify the table.This field is typically used when there's a mismatch between data types reported by SQLGetTypeInfo and SQLColumns.More information: Overriding SQLGetTypeInfo |
SQLTables |
A function that allows you to modify the table metadata returned by a call to SQLTables. |
TolerateConcatOverflow |
Allows concatenation of text values to occur even if the result might be truncated to fit within the range of an available type. For example, when concatenating a VARCHAR(4000) field with a VARCHAR(4000) field on a system that supports a maximize VARCHAR size of 4000 and no CLOB type, the concatenation is folded even though the result might get truncated. Default: false |
UseEmbeddedDriver |
(internal use): A logical value that controls whether the ODBC driver should be loaded from a local directory (using new functionality defined in the ODBC 4.0 specification). This value is generally only set by connectors created by Microsoft that ship with Power Query. When set to false, the system ODBC driver manager is used to locate and load the driver. Most connectors shouldn't need to set this field. Default: false |
Overriding AstVisitor
The AstVisitor field is set through the Odbc.DataSource options record. It's used to modify SQL statements generated for specific query scenarios.
Note
Drivers that support LIMIT and OFFSET clauses (rather than TOP) will want to provide a LimitClause override for AstVisitor.
Constant
Providing an override for this value has been deprecated and may be removed from future implementations.
LimitClause
This field is a function that receives two Int64.Type arguments (skip, take), and returns a record with two text fields (Text, Location).
LimitClause = (skip as nullable number, take as number) as record => ...
The skip parameter is the number of rows to skip (that is, the argument to OFFSET). If an offset isn't specified, the skip value will be null. If your driver supports LIMIT, but doesn't support OFFSET, the LimitClause function should return an unimplemented error (...) when skip is greater than 0.
The take parameter is the number of rows to take (that is, the argument to LIMIT).
The Text field of the result contains the SQL text to add to the generated query.
The Location field specifies where to insert the clause. The following table describes supported values.
| Value | Description | Example |
|---|---|---|
AfterQuerySpecification |
LIMIT clause is put at the end of the generated SQL. This is the most commonly supported LIMIT syntax. |
SELECT a, b, c FROM table WHERE a > 10 LIMIT 5 |
BeforeQuerySpecification |
LIMIT clause is put before the generated SQL statement. | LIMIT 5 ROWS SELECT a, b, c FROM table WHERE a > 10 |
AfterSelect |
LIMIT goes after the SELECT statement, and after any modifiers (such as DISTINCT). | SELECT DISTINCT LIMIT 5 a, b, c FROM table WHERE a > 10 |
AfterSelectBeforeModifiers |
LIMIT goes after the SELECT statement, but before any modifiers (such as DISTINCT). | SELECT LIMIT 5 DISTINCT a, b, c FROM table WHERE a > 10 |
The following code snippet provides a LimitClause implementation for a driver that expects a LIMIT clause, with an optional OFFSET, in the following format: [OFFSET <offset> ROWS] LIMIT <row_count>
LimitClause = (skip, take) =>
let
offset = if (skip > 0) then Text.Format("OFFSET #{0} ROWS", {skip}) else "",
limit = if (take <> null) then Text.Format("LIMIT #{0}", {take}) else ""
in
[
Text = Text.Format("#{0} #{1}", {offset, limit}),
Location = "AfterQuerySpecification"
]
The following code snippet provides a LimitClause implementation for a driver that supports LIMIT, but not OFFSET. Format: LIMIT <row_count>.
LimitClause = (skip, take) =>
if (skip > 0) then error "Skip/Offset not supported"
else
[
Text = Text.Format("LIMIT #{0}", {take}),
Location = "AfterQuerySpecification"
]
Overriding SqlCapabilities
| Field | Details |
|---|---|
FractionalSecondsScale |
A number value ranging from 1 to 7 that indicates the number of decimal places supported for millisecond values. This value should be set by connectors that want to enable query folding over datetime values. Default: null |
PrepareStatements |
A logical value that indicates that statements should be prepared using SQLPrepare. Default: false |
SupportsTop |
A logical value that indicates the driver supports the TOP clause to limit the number of returned rows. Default: false |
StringLiteralEscapeCharacters |
A list of text values that specify the character(s) to use when escaping string literals and LIKE expressions. Example: {""}Default: null |
SupportsDerivedTable |
A logical value that indicates the driver supports derived tables (sub-selects). This value is assumed to be true for drivers that set their conformance level to SQL_SC_SQL92_FULL (reported by the driver or overridden with the Sql92Conformance setting. For all other conformance levels, this value defaults to false. If your driver doesn't report the SQL_SC_SQL92_FULL compliance level, but does support-derived tables, set this value to true. Supporting derived tables is required for many DirectQuery scenarios. |
SupportsNumericLiterals |
A logical value that indicates whether the generated SQL should include numeric literals values. When set to false, numeric values are always specified using parameter binding. Default: false |
SupportsStringLiterals |
A logical value that indicates whether the generated SQL should include string literals values. When set to false, string values are always specified using parameter binding. Default: false |
SupportsOdbcDateLiterals |
A logical value that indicates whether the generated SQL should include date literals values. When set to false, date values are always specified using parameter binding. Default: false |
SupportsOdbcTimeLiterals |
A logical value that indicates whether the generated SQL should include time literals values. When set to false, time values are always specified using parameter binding. Default: false |
SupportsOdbcTimestampLiterals |
A logical value that indicates whether the generated SQL should include timestamp literals values. When set to false, timestamp values are always specified using parameter binding. Default: false |
Overriding SQLColumns
SQLColumns is a function handler that receives the results of an ODBC call to SQLColumns. The source parameter contains a table with the data type information. This override is typically used to fix up data type mismatches between calls to SQLGetTypeInfo and SQLColumns.
For details of the format of the source table parameter, go to SQLColumns Function.
Overriding SQLGetFunctions
This field is used to override SQLFunctions values returned by an ODBC driver. It contains a record whose field names are equal to the FunctionId constants defined for the ODBC
SQLGetFunctions function. Numeric constants for each of these fields can be found in the ODBC
specification.
| Field | Details |
|---|---|
SQL_CONVERT_FUNCTIONS |
Indicates which function(s) are supported when doing type conversions. By default, the M Engine attempts to use the CONVERT function. Drivers that prefer the use of CAST can override this value to report that only SQL_FN_CVT_CAST (numeric value of 0x2) is supported. |
SQL_API_SQLBINDCOL |
A logical (true/false) value that indicates whether the mashup engine should use the SQLBindCol API when retrieving data. When set to false, SQLGetData is used instead. Default: false |
The following code snippet provides an example explicitly telling the M engine to use CAST rather than CONVERT.
SQLGetFunctions = [
SQL_CONVERT_FUNCTIONS = 0x2 /* SQL_FN_CVT_CAST */
]
Overriding SQLGetInfo
This field is used to override SQLGetInfo values returned by an ODBC driver. It contains a record whose fields are names equal to the InfoType constants defined for the ODBC SQLGetInfo function. Numeric constants for each of these fields can be found in the ODBC specification. The full list of InfoTypes that are checked can be found in the mashup engine trace files.
The following table contains commonly overridden SQLGetInfo properties:
| Field | Details |
|---|---|
SQL_SQL_CONFORMANCE |
An integer value that indicates the level of SQL-92 supported by the driver: (1) SQL_SC_SQL92_ENTRY: Entry level SQL-92 compliant. (2) SQL_SC_FIPS127_2_TRANSITIONAL: FIPS 127-2 transitional level compliant. (4) SQL_SC_ SQL92_INTERMEDIATE" Intermediate level SQL-92 compliant. (8) SQL_SC_SQL92_FULL: Full level SQL-92 compliant. In Power Query scenarios, the connector is used in a Read Only mode. Most drivers will want to report a SQL_SC_SQL92_FULL compliance level, and override specific SQL generation behavior using the SQLGetInfo and SQLGetFunctions properties. |
SQL_SQL92_PREDICATES |
A bitmask enumerating the predicates supported in a SELECT statement, as defined in SQL-92. Go to SQL_SP_* constants in the ODBC specification. |
SQL_AGGREGATE_FUNCTIONS |
A bitmask enumerating support for aggregation functions. SQL_AF_ALL SQL_AF_AVG SQL_AF_COUNT SQL_AF_DISTINCT SQL_AF_MAX SQL_AF_MIN SQL_AF_SUM Go to SQL_AF_* constants in the ODBC specification. |
SQL_GROUP_BY |
An integer value that specifies the relationship between the columns in the GROUP BY clause and the non-aggregated columns in the select list: SQL_GB_COLLATE: A COLLATE clause can be specified at the end of each grouping column. SQL_GB_NOT_SUPPORTED: GROUP BY clauses aren't supported. SQL_GB_GROUP_BY_EQUALS_SELECT: The GROUP BY clause must contain all non-aggregated columns in the select list. It can't contain any other columns. For example, SELECT DEPT, MAX(SALARY) FROM EMPLOYEE GROUP BY DEPT. SQL_GB_GROUP_BY_CONTAINS_SELECT: The GROUP BY clause must contain all non-aggregated columns in the select list. It can contain columns that aren't in the select list. For example, SELECT DEPT, MAX(SALARY) FROM EMPLOYEE GROUP BY DEPT, AGE. SQL_GB_NO_RELATION: The columns in the GROUP BY clause and the select list aren't related. The meaning of non-grouped, non-aggregated columns in the select list is data source–dependent. For example, SELECT DEPT, SALARY FROM EMPLOYEE GROUP BY DEPT, AGE. Go to SQL_GB_* constants in the ODBC specification. |
The following helper function can be used to create bitmask values from a list of integer values:
Flags = (flags as list) =>
let
Loop = List.Generate(
()=> [i = 0, Combined = 0],
each [i] < List.Count(flags),
each [i = [i]+1, Combined =*Number.BitwiseOr([Combined], flags{i})],
each [Combined]),
Result = List.Last(Loop, 0)
in
Result;
Overriding SQLGetTypeInfo
SQLGetTypeInfo can be specified in two ways:
- A fixed
tablevalue that contains the same type information as an ODBC call toSQLGetTypeInfo. - A function that accepts a table argument, and returns a table. The argument contains the original results of the ODBC call to
SQLGetTypeInfo. Your function implementation can modify or add to this table.
The first approach is used to completely override the values returned by the ODBC driver. The second approach is used if you want to add to or modify these values.
For details of the format of the types table parameter and expected return value, go to SQLGetTypeInfo function reference.
SQLGetTypeInfo using a static table
The following code snippet provides a static implementation for SQLGetTypeInfo.
SQLGetTypeInfo = #table(
{ "TYPE_NAME", "DATA_TYPE", "COLUMN_SIZE", "LITERAL_PREF", "LITERAL_SUFFIX", "CREATE_PARAS", "NULLABLE", "CASE_SENSITIVE", "SEARCHABLE", "UNSIGNED_ATTRIBUTE", "FIXED_PREC_SCALE", "AUTO_UNIQUE_VALUE", "LOCAL_TYPE_NAME", "MINIMUM_SCALE", "MAXIMUM_SCALE", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "NUM_PREC_RADIX", "INTERNAL_PRECISION", "USER_DATA_TYPE" }, {
{ "char", 1, 65535, "'", "'", "max. length", 1, 1, 3, null, 0, null, "char", null, null, -8, null, null, 0, 0 },
{ "int8", -5, 19, "'", "'", null, 1, 0, 2, 0, 10, 0, "int8", 0, 0, -5, null, 2, 0, 0 },
{ "bit", -7, 1, "'", "'", null, 1, 1, 3, null, 0, null, "bit", null, null, -7, null, null, 0, 0 },
{ "bool", -7, 1, "'", "'", null, 1, 1, 3, null, 0, null, "bit", null, null, -7, null, null, 0, 0 },
{ "date", 9, 10, "'", "'", null, 1, 0, 2, null, 0, null, "date", null, null, 9, 1, null, 0, 0 },
{ "numeric", 3, 28, null, null, null, 1, 0, 2, 0, 0, 0, "numeric", 0, 0, 2, null, 10, 0, 0 },
{ "float8", 8, 15, null, null, null, 1, 0, 2, 0, 0, 0, "float8", null, null, 6, null, 2, 0, 0 },
{ "float8", 6, 17, null, null, null, 1, 0, 2, 0, 0, 0, "float8", null, null, 6, null, 2, 0, 0 },
{ "uuid", -11, 37, null, null, null, 1, 0, 2, null, 0, null, "uuid", null, null, -11, null, null, 0, 0 },
{ "int4", 4, 10, null, null, null, 1, 0, 2, 0, 0, 0, "int4", 0, 0, 4, null, 2, 0, 0 },
{ "text", -1, 65535, "'", "'", null, 1, 1, 3, null, 0, null, "text", null, null, -10, null, null, 0, 0 },
{ "lo", -4, 255, "'", "'", null, 1, 0, 2, null, 0, null, "lo", null, null, -4, null, null, 0, 0 },
{ "numeric", 2, 28, null, null, "precision, scale", 1, 0, 2, 0, 10, 0, "numeric", 0, 6, 2, null, 10, 0, 0 },
{ "float4", 7, 9, null, null, null, 1, 0, 2, 0, 10, 0, "float4", null, null, 7, null, 2, 0, 0 },
{ "int2", 5, 19, null, null, null, 1, 0, 2, 0, 10, 0, "int2", 0, 0, 5, null, 2, 0, 0 },
{ "int2", -6, 5, null, null, null, 1, 0, 2, 0, 10, 0, "int2", 0, 0, 5, null, 2, 0, 0 },
{ "timestamp", 11, 26, "'", "'", null, 1, 0, 2, null, 0, null, "timestamp", 0, 38, 9, 3, null, 0, 0 },
{ "date", 91, 10, "'", "'", null, 1, 0, 2, null, 0, null, "date", null, null, 9, 1, null, 0, 0 },
{ "timestamp", 93, 26, "'", "'", null, 1, 0, 2, null, 0, null, "timestamp", 0, 38, 9, 3, null, 0, 0 },
{ "bytea", -3, 255, "'", "'", null, 1, 0, 2, null, 0, null, "bytea", null, null, -3, null, null, 0, 0 },
{ "varchar", 12, 65535, "'", "'", "max. length", 1, 0, 2, null, 0, null, "varchar", null, null, -9, null, null, 0, 0 },
{ "char", -8, 65535, "'", "'", "max. length", 1, 1, 3, null, 0, null, "char", null, null, -8, null, null, 0, 0 },
{ "text", -10, 65535, "'", "'", "max. length", 1, 1, 3, null, 0, null, "text", null, null, -10, null, null, 0, 0 },
{ "varchar", -9, 65535, "'", "'", "max. length", 1, 1, 3, null, 0, null, "varchar", null, null, -9, null, null, 0, 0 },
{ "bpchar", -8, 65535, "'", "'", "max. length", 1, 1, 3, null, 0, null, "bpchar", null, null, -9, null, null, 0, 0 } }
);
SQLGetTypeInfo using a function
The following code snippets append the bpchar type to the existing types returned by the driver.
SQLGetTypeInfo = (types as table) as table =>
let
newTypes = #table(
{
"TYPE_NAME",
"DATA_TYPE",
"COLUMN_SIZE",
"LITERAL_PREF",
"LITERAL_SUFFIX",
"CREATE_PARAS",
"NULLABLE",
"CASE_SENSITIVE",
"SEARCHABLE",
"UNSIGNED_ATTRIBUTE",
"FIXED_PREC_SCALE",
"AUTO_UNIQUE_VALUE",
"LOCAL_TYPE_NAME",
"MINIMUM_SCALE",
"MAXIMUM_SCALE",
"SQL_DATA_TYPE",
"SQL_DATETIME_SUB",
"NUM_PREC_RADIX",
"INTERNAL_PRECISION",
"USER_DATA_TYPE"
},
// we add a new entry for each type we want to add
{
{
"bpchar",
-8,
65535,
"'",
"'",
"max. length",
1,
1,
3,
null,
0,
null,
"bpchar",
null,
null,
-9,
null,
null,
0,
0
}
}),
append = Table.Combine({types, newTypes})
in
append;
Setting the connection string
The connection string for your ODBC driver is set using the first argument to the Odbc.DataSource and Odbc.Query functions. The value can be text, or an M record. When using the record, each field in the record will become a property in the connection string. All connection strings require a Driver field (or DSN field if you require users to pre-configure a system level DSN). Credential-related properties are set separately. Other properties are driver specific.
The code snippet below shows the definition of a new data source function, creation of the ConnectionString record, and invocation of the Odbc.DataSource function.
[DataSource.Kind="SqlODBC", Publish="SqlODBC.Publish"]
shared SqlODBC.Contents = (server as text) =>
let
ConnectionString = [
Driver = "SQL Server Native Client 11.0",
Server = server,
MultiSubnetFailover = "Yes",
ApplicationIntent = "ReadOnly",
APP = "PowerBICustomConnector"
],
OdbcDatasource = Odbc.DataSource(ConnectionString)
in
OdbcDatasource;
Next steps
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for