Odbc.DataSource 的參數
Odbc.DataSource 函式會採用兩個connectionString
options
參數,也就是驅動程式的 ,以及可讓您覆寫各種驅動程式行為的記錄。 透過選項記錄,您可以覆寫驅動程式所報告的功能和其他資訊、控制導覽器行為,以及影響 M 引擎所產生的 SQL 查詢。
支援的選項會記錄欄位分為兩個類別:公用且一律可用,以及只能在擴充性內容中使用的選項。
下表描述選項記錄中的公用欄位。
欄位 | 描述 |
---|---|
CommandTimeout |
持續時間值,控制在取消之前允許伺服器端查詢執行的時間長度。 預設值:10 分鐘 |
ConnectionTimeout |
持續時間值,控制在放棄嘗試與伺服器建立連線之前要等待的時間長度。 預設值:15 秒 |
CreateNavigationProperties |
邏輯值,設定是否要在傳回的數據表上產生導覽屬性。 導覽屬性是以驅動程式所報告的外鍵關聯性為基礎。 這些屬性會顯示為可在查詢編輯器中展開的「虛擬」數據行,並建立適當的聯結。 如果計算外鍵相依性是驅動程式的昂貴作業,您可能想要將此值設定為 false。 預設值:true |
HierarchicalNavigation |
邏輯值,設定是否要檢視依其架構名稱分組的數據表。 當設定為 false 時,數據表會顯示在每個資料庫底下的一般清單中。 預設值:false |
SqlCompatibleWindowsAuth |
邏輯值,決定在使用 Windows 驗證Trusted_Connection=Yes 時,是否要產生與 SQL Server 相容的 連接字串。如果您的驅動程式支援 Windows 驗證,但在 連接字串 中需要額外的或替代設定,您應該將此值設定為 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 傳回的值。此欄位的常見用法是停用參數係結的使用,或指定產生的查詢應該使用 CAST 而非 CONVERT。 詳細資訊: 覆寫 SQLGetFunctions |
SQLGetInfo |
記錄,可讓您覆寫呼叫 所 SQLGetInfo 傳回的值。詳細資訊: 覆寫 SQLGetInfo |
SQLGetTypeInfo |
傳回數據表或函式,這個數據表會覆寫 所 SQLGetTypeInfo 傳回的類型資訊。當值設定為數據表時,值會完全取代驅動程式所報告的類型資訊。 SQLGetTypeInfo 不會呼叫。當值設定為函式時,您的函式將會收到原始呼叫 SQLGetTypeInfo 的結果,讓您修改數據表。當和 SQLGetTypeInfo SQLColumns 所報告的數據類型不相符時,通常會使用此欄位。詳細資訊: 覆寫 SQLGetTypeInfo |
SQLTables |
函式,可讓您修改呼叫 SQLTables 所傳回的數據表元數據。 |
TolerateConcatOverflow |
允許串連文字值,即使結果可能被截斷以符合可用類型的範圍也一樣。 例如,在系統上串連 VARCHAR(4000) 字段與 VARCHAR(4000) 字段時,支援將 VARCHAR 大小最大化為 4000 且沒有 CLOB 類型時,即使結果可能會遭到截斷,串連仍會折疊。 預設值:false |
UseEmbeddedDriver |
(內部使用): 邏輯值,控制是否應該從本機目錄載入 ODBC 驅動程式(使用 ODBC 4.0 規格中定義的新功能)。 此值通常只會由隨附於Power Query的 Microsoft所建立的連接器所設定。 當設定為 false 時,系統會使用系統 ODBC 驅動程式管理員來尋找和載入驅動程式。 大部分連接器都不需要設定此欄位。 預設值:false |
欄位 AstVisitor
是透過 Odbc.DataSource 選項記錄來設定。 它用來修改針對特定查詢案例所產生的 SQL 語句。
注意
支援 LIMIT 和 OFFSET 子句的驅動程式(而非 TOP)會想要提供 的LimitClause
AstVisitor
覆寫。
提供此值的覆寫已被取代,而且可能會從未來的實作中移除。
此欄位是函式,可接收兩 Int64.Type
個自變數 (skip
, take
), 並傳回具有兩個文字欄位的記錄 (Text
, Location
。
LimitClause = (skip as nullable number, take as number) as record => ...
參數 skip
是要略過的數據列數目(也就是 OFFSET 的自變數)。 如果未指定位移,則skip值會是 Null。 如果您的驅動程式支援 LIMIT,但不支援 OFFSET,則當 skip 大於 0 時,函 LimitClause
式應該會傳回未實作的錯誤 (...)。
參數 take
是要接受的數據列數目(也就是 LIMIT 的自變數)。
結果 Text
的欄位包含要加入至所產生查詢的 SQL 文字。
欄位 Location
會指定要插入 子句的位置。 下表描述支援的值。
值 | Description | 範例 |
---|---|---|
AfterQuerySpecification |
LIMIT 子句會放在產生的 SQL 結尾。 這是最常支援的 LIMIT 語法。 |
SELECT a, b, c FROM 數據表 WHERE a > 10 限制 5 |
BeforeQuerySpecification |
LIMIT 子句會放在產生的 SQL 語句之前。 | 限制 5 個數據列 SELECT a, b, c FROM 數據表 WHERE 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 數據表 WHERE a > 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 但不支援 OFFSET 之驅動程式的實作。 格式: LIMIT <row_count>
。
LimitClause = (skip, take) =>
if (skip > 0) then error "Skip/Offset not supported"
else
[
Text = Text.Format("LIMIT #{0}", {take}),
Location = "AfterQuerySpecification"
]
欄位 | 詳細資料 |
---|---|
FractionalSecondsScale |
從 1 到 7 的數位值,表示毫秒值所支援的小數位數。 此值應該由想要啟用查詢折疊日期時間值的連接器設定。 默認值:null |
PrepareStatements |
邏輯值,指出應該使用 SQLPrepare 來準備語句。 預設值:false |
SupportsTop |
邏輯值,表示驅動程序支援 TOP 子句來限制傳回的數據列數目。 預設值:false |
StringLiteralEscapeCharacters |
文字值清單,指定逸出字串常值和 LIKE 運算式時要使用的字元。。 範例: {""} 默認值:null |
SupportsDerivedTable |
邏輯值,表示驅動程式支援衍生數據表(子選取)。 對於將一致性層級設定為 SQL_SC_SQL92_FULL 的驅動程序,假設此值為 true(由驅動程序回報,或使用 Sql92Conformance 設定覆寫。 對於所有其他一致性層級,此值預設為 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 之 ODBC 呼叫的結果。 來源參數包含具有數據類型信息的數據表。 此覆寫通常用來修正 對 SQLGetTypeInfo
和 SQLColumns
呼叫之間的數據類型不符。
如需源數據表參數格式的詳細數據,請移至 SQLColumns 函式。
此欄位用來覆寫 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
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合規性層級,並使用 和 SQLGetFunctions 屬性覆寫特定的 SQL 產生行為SQLGetInfo 。 |
SQL_SQL92_PREDICATES |
位掩碼,列舉 SELECT 語句中支援的述詞,如 SQL-92 中所定義。 移至 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 DEPT,MAX(SALARY) FROM EMPLOYEE GROUP BY DEPT, AGE. SQL_GB_NO_RELATION:GROUP BY 子句中的數據行與選取清單無關。 選取清單中的非群組、非匯總數據行的意義取決於數據源。 例如,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
可以透過兩種方式指定:
- 固定
table
值,其中包含與 ODBC 呼叫SQLGetTypeInfo
相同的型別資訊。 - 接受數據表自變數並傳回數據表的函式。 自變數包含 ODBC 呼叫
SQLGetTypeInfo
的原始結果。 您的函式實作可以修改或新增至此數據表。
第一種方法是用來完全覆寫 ODBC 驅動程式所傳回的值。 如果您想要新增或修改這些值,則會使用第二種方法。
如需類型數據表參數和預期傳回值格式的詳細數據,請移至 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 } }
);
下列代碼段會將類型附加 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 記錄。 使用記錄時,記錄中的每個字段都會成為 連接字串 中的屬性。 如果您需要用戶預先設定系統層級 DSN,則所有 連接字串 都需要Driver
欄位(或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;