technika – archiv – Feb 2011
ODBC, Windows a 32/64bit - 23. 2. 2011
systém Windows je. s tím už se nedá nic moc dělat. občas dokonce je ve dvou verzích, o mocnosti 32 nebo 64 bitů. ODBC je trochu podivná a staromódní možnost, jak se dostat k nějakým datům. co se verzí týče, je na tom ODBC podobně – jak 32, tak 64 bitů. i tak je v tom dost bordel.
v 32 bitové verzi Windows, řekněme třeba Windows 7, hnízdí 32 bitová verze ODBC. jeho služeb dokáže využít každá 32 bitová aplikace, která o to projeví zájem. v dávných, dnes již zapomenutých dobách, už ke zdvojení mocnosti datové sběrnice jednou došlo a tak služeb 32 bitového ODBC mohou využívat i aplikace, kterým k životu postačí trapných 16 bitů.
součástí 64 bitové verze Windows je (a nikoho to jistě moc nepřekvapí) 64 bitové ODBC. jeho výsostných služeb mohou však využívat jen 64 bitové aplikace. aby 32 bitová lůza nepřišla úplně zkrátka, obsahují 64 bitové Windows i 32 bitové ODBC. je však v systému zdařile maskované.
64 bitové ODBC se, pravděpodobně díky vlastní neflexibilitě, nachází tam, kde 32 bitové v 32 bitových Windows. kromě stejného místa v registrech jde i o stejný adresář a stejný název souboru jeho konfiguračního nástroje. ten potřebujete k přidání DSN. spustit se dá buď z ovládacích panelů, nebo přímo spuštěním:
%WINDIR%\system32\odbcad32.exe
soubor se skutečně jmenuje odbcad32.exe a jde o 64 bitovou aplikaci. je už jen dotažením logiky, že leží v adresáři system32. konfigurační nástroj pro 32 bitové ODBC už z ovládacích panelech najít nelze, ale dá se přímo spustit odtud:
%WINDIR%\SysWOW64\odbcad32.exe
tentokrát jde o 32 bitovou aplikaci a její umístění v adresáři SysWOW64 už je jen třešničkou na tomhle dortíku. DSN nakonfigurovné tímto nástrojem je pro 32 bitové aplikace viditelné a blahosklonně použitelné.
záznam v registrech je o něco lepší. nastavení 64 bitových DSN lze najít zde:
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
a pro 32 bitové je třeba sáhnout sem:
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\ODBC Data Sources]
problém je jen u systémových DSN, uživatelských se to netýká. vše je odzkoušeno na Windows 7, pro někeré 64 bitové verze Windows to však zřejmě neplatí, 32 bitové ODBC v nich chybí (nejspíš XP Pro, ověřeno to nemám).
něco o tom je možno se dopátrat i zde: http://support.microsoft.com/kb/942976/en-us
INSERT EXEC - 4. 2. 2011
An INSERT EXEC statement cannot be nested. T-SQL chyba číslo 8164, která mě dokáže vytočit. proč v MS SQL Serveru tohle trapné omezení je ? odpověď na tuto záludnou otázku znám, ale neobešla by se bez hodně sprostých slov (např. "sráči" je v ní minilámně 4x) a tak raději zkusím odpovědět na otázku jinou – dá se to nějak obejít ? čtenáři, kteří se o podrobnosti nezajímají, mohou skočit rovnou na konec článku. jedno univerzální řešení tam je.
o co přesně jde ? v T-SQL existuje užitečná kombinace příkazů INSERT a EXEC, která umožní vložit data z uložené procedury do tabulky. data pak lze dále zpracovávat přímo na serveru, což jinak s výstupem z uložené procedury dělat nelze. problém je ale v tom, že v rámci jednoho volání procedury lze tuto vymoženost použít jen jednou. pokud se v proceduře, která je volána pomocí INSERT EXEC, vyskutuje jiný INSERT EXEC, zahlásí SQL server tuto milou zprávu:
Msg 8164, Level 16, State 1, Procedure PPP, Line XXX An INSERT EXEC statement cannot be nested.
tak tedy – dá se to nějak obejít ? existují dvě skupiny řešení. řešení z té první skupiny vyžadují přepsání původního kódu uložené procedury – jinými slovy, vyhnout se kombinaci INSERT EXEC. jednou z možností je přepsat proceduru, z které data primárně pořizujeme, na funkci vracející tabulku. místo kombinace INSERT EXEC pak lze použít bezproblémové INSERT SELECT:
insert into tabulka select * from dbo.moje_funkce(parametr1, parametr2, ...)
funkce má však oproti proceduře hromadu omezení a tak toto řešení není zrovna univerzální. je ale poměrně čisté a tak se může někdy hodit. další možností je neposílat data z uložené procedury na výstup, ale uložit je do nějaké tabulky – třeba temporární a ani nemusí být globální (lokální tabulka je vidět v rámci jedné session a volání procedur, byť vnořené, v jedné session probíhá). pak probíhá volání nějak takhle:
create #tabulka (col1 int, col2 varchar(128)) exec ulozena_proceruda parametr1, parametr2, ... -- v procedure je realizovano plneni tabulky #tabulka napr. pomoci INSERT
tohle je už řešení univerzálnější, ale zase není tak elegantní a vyžaduje práva pro zakládání temporárních tabulek. řešení lze i modifikovat na použítí běžné, ne temporární tabulky, ale pak je třeba řešit multiuser provoz a v tabulce si označovat, která data patří ke kterému volání.
další možností je použít výstupní parametry procedury. jelikož se bavíme o procedurách, které vracejí tabulku, jde o větší objem dat a pochopitelně nemá smysl rozšiřovat proceduru o dalších 7845 parametrů, nehledě na to, že počet parametrů procedury je omezen na 2100. samozřejmě, data lze narvat do jednoho stringu nebo binárky, ale to není moc elegantní a taky se s tím nepracuje nejlépe (i když ukládání do binární proměnné už jsem jednou použil, ale v jiném kontextu). zbývají tedy jen dva typy – cursor a XML. použití je jasné – po zavolání procedury otevřu nějakým cursorem data a ty pak načtu, nebo si data předám pomocí XML struktury. případ cursor by mohl vypadat asi takhle:
create procedure procedura @parametr int, @kurzor cursor varying output as set @kurzor = cursor local for select * from type_instr open @kurzor go -- volani je nasledujici declare @kurzor cursor exec procedura parametr1, @kurzor output fetch next from @kurzor into ... while @@fetch_status begin ... end close @kurzor
parametr @kurzor musí být deklarován jako varying (význam viz. MS help, přeji pěkné počtení) a samozřejmě jako output a musí být otevřen už ve volané proceduře. volající ho pak jen projede řádek po řádku a zavře. nevýhoda je jasná – nutnost data prohánět smyčkou a zpracovávat každý řádek zvlášť. použít XML strukturu je o něco pohodlnější:
create procedure procedura
@parametr int,
@data XML output
as
select @data = (select sloupec1, sloupec2, ... from tabulka for XML raw('data'))
go
-- volani je nasledujici
declare @data XML
exec procedura parametr1, @data output
select c.value('@sloupec1', 'varchar(32)'),
c.value('@sloupec2', 'int'),
...
from @data.nodes('/data') as t(c)
řešení z první skupiny jsou vyčerpána, tak hurá na skupinu drouhou. ta zahrnuje řešení univerzální, která se obejdou bez modifikace uložené procedury. kombinace INSERT EXEC v ní klidně může zůstat. prvním řešením je napsat si v .NOTu (moje přezdívka pro .NET) CLR proceduru. ta bude velice jednoduchá – prostě zavoláme naší proceduru, která používá INSERT EXEC, data uchováme v nějakém vhodném datasetu a ten pak vrátíme jako výstup CLR procedury. tento výstup už jde dále pomocí INSERT EXEC zpracovávat. tenhle článek má být ale primárně o T-SQL a tak je tu ještě druhé řešení – použití OPENROWSET:
declare @openr varchar(max)
set @openr = 'exec procedura '+ parametr1 + ', ' + parametr2 + ', ' + …
set @openr = 'select * from openrowset(''SQLNCLI'',
''SERVER=muj_server;DATABASE=moje_databaze;Trusted_Connection=Yes'',
''' + replace(@openr, '''', '''''') + ''')'
insert into tabulka
exec (@openr)
OPENROWSET totiž spustí dotaz externě – pro SQL server se tváří jako dotaz z venku. tím se volaní oddělí, nedojde k vnoření a tudíž ani k vyvolání chyby 8164. samotný OPENROWSET ale řešením není. neumožňuje totiž jako parametry používat proměnné, musí být zapsány jako text, součást zdrojového kódu. je tedy nutné si nejdříve select s OPENROWSETem sestavit. abych nemusel přemýšlet nad počtem apostrofů, uložím do proměnné nejdříve T-SQL příkaz pro spuštění procedury a pak následně sestavím T-SQL příkaz pro OPENROWSET, v němž pomocí replace všechny apostrofy zdvojím. jde vlastně o T-SQL ve stringu obsahující T-SQL ve stringu, takže zdvojování je nutné (+ další zdvojování na úrovni zdrojáku, to je ale legrace).
nezbývá mi než připomenout, že toto řešení je neskutečná prasárna a vlastně by se nemělo vůbec používat. jenže… co když už nic jiného nezbývá ?
XML jako tabulka - 3. 2. 2011
internet je plný různých příkladů, jak tabulkově orientovaná data dostat z XML do SQL databáze. já ale musel řešit jiný problém – jak dostat XML do tabulky tak, aby v ní nebyla jen data, ale celý XML soubor, pěkně řádek po řádku.
typické XML, které musím zpracovávat, vypadá nějak tako:
<?xxml version="1.0" encoding="UTF-8"?><VYDANI><IDENTIFIKACE><DOKUMENT KOD="XML-structure"/> <VERZE>700778</VERZE><FUNKCE KOD="Ukázka"/><DATUM>20110203</DATUM></IDENTIFIKACE><VYKAZ><INF O><SUBJEKT>001</SUBJEKT><ROZSAH>XS-44</ROZSAH><DATUM>20110131</DATUM></INFO></VYKAZ><DATA><R ADEK PORADI="1"><SLOUPEC PORADI="1">1101000001</SLOUPEC><SLOUPEC PORADI="3">CZ</SLOUPEC><SLO UPEC PORADI="4">1667064</SLOUPEC><SLOUPEC PORADI="5">08</SLOUPEC></RADEK><RADEK PORADI="2">< SLOUPEC PORADI="1">1101000002</SLOUPEC><SLOUPEC PORADI="3">CZ</SLOUPEC><SLOUPEC PORADI="4">1 667064</SLOUPEC><SLOUPEC PORADI="5">08</SLOUPEC></RADEK><RADEK PORADI="3"><SLOUPEC PORADI="1 ">1101000003</SLOUPEC><SLOUPEC PORADI="3">CZ</SLOUPEC><SLOUPEC PORADI="4">1667064</SLOUPEC>< SLOUPEC PORADI="5">08</SLOUPEC></RADEK><RADEK PORADI="4"><SLOUPEC PORADI="1">1101000004</SLO UPEC><SLOUPEC PORADI="3">CZ</SLOUPEC><SLOUPEC PORADI="4">1643064</SLOUPEC><SLOUPEC PORADI="5 ">08</SLOUPEC></RADEK><RADEK PORADI="5"><SLOUPEC PORADI="1">1101000005</SLOUPEC><SLOUPEC POR ADI="3">CZ</SLOUPEC><SLOUPEC PORADI="4">1466932</SLOUPEC><SLOUPEC PORADI="5">08</SLOUPEC></R ADEK><RADEK PORADI="6"><SLOUPEC PORADI="1">1101000006</SLOUPEC><SLOUPEC PORADI="3">CZ</SLOUP EC><SLOUPEC PORADI="4">1466932</SLOUPEC><SLOUPEC PORADI="5">08</SLOUPEC></RADEK><RADEK PORAD I="7"><SLOUPEC PORADI="1">1101000007</SLOUPEC><SLOUPEC PORADI="3">CZ</SLOUPEC><SLOUPEC PORAD I="4">1666800</SLOUPEC><SLOUPEC PORADI="5">08</SLOUPEC></RADEK><RADEK PORADI="8"><SLOUPEC PO RADI="1">1101000008</SLOUPEC><SLOUPEC PORADI="3">SK</SLOUPEC><SLOUPEC PORADI="4">2670004</SL OUPEC><SLOUPEC PORADI="5">08</SLOUPEC></RADEK></DATA></VYDANI>
není ani formátované, žádné konce řádků. zpracovat to samozřejmě jde, ale pro lidské bytosti je to trochu nečitelné. kromě zmíněného přesunu do tabulky je tedy nutné provést i nějaké to formátování. požadovaný výstup vypadá takto (je ho jen kousek):
jak toho dosáhnout ? nejdříve jsem to zkusil přístupem klasickým – T-SQL proměnou typu XML jsem přetypoval na varchar(max). vzniklý text jsem rozsekal, přidal tabelátory a každý řádek nacpal do tabulky. celá akce byla ale neskutečně pomalá – XML o velikosti 1 MB to parsovalo 5 minut. holt T-SQL jazyk není zrovna ideální na zpracovávání textu, natož textu velkého. zkusil jsem tedy jinou metodu – rozebrat XML pomocí XQuery prostřednictvím funkcí datového typu. jelikož XQuery dotazy vrací jako typ XML, nejde celá akce udělat najednou. navíc, jak jsem po bližším zkoumání zjistil, implementace XQuery v podání Microsoftu je méně než polovičatá. celý kód jsem pojal jako fukci, ne uloženou proceduru – pro mojí potřebu se ukázala výhodnější, i když T-SQL user-defined funkce jsou také polovičaté až hrůza. zde je:
create function ftbl_XMLI(@xml as XML, @i as int)
returns @ret table(row int identity(1,1) unique, text varchar(1024))
as begin
-- vraci XML promennou jako tabulku, kde jednotlive radky odpovidaji radkum xml souboru
-- jeden radek souboru je omezen na 1024 znaku
-- pouzit varchar(max) by bylo univerzalnejsi, ale pomalejsi, na velkych xml souborech
-- je to znat
-- zalozime tabulku pro mezivysledek, jendu promenou pro drzeni aktualniho node a
-- druhou pro jeho textovou podobu
declare @buf table(text varchar(1024))
declare @node XML,
@ntxt varchar(1024)
-- pokud node obsahuje nejake komentare nebo instrukce, vyplivneme je na zacatek
set @ntxt = cast(@xml.query('processing-instruction()') as varchar(1024))
if @ntxt <> ''
insert into @ret (text)
values (@ntxt)
set @ntxt = cast(@xml.query('comment()') as varchar(1024))
if @ntxt <> ''
insert into @ret (text)
values (@ntxt)
-- vyselectujeme vsechny rootove nody
-- XQuery "*" nam vrati kazdy element, ktery najde v rootu, jako samostatny radek
declare cxml cursor local for
select node.col.query('.')
from @xml.nodes('*') as node(col)
open cxml
-- nacteme 1. element
fetch next from cxml into @node
while @@fetch_status = 0 begin
-- zavolanim sebe sama provedeme vypis dalsich elementu
-- XQuery "*[1]" vrati prvni element v rootu (je tam vzdy jen jeden)
-- "/*" pak vsechny jeho sub-elementy, ktere se tak predaji do rekurze
delete @buf
insert into @buf (text)
select text
from ftbl_XMLI(@node.query('*[1]/*'), @i + 1)
if @@rowcount = 0 begin
-- pokud se nic nevratilo, jsem na nejnizsim levelu a staci element
-- jen vypsat a opravit pouze drobny prohresek proti standardu
set @ntxt = rtrim(cast(@node as varchar(1024)))
if right(@ntxt, 2) = '/>'
set @ntxt = left(@ntxt, len(@ntxt) - 2) + ' />'
insert into @ret (text)
values (space(@i * 2) + @ntxt)
end else begin
-- pokud se nam neco vratilo, musime nejdrive vlozit tag zacatku elementu
-- se vsemi jeho attributy, pak vlozit obsah elementu a uzavrit to tagem konce
-- prijde mi zbytecne vytahovat attributy pomoci XQuery, pracovat s elementem
-- jako s textem bude jednoduzsi
-- v @node je ale ulozen cely element, ktery bude rozhodne delsi nez
-- zvolena delka 1 radku, takze ho trochu orezeme
-- provedme to tak, ze z elementu pomoci XQuery vymazeme vnitrek
-- (nepotrebujeme ho, uz je ulozen v tabulce @buf)
-- a pak jen rozparsujeme to, co se nam vrati jako text
set @node.modify('delete *[1]/*')
set @ntxt = cast(@node as varchar(1024))
insert into @ret (text)
values (space(@i * 2) + replace(@ntxt, '/>', '>'))
insert into @ret (text)
select text
from @buf
insert into @ret (text)
values (space(@i * 2) + '')
end
-- hura, jdeme na dalsi element
fetch next from cxml into @node
end
-- trochu po sobe uklidime a skoncime
close cxml
deallocate cxml
return
end
parametry funkce jsou dva – do prvního se vejde XML, které chceme zkonvertovat, druhý je používán při rekurzi. jeho hodnota se při každém vnoření zvedne o jednu. parametr je zároveň použit pro odsazení řádku. návratovou hodnotou funkce je klasická tabulka, která má definované dva sloupce. první sloupec se jmenuje row a je definován jako int. obsahuje čísla řádků a je tam proto, že se mi hodí. v jeho definici je mimo jiné použito klíčové slovo unique. jde o takovou malou fintu, s jeho pomocí lze držet paměťovou tabulku indexovanou. index lze pak využít například při třídění nebo rovnání, práce s tabulkou bude rychlejší. druhý sloupec se fádně jmenuje text, je typu varchar(1204) a je v něm jeden řádek převáděného XML. jako typ mohl být i varchar(max) nebo třeba nvarchar(max), ale mě 1 KB na řádek bohatě stačí.
na začátku funkce jsou založeny dvě proměnné. ta první, tabulková, je používána jako buffer na rozebraní jednoho XML elementu. ta druhá, typu XML, drží XML element, jak typ napovídá, v XML tvaru. po založení proměnných se funkce mrkne, jestli v XML elementu nejsou nějaké komentáře či jiná non-XML data a pokud ano, hned je uloží na výstup. následuje kurzor, který projede všechny elementy v nejvyšší úrovni přijatého XML, pěkně řádek po řádku. rozklad na řádky zajistí funkce nodes, která vyrobí sloupec col vr virtuální tabulce pojmenované node. pomocí funkce query je pak ze sloupce col vytažen XML element. kdyby byl sloupec typu XML, jak uvádí MS help, asi by to nebylo nutné, jenže on je typ XML tak nějak napůl. už si pomalu zvykám.
následující smyčka dělá hlavní práci. nejdříve jsou do bufferové tabulky načteny elementy, řazené pod elementem, který se zrovna projíždí. to je řešeno rekurzí – funkce prostě zavolá samu sebe, jen se pomocí XQuery posune na nižší úroveň v XML struktuře. pokud ani tato úroveň není nejnižší, rekurze se prohloubí. vnořování skončí v případě, že element už nemá žádné podelementy. technicky to zajistí smyčka, která neproběhne, protože kurzor, na který je navázána, nevrátí žádné řádky. po naplnění pomocné tabulky následuje rozhodování, zda se něco vrátilo nebo ne a na základě toho se provede uložení elementu do výstupní tabulky už v podobě textu.
téhle funkci trvá necelou minutu, než 1 MB velké XML rozparsuje. to je hodně mizerný výsledek a v žádném případě s ním nejsem spokojen. jenže v mé situaci je lepší pomalá funkčnost, než žádná a je to přecijen lepší, než verze s parsováním textu. nejde mi to do hlavy, protože parsovaní přes text je rozhodně méně náročné na prostředky, ale tak to prostě je. pro úplnost, kód textového parsování vypadá takto:
create function [dbo].[ftbl_XML](@xml xml)
returns @ret table(row int identity(1,1), text varchar(max))
as begin
-- vraci XML jako textovy vystup, radek po radku, odsazeny
-- jeho nevyhoda ale je, ze neni zrovna z nejrychlejsich
declare @rows varchar(max),
@row varchar(max),
@chari int,
@charx int,
@chart int = 0
-- zkonvertujeme xml na varchar a jedem na to
set @rows = convert(varchar(max), @xml)
set @chari = isnull(charindex('><', @rows, 0), 0)
while @chari <> 0 begin
--nacteni jednoho radku
set @row = left(@rows, @chari)
--posunuti na dalsi radek
set @rows = substring(@rows, @chari+1, 4294967295)
set @chari = isnull(charindex('><', @rows, 0), 0)
--odsazovani, v @chart je pocet odsazeni, v @row je co se tiskne
set @charx = 0
if @row like '%<[^?/]%[^?/]>%'
set @charx = @charx + 1
if @row like '%%'
set @charx = @charx - 1
if @charx < 0
set @chart = @chart + @charx
if @chart < 0
set @chart = 0
--vlozeni radku do tabulky
insert into @ret (text)
values (space(2*@chart)+@row)
-a jdeme na dalsi krok
if @charx > 0
set @chart = @chart + @charx
end
insert into @ret (text)
values (@rows)
return
end
funkce dělá totéž, co ta předchozí, jen to nebere rekurzí, ale postupným rozebráním XML v textové podobě. následující příklad ukazuje použití funkce (pro obě funkce je stejné). místo
declare @xml XML = 'tady je XML '
select text as line
from ftbl_XMLI(@xml, 0)
order by row