Surprises Questions Learning: listopada 2010

wtorek, 30 listopada 2010

translate

Jeżeli robimy dużo zmian w tekście używając funkcji REPLACE dochodzimy do momentu w którym edycja jest możliwa wyłącznie przy odpowiednim formatowaniu kodu.

Przykładem jest funkcja konwertująca stronę kodową Mazovia (CP620) a stroną Windows-1250 napisaną przez Pawła Potasińskiego.

CREATE FUNCTION dbo.ufn_MazoviaTo1250 (@String varchar(maX))

RETURNS varchar(max)

WITH SCHEMABINDING

BEGIN

SET @String = @String COLLATE Polish_BIN;

RETURN

REPLACE (

@String, CHAR(165), CHAR(209) -- Ñ

), CHAR(143), CHAR(165) -- ¥

), CHAR(163), CHAR(211) -- Ó

), CHAR(156), CHAR(163) -- £

), CHAR(149), CHAR(198) -- Æ

), CHAR(144), CHAR(202) -- Ê

), CHAR(152), CHAR(140) -- Œ

), CHAR(160), CHAR(143) --

), CHAR(161), CHAR(175) -- ¯

), CHAR(134), CHAR(185) -- ¹

), CHAR(141), CHAR(230) -- æ

), CHAR(145), CHAR(234) -- ê

), CHAR(146), CHAR(179) -- ³

), CHAR(164), CHAR(241) -- ñ

), CHAR(162), CHAR(243) -- ó

), CHAR(158), CHAR(156) -- œ

), CHAR(166), CHAR(159) -- Ÿ

), CHAR(167), CHAR(191) -- ¿

) COLLATE database_default;

END;

Wygląda nieźle ale jakby trzeba było zamienić więcej niż 100 znaków?

Można zrobić coś takiego, żeby było łatwiej tworzymy tabelę mapowania

CREATE TABLE mapa (id INT IDENTITY(1,1), map VARCHAR(256), rep NVARCHAR(256))

Dodajemy do niej wartości kodu ascii

INSERT mapa (map,rep)

VALUES (165,209)

, (143 , 165)

, (163 , 211)

, (156 , 163)

, (149 , 198)

, (144 , 202)

, (152 , 140)

, (160 , 143)

, (161 , 175)

, (134 , 185)

, (141 , 230)

, (145 , 234)

, (146 , 179)

, (164 , 241)

, (162 , 243)

, (158 , 156)

, (166 , 159)

, (167 , 191)

I robimy funkcję

create function dbo.translate(@txt nvarchar(max))

returns nvarchar(max)

begin

SET @txt =@txt COLLATE Polish_BIN

select @txt = replace(@txt, CHAR(map), CHAR(rep))

from mapa

return @txt;

end

metody tej możemy użyc również do zamieniania dowolnych ciągów znakowych w tekście, należy zmodyfikować funkcję

alter function dbo.translate(@txt nvarchar(max))

returns nvarchar(max)

begin

select @txt = replace(@txt, map, rep)

from mapa

return @txt

end

zmieniamy dane do mapowania

DELETE FROM mapa

INSERT mapa (map,rep)

VALUES (1,'jeden*')

, (2 , 'dwa*')

, (3 , 'trzy*')

, (4 , 'cztery*')

, (5, 'pięć*')

, (6 , 'sześć*')

, (7 , 'siedem*')

, (8 , 'osiem*')

, (9 , 'dziewięć*')

, (0 , 'zero*')

testujemy

SELECT dbo.translate ('167870349076');

sobota, 20 listopada 2010

Normalizacja, pierwsza postać normalna (1FN) sobie a życie czasami sobie.
Sposobów „dobierania” się do danych oddzielonych przecinkiem, średnikiem, spacją i innymi jest wiele, jednym z dobrych pomysłów jest użycie potęgi XML.
Mamy tabelę, przykładowo dane oddzieliłem przecinkiem ale może to być inny znak.

CREATE TABLE #test (id INT IDENTITY(1,1),tekst VARCHAR(255))

INSERT #test(tekst)

SELECT'jeden,dwa,trzy,cztery,pięć,sześć,siedem,osiem,dziewięć'

UNION ALL

SELECT'112,134,12345,1234,1789,234,28'

Rozwiązanie problemu

SELECT id,

tx.parser.value('@element', 'varchar(100)')AS elementy

FROM(SELECT id,

CONVERT(XML,'<rekord element="'

+ REPLACE(tekst,',','"/><rekord element="')+'"/>')

AS parser

FROM #test)AS t

CROSS APPLY t.parser.nodes('/rekord')AS tx (parser)

Dostajemy wynik

id elementy

--------------------------

1 jeden

1 dwa

1 trzy

1 cztery

1 pięć

1 sześć

1 siedwm

1 osiem

1 dziewięć

2 112

2 134

2 12345

2 1234

2 1789

2 234

2 28

A teraz w drugą stronę

Zrobimy tabelę #test2

SELECT id,

tx.parser.value('@element', 'varchar(100)')AS elementy

into #test2

FROM(SELECT id,

CONVERT(XML,'<rekord element="'

+ REPLACE(tekst,',','"/><rekord element="')+'"/>')

AS parser

FROM #test)AS t

CROSS APPLY t.parser.nodes('/rekord')AS tx (parser)

SELECT * FROM #test2

id elementy

----------- ----------

1 jeden

1 dwa

1 trzy

1 cztery

1 pięć

1 sześć

1 siedem

1 osiem

1 dziewięć

2 112

2 134

2 12345

2 1234

2 1789

2 234

2 28

Zapytnie

SELECT p1.id,

( SELECT elementy + ','

FROM #test2 p2

WHERE p2.id = p1.id

ORDER BY id

FOR XML PATH('') ) AS tekst

FROM #test2 p1

GROUP BY id

lub

SELECT DISTINCT id,tekst

FROM #test2 p1

CROSS APPLY ( SELECT elementy + ','

FROM #test2 p2

WHERE p2.id = p1.id

ORDER BY id

FOR XML PATH('') ) D ( tekst )

Wynik

id tekst

----------------------------------------------------------------------

1 jeden,dwa,trzy,cztery,pięć,sześć,siedem,osiem,dziewięć,

2 112,134,12345,1234,1789,234,28,

wtorek, 16 listopada 2010

dual

Tabela dual w bazie Oracle jest tabelą w schemacie SYS do którego uprawnienia odczytu na rola PUBLIC wprowadzonym by pomóc rozwiązać problemy z zapytaniami nie operującymi na zbiorach. Oracle inaczej niż SQL Server nie pozwala na użycie klauzuli SELECT bez FROM.

Używamy jej gdy chcemy wyciągnąć zmienne systemowe, pobrania nowej wartości sekwencji i wielu innych miejscach.

SQL> select sysdate from dual;

SYSDATE

-------------------

2010-11-15 15:22:13

SQL> select user from dual;

USER

------------------------------

SYS

Tabelę jako taka możemy obejrzeć przy pomocy DESC

SQL> desc dual;

Name Null? Type

---------- -------- --------------

DUMMY VARCHAR2(1)

Możemy również obejrzeć jej zawartość

SQL> select * from dual;

Jednym z podstawowych założeń jest fakt że tabela dual ma jeden rekord w kolumnie DUMMY

SQL> select count(*) from dual;

COUNT(*)

----------

O tyle ciekawym wydaje się fakt, że bez większych problemów możemy go usunąć lub dodać następne a nawet zmienić samą tabelę co nie jest dobrą praktyką.

Po dodaniu wiersza do tabeli

SQL> select count(*) from dual;

COUNT(*)

----------

Ale musimy się liczyć z błędami

ORA-01422: exact fetch returns more than requested number of rows

Podczas operacji pozornie nie związanymi z ta tabela np

SQL> Drop table test

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows

Ciekawostką jest to że tabela dual jest dostępna również przed otwarciem bazy ale zwraca inne wyniki

SQL> select * from dual;

ADDR INDX INST_ID D

-------- ---------- ---------- -

0A6231A7 0 1 X

niedziela, 14 listopada 2010

management studio

Ciekawostka, miałem do wgrania plik sql zapisany na dysku sieciowym, link do pliku dostałem pocztą. Otworzyłem plik i Microsoft SQL Server Management Studio zakomunikował mi o krytycznym błędzie i się zamknął.

Czasami się zdarza, może za dużo otwartych obiektów. Otworzyłem plik jeszcze raz studio otworzyło swoje GUI i znowu nakrzyczało błędem, spróbowałem inny skrypt, poszło bez problemu. Co może być w takim skrypcie co wywala mi studio, podejrzałem plik w notatniku nie było w nim nic podejrzanego, zwykły alter obiektu bazy. Może to moje środowisko z zainstalowanym np. SQL PROMPT firmy Redgate i innymi pomocnymi dodatkami, uruchomiłem skrypt z innej stacji. Tym razem dostałem jaśniejszy komunikat

Okazało się, że nazwa razem ze ścieżką miała ponad 260 znaków. Bez tego komunikatu nie było to takie oczywiste.

czwartek, 11 listopada 2010

tabela tymczasowa i jej spid z trace

Inna metoda znalezienia sesji (SPID) , na której została utworzona tabela tymczasowa bazuje na monitorowaniu SQL Servera za pomocą trace. Z tego samego źródła możemy korzystać jeżeli potrzebujemy informację kto zmienił obiekty w bazie lub je skasował, zmienił ustawienia serwera itp.

Do poszukiwania zdarzeń należy odfiltrować odpowiednie eventy, listę wszystkich możemy znaleźć zapytaniem

SELECT e.trace_event_id,e.name,c.name category

FROM sys.trace_events e INNER JOIN

sys.trace_categories c ON e.category_id = c.category_id

dla tworzenia nowych obiektów będzie to trace_event równy 46

CREATE PROC dbo.rk_get_spid_temp @name varchar(128)=''

DECLARE @FileName VARCHAR(MAX)

SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'

FROM sys.traces

WHERE is_default = 1;

SET @name =@name+'%'

SELECT

o.name,

o.OBJECT_ID,

o.create_date,

gt.NTUserName,

gt.HostName,

gt.SPID,

gt.DatabaseName,

gt.TEXTData

FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt

JOIN tempdb.sys.objects AS o

ON gt.ObjectID = o.OBJECT_ID

WHERE gt.DatabaseID = 2

AND gt.EventClass = 46

AND o.create_date >= DATEADD(ms, -100, gt.StartTime)

AND o.create_date <= DATEADD(ms, 100, gt.StartTime)

AND o.name LIKE @name

Procedura wywołana z parametrem zwraca dane dotyczące konkretnej tabeli , bez parametru zwraca informacje o wszystkich tabelach tymczasowych