Odbc.DataSource 的參數
Odbc 資料來源函式 options 會採用兩個參數： connectionString 適用于您的驅動程式，以及可讓您覆寫各種驅動程式行為的記錄。 透過選項記錄，您可以覆寫驅動程式所報告的功能和其他資訊、控制導覽器行為，並影響 M 引擎所產生的 SQL 查詢。
支援的選項會記錄欄位分成兩個類別：「公用」和「永遠可用」，以及只能在擴充性內容中使用的欄位。
下表說明選項記錄中的公用欄位。
| 欄位 | 描述 |
|---|---|
CommandTimeout |
持續時間值，可控制伺服器端查詢在取消之前，允許執行的時間長度。 預設值：10分鐘 |
ConnectionTimeout |
持續時間值，可控制在放棄嘗試連接到伺服器之前，要等待的時間長度。 預設值：15秒 |
CreateNavigationProperties |
邏輯值，設定是否要在傳回的資料表上產生導覽屬性。 導覽屬性是以驅動程式所報告的外鍵關聯性為基礎。 這些屬性會顯示為「虛擬」資料行，可在 [查詢編輯器] 中展開，以建立適當的聯結。 如果計算外鍵相依性是驅動程式的昂貴作業，您可能會想要將此值設定為 false。 預設值：True |
HierarchicalNavigation |
邏輯值，設定是否要依架構名稱分組來查看資料表。 當設定為 false 時，資料表會顯示在每個資料庫底下的一般清單中。 預設值：false |
SqlCompatibleWindowsAuth |
邏輯值，決定是否要在使用 Windows Authentication Trusted_Connection=Yes 時產生 SQL Server 相容的連接字串。如果您的驅動程式支援 Windows Authentication，但您的連接字串中需要額外的替代設定，您應該將此值設定為 false，並使用下表所述的選項 [ CredentialConnectionString 記錄] 欄位。預設值：True |
下表描述只能透過擴充性使用的選項記錄欄位。 非簡單常值的欄位會在稍後的章節中說明。
| 欄位 | 描述 |
|---|---|
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 |
接收 errorRecord 型 record 別參數的錯誤處理函數。此函式的常見用途包括處理 SSL 連線失敗，並在系統上找不到您的驅動程式時提供下載連結，以及報告驗證錯誤。 |
SoftNumbers |
當兩個特定數數值型別之間的轉換未宣告為 SQL_CONVERT_ * 功能的支援時，允許 M 引擎選取相容的資料類型。 預設值：false |
SqlCapabilities |
提供各種覆寫驅動程式功能的記錄，以及指定未透過 ODBC 3.8 表示之功能的一種方式。 詳細資訊：覆 寫 SqlCapabilities |
SQLColumns |
函數，可讓您修改函式所傳回 SQLColumns 的資料行中繼資料。詳細資訊：覆 寫 SQLColumns |
SQLGetFunctions |
一種記錄，可讓您覆寫呼叫 SQLGetFunctions 所傳回的值。此欄位的常見用法是停用參數系結的使用，或指定產生的查詢應該使用轉換而非轉換。 詳細資訊：覆 寫 SQLGetFunctions |
SQLGetInfo |
一種記錄，可讓您覆寫呼叫 SQLGetInfo 所傳回的值。詳細資訊：覆 寫 SQLGetInfo |
SQLGetTypeInfo |
傳回資料表的資料表或函數，這個資料表會覆寫所傳回 SQLGetTypeInfo 的類型資訊。當值設定為數據表時，此值會完全取代驅動程式所報告的類型資訊。 SQLGetTypeInfo 不會呼叫。當值設定為函式時，您的函式會收到原始呼叫 SQLGetTypeInfo 的結果，讓您可以修改資料表。當和 SQLColumns 所報告 SQLGetTypeInfo 的資料類型不相符時，通常會使用這個欄位。詳細資訊：覆 寫 SQLGetTypeInfo |
SQLTables |
函數，可讓您修改呼叫 SQLTables 所傳回的資料表中繼資料。 |
TolerateConcatOverflow |
即使結果可能被截斷，以符合可用類型的範圍，也允許串連文字值。 例如，當將 VARCHAR (4000) 欄位與支援最大化 VARCHAR 大小為4000且沒有 CLOB 類型的系統上的 VARCHAR (4000) 欄位串連時，即使結果可能被截斷，還是會折迭串連。 預設值：false |
UseEmbeddedDriver |
(內部使用) ： 邏輯值，控制 ODBC 驅動程式是否應該從本機目錄載入 (使用 ODBC 4.0 規格) 中定義的新功能。 此值通常只會由 Microsoft 所建立的連接器（隨附于 Power Query）來設定。 設定為 false 時，系統會使用系統 ODBC 驅動程式管理員來找出並載入驅動程式。 大部分的連接器都不需要設定此欄位。 預設值：false |
覆寫 AstVisitor
此 AstVisitor 欄位是透過 Odbc. DataSource 選項記錄來設定。 它是用來修改針對特定查詢案例所產生的 SQL 語句。
注意
支援 LIMIT 和 OFFSET 子句 (而非 TOP) 的驅動程式會想要提供的 LimitClause 覆寫 AstVisitor 。
常數
為此值提供覆寫已被取代，而且可能會從未來的執行中移除。
LimitClause
此欄位是一個函式，可接收兩個 Int64.Type 引數 (skip 、 take) ，並傳回包含兩個文字欄位的記錄 (Text ， Location) 。
LimitClause = (skip as nullable number, take as number) as record => ...
skip參數是要跳過的資料列數目 (也就是要位移) 的引數。 如果未指定位移，則 skip 值會是 null。 如果您的驅動程式支援限制，但不支援位移，則當 skip 大於0時，此 LimitClause 函式應該會傳回未完成的錯誤 ( ... ) 。
take參數是要用來 (的資料列數目，也就是限制) 的引數。
Text結果的欄位包含要加入至所產生查詢的 SQL 文字。
Location欄位會指定要插入子句的位置。 下表說明支援的值。
| 值 | 描述 | 範例 |
|---|---|---|
AfterQuerySpecification |
LIMIT 子句會放置在產生的 SQL 的結尾。 這是最常支援的限制語法。 |
SELECT a、b、c 從資料表 >10 限制5 |
BeforeQuerySpecification |
LIMIT 子句會放置在產生的 SQL 語句之前。 | 限制5個數據列 SELECT a、b、c 從資料表 >10 |
AfterSelect |
LIMIT 會在 SELECT 語句之後，以及在任何修飾詞之後 (例如 DISTINCT) 。 | SELECT DISTINCT LIMIT 5 a，b，c 從資料表 >10 |
AfterSelectBeforeModifiers |
LIMIT 會在 SELECT 語句之後，但在任何修飾詞之前， (例如 DISTINCT) 。 | SELECT LIMIT 5 DISTINCT a，b，c 從資料表 >10 |
下列程式碼片段會針對預期 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"
]
下列程式碼片段提供 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 運算式進行轉義時，所要使用的字元 (s) 。 範例： {""}預設值： 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 呼叫的結果。 Source 參數包含具有資料類型資訊的資料表。 這項覆寫通常用來修正對和 SQLColumns 的呼叫 SQLGetTypeInfo 之間的資料類型不符。
如需來源資料表參數格式的詳細資訊，請移至 SQLColumns 函數。
覆寫 SQLGetFunctions
這個欄位是用來覆寫 SQLFunctions ODBC 驅動程式所傳回的值。 它包含的記錄，其功能變數名稱等於為 ODBC SQLGetFunctions函式定義的 FunctionId 常數。 您可以在 ODBC 規格中找到每個欄位的數值常數。
| 欄位 | 詳細資料 |
|---|---|
SQL_CONVERT_FUNCTIONS |
指出在執行類型轉換時，支援) (s 函數。 根據預設，M 引擎會嘗試使用 CONVERT 函數。 偏好使用 CAST 的驅動程式可以覆寫這個值，以報告僅支援 (值為 0x2) 的 SQL_FN_CVT_CAST。 |
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 驅動程式所傳回的值。 它包含的記錄，其欄位的名稱等於為 ODBC SQLGetInfo函式所定義的 InfoType 常數。 您可以在 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 相容性層級，並使用和 SQLGetFunctions 屬性覆寫特定的 SQL 產生行為。 |
SQL_SQL92_PREDICATES |
用來列舉 SELECT 語句中所支援述詞的位元遮罩，如 SQL-92 中所定義。 移至 SQL_SP_ * ODBC 規格中的常數。 |
SQL_AGGREGATE_FUNCTIONS |
以位元遮罩列舉彙總函式的支援。 SQL_AF_ALL SQL_AF_AVG SQL_AF_COUNT SQL_AF_DISTINCT SQL_AF_MAX SQL_AF_MIN SQL_AF_SUM 移至 SQL_AF_ * ODBC 規格中的常數。 |
SQL_GROUP_BY |
整數值，指定 GROUP BY 子句中的資料行與選取清單中的非匯總資料行之間的關聯性： SQL_GB_COLLATE：您可以在每個群組資料行的結尾指定 COLLATE 子句。 SQL_GB_NOT_SUPPORTED：不支援 GROUP BY 子句。 SQL_GB_GROUP_BY_EQUALS_SELECT： GROUP BY 子句必須包含選取清單中的所有非匯總資料行。 它不能包含任何其他資料行。 例如，從員工群組依部門選取 [部門]、[最大 (薪資) 。 SQL_GB_GROUP_BY_CONTAINS_SELECT： GROUP BY 子句必須包含選取清單中的所有非匯總資料行。 它可以包含不在選取清單中的資料行。 例如，從員工群組依部門、年齡 (選取 [部門]、[最大薪資) 。 SQL_GB_NO_RELATION： GROUP BY 子句中的資料行與選取清單之間的資料行不相關。 在選取清單中，非群組、非匯總資料行的意義與資料來源相依。 例如，從員工群組依部門、年齡選取部門、薪資。 移至 SQL_GB_ * ODBC 規格中的常數。 |
下列 helper 函式可以用來從整數值清單建立位元遮罩值：
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 可以用兩種方式來指定：
- 固定
table值，包含與 ODBC 呼叫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 函數的第一個引數所設定。 值可以是文字或 M 記錄。 使用記錄時，記錄中的每個欄位都會變成連接字串中的屬性。 如果您需要使用者預先設定系統層級 DSN) ，則所有連接字串都需要 Driver 欄位 (或 DSN 欄位。 與認證相關的屬性會分別設定。 其他屬性則是驅動程式特定的。
下列程式碼片段顯示新資料來源函數的定義、記錄的建立 ConnectionString ，以及 Odbc 的調用函數。
[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;