閱讀英文

共用方式為


Odbc.DataSource 的參數

Odbc.DataSource 函式會採用兩個connectionStringoptions參數,也就是驅動程式的 ,以及可讓您覆寫各種驅動程式行為的記錄。 透過選項記錄,您可以覆寫驅動程式所報告的功能和其他資訊、控制導覽器行為,以及影響 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 子句。

欄位包括 ConstantLimitClause

詳細資訊: 覆寫 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

欄位 AstVisitor 是透過 Odbc.DataSource 選項記錄來設定。 它用來修改針對特定查詢案例所產生的 SQL 語句。

注意

支援 LIMIT 和 OFFSET 子句的驅動程式(而非 TOP)會想要提供 的LimitClauseAstVisitor覆寫。

持續性

提供此值的覆寫已被取代,而且可能會從未來的實作中移除。

LimitClause

此欄位是函式,可接收兩 Int64.Type 個自變數 (skiptake), 並傳回具有兩個文字欄位的記錄 (TextLocation

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"
    ]

覆寫 SqlCapabilities

欄位 詳細資料
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是函式處理程式,可接收對 SQLColumns 之 ODBC 呼叫的結果。 來源參數包含具有數據類型信息的數據表。 此覆寫通常用來修正 對 SQLGetTypeInfoSQLColumns呼叫之間的數據類型不符。

如需源數據表參數格式的詳細數據,請移至 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合規性層級,並使用 和 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

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.DataSourceOdbc.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;

下一步