Povolenie priameho dotazu pre konektor založený na ODBC

Prehľad

Použitie vstavanej funkcie Odbc.DataSource v M je odporúčaným spôsobom vytvorenia vlastných konektorov pre zdroje údajov, ktoré majú existujúci ovládač ODBC alebo podporujú syntax SQL dotazu. Zabalenie funkcie Odbc.DataSource umožní konektoru dediť predvolené správanie pri skladanie dotazov na základe možností, ktoré nahlásil váš ovládač. To umožní nástroju M generovať príkazy SQL na základe filtrov a iných transformácií definovaných používateľom v rámci rozhrania Power Query bez potreby poskytnúť túto logiku v rámci samotného konektora.

Rozšírenia ODBC môžu voliteľne povoliť režim Direct Query, čo službe Power BI umožňuje dynamicky generovať dotazy počas spustenia bez nutnosti uloženia dátového modelu používateľa do vyrovnávacej pamäť.

Poznámka

Povolenie podpory priameho dotazu zvyšuje náročnosť a úroveň zložitosti konektora. Keď je povolený priamy dotaz, Power BI zabráni, aby sa nástroj M zhodoval s operáciami, ktoré nemožno úplne posunúť do základného zdroja údajov.

Táto časť vychádza z konceptov prezentovaných v odkaze na rozšíriteľnosť M a predpokladá znalosť vytvorenia základného konektora údajov.

Väčšina príkladov kódu, ktoré sú uvedené v nižšie uvedených sekciách, nájdete v ukážke jazyka SqlODBC. Ďalšie ukážky nájdete v adresári vzoriek ODBC.

Funkcie rozšíriteľnosti ODBC

Nástroj M poskytuje dve funkcie zdroja údajov súvisiace s ODBC: Odbc.DataSourcea Odbc.Query.

Funkcia Odbc.DataSource poskytuje predvolenú navigačnú tabuľku so všetkými databázami, tabuľkami a zobrazeniami zo systému, podporuje skladanie dotazov a umožňuje celý rad možností prispôsobenia. Väčšina rozšírení založených na ODBC to použije ako svoju primárnu funkciu rozšíriteľnosti. Funkcia prijíma dva argumenty — reťazec pripojenia a záznam možností na poskytnutie prepísania správania.

Funkcia Odbc.Query vám umožňuje vykonať SQL prostredníctvom ovládača ODBC. Funguje ako odovzdávací návod na vykonanie dotazov. Na rozdiel od funkcie Odbc.DataSource neposkytuje funkcie skladanie dotazov a vyžaduje, SQL konektora (alebo koncový používateľ). Pri budovaní vlastného konektora sa táto funkcia zvyčajne interne používa na spustenie dotazov na načítanie metaúdajov, ktoré sa nemusia zobraziť prostredníctvom bežných kanálov ODBC. Funkcia akceptuje dva — argumenty reťazec pripojenia a dotaz SQL dotazu.

Parametre pre funkciu zdroja údajov

Vlastné konektory môžu prijať ľubovoľný počet argumentov funkcie, ale v súlade so vstavanými funkciami zdroja údajov, ktoré sú dodávané s funkciou Power Query, sa odporúčajú tieto pokyny:

  • Požadovať minimálnu množinu parametrov používaných na vytvorenie pripojenia k serveru. Menej parametrov musia koncoví používatelia poskytnúť, tým jednoduchší konektor sa bude používať.

  • Aj keď je možné definovať parametre s pevným počtom hodnôt (t. j. rozbaľovací zoznam v používateľskom rozhraní), parametre sa zadávajú pred overení používateľa. Všetky hodnoty, ktoré sa zistí pomocou programovania po overení používateľa (napríklad názov katalógu alebo databázy), by ste mali vybrať prostredníctvom navigátora. Predvoleným správaním funkcie Odbc.DataSource bude vrátenie hierarchickej navigačnej tabuľky pozostávajúcej z katalógu (databáza), názvov schém a tabuliek, hoci to možno prepísať v rámci konektora.

  • Ak máte pocit, že používatelia zvyčajne vedia, aké hodnoty chcete zadať do položiek, ktoré by si vyberu v navigátore (napríklad názov databázy), premenujte tieto parametre na voliteľné. Parametre, ktoré je možné zistiť pomocou programovania, by sa nemali vyžadovať.

  • Posledný parameter funkcie by mal byť voliteľný záznam s názvom možnosti. Tento parameter zvyčajne umožňuje pokročilým používateľom nastaviť bežné vlastnosti súvisiace s ODBC (napríklad CommandTimeout), nastaviť prepísania správania špecifické pre váš konektor a umožňuje budúcu rozšíriteľnosť bez toho, aby to ovplyvnila spätnú kompatibilitu pre vašu funkciu.

  • Argumenty súvisiace so zabezpečením alebo povereniami MUSIA nikdy byť súčasťou parametrov funkcie zdroja údajov, pretože hodnoty zadané v dialógovom okne pripojenia budú trvalé v dotaze používateľa. Parametre súvisiace s povereniami by mali byť zadané ako súčasť metód overovania podporovaných konektorom.

Všetky požadované parametre funkcie zdroja údajov sú predvolene faktorom pre hodnotu Cesta k zdroju údajov, ktorá sa používa na identifikáciu poverení používateľa.

Všimnite si, že zatiaľ čo používateľské rozhranie pre vstavanú funkciu Odbc.DataSource poskytuje rozbaľovací zoznam, ktorý umožňuje používateľovi vybrať si DSN, táto funkcia nie je k dispozícii prostredníctvom rozšíriteľnosti. Ak je vaša konfigurácia zdroja údajov dostatočne zložitá na to, aby vyžadovala plne prispôsobiteľné dialógové okno konfigurácie, odporúča sa, aby koncoví používatelia nakonfigurovali systém DSN a chceli by ste, aby funkcia zaberá názov DSN ako textové pole.

Parametre Odbc.DataSource

Funkcia Odbc.DataSource má dva parametre, connectionString pre váš ovládač a záznam možností, ktorý — umožňuje prepísať rôzne správanie ovládača. Prostredníctvom záznamu možností môžete prepísať možnosti a ďalšie informácie hlásené ovládačom, ovládať správanie navigátora a ovplyvniť SQL vygenerovaných nástrojom M.

Podporované možnosti zaznamenávajú polia do dvoch kategórií tie, ktoré sú verejné/vždy k dispozícii, a tie, ktoré sú k dispozícii — len v kontexte rozšíriteľnosti.

Nasledujúca tabuľka popisuje verejné polia v zázname možností.

Pole Description
CommandTimeout (Časový limit príkazu)

Hodnota trvania, ktorá určuje, ako dlho má dotaz na strane servera povolené byť spustený, kým je zrušený.

Predvolená hodnota: 10 minút

ConnectionTimeout (Časový limit pripojenia)

Hodnota trvania, ktorá určuje, ako dlho sa má počkať, kým sa neopustí pokus o vytvorenie pripojenia na server.

Predvolená hodnota: 15 sekúnd

CreateNavigationProperties

Logická hodnota, ktorá nastavuje, či sa vo vrátených tabuľkách majú generovať vlastnosti navigácie. Vlastnosti navigácie sú založené na vzťahoch cudzieho kľúča hlásené ovládačom a zobrazujú sa ako virtuálne stĺpce, ktoré je možné rozbaliť v editore dotazov a vytvoriť vhodné spojenie.

Ak výpočet závislostí cudzieho kľúča je pre vášho ovládača veľmi náročné, možno budete chcieť nastaviť túto hodnotu na hodnotu false.

Predvolená hodnota: true

HierarchickáNavigácia

Logická hodnota, ktorá nastavuje, či sa majú zobraziť tabuľky zoskupené podľa názvov schémy. Keď je nastavená hodnota false, tabuľky sa budú zobrazovať v plochom zozname pre každú databázu.

Predvolené: false

SqlCompatibleWindowsAuth

Logická hodnota, ktorá určuje, či sa pri použití SQL Server na overenie Windows vytvoriť — Trusted_Connection=Áno.

Ak ovládač podporuje overovanie Windows ale vyžaduje ďalšie alebo alternatívne nastavenia v reťazci pripojenia, mali by ste túto hodnotu nastaviť na false a použiť pole záznamu CredentialConnectionString, ako je popísané nižšie.

Predvolená hodnota: true

Nasledujúca tabuľka popisuje polia záznamu možností, ktoré sú k dispozícii len prostredníctvom rozšíriteľnosti. Polia, ktoré nie sú jednoduchými literálmi, sú popísané v nasledujúcich sekciách.

Pole Description
AstVisitor

Záznam obsahujúci jedno alebo viacero prepísania na ovládanie SQL generovanie dotazu. Toto pole sa najčastejšie používa na poskytnutie logiky na generovanie klauzuly LIMIT/OFFSET pre ovládače, ktoré podporujú funkcia TOP.

Polia zahŕňajú:

  • Konštantný

  • LimitClause (LimitClause)

Ďalšie informácie nájdete v časti AstVisitor.

CancelQueryExplicitly

Logická hodnota, ktorá pokyn nástroju M, aby explicitne zrušil všetky spustené volania prostredníctvom ovládača ODBC pred ukončením pripojenia k serveru ODBC.

Toto pole je užitočné v situáciách, keď sa spúšťanie dotazov spravuje nezávisle od sieťových pripojení na server, napríklad v niektorých nasadeniach spark. Vo väčšine prípadov túto hodnotu nie je potrebné nastaviť, pretože dotaz na serveri sa zruší po ukončení sieťového pripojenia k serveru.

Predvolené: false

ClientConnectionPooling

Logická hodnota, ktorá umožňuje združovanie pripojení na strane klienta pre ovládač ODBC. Väčšina ovládačov bude chcieť nastaviť túto hodnotu na hodnotu true.

Predvolené: false

CredentialConnectionString

Text alebo hodnota záznamu použitá na zadanie vlastností reťazca pripojenia súvisiacich s povereniami.

Ďalšie informácie nájdete v časti Poverenia.

HideNativeQuery

Logická hodnota, ktorá určuje, či váš konektor umožňuje natívne SQL natívne príkazy predaného dotazom pomocou funkcie Value.NativeQuery().

Poznámka: táto funkcia sa momentálne v používateľskom Power Query funkcii. Používatelia budú musieť svoje dotazy manuálne upraviť, aby mohli využiť túto možnosť.

Predvolené: false

Implicitný typKonverzia

Hodnota tabuľky obsahujúca implicitné konverzie typu podporované vaším ovládačom alebo serverom backend. Hodnoty v tejto tabuľke sú pripočítané k konverziám hláseným samotným ovládačom.

Toto pole sa zvyčajne používa v spojení s poľom SQLGetTypeInfo pri prepísaní informácií o type údajov nahlásených ovládačom.

Ďalšie informácie nájdete v časti ImplicitTypeConversions.

Chyba onError

Funkcia spracovania chýb, ktorá prijíma parameter errorRecord typu záznamu.

Medzi bežné použitie tejto funkcie patrí spracovanie zlyhaní pripojenia SSL, poskytovanie prepojenia na stiahnutie, ak sa váš ovládač v systéme nenašiel, a hlásenie chýb overovania.

Ďalšie informácie nájdete v časti OnError.

SoftNumbers (Nečíslo)

Umožňuje nástroju M vybrať kompatibilný typ údajov, ak konverzia medzi dvomi konkrétnymi číselnými typmi nie je deklarovaná ako podporovaná v možnostiach SQL_CONVERT_*.

Predvolené: false

SqlCapabilities (Možnosti SqlCapabilities)

Záznam s rôznymi prepísaniami funkcií ovládača a spôsobom, ako určiť možnosti, ktoré nie sú vyjadrené prostredníctvom ODBC 3.8.

Ďalšie informácie nájdete v časti SqlCapabilities.

SQLColumns

Funkcia, ktorá umožňuje upraviť metaúdaje stĺpcov vrátené funkciou SQLColumns.

Ďalšie informácie nájdete v časti SQLColumns.

SQLGetFunctions

Záznam, ktorý vám umožňuje prepísať hodnoty vrátené volaniami funkcie SQLGetFunctions.

Toto pole sa často používa na zakázanie používania väzby parametra alebo na určenie, že generované dotazy by mali používať funkciu CAST namiesto funkcie CONVERT.

Ďalšie informácie nájdete v časti SQLGetFunctions.

SQLGetInfo

Záznam, ktorý umožňuje prepísať hodnoty vrátené volaniami do služby SQLGetInfo.

Ďalšie informácie nájdete v časti SQLGetInfo.

SQLGetTypeInfo

Tabuľka alebo funkcia, ktorá vracia tabuľku, prepíše informácie o type vrátené funkciou SQLGetTypeInfo.

Keď je hodnota nastavená na tabuľku, hodnota úplne nahradí informácie o type hlásené ovládačom. Nevolá sa SQLGetTypeInfo.

Keď je hodnota nastavená na funkciu, funkcia dostane výsledok pôvodného volania funkcie SQLGetTypeInfo, čo vám umožní upraviť tabuľku.

Toto pole sa zvyčajne používa v prípade nesúladu medzi typmi údajov hlásenými funkciami SQLGetTypeInfo a SQLColumns.

Ďalšie informácie nájdete v časti SQLGetTypeInfo.

SQLTables

Funkcia, ktorá umožňuje upraviť metaúdaje tabuľky vrátené vol volom funkcie SQLTables.

Ďalšie informácie nájdete v časti SQLTables.

TolerateConcatOverflow

Umožňuje konverziu číselných a textových typov na väčšie typy v prípade, že by operácia spôsobí, že hodnota vypadá z rozsahu pôvodného typu.

Napríklad pri pridávaní hodnoty Int32.Max + Int32.Max bude nástroj predaných výsledok do hodnoty Int64, keď je toto nastavenie nastavené na hodnotu true. Pri pridávaní poľa VARCHAR(4000) a VARCHAR(4000) do systému, ktorý podporuje maximálnu veľkosť VARCHAR 4 000, nástroj prenesie výsledok do typu CLOB.

Predvolené: false

UseEmbeddedDriver

(interné použitie): Logická hodnota, ktorá určuje, či sa má ovládač ODBC načítať z lokálneho adresára (pomocou novej funkcie definovanej v špecifikácii ODBC 4.0). Toto je vo všeobecnosti nastavené len konektormi vytvorenými spoločnosťou Microsoft, ktoré odosielali Power Query.

Ak je nastavená hodnota false, na vyhľadanie a načítanie ovládača sa použije správca ovládača ODBC systému.

Vo väčšine konektorov by toto pole nemalo byť potrebné nastavovať.

Predvolené: false

Prepísanie AstVisitor

Pole AstVisitor je nastavené prostredníctvom záznamu možností Odbc.DataSource. Používa sa na úpravu SQL vygenerovaných pre konkrétne scenáre dotazov.

Poznámka

Ovládače, ktoré podporujú klauzuly a (a nie ), budú chcieť pre LIMIT OFFSET TOP AstVisitor poskytnúť prepísanie LimitClause.

Konštantný

Poskytnutie prepísania pre túto hodnotu sa už nepoužíva a môže sa odstrániť z budúcich implementovaní.

LimitClause (LimitClause)

Toto pole je funkcia, ktorá prijíma dva argumenty (vynechať, prijať) a vráti záznam s dvomi Int64.Type textovými poľami (Text, Umiestnenie).

LimitClause = (skip as nullable number, take as number) as record => ...

Parameter vynechať je počet riadkov, ktoré sa majú vynechať (to znamená argument posunu). Ak nie je zadaný posun, hodnota vynechanie bude null. Ak váš ovládač podporuje , ale nepodporuje , funkcia LimitClause by v prípade, že vynechanie je väčšie ako 0, mala by vrátiť nevybavenú LIMIT OFFSET chybu (...).

Parameter take je počet riadkov, ktoré sa majú zobrať (to znamená argument limitu).

Pole Text výsledku obsahuje výsledný SQL, ktorý sa má pridať do generovaného dotazu.

Pole Location určuje, kam sa má klauzula vložiť. Nasledujúca tabuľka popisuje podporované hodnoty.

Hodnota Description Príklad
AfterQuerySpecification (PoQuerySpecification)

Klauzula LIMIT sa umiestni na koniec vygenerovaného SQL.

Toto je najčastejšie podporovaná syntax funkcie LIMIT.

SELECT a, b, c

TABUĽKA FROM

KDE > 10

LIMIT 5

BeforeQuerySpecification (PredQuerySpecification) Klauzula LIMIT sa umiestni pred vygenerovaný SQL príkaz.

OBMEDZENIE POČTU RIADKOV: 5

SELECT a, b, c

TABUĽKA FROM

KDE > 10

Po vybratí Limit pokračuje za príkazOM SELECT a po všetkých modifikátoroch (napríklad DISTINCT).

VYBERTE DISTINCT LIMIT 5 a, b, c

TABUĽKA FROM

KDE > 10

AfterSelectBeforeModifiers Limit pokračuje za príkazom SELECT, ale pred akýmkoľvek modifikátorom (napríklad DISTINCT).

SELECT LIMIT 5 DISTINCT a, b, c

TABUĽKA FROM

KDE > 10

Nasledujúci zlomok kódu poskytuje implementáciu LimitClause pre ovládač, ktorý očakáva klauzulu LIMIT s voliteľným posunom v nasledujúcom formáte: [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"
        ]

Nasledujúci zlomok kódu poskytuje implementáciu LimitClause pre ovládač, ktorý podporuje funkciu LIMIT, ale nie OFFSET. Formát: LIMIT <row_count> .

LimitClause = (skip, take) =>
    if (skip > 0) then error "Skip/Offset not supported"
    else
    [
        Text = Text.Format("LIMIT #{0}", {take}),
        Location = "AfterQuerySpecification"
    ]

Prepísanie funkcií SQLCapabilities

Pole Podrobnosti
FractionalSecondsScale

Číselná hodnota v rozsahu od 1 do 7 udávajúcu počet desatinných miest podporovaných pre milisekundové hodnoty. Túto hodnotu majú nastaviť konektory, pomocou ktorých chcete povoliť skladanie dotazov v hodnotách datetime.

Predvolená hodnota: null

PripraviťŠtáty

Logická hodnota, ktorá označuje, že príkazy sa majú pripraviť pomocou funkcie SQLPrepare.

Predvolené: false

SupportsTop

Logická hodnota, ktorá označuje, že ovládač podporuje klauzulu TOP na obmedzenie počtu vrátených riadkov.

Predvolené: false

StringLiteralEcharacters (Reťazcové priem.

Zoznam textových hodnôt, ktoré určujú znaky, ktoré sa majú použiť pri uchýliní reťazcových literálov a výrazov LIKE.

Ex. {""}

Predvolená hodnota: null

SupportsDerivedTable

Logická hodnota, ktorá označuje, že ovládač podporuje odvodené tabuľky (pododá).

Predpokladá sa, že táto hodnota platí pre ovládače, ktoré nastavili úroveň zhody na SQL_SC_SQL92_FULL (nahlásené ovládačom alebo prepísané nastavením Sql92Conformance (pozri nižšie)). Pre všetky ostatné úrovne zhody sa táto hodnota predvolene nastaví na false.

Ak ovládač nenahlási úroveň dodržiavania SQL_SC_SQL92_FULL, ale podporuje odvodené tabuľky, nastavte túto hodnotu na hodnotu true.

Všimnite si, že podpora odvodených tabuliek je potrebná pre mnoho scenárov priameho dotazu.

SupportsNumericLiterals

Logická hodnota, ktorá označuje, či by generované SQL mali obsahovať číselné doslovné hodnoty. Keď je nastavená na hodnotu false, číselné hodnoty budú vždy zadané pomocou viazanie parametrov.

Predvolené: nepravdivé

SupportsStringLiterals

Logická hodnota, ktorá označuje, či by generované SQL mali obsahovať hodnoty reťazcových doslovných hodnôt. Ak je nastavená na hodnotu false, hodnoty reťazca budú vždy zadané pomocou viazanie parametrov.

Predvolené: nepravdivé

SupportsOdbcDateLiterals

Logická hodnota, ktorá označuje, či by generované SQL mali obsahovať hodnoty doslovných dátumov. Keď je nastavená na hodnotu false, hodnoty dátumu sa vždy zadá pomocou viazanej hodnoty parametrov.

Predvolené: nepravdivé

SupportsOdbcTimeLiterals

Logická hodnota, ktorá označuje, či by generované SQL mali obsahovať hodnoty časových doslovných hodnôt. Keď je nastavená na hodnotu false, časové hodnoty budú vždy zadané pomocou väzby parametrov.

Predvolené: nepravdivé

SupportsOdbcTimestampLiterals

Logická hodnota, ktorá označuje, či by generované SQL mali obsahovať hodnoty časovej pečiatky doslovne. Ak je nastavená na hodnotu false, hodnoty časovej pečiatky budú vždy zadané pomocou viazanie parametrov.

Predvolené: nepravdivé

Prepisovanie SQLColumns

SQLColumns je funkčný manipulátor, ktorý prijíma výsledky volania ODBC sqlColumns. Zdrojový parameter obsahuje tabuľku s informáciami o type údajov. Toto prepísatie sa zvyčajne používa na odstránenie nesúladu typov údajov medzi hovormi SQLGetTypeInfo a SQLColumns .

Podrobnosti o formáte parametra zdrojovej tabuľky nájdete vo funkcii SQLColumns .

Prepisovanie SQLGetFunctions

Toto pole sa používa na prepísanie hodnôt SQLFunctions vrátených ovládačom ODBC. Obsahuje záznam, ktorého názvy polí sa rovnajú konštantám funkcie FunctionId definovaným pre funkciu ODBC SQLGetFunctions. Číselné konštanty pre každé z týchto polí nájdete v špecifikácii ODBC.

Pole Podrobnosti
SQL_CONVERT_FUNCTIONS Označuje, ktoré funkcie sú podporované pri konverziách typu. V predvolenom nastavení sa M Engine pokúsi použiť funkciu CONVERT. Ovládače, ktoré uprednostňujú použitie cast, môžu prepísať túto hodnotu a nahlásiť, že je podporovaná iba SQL_FN_CVT_CAST (číselná hodnota 0x2).
SQL_API_SQLBINDCOL

Logická (true/false) hodnota, ktorá označuje, či má mashup engine používať rozhranie SQLBindCol API pri získavaní údajov. Ak je nastavená na hodnotu false, namiesto toho sa použije SQLGetData.

Predvolené: nepravdivé

Nasledujúci úryvok kódu poskytuje príklad, ktorý výslovne hovorí motoru M, aby používal CAST namiesto CONVERT.

SQLGetFunctions = [
    SQL_CONVERT_FUNCTIONS = 0x2 /* SQL_FN_CVT_CAST */
]

Prepísať SQLGetInfo

Toto pole sa používa na prepísadlo hodnôt SQLGetInfo vrátených ovládačom ODBC. Obsahuje záznam, ktorého polia sú názvy rovné konštantám Typu InfoType definovaným pre funkciu ODBC SQLGetInfo. Číselné konštanty pre každé z týchto polí nájdete v špecifikácii ODBC. Úplný zoznam typov infotypov, ktoré sú skontrolované, nájdete v súboroch sledovania mashup engine.

Nasledujúca tabuľka obsahuje bežne prepísané vlastnosti SQLGetInfo:

Pole Podrobnosti
SQL_SQL_CONFORMANCE

Celočíselná hodnota, ktorá označuje úroveň SQL-92 podporovanú vodičom:

(1) SQL_SC_SQL92_ENTRY = v súlade so vstupným SQL-92.

(2) SQL_SC_FIPS127_2_TRANSITIONAL = prechodná úroveň FIPS 127-2.

(4) SQL_SC_ SQL92_INTERMEDIATE = stredná úroveň SQL-92 v súlade.

(8) SQL_SC_SQL92_FULL = v súlade s plnou úrovňou SQL-92.

Všimnite si, že v scenároch doplnku Power Query sa konektor použije v režime iba na čítanie. Väčšina ovládačov bude chcieť nahlásiť úroveň dodržiavania SQL_SC_SQL92_FULL a prepísať špecifické správanie generácie SQL pomocou vlastností SQLGetInfo a SQLGetFunctions.

SQL_SQL92_PREDICATES

Bitmask enumerácia predikátov podporovaných vo vyhlásení SELECT, ako je definované v SQL-92.

Pozrite si konštanty SQL_SP_* v špecifikácii ODBC.

SQL_AGGREGATE_FUNCTIONS

Bitmask enumerácia podpory agregačných funkcií.

SQL_AF_ALL

SQL_AF_AVG

SQL_AF_COUNT

SQL_AF_DISTINCT

SQL_AF_MAX

SQL_AF_MIN

SQL_AF_SUM

Pozrite si konštanty SQL_AF_* v špecifikácii ODBC.

SQL_GROUP_BY

Celočíselná hodnota, ktorá určuje vzťah medzi stĺpcami v klauzule GROUP BY a ne agregovanými stĺpcami vo vybranom zozname:

SQL_GB_COLLATE = Klauzula COLLATE môže byť zadaná na konci každého stĺpca zoskupenia.

SQL_GB_NOT_SUPPORTED = doložky GROUP BY nie sú podporované.

SQL_GB_GROUP_BY_EQUALS_SELECT = Klauzula GROUP BY musí obsahovať všetky ne agregované stĺpce vo vybranom zozname. Nemôže obsahovať žiadne iné stĺpce. NAPRÍKLAD VYBERTE DEPT, MAX (MZDA) ZO SKUPINY ZAMESTNANCOV PODĽA DEPT.

SQL_GB_GROUP_BY_CONTAINS_SELECT = Klauzula GROUP BY musí obsahovať všetky ne agregované stĺpce vo vybranom zozname. Môže obsahovať stĺpce, ktoré sa nachádzajú v zozname výberov. NAPRÍKLAD VYBERTE DEPT, MAX (MZDA) ZO SKUPINY ZAMESTNANCOV PODĽA DEPT, AGE.

SQL_GB_NO_RELATION = Stĺpce v klauzule GROUP BY a zozname výberu spolu nesúvisia. Význam nezdružených, ne agregovaných stĺpcov vo vybranom zozname závisí od zdroja údajov. NAPRÍKLAD VYBERTE DEPT, PLAT ZO SKUPINY ZAMESTNANCOV PODĽA DEPT, VEK.

Pozrite si konštanty SQL_GB_* v špecifikácii ODBC.

Na vytvorenie hodnôt bitovej masky zo zoznamu celočíselných hodnôt možno použiť nasledujúcu funkciu pomocníka:

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;

Prepísať SQLGetTypeInfo

SQLGetTypeInfo možno špecifikovať dvoma spôsobmi:

  • Pevná table hodnota, ktorá obsahuje rovnaké informácie o type ako volanie ODBC SQLGetTypeInfo .
  • Funkcia, ktorá akceptuje argument tabuľky a vráti tabuľku. Argument bude obsahovať pôvodné výsledky výzvy ODBC SQLGetTypeInfo . Implementácia funkcie môže upravovať alebo pridávať do tejto tabuľky.

Prvý prístup sa používa na úplné prepísatie hodnôt vrátených ovládačom ODBC. Druhý prístup sa používa, ak chcete pridať alebo upraviť tieto hodnoty.

Podrobnosti o formáte parametra tabuľky typov a očakávanej návratovej hodnote nájdete v odkaze na funkciu SQLGetTypeInfo.

SQLGetTypeInfo pomocou statickej tabuľky

Nasledujúci úryvok kódu poskytuje statickú implementáciu 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 pomocou funkcie

Nasledujúce úryvky kódu pridajú bpchar typ k existujúcim typom vráteným ovládačom.

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;

Nastavenie reťazca pripojenia

Reťazec pripojenia ovládača ODBC je nastavený pomocou prvého argumentu funkcií Odbc.DataSource a/alebo Odbc.Query. Hodnota môže byť text alebo M záznam. Pri používaní záznamu sa každé pole v zázname stane vlastnosťou v reťazci pripojenia. Všetky reťazce pripojenia budú vyžadovať pole Ovládač (alebo pole DSN, ak požadujete, aby používatelia predkonfigurovať DSN na úrovni systému). Vlastnosti súvisiace s poverením sa nastavia samostatne (pozri nižšie). Ďalšie vlastnosti budú špecifické pre vodiča.

Nižšie uvedený úryvok kódu zobrazuje definíciu novej funkcie zdroja údajov, vytvorenie záznamu ConnectionString a vyvolanie funkcie 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;

Riešenie problémov a testovanie

Povolenie sledovania v Power BI Desktop:

  1. Prejdite na položky Súbor > Možnosti a nastavenia > Možnosti.
  2. Vyberte položku Na karte Diagnostika.
  3. Vyberte možnosť Povoliť sledovanie.
  4. Vyberte prepojenie Otvoriť priečinok stopy (malo by byť %LOCALAPPDATA%/Microsoft/Power BI Desktop/Traces ).
  5. Odstráňte existujúce súbory sledovania.
  6. Vykonajte svoje testy.
  7. Zatvorte Power BI Desktop, aby ste sa uistili, že všetky súbory denníka sú spláchnuté na disk.

Tu sú kroky, ktoré môžete podniknúť pre počiatočné testovanie v Power BI Desktop:

  1. Zatvorte aplikáciu Power BI Desktop.
  2. Vymažte adresár sledovania.
  3. Otvorte pracovnú plochu služby Power BI a povoľte sledovanie.
  4. Pripojenie na zdroj údajov a vyberte režim priameho dotazu.
  5. Vyberte tabuľku v navigátore a vyberte položku Upraviť.
  6. Manipulujte s dotazom rôznymi spôsobmi, vrátane:
  • Vezmite prvé riadky N (napríklad 10).
  • Nastavte filtre rovnosti na rôzne typy údajov (int, string, bool ata).
  • Nastavte iné filtre rozsahu (väčšie ako, menšie ako).
  • Filter na NULL / NIE NULL.
  • Vyberte podsúbor stĺpcov.
  • Agregácia / zoskupenie podľa rôznych kombinácií stĺpcov.
  • Pridajte stĺpec vypočítaný z iných stĺpcov ([C] = [A] + [B]).
  • Zoradiť na jeden stĺpec, viacero stĺpcov. 7. Výrazy, ktoré sa nezložia, budú mať za následok výstražný panel. Všimnite si zlyhanie, odstráňte krok a prejdite na ďalšie skúšobné puzdro. Podrobnosti o príčine poruchy by sa mali do denníkov sledovania vynechať. 8. Zatvorte aplikáciu Power BI Desktop. 9. Skopírujte súbory sledovania do nového adresára. 10. Pomocou odporúčaného zošita služby Power BI analyzujte a analyzujte súbory sledovania.

Po práci jednoduchých dotazov môžete skúsiť scenáre priameho dotazu (napríklad vytváranie zostáv v zobrazeniach zostavy). Dotazy generované v režime priameho dotazu budú podstatne zložitejšie (t. j. použitie čiastkových výberov, vyhlásení COALESCE a agregácií).

Zreťazenie reťazcov v režime priameho dotazu

Motor M robí overenie základného limitu veľkosti typu ako súčasť svojej logiky skladania dotazov. Ak sa pri pokuse o zreťazenie dvoch reťazcov, ktoré potenciálne pretečú maximálnu veľkosť základného typu databázy, zobrazí chyba skladania:

  1. Uistite sa, že databáza môže podporovať konverziu na typy CLOB pri pretečení reťazca.
  2. Nastavte TolerateConcatOverflow možnosť pre Odbc.DataSource na true .

Funkcia DAX CONCATENATE momentálne nie je podporovaná rozšíreniami Power Query/ODBC. Autori rozšírení by mali zabezpečiť, aby zreťazenie reťazcov fungovalo prostredníctvom editora dotazov pridaním vypočítavaných stĺpcov ( [stringCol1] & [stringCol2] ). Keď sa v budúcnosti pridá možnosť zložiť operáciu CONCATENATE, mala by bezproblémovo pracovať s existujúcimi rozšíreniami.