PL/SQL Oracle wprowadzenie

Język PL/SQL jest językiem stosowanym w bazach firmy Oracle.
Stosuje się go z kilku przyczyn:

• niektóre zapytania SQL łatwiej w nim wyrazić
• duża wydajność 
• dostępne mechanizmy, których normalnie brakuje w zwykłym SQLu
• stałe, zmienne
• struktury sterujące
• wyjątki
• w przypadku używania baz Oracle kod jest przenaszalny pomiędzy instancjami bazy
• możliwość tworzenia pakietów funkcji ogólnych, które następnie można wykorzystać w wielu bazach danych

Podstawową jednostką w PL/SQL jest blok anonimowy, który ma następującą postać:

Code:

DECLARE
//stałe zmienne

BEGIN
//instrukcje

EXCEPTION
//obsługa wyjątków

END;

Zmienne
Umożliwiają przechowywanie danych, np. wyników zapytań. Deklarujemy je w sekcji DECLARE. W zależności od potrzeby mamy zmienne:

• proste - typy podstawowe
• złożone - rekord, tablica

Można także zadeklarować wartość domyślną dla zmiennej, oraz to czy może ona przyjmować wartości puste - NULL. Kilka przykładów definiowania zmiennych:

Code:

DECLARE
nazwa_zmiennej NUMERIC; //zainicjowanie wartością pustą

nazwa_zmiennej NUMERIC := 10; //zainicjowanie wartością

nazwa_zmiennej NUMERIC := DEFAULT 10; //zainicjowanie wartością //zainicjowanie wartością z określeniem wartości domyślnej

nazwa_zmiennej NUMERIC := DEFAULT 10; //zainicjowanie wartością //zainicjowanie wartością z określeniem wartości domyślnej, nie mogącej przyjąć wartość NULL

Zmienne rekordowe są podobne do rekordów znanych z języków programowania (C++, C# itp). Deklarujemy rekord który może przechowywać kilka różnych typów danych, dostęp do poszczególnych typów odbywa się poprzez ".". Przykład:

Code:

DECLARE
TYPE Osoba IS RECORD
(
Id INT,
Imie VARCHAR2(50),
Nazwisko VARCHAR2(50)
);
jacek Osoba;
BEGIN
jacek.Id := 10;
jacek.Imie := 'Jacek';
jacek.Nazwisko := 'Kowalski';
END

Stałe
Jak można się domyślić są to zmienne którym przypisujemy wartość i jej nie zmieniamy. Deklaracja:

Code:

DECLARE
pi CONSTANT NUMERIC := 3.14;

Dynamiczne dodanie elmentów do Filtru typu ComboBox w Gridzie

Korzystając z RadGrida możemy stworzyć szablon filtrowania dla kolumny. Jeżeli chcemy aby zamiast zwykłego TextBoxa można było wybrać wartość z ComboBoxa bardzo łatwo to osiągnąć.
Zobaczmy na przykład:

Aby zmienić filtr kategorii ze zwykłego TextBoxa na np. ComboBox możemy posłużyć się poniższym kodem:

Code:

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <telerik:RadScriptManager ID="RadScriptManager1" runat="server">
    </telerik:RadScriptManager>
    <telerik:RadGrid ID="rgProducts" runat="server" DataSourceID="lqProducts"
    AllowPaging="True" PageSize="10" AutoGenerateColumns="False" AllowFilteringByColumn="True"
    EnableLinqExpressions="True" onitemcommand="rgProducts_ItemCommand" onitemdatabound="rgProducts_ItemDataBound" 
    >
        <MasterTableView>
            <Columns>
                <telerik:GridBoundColumn UniqueName="ProductName" SortExpression="ProductName" 
                HeaderText="Nazwa" DataField="ProductName" DataType="System.String" />
                <telerik:GridBoundColumn UniqueName="ProductModel" SortExpression="ProductModel" 
                HeaderText="Model" DataField="ProductModel" />
                <telerik:GridBoundColumn UniqueName="ProductCategoryName" SortExpression="ProductCategoryName" 
                HeaderText="Kategoria" DataField="ProductCategoryName">
                    <%-- 
                    <FilterTemplate>
                        <telerik:RadComboBox runat="server" ID="rcbCategoryName"></telerik:RadComboBox>
                    </FilterTemplate>
                    --%>
                </telerik:GridBoundColumn>
            </Columns>
        </MasterTableView>
    </telerik:RadGrid>
    <asp:LinqDataSource runat="server" ID="lqProducts" 
        onselecting="lqProducts_Selecting"></asp:LinqDataSource>

W code behind umieszczamy następujący kod:

Code:

protected void lqProducts_Selecting(object sender, LinqDataSourceSelectEventArgs e)
        {
            var adventureWorksLt2012Entities = new AdventureWorksLT2012Entities();
            e.Result = adventureWorksLt2012Entities.Products.Select(x => new { x.ProductID, ProductName = x.Name, ProductModel = x.ProductModel.Name, ProductCategoryName = x.ProductCategory.Name }).OrderBy(x => x.ProductID);
        }

        protected void rgProducts_ItemCommand(object sender, GridCommandEventArgs e)
        {
        }

        protected void rgProducts_ItemDataBound(object sender, GridItemEventArgs e)
        {
            if (e.Item is GridFilteringItem)
            {
                var filterItem = (GridFilteringItem)e.Item;
                var categoriesComboBox = (RadComboBox)filterItem.FindControl("rcbCategoryName");// accessing RadComboBox in FilterTemplate
                var adventureWorksLt2012Entities = new AdventureWorksLT2012Entities();
                foreach (var consultant in adventureWorksLt2012Entities.Products.GroupBy(x => x.ProductCategory).Select(x => new { x.Key.Name, x.Key.ProductCategoryID }))
                {
                    categoriesComboBox.Items.Add(new RadComboBoxItem(consultant.Name, consultant.ProductCategoryID.ToString()));
                }
            }
        }

PostgreSql - konfiguracja ODBC na Windows 7

W przypadku Windows 7 x64.
Aby stworzyć połączenie via ODBC dla bazy PostgreSql należy kolejno przejść do:

1. Panel Sterowania
2. Narzędzia administracyjne
3. Źródła danych (ODBC)

 Ukaże się nam następujący ekran:

Wybieramy zakładkę System DSN i klikamy na dodaj:

Okazuje się że na liście nie ma bazy PostgreSql. Dzieje się tak, dlatego iż w systemie są dwie aplikacje umożliwiające dodawanie połączenia ODBC.
Prawidłowy kreator do dodawania znajduje się w lokalizacji:

C:\Windows\SysWOW64\odbcad32.exe

Uruchamiamy narzędzie i już teraz na liście widnieje odpowiedni sterownik:

Klikamy na zakończ, teraz ukazuje się ekran na którym konfigurujemy połączenie do bazy danych:

Po zakończeniu konfiguracji klikamy na zakończ.

Aby następnie  w aplikacji .NET można było się połączyć do tak skonfigurowanego DSNa, należy w App.config/Web.config wstawić następujący ConnectionString:

Code:

 <connectionStrings>
  <add name="DataBaseConnectionString" connectionString="Dsn=Dsn_Name" providerName="System.Data.Odbc"/>
 </connectionStrings>

Kody statusów HTTP

Kody statusów mówią w jaki sposób zostało zrealizowane zapytanie klienta. Ze względu na informacje które przekazują możemy je podzielić na 5 kategorii:

• 100 - 199 - kody zawierające informacje (np. informacja o przekroczonym czasie połączenia)
• 200 - 299 - kody powodzenia 
• 300 - 309 - kody przekierowania
• 400 - 499 - błędy aplikacji
• 500 - 599 - błędy serwera

Kilka statusów z którymi możemy się najczęściej spotkać:

• 200 - wszystko przebiegło pomyślnie
• 301 - zawartość przeniesiona w inne miejsce, nie sprawdzaj więcej tej lokalizacji
• 302 - zawartość przeniesiona w inne miejsce, sprawdź to miejsce
• 304 - zawartość nie zmieniła się od ostatniej wizyty
• 400 - błędna składnia zapytania
• 401 - nieautoryzowane zapytania (być może trzeba się najpierw zalogować)
• 403 - dostęp odrzucony
• 404 - zasób nie istnieje
• 500 - błąd wewnętrzny serwera - np. w aplikacji został rzucony wyjątek lub też problem z samym oprogramowaniem zainstalowanym na serwerze
• 503 - serwer niedostępny - może zostać wysłany np. w przypadku dużego obciążenia serwera

Id autoinkrementowanego pola w bazie

Zdarza się często, iż musimy pobrać id ostatnio wstawionego wiersza w tabeli. Dla różnych serwerów bazodanowych, pobranie tej wartości następuje w inny sposób.
Swoją drogą szkoda że w SQLu nie ma jednej standaryzowanej metody pobierania id wstawionego rekordu. Przez to niestety musimy dla każdej bazy konstruować inny rodzaj inserta.

ORACLE.

Tutaj sprawa jest bardzo prosta i wszystko właściwie załatwia jedno słowo kluczowe - returning:

Code:

INSET INTO Table(Id, FirstName) VALUES(MySeq.nextval, 'Marek') RETURNING Id INTO @id

MS SQL

Tutaj sprawa jest pozornie bardziej skomplikowana. Nie ma takiego wygodnego sposobu jak w Oracle zwrotu wartości pola po wykonanym Insercie.
Funkcją, która zwraca Id jest tutaj SCOPE_IDENTITY(). Tak więc po każdym insercie należy zawołać tę funkcję a ona zwróci numer. Należy pamiętać o jednym bardzo ważnym aspekcie: instrukcja insert i później select muszą być wykonane inline, czyli jedna po drugiej, w przeciwnym razie zamiast oczekiwanego id otrzymamy nulla:

Code:

INSET INTO Table(Id, FirstName) VALUES(MySeq.nextval, 'Marek') RETURNING Id INTO @id

Kolejne bazy będą dodawane wraz z zapotrzebowaniem na zapytania :)
A może macie już gotowe instrukcje pobierania id? Czekam na propozycje w komentarzach.

SQLITE

W tej bazie znam dwa sposoby na znalezienie ostatnio wstawianej wartości. Jeżeli w tabeli istnieje pole autoinkrementowane możemy skorzystać z instrukcji:

Code:

SELECT seq FROM sqlite_sequence WHERE name='TableName'

MS SQL: cycles or multiple cascade paths

Często możemy spotkać się z błędem który brzmi w następujący sposób:

Msg 1785, Level 16, State 0, Line 55
Introducing FOREIGN KEY constraint 'FK_Comments_Users' on table 'Comments' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 55
Could not create constraint. See previous errors.

Co jest powodem tego błędu?
Najłatwiej rozważyć prostą sytuację:

Rozważać właściwie będziemy tylko trzy tabele Users, Articles oraz Comments.
Z diagramu wynika że użytkownik może mieć wiele komentarzy jak i artykułów. Artykuł może mieć wiele komentarzy i jednego autora. Tworząc klucze obce wydawało by się, że prawidłowym rozwiązaniem byłoby stworzenie następujących kluczy:

ALTER TABLE dbo.Articles
   ADD CONSTRAINT FK_Articles_Users FOREIGN KEY(UserId)
   REFERENCES dbo.Users(Id)
   ON DELETE CASCADE
   ON UPDATE NO ACTION;ALTER TABLE dbo.Comments
   ADD CONSTRAINT FK_Comments_Articles FOREIGN KEY(ArticleId)
   REFERENCES dbo.Articles(Id)
   ON DELETE CASCADE
   ON UPDATE NO ACTION;ALTER TABLE dbo.Comments
   ADD CONSTRAINT FK_Comments_Users FOREIGN KEY(UserId)
   REFERENCES dbo.Users(Id)
   ON DELETE CASCADE
   ON UPDATE NO ACTION;

Ostatni klucz jest tutaj kluczowy. Wydaje się logiczne, że jeżeli usuwam użytkownika, powinny zostać usunięte jego komentarze. Zapominamy o jednym. W momencie kiedy usuwamy użytkownika, są usuwane jego artykuły a następnie kaskadowo komentarze do nich. Nie ma potrzeby usuwania ich w sposób kaskadowy jeszcze z poziomu użytkownika.

Tak więc powyższe klucze powinny mieć postać:

ALTER TABLE dbo.Articles
   ADD CONSTRAINT FK_Articles_Users FOREIGN KEY(UserId)
   REFERENCES dbo.Users(Id)
   ON DELETE CASCADE
   ON UPDATE NO ACTION;
ALTER TABLE dbo.Comments
   ADD CONSTRAINT FK_Comments_Articles FOREIGN KEY(ArticleId)
   REFERENCES dbo.Articles(Id)
   ON DELETE CASCADE
   ON UPDATE NO ACTION;
ALTER TABLE dbo.Comments
   ADD CONSTRAINT FK_Comments_Users FOREIGN KEY(UserId)
   REFERENCES dbo.Users(Id)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION;


Pytanie jeszcze może budzić operacja NO ACTION w przypadku instrukcji UPDATE. Nie jest ona tutaj wymagana. Dlaczego? Ponieważ nie przewiduje w aplikacji możliwości zmiany klucza głównego osoby - są to auto numerowane pola i nie będą zmieniane w przyszłości, gdyż wprowadzić mogłoby to więcej chaosu niż pożytku.

Skrypt procedury w bazie MSSQL

Dziś szybki post.
Czasami potrzebujemy pobrać skrypt procedury zawartej już w MSSQL. Jeżeli z jakichś powodów nie da się tego zrobić z poziomu GUI można skorzystać z słowa kluczowego sp_helptex

Przykładowe użycie:
W bazie mamy procedurę GetAllComments, aby pobrać kod za pomocą którego została utworzona, wystarczy użyć następującej składni:

EXECUTE sp_helptext GetAllComments 

Wywołanie tego polecenia SQL da następujący rezultat: