Wykład 13

Środowiska programistyczne dla języka SQL

Streszczenie

Na tym wykładzie zostaną omówione interfejsy programistyczne określone przez Standard SQL. Dla porównania, przedstawimy tak samo popularny jak ADO interfejs programistyczny do bazy danych – mianowicie JDBC. Należy on do środowiska języka programowania Java i będzie używany na zajęciach dotyczących Javy. Nas interesuje tutaj przede wszystkim porównanie ADO z JDBC - jak bardzo są do siebie podobne, co nie powinno stanowić niespodzianki bo oba wywodzą się z tego samego standardu interfejsu poziomu wywołań (CLI) języka SQL.

Wspomnimy także o łączeniu się z bazami danych w skryptach języka PHP.
 

Interfejsy programistyczne określone przez Standard SQL

1. moduły, 
2. osadzony SQL,
3. Interfejs Poziomu Wywołań - przykładem jest tu poznane na poprzednim wykładzie ADO i ADO.NET,
4. bezpośredni SQL (omawiany na wykładzie 9).

Język modułowy

Każdy program zewnętrzny, który chce skorzystać z bazy danych SQL, ma związane ze sobą jeden lub więcej modułów, każdy z nich składa się ze zbioru procedur.

Z kolei każda procedura zawiera zbiór definicji parametrów i pojedynczą instrukcję SQL używającą tych parametrów:

PROCEDURE delete_emp (SQLSTATE, :num_prac NUMBER(4));
  DELETE FROM Emp WHERE Emp.Empno = :num_prac;

Parametr SQLSTATE

Służy do przekazania kodu statusu wykonania instrukcji SQL z powrotem do programu, który wywołuje procedurę.

Wartość SQLSTATE równa 00000 oznacza, że instrukcja SQL została wykonana w całości i nie wystąpił żaden błąd.

Zastosowanie procedury modułowej w C

retcode CHAR(5);
num_prac NUMBER(4);
. . . . . . . . .
CALL delete_emp(RETCODE, num_prac);
IF RETCODE = '00000' 
THEN . . . . . . ; /* operacja usuwania powiodła się */
ELSE . . . . . . ; /* wystąpił wyjątek */

Osadzony SQL

Metodą bardziej naturalną niż tworzenie modułów jest osadzanie instrukcji SQL wprost w tekście programu języka. Tak jak parametry w języku modułowym, nazwy zmiennych języka zewnętrznego w instrukcji osadzonego SQL muszą być poprzedzone dwukropkiem.
 

EXEC SQL
  BEGIN DECLARE SECTION;
     SQLSTATE CHAR(5);
     num_pr NUMBER(4);
  EXEC SQL END DECLARE SECTION;
. . . . . . . . .
EXEC SQL
  DELETE FROM Emp WHERE Emp.Empno = :num_pr;
 
IF SQLSTATE = '00000'
THEN . . . . . . ; /* operacja usuwania powiodła się */
ELSE . . . . . . ; /* wystąpił wyjątek */

Wszystkie zmienne zewnętrzne, które są używane w instrukcjach SQL muszą być zdefiniowane w sekcji deklaracji, ograniczonej instrukcjami BEGIN DECLARE SECTION i END DECLARE SECTION.

Po wykonaniu każdej instrukcji SQL do programu na zmiennej SQLCODE lub SQLSTATE (lub obu) jest przekazywana wartość statusu wykonania instrukcji SQL.

Interfejs Poziomu Wywołań na przykładzie JDBC

Dla języka programowania Java zostały opracowane dwie wersje:

• języka SQL osadzonego w Javie o nazwie SQLJ;
• interfejsu poziomu wywołań z języka Java o nazwie JDBC.

Szczególnie interfejs JDBC stał się popularny i jest używany między innymi w:

• programach aplikacyjnych napisanych w Javie - po stronie klienta;
• apletach czyli małych programach Javy realizowanych na przeglądarce użytkownika;
• serwletach czyli programach Javy spełniających rolę małych serwerów aplikacyjnych to jest programów realizujących przez sieć zlecenia użytkowników;
• procedurach i funkcjach zakodowanych w Javie składowanych w bazie danych.
  

JDBC jest zbiorem klas i interfejsów w Javie, które definiują jednolity dostęp do bazy danych z programów napisanych w Javie. Klasy te i interfejsy tworzą pakiet java.sql. Na początku programu zamieszcza się zwykle dyrektywę importu:

import java.sql.*;

Do określenia sposobu łączenia się z bazą danych służy napis połączenia URL (jdbc oznacza nazwę głównego protokołu połączenia):

jdbc:<podprotokół>:<podnazwa>

np.

jdbc:odbc:moja_baza
jdbc:oracle:thin:@xeon.pjwstk.edu.pl:1521:ORCL

Sterownik JDBC - zestaw klas, które implementują interfejsy pakietu java.sql dla konkretnego systemu baz danych. Aby połączyć się z bazą danych należy wykonać dwie instrukcje:

Krok 1: załadowanie odpowiedniego sterownika JDBC do pamięci:

   - albo za pomocą mechanizmu Javy dynamicznego ładowania klas:

      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

   - albo używając statycznej metody registerDriver klasy DriverManager:

      DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());

Krok 2: użycie metody getConnection klasy DriverManager do otwarcia połączenia z bazą danych, która zwraca referencję do obiektu klasy Connection.

   String URL = "jdbc:odbc:moja_baza"
   Connection con = DriverManager.getConnection(URL, "użytkownik", "hasło");
 

Obiekty JDBC

•  Sesja z bazą danych rozpoczyna się od utworzenia obiektu klasy Connection (odpowiednik klasy Connection w ADO).
•  Obiekt klasy Statement reprezentuje wykonywaną instrukcję SQL (odpowiednik klasy Command w ADO). Podstawowe dwie metody to:
  • executeQuery - jako parametr bierze tekst zapytania SQL a zwraca obiekt typu ResultSet – z danymi.
  • executeUpdate – jako parametr bierze instrukcję SQL i zwraca liczbę przetworzonych przez nią w bazie danych rekordów.
•  Obiekt klasy ResultSet reprezentuje zbiór rekordów zwracanych przez zapytanie - przy czym dokładnie jeden rekord jest dostępny w danej chwili (odpowiednik klasy RecordSet w ADO).
  • Kolejne rekordy uzyskuje się stosując metodę next, która zwraca wartość logiczną, przy czym:
    • false oznacza, że doszliśmy do końca zbioru wynikowego rekordów.
  • Poszczególne wartości pól uzyskuje się stosując metody takie jak getString i getFloat, które mając daną nazwę pola (odpowiedniego typu) zwracają wartość tego pola w bieżącym rekordzie.
•  Obiekt klasy SQLException reprezentuje błąd (wyjątek) związany z wykonywaniem instrukcji SQL na bazie danych.

Oto przykład zastosowania interfejsu JDBC do bazy danych określonej przez połączenie ODBC o nazwie oras z logowaniem na konto scott z hasłem tiger. Zakładamy, że w bazie danych znajduje się tabela Emp mająca kolumny: Ename typu napisowego oraz Sal typu numerycznego.
 

//połącz się z bazą danych try{   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");    } catch(ClassNotFoundException ex) {       System.out.println(ex.getMessage ());     } try {    Connection con =    DriverManager.getConnection("jdbc:odbc:oras", "scott", "tiger"); //zbuduj obiekt reprezentujący instrukcję SQL i wykonaj ją    Statement stmt = con.createStatement();    String query = "SELECT Ename, Sal FROM Emp";    ResultSet rs = stmt.executeQuery(query); //przejdź w pętli po wszystkich wierszach wynikowych   while (rs.next()) {       String s = rs.getString("Ename");       float z = rs.getFloat("Sal");       System.out.println(s + ": " + z);    } } catch(SQLException ex) {// obsłuż wyjątki       System.out.println(ex.getMessage ()       + ex.getSQLState () + ex.getErrorCode ());    } }

W przykładzie został użyty sterownik typu most między JDBC a ODBC. Można byłoby użyć innego sterownika, na przykład sterownika firmy Oracle. Wtedy przykładowo URL = "jdbc:oracle:thin:@elektron.pjwstk.edu.pl:1521:ORCL". Jako ćwiczenie, czytelnik sprawdzi działanie załączonego pełnego programu w Javie - obejmującego powyższy przykład dla bazy danych MS Access.
 

Modyfikowanie danych w bazie danych

 

Statement stm = con.createStatement(); int liczba = stm.executeUpdate(                  "UPDATE Emp SET Sal = Sal*1.1"); System.out.println(      "Podniesiono zarobki o 10%: "+ liczba +" osobom."); stm.close();

Wywołanie  stm.executeUpdate zwraca pojedynczą liczbę, określającą liczbę wierszy tabeli, których dotyczyło zapytanie (tzn. które zostały zaktualizowane). Metoda stm.executeUpdate używana jest do wykonywania operacji: INSERT, UPDATE, DELETE, a także operacji definiowania danych takich jak: CREATE TABLE, ALTER TABLE, DROP TABLE.

Modyfikowanie danych w bazie danych przy użyciu obiektu ResultSet

 

Statement stm = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE) String sql = "SELECT * FROM Emp"; ResultSet rs = stm.executeQuery(sql); rs.first(); rs.updateString("Ename","Kowalski"); rs.updateFloat("Sal",10000); rs.updateRow();

Należy zdawać sobie sprawę, że nie każdy system obsługuje modyfikowalne obiekty ResultSet (podobnie jest dla obiektów RecordSet w ADO).

Usunięcie bieżącego rekordu

 

rs.deleteRow();

Dodanie nowego rekordu

 

rs.moveToInsertRow(); rs.updateString("Ename","Kowalski"); rs.updateFloat("Sal", 10000); rs.insertRow();

Obsługa transakcji

Domyślnie w JDBC każda instrukcja SQL kończy się automatycznym zatwierdzeniem (auto-commit). Poniżej zmieniamy to domyślne ustawienie. Gdy wykonywanie wszystkich instrukcji SQL kończy się pomyślnie, explicite wykonujemy zatwierdzanie przy użyciu metody commit obiektu Connection. Gdy w trakcie wykonywania instrukcji SQL powstanie błąd, wykonujemy wycofanie transakcji przy użyciu metody rollback obiektu Connection.

 

try {    con.setAutoCommit(false);    Statement stm=con.createStatement();    stm.executeUpdate(" .....");    stm.executeUpdate(" .....");    .....    con.commit(); } catch(SQLException e){    con.rollback(); }

Obsługa NULL - funkcja wasNull()

float Sal = rs.getFloat("Sal"); if (rs.wasNull(){    Sal = 0; }

Przygotowanie instrukcji - PreparedStatement

Gdy wielokrotnie wykonuje się tę samą instrukcję, warto ją wcześniej raz przygotować dokonując analizy składniowej, a potem wykonywać wielokrotnie. Co więcej, taka instrukcja może mieć parametry zmieniane przy każdym wywołaniu. W miejsce klasy Statement korzystamy w tym przypadku z jej podklasy PreparedStatement. Parametry formalne są w treści instrukcji oznaczone przez znaki zapytania:

 

PreparedStatement stmt = con.prepareStatement("UPDATE Emp SET Deptno = ? WHERE Deptno = ?");

Następujące metody służą do nadania wartości parametrom: setInt(), setString(), setDate() etc. Każda z nich ma dwa argumenty: numer parametru i jego wartość. Po przygotowaniu można wielokrotnie nadawać różne wartości temu samemu parametrowi. Oto przykładowy fragment kodu, który przenosi wszystkich pracowników z działu numer 10 do działu nr 50.
 

PreparedStatement stmt = con.prepareStatement(        "UPDATE Emp SET Deptno = ? WHERE Deptno = ?"); stmt.setInt(1, 50); stmt.setInt(2, 10); System.out.println(stmt.executeUpdate()+" przeniesionych"); stmt.close();

Informacja o PHP

PHP ma bardzo prostą i intuicyjną składnię wzorowaną na C, Javie i Perlu. Popularność zawdzięcza ogromnej liczbie bibliotek, które m.in. pozwalają połączyć się z praktycznie dowolnym systemem zarządzania bazą danych. Co więcej, jest dostępny za darmo i jest oprogramowaniem o otwartym kodzie źródłowym. Stanowi fenomen podobny do Linuxa.

PHP definiuje interfejsy programistyczne dostępu do baz danych - osobno dla każdego konkretnego rodzaju systemu zarządzania bazą danych. W tym punkcie przedstawimy przykład dla bazy danych Oracle.

Skrypty PHP umieszcza się między znacznikami <?php oraz ?> lub po prostu <? oraz ?>

Instrukcje:

echo "treść do wypisania"
print("treść do wypisania")

<?
$zmienna = "PHP";
echo "Tematem dzisiejszego wykładu jest ". $zmienna;
?>

Połączenie z bazą danych otrzymujemy wywołując funkcję OCILogon. Funkcja OCILogon przekazuje obiekt-połączenie, który pozwala tworzyć obiekty-instrukcje na bazie danych:

$con = OCILogon("scott", "tiger", "myDb");

OCILogoff($con);

$stmt = OCIParse($con, "DELETE FROM Dept WHERE Deptno = 40");

OCIExecute($stmt);

<?php 
$conn = OCILogon("scott", "tiger", "baza");
if ($conn == false){
   echo OCIError($conn)."<BR> "; 
}
$query = "SELECT Ename, Job, Sal FROM Emp";
$stmt = OCIParse($conn, $query);
if(!$stmt) {// w przypadku błędu przy parsowaniu
  $oerr = OCIError($stmt);
  echo $oerr["message"];
} else {// gdy nie ma błędu
  OCIExecute($stmt);
  echo "<TABLE>";
  while (OCIFetchInto($stmt, $row, OCI_ASSOC))) {
       echo "<TR><TD>$row[ENAME]</TD>"
       echo "<TD>$row[JOB]</TD> <TD>$row[SAL]</TD></TR>";
    }
  echo "</TABLE>"; 
}
?>


Zwróćmy uwagę na wygodne indeksowanie tabeli zawierającej wiersz wynikowy $row za pomocą nazw kolumn np. $row[ENAME].

Podsumowanie

Na dwóch ostatnich wykładach czytelnik zaznajomił się z programistycznym interfejsem poziomu wywołań czyli ze sposobem wykonywania w programie konwencjonalnego języka programowania instrukcji SQL na bazie danych. Porównaliśmy podejścia zastosowane w ADO i JDBC. W obu przypadkach najpierw inicjujemy połączenia z bazą danych za pomocą obiektu klasy Connection. Następnie korzystając z odpowiednich metod albo samego obiektu klasy Connection albo obiektu reprezentującego instrukcję SQL czyli obiektu klasy Command w VBA i Statement w Javie następuje wykonanie instrukcji SQL. W przypadku wykonywania instrukcji SELECT otrzymywane z bazy danych wiersze są zapisywane i przetwarzane przy pomocy metod obiektu RecordSet w VBA i ResultSet w Javie.

W przypadku PHP i bazy danych Oracle używamy funkcji:

• OCILogon do nawiązania połączenia z bazą danych,
• OCIParse do przygotowania instrukcji SQL,
• OCIExecute do wykonania przygotowanej instrukcji SQL,
• OCIFetchInto do sprowadzenia kolejnego wiersza wynikowego - dla instrukcji SELECT.
   

Słownik

Zadania programistyczne z użyciem interfejsu JDBC pozna czytelnik w ramach zajęć poświęconych językowi programowania Java.

Strona przygotowana przez Lecha Banachowskiego - 06/12/03 .