Odbc.DataSource 函数采用两个参数：一个connectionString用于驱动程序，一个options记录，用于替代各种驱动程序行为。 通过选项记录，可以替代驱动程序报告的功能和其他信息，控制导航器行为，并影响 M 引擎生成的 SQL 查询。
支持的选项记录字段分为两个类别：公共字段和始终可用字段，以及仅在扩展性上下文中可用的字段。
下表描述了选项记录中的公共字段。
| 领域 | Description |
|---|---|
CommandTimeout |
一个持续时间值，用于控制在取消查询之前允许服务器端查询运行的时间。 默认值：10 分钟 |
ConnectionTimeout |
一个持续时间值，该值控制在放弃尝试与服务器建立连接之前等待的时间。 默认值：15 秒 |
CreateNavigationProperties |
一个逻辑值，该值设置是否在返回的表上生成导航属性。 导航属性基于驱动程序报告的外键关系。 这些属性显示为可在查询编辑器中展开的“虚拟”列，以实现适当的联接。 如果计算外键依赖项是驱动程序的昂贵操作，可以将此值设置为 false。 默认值：true |
HierarchicalNavigation |
一个逻辑值，该值设置是否查看按其架构名称分组的表。 设置为 false 时，表将显示在每个数据库下的平面列表中。 默认值：false |
SqlCompatibleWindowsAuth |
一个逻辑值，用于确定在使用 Windows 身份验证时是否生成与 SQL Server 兼容的连接字符串。Trusted_Connection=Yes如果驱动程序支持 Windows 身份验证，但需要连接字符串中的额外或备用设置，则应将此值设置为 false，并使用 CredentialConnectionString 下表中所述的选项记录字段。默认值：true |
下表描述了仅可通过扩展性获取的选项记录字段。 后面的部分将介绍不是简单文本值的字段。
| 领域 | Description |
|---|---|
AstVisitor |
包含一个或多个替代的记录，用于控制 SQL 查询生成。 此字段的最常见用法是提供逻辑来为不支持 TOP 的驱动程序生成 LIMIT/OFFSET 子句。 字段包括 Constant 和 LimitClause。详细信息： 覆盖 AstVisitor |
CancelQueryExplicitly |
一个逻辑值，指示 M 引擎在终止与 ODBC 服务器的连接之前通过 ODBC 驱动程序显式取消任何正在运行的调用。 在查询执行独立于与服务器的网络连接管理的情况下，例如在某些 Spark 部署中，此字段非常有用。 在大多数情况下，不需要设置此值，因为当与服务器的网络连接终止时，服务器中的查询将被取消。 默认值：false |
ClientConnectionPooling |
一个逻辑值，用于为 ODBC 驱动程序启用客户端连接池。 大多数驱动程序都希望将此值设置为 true。 默认值：false |
CredentialConnectionString |
用于指定凭据相关的连接字符串属性的文本或记录值。 |
HideNativeQuery |
一个逻辑值，用于控制连接器是否在 Power Query 用户体验中显示生成的 SQL 语句。 仅当后端数据源本机支持 SQL-92 时，才应将其设置为 true。 默认值：false |
ImplicitTypeConversions |
包含驱动程序或后端服务器支持的隐式类型转换的表值。 此表中的值与驱动程序本身报告的转换相加。 在覆盖驱动程序报告的数据类型信息时，此字段通常与 SQLGetTypeInfo 字段一起使用。 |
OnError |
接收类型为record的errorRecord参数的错误处理函数。此函数的常见用途包括处理 SSL 连接失败、提供下载链接（如果系统上找不到驱动程序）以及报告身份验证错误。 |
SoftNumbers |
当两种特定数字类型之间的转换在 SQL_CONVERT_* 功能中不受声明为支持时，允许 M 引擎选择兼容的数据类型。 默认值：false |
SqlCapabilities |
提供各种驱动程序功能的替代的记录，以及指定未通过 ODBC 3.8 表示的功能的方法。 详细信息： 重写 SqlCapabilities |
SQLColumns |
一个函数，可用于修改函数返回的 SQLColumns 列元数据。详细信息： 重写 SQLColumns |
SQLGetFunctions |
一条记录，允许您覆盖SQLGetFunctions调用返回的值。此字段的常见用途是禁用参数绑定的使用，或指定生成的查询应使用 CAST 而不是 CONVERT。 详细信息：覆盖 SQLGetFunctions |
SQLGetInfo |
一条记录，用于覆盖调用SQLGetInfo时返回的值。详细信息：重载 SQLGetInfo |
SQLGetTypeInfo |
一个返回表格的表或函数，它覆盖了SQLGetTypeInfo返回的类型信息。当该值设置为表时，该值将完全替换驱动程序报告的类型信息。 SQLGetTypeInfo 不会调用。当该值设置为函数时，函数将收到对原始调用 SQLGetTypeInfo的结果，允许修改表。在 SQLGetTypeInfo 和 SQLColumns 报告的数据类型不匹配时，通常会使用此字段。详细信息： 重写 SQLGetTypeInfo |
SQLTables |
一个函数，可用于修改调用 SQLTables返回的表元数据。 |
TolerateConcatOverflow |
允许串联文本值，即使结果可能被截断以适应可用类型的范围。 例如，当在一个支持最大 VARCHAR 大小为 4000 且不支持 CLOB 类型的系统上将 VARCHAR(4000) 字段与 VARCHAR(4000) 字段连接起来时，尽管结果可能会被截断，但连接仍会被压缩。 默认值：false |
UseEmbeddedDriver |
（内部使用）： 一个逻辑值，用于控制是否应从本地目录加载 ODBC 驱动程序（使用 ODBC 4.0 规范中定义的新功能）。 此值通常仅由随 Power Query 一起提供的Microsoft创建的连接器设置。 设置为 false 时，系统 ODBC 驱动程序管理器用于查找和加载驱动程序。 大多数连接器不需要设置此字段。 默认值：false |
重写 AstVisitor
字段 AstVisitor 是通过 Odbc.DataSource 选项记录设置的。 它用于修改针对特定查询方案生成的 SQL 语句。
注释
支持 LIMIT 和 OFFSET 子句（而不是 TOP）的驱动程序需要为LimitClauseAstVisitor提供一个重写。
恒定
为此值提供替代已被弃用，并可能从将来的实现中删除。
LimitClause
此字段是一个函数，它接收两 Int64.Type 个参数（skip， take），并返回一条包含两个文本字段（Text， ） Location的记录。
LimitClause = (skip as nullable number, take as number) as record => ...
skip 参数是要跳过的行数（即 OFFSET 的参数）。 如果未指定偏移量，则跳过值将为 null。 如果驱动程序支持 LIMIT，但不支持 OFFSET，则当跳过大于 0 时，该 LimitClause 函数应返回未实现的错误 （...）。
参数 take 是要提取的行数（即作为 LIMIT 语句的参数）。
结果 Text 的字段包含要添加到生成的查询的 SQL 文本。
该 Location 字段指定插入子句的位置。 下表描述了支持的值。
| 价值 | Description | Example |
|---|---|---|
AfterQuerySpecification |
LIMIT 子句放在生成的 SQL 的末尾。 这是最常用的 LIMIT 语法。 |
SELECT a、 b、 c FROM 表 WHERE a > 10 限制 5 |
BeforeQuerySpecification |
LIMIT 子句放在生成的 SQL 语句之前。 |
限制 5 行 SELECT a、 b、 c FROM 表 其中 a > 10 |
AfterSelect |
LIMIT 在 SELECT 语句和任何修饰符（如 DISTINCT）之后执行。 | SELECT DISTINCT LIMIT 5 a， b， c FROM 表 WHERE a > 10 |
AfterSelectBeforeModifiers |
LIMIT 在 SELECT 语句之后，但在任何修饰符（如 DISTINCT）之前。 | SELECT LIMIT 5 DISTINCT a， b， c FROM 表 在 a > 10 的条件下 |
以下代码片段为需要具有可选 OFFSET 的 LIMIT 子句的驱动程序提供 LimitClause 实现，格式如下： [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"
]
以下代码片段为支持 LIMIT 但不支持 OFFSET 的驱动程序提供 LimitClause 实现。 格式：LIMIT <row_count>。
LimitClause = (skip, take) =>
if (skip > 0) then error "Skip/Offset not supported"
else
[
Text = Text.Format("LIMIT #{0}", {take}),
Location = "AfterQuerySpecification"
]
重写 SqlCapabilities
| 领域 | 详细信息 |
|---|---|
FractionalSecondsScale |
一个介于 1 到 7 之间的数字值，指示毫秒值支持的小数位数。 此值应由想要启用查询折叠日期时间值的连接器设置。 默认值：null |
PrepareStatements |
一个逻辑值，指示应使用 SQLPrepare 准备语句。 默认值：false |
SupportsTop |
一个逻辑值，指示驱动程序支持 TOP 子句来限制返回的行数。 默认值：false |
StringLiteralEscapeCharacters |
指定转义字符串文本和 LIKE 表达式时要使用的字符的文本值列表。 示例： {""}默认值：null |
SupportsDerivedTable |
一个逻辑值，指示驱动程序支持派生表（子选择）。 对于将其符合性级别设置为 SQL_SC_SQL92_FULL 的驱动程序（由驱动程序报告或通过 Sql92Conformance 设置重写），假设此值为 true。 对于所有其他一致性级别，此值默认为 false。 如果驱动程序未报告SQL_SC_SQL92_FULL符合性级别，但支持派生表，请将此值设置为 true。 许多 DirectQuery 方案都需要支持派生表。 |
SupportsNumericLiterals |
一个逻辑值，该值指示生成的 SQL 是否应包含数值文本值。 当设置为 false 时，数值始终通过参数绑定来指定。 默认值：false |
SupportsStringLiterals |
一个逻辑值，该值指示生成的 SQL 是否应包含字符串文本值。 设置为 false 时，始终使用参数绑定指定字符串值。 默认值：false |
SupportsOdbcDateLiterals |
一个逻辑值，该值指示生成的 SQL 是否应包含日期文本值。 设置为 false 时，始终使用参数绑定指定日期值。 默认值：false |
SupportsOdbcTimeLiterals |
一个逻辑值，该值指示生成的 SQL 是否应包含时间文本值。 将其设置为 false 时，时间值始终使用参数绑定进行指定。 默认值：false |
SupportsOdbcTimestampLiterals |
一个逻辑值，该值指示生成的 SQL 是否应包含时间戳文本值。 设置为 false 时，始终使用参数绑定来定义时间戳值。 默认值：false |
重写 SQLColumns
SQLColumns 是一个函数处理程序，用于接收对 SQLColumns 的 ODBC 调用的结果。 源参数包含具有数据类型信息的表。 此重写通常用于修复对 SQLGetTypeInfo 和 SQLColumns 的调用中数据类型不匹配的问题。
有关源表参数格式的详细信息，请转到 SQLColumns 函数。
重写 SQLGetFunctions
此字段用于替代 SQLFunctions ODBC 驱动程序返回的值。 它包含一条记录，其字段名称等于 FunctionId 为 ODBC SQLGetFunctions 函数定义的常量。 可以在 ODBC 规范中找到每个字段的数值常量。
| 领域 | 详细信息 |
|---|---|
SQL_CONVERT_FUNCTIONS |
指示执行类型转换时支持哪个函数。 默认情况下，M 引擎尝试使用 CONVERT 函数。 倾向于使用 CAST 的驱动程序可以覆盖此值，以报告仅支持 SQL_FN_CVT_CAST（数值为 0x2）。 |
SQL_API_SQLBINDCOL |
一个逻辑值（true/false），该值指示混合引擎在检索数据时是否应使用 SQLBindCol API 。 如果设置为 false，则改用 SQLGetData 。 默认值：false |
以下代码片段提供了一个显式指示 M 引擎使用 CAST 而不是 CONVERT 的示例。
SQLGetFunctions = [
SQL_CONVERT_FUNCTIONS = 0x2 /* SQL_FN_CVT_CAST */
]
重写 SQLGetInfo
此字段用于替代 SQLGetInfo ODBC 驱动程序返回的值。 它包含一条记录，其字段名称等于 InfoType 为 ODBC SQLGetInfo 函数定义的常量。 可以在 ODBC 规范中找到每个字段的数值常量。 在混合引擎跟踪文件中可以找到 InfoTypes 检查的完整列表。
下表列出了常被重写的SQLGetInfo属性列表：
| 领域 | 详细信息 |
|---|---|
SQL_SQL_CONFORMANCE |
一个整数值，指示驱动程序支持的 SQL-92 级别： （1）SQL_SC_SQL92_ENTRY：符合 SQL-92 入门级标准。 （2） SQL_SC_FIPS127_2_TRANSITIONAL：符合 FIPS 127-2 过渡级别。 （4） SQL_SC_ SQL92_INTERMEDIATE“中级 SQL-92 兼容。 （8） SQL_SC_SQL92_FULL：完全符合 SQL-92 标准。 在 Power Query 方案中，连接器在只读模式下使用。 大多数驱动程序希望报告SQL_SC_SQL92_FULL符合性级别，并使用属性 SQLGetInfo替代特定的 SQL 生成行为SQLGetFunctions。 |
SQL_SQL92_PREDICATES |
SQL-92 中定义的，用于枚举 SELECT 语句中支持条件的位掩码。 转到 ODBC 规范中的 SQL_SP_* 常量 。 |
SQL_AGGREGATE_FUNCTIONS |
对聚合函数的位掩码枚举支持。 SQL_AF_ALL SQL_AF_AVG SQL_AF_COUNT SQL_AF_DISTINCT SQL_AF_MAX SQL_AF_MIN SQL_AF_SUM 转到 ODBC 规范中的 SQL_AF_* 常量 。 |
SQL_GROUP_BY |
一个整数值，该值指定 GROUP BY 子句中的列与选择列表中的非聚合列之间的关系： SQL_GB_COLLATE：可以在每个分组列的末尾指定 COLLATE 子句。 SQL_GB_NOT_SUPPORTED：GROUP BY 子句不被支持。 SQL_GB_GROUP_BY_EQUALS_SELECT：GROUP BY 子句必须包含选择列表中的所有非聚合列。 它不能包含任何其他列。 例如，SELECT DEPT、MAX（SALARY） FROM EMPLOYEE GROUP BY DEPT。 SQL_GB_GROUP_BY_CONTAINS_SELECT：GROUP BY 子句必须包含选择列表中的所有非聚合列。 某个表中可以包含不在 SELECT 列表中的列。 例如，SELECT DEPT、MAX（SALARY） FROM EMPLOYEE GROUP BY DEPT、 AGE。 SQL_GB_NO_RELATION：GROUP BY 子句中的列和 SELECT 列表不相关。 选择列表中的非分组非聚合列的含义取决于数据源。 例如，SELECT DEPT、EMPLOYEE GROUP BY DEPT、AGE。 转到 ODBC 规范中的 SQL_GB_* 常量 。 |
以下帮助程序函数可用于从整数值列表中创建位掩码值：
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;
重写 SQLGetTypeInfo
SQLGetTypeInfo 可以通过两种方式指定：
- 包含与 ODBC 调用
table相同的类型信息的固定SQLGetTypeInfo值。 - 一个接受表参数并返回表的函数。 该参数包含 ODBC 调用
SQLGetTypeInfo的原始结果。 您对函数的实现可以修改或添加到此表。
第一种方法用于完全替代 ODBC 驱动程序返回的值。 如果要添加或修改这些值，则使用第二种方法。
有关类型表参数和预期返回值的格式的详细信息，请转到 SQLGetTypeInfo 函数引用。
使用静态表的 SQLGetTypeInfo
以下代码片段提供了一个 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
以下代码片段将 bpchar 类型追加到驱动程序返回的现有类型。
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;
设置连接字符串
ODBC 驱动程序的连接字符串是使用 Odbc.DataSource 和 Odbc.Query 函数的第一个参数设置的。 该值可以是文本，也可以是 M 记录。 使用记录时，记录中的每个字段都将成为连接字符串中的属性。 所有连接字符串都需要Driver字段（如果需要用户预配置系统级DSN，则需DSN字段）。 凭据相关的属性单独设置。 其他属性特定于驱动程序。
下面的代码片段显示了新的数据源函数的定义、记录的 ConnectionString 创建和 Odbc.DataSource 函数的调用。
[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;