Working With TSQLQuery/pl

│ English (en) │ español (es) │ français (fr) │ 日本語 (ja) │ polski (pl) │ 中文（中国大陆）‎ (zh_CN) │

Praca z TSQLQuery

Databases portal

References: General info Libraries Field types Controls FAQ SQL how-to Working With TSQLQuery In-memory database applications Tutorials/practical articles: Overview 0 - Database set-up 1 - Getting started 2 - Editing 3 - Queries 4 - Data modules SQLdb Programming Reference Databases Advantage - MySQL - MSSQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos

Informacje ogólne

TSQLQuery to obiekt, który może zawierać zbiór danych pochodzący z bazy danych (RDBMS wykorzystujący SQL, taki jak Firebird, MS SQL Server, Oracle...). Poprzez użycie instrukcji SELECT SQL we właściwości SQL TSQLQuery, możesz określić, jakie dane są pobierane z bazy danych do zestawu danych. Gdy zestaw danych zostanie zmieniony przez program (lub użytkownika), zmiany można przesłać z powrotem do bazy danych.

TSQLQuery można również użyć do bezpośredniej modyfikacji danych: jeśli określisz żądaną instrukcję SQL jak INSERT, UPDATE, DELETE itp. we właściwości SQL i wywołasz metodę ExecSQL TSQLQuery, obiekt zapytania wyśle ​​​​SQL do bazy danych bez pobierania jakichkolwiek wynikiów.

Możesz używać klasy TSQLQuery w FPC, ale Lazarus dostarcza również komponent: TSQLQuery

Oficjalna dokumentacja

Zobacz dokumentację TSQLQuery

Wiele dokumentacji kontekstowej jest teraz dostępnych w Lazarusie. Niestety TSQLQuery nie pojawia się w indeksie pomocy Lazarusa 1.0. Jeśli umieścisz kursor na metodach i właściwościach TSQLQuery, spróbuj nacisnąć klawisz F1, aby sprawdzić, czy ten kod jest udokumentowany; np. to zadziała:

var
Q: TSQLQuery
...
  Q.Open; //<--- umieść kursor na Open i naciśnij F1

Powszechnie używane kontrolki

Zestaw danych zwrócony przez TSQLQuery może być wygodnie wyświetlany za pomocą kontrolki TDBGrid, ale nie jest ona zbyt dobra do edycji danych w poszczególnych polach i komórkach. W tym celu należy umieścić w formularzu niektóre kontrolki jednopolowe obsługujące dane, takie jak TDBEdit, i ustawić ich właściwość DataSource na używane źródło danych. Właściwość DataField powinna mieć ustawioną nazwę pola (np. 'IDENTITY') lub jakieś wyrażenie, które zwraca odpowiedni ciąg znaków.

Dodanie paska narzędzi TDBNavigator ułatwia poruszanie się po rekordach i wybieranie rekordów do edycji. Po wybraniu rekordu za pomocą paska narzędzi lub przesuwania myszy po siatce danych, dane dla odpowiedniego wiersza i kolumny pojawiają się w polu TDBEdit, a po kliknięciu przycisku 'Edit' zawartość w polu Edycji może być modyfikowana. Kliknięcie przycisku 'Post' potwierdza zmianę, a kliknięcie przycisku 'Cancel' anuluje zmiany.

Ogólnie proces wygląda następująco:

1. Upuść TSQLQuery na formularz/moduł danych i ustaw odpowiednie właściwości Database, Transaction i zapytanie SQL w SQL.
2. Upuść komponent TDataSource i ustaw jego właściwość DataSet na instancję TSQLQuery.
3. Upuść TDBGrid na formularzu i ustaw jego właściwość DataSource na instancję TDataSource.
4. Opcjonalnie upuść instancję TDBNavigator na formularzu i ustaw jej właściwość DataSource na instancję TDataSource.

Następnie można ustawić właściwość Active na True i powinno być możliwe wyświetlenie danych pobranych przez zapytanie. (pod warunkiem, że składniki TSQLConnection i TSQLTransaction są aktywne)

Aktualizacja danych

Jeśli chcesz mieć możliwość USUWANIA lub modyfikowania rekordów, to twoja tabela bazy danych powinna:

1. zawierać jedną kolumnę PRIMARY KEY.
2. mieć zestaw pól, które jednoznacznie określają rekord. Zwykle powinny być częścią unikalnego indeksu. Nie jest to wymagane, ale znacznie przyspieszy zapytania.

Jeśli nie ma pola podstawowego lub pól, które jednoznacznie określają twój rekord, należy dodać pole klucza podstawowego. Najlepiej robi się to, gdy struktura tabeli jest projektowana poleceniem CREATE, ale można ją dodać w późniejszym czasie.

Na przykład poniższy przykładowy kod w twoim kliencie MySQL doda unikalny indeks do tabeli:

ALTER TABLE testrig 
ADD COLUMN autoid INT
PRIMARY KEY AUTO_INCREMENT;

Dodanie tego pola nie zaszkodzi, a pozwoli aplikacjom na aktualizację innych pól.

Aktualizacje z pamięci podręcznej

Komponent TSQLQuery buforuje wszystkie aktualizacje. Oznacza to, że aktualizacje nie są wysyłane natychmiast do bazy danych, ale są przechowywane w pamięci do momentu wywołania metody ApplyUpdates. W tym momencie aktualizacje zostaną przekształcone w instrukcje aktualizacji SQL i zostaną zastosowane w bazie danych. Jeśli nie wywołasz „ApplyUpdates”, baza danych nie zostanie zaktualizowana o lokalne zmiany.

Pola klucza podstawowego

Podczas aktualizowania rekordów TSQLQuery musi wiedzieć, które pola składają się na klucz podstawowy, którego można użyć do aktualizacji rekordu, i które pola należy zaktualizować: na podstawie tych informacji konstruuje polecenie SQL UPDATE, INSERT lub DELETE.

Konstrukcja instrukcji SQL jest kontrolowana przez właściwości UsePrimaryKeyAsKey i ProviderFlags.

Właściwość ProviderFlags to zestaw 3 flag:

pfInkey

To pole jest częścią klucza podstawowego

pfInWhere

To pole powinno być używane w klauzuli WHERE instrukcji SQL.

pfInUpdate

To pole powinno być zawarte podczas aktualizacji lub wstawiania rekordu.

Domyślnie ProviderFlags ustawione jest zawsze na pfInUpdate.

Jeśli Twoja tabela ma klucz podstawowy (jak opisano powyżej), wystarczy ustawić właściwość UsePrimaryKeyAsKey na True i wszystko zostanie dla ciebie zrobione. To ustawi flagę pfInKey dla pól klucza podstawowego.

Jeśli tabela nie ma indeksu klucza podstawowego, ale zawiera pola, których można użyć do jednoznacznej identyfikacji rekordu, to możesz ustawić opcję pfInKey we właściwości ProviderFlags dla wszystkich pól, które jednoznacznie określają rekord.

Następna właściwość UpdateMode określa, które dokładnie pola zostaną użyte w klauzuli WHERE:

upWhereKeyOnly

Gdy TSQLQuery musi utworzyć klauzulę WHERE dla aktualizacji, zbierze wszystkie pola, które mają flagę pfInKey we właściwości ProviderFlags i użyje wartości do utworzenia Klauzula WHERE, która jednoznacznie określa rekord do aktualizacji — zwykle jest to potrzebne tylko w przypadku instrukcji UPDATE lub DELETE.

upWhereChanged

Oprócz pól, które mają pfInKey we właściwości ProviderFlags, w klauzuli WHERE uwzględnione zostaną także wszystkie pola, które mają ustawione pfInWhere we właściwości ProviderFlags i które uległy zmianie.

upWhereAll

Oprócz pól, które mają pfInKey we właściwości ProviderFlags, w klauzuli WHERE uwzględnione zostaną także wszystkie pola, które mają ustawione pfInWhere we właściwości ProviderFlags.

Kontrola aktualizacji

Możliwe jest określenie, które pola powinny zostać zaktualizowane. Jak wspomniano powyżej, tylko pola, które mają właściwość pfInUpdate we właściwości ProviderOptions zostaną uwzględnione w instrukcjach SQL UPDATE lub INSERT. Domyślnie „pfInUpdate” jest zawsze zawarty we właściwości ProviderOptions.

Dostosowywanie SQL w TSQLQuery

Normalnie TSQLQuery użyje ogólnych instrukcji SQL opartych na właściwościach, które omówiono powyżej. Jednak ogólny kod SQL utworzony przez sqldb może nie być poprawny w danej sytuacji. TSQLQuery pozwala na dostosowanie instrukcji SQL używanych do różnych działań, aby działały najlepiej w Twojej sytuacji z bazą danych. W tym celu używa się właściwości SQL, InsertSQL, UpdateSQL i DeleteSQL.

Wszystkie te właściwości są typu TStringList, lista ciągów, która akceptuje wiele wierszy SQL. Wszystkie cztery są wyposażone w edytor właściwości w IDE. W środowisku IDE wybierz właściwość i otwórz edytor, klikając przycisk z wielokropkiem. W tym edytorze (TSQLQuery narzędzie metadanych) możesz również wyszukać informacje z tabeli itp.

W kodzie użyj na przykład InsertSQL.Text lub InsertSQL.Add(), aby ustawić lub dodać wiersze instrukcji SQL. Jedna instrukcja może obejmować kilka wierszy i kończyć się średnikiem.

Ponadto wszystkie cztery właściwości akceptują parametry wyjaśnione poniżej.

SQL - Podstawowe dostosowywanie SQL

Właściwość SQL jest zwykle używana do pobierania danych z bazy danych. Ogólny kod SQL dla tej właściwości to SELECT * FROM fpdev, gdzie fpdev jest istniejącą tabelą w bazie danych.

Zestaw danych zwrócony przez ogólną instrukcję SQL będzie dość szorstki. Jeśli pokażesz wynik w TDBGrid, kolejność rekordów może wydawać się losowa, kolejność kolumn może nie być taka, jak chcesz, a nazwy pól mogą być poprawne technicznie, ale nieprzyjazne dla użytkownika. Korzystając z dostosowanego języka SQL, możesz to poprawić. Dla tabeli o nazwie fpdev z kolumnami id, UserName i InstEmail, możesz zrobić coś takiego:

SELECT id AS 'ID', UserName AS 'User', InstEmail AS 'e-mail' FROM fpdev ORDER BY id;

Zestaw danych, który wynika z powyższego zapytania, wykorzystuje nazwy pól podane w zapytaniu (ID, Użytkownik i e-mail), kolejność kolumn podaną w zapytaniu, a rekordy są sortowane według ich id.

InsertSQL, UpdateSQL i DeleteSQL - Podstawy użycia parametrów

Kiedy przypiszesz zapytanie SELECT do właściwości SQL w SQLQuery, to SQLQuery wie, jak pobrać dane z bazy danych. Jednak w przypadku korzystania z kontrolek powiązanych z danymi, takich jak DBGrid, SQLQuery będzie również musiało mieć możliwość wstawiania, aktualizowania i usuwania wierszy z bazy danych na podstawie działań użytkownika.

Aby przyspieszyć rozwój, SQLQuery może próbować wydedukować wymagane instrukcje SQL. Jeśli właściwość SQL istnieje, a właściwość ParseSQL ma wartość true (jest to wartość domyślna), SQLQuery spróbuje wygenerować te instrukcje, analizując właściwość SQL. SQLDB przechowuje te instrukcje we właściwościach „InsertSQL”, „UpdateSQL” i „DeleteSQL”.

Jednak czasami wygenerowane instrukcje nie będą działać (np. przy wstawianiu do tabel z automatycznym przyrostem/autonumerowaniem kluczy podstawowych) lub będą bardzo wolne. W razie potrzeby można ręcznie przypisać zestawienia.

Instrukcje we właściwościach InsertSQL, UpdateSQL i DeleteSQL akceptują parametry reprezentujące pola w zestawie danych. Obowiązują następujące zasady:

• Nazwy parametrów muszą być dokładnie takie same jak nazwy pól używane w zestawie danych. Nazwy pól w zestawie danych mogą różnić się od nazw kolumn w tabeli, w zależności od użytej instrukcji select (patrz powyżej).
• Podobnie jak parametry w innych zapytaniach SQLDB, nazwy parametrów muszą być poprzedzone dwukropkiem.
• Aby parametry użyć w instrukcjach aktualizacji/usuwania, poprzedź nazwę pola zestawu danych przedrostkiem OLD_ (bezwzględnie wielkimi literami, przynajmniej w Lazarus v. 1.0), aby uzyskać wartość rekordu przed jego edytowaniem zamiast nowej wartości.

Jeśli masz tabelę o nazwie fpdev i kolumnach id, UserName i InstEmail, połączoną z zestawem danych z polami ID, User i e-mail (patrz przykład w instrukcji SELECT), możesz napisać to zapytanie InsertSQL:

INSERT INTO fpdev(id, UserName, InstEmail) VALUES(:ID,:User,:e-mail);

Ta instrukcja wstawi wartości ID, User i e-mail z bieżącego rekordu zbioru danych do odpowiednich pól tabeli fpdev.

Ta przykładowa instrukcja jest w rzeczywistości mniej więcej tym, co sama baza danych SQLDB wygeneruje automatycznie. Podana instrukcja może skutkować błędami, gdy pole id jest polem automatycznego przyrostu w unikalnym kluczu. Różne bazy danych na różne sposoby rozwiązują ten problem. Na przykład poniższe działa dla MySQL.

INSERT INTO fpdev(id, UserName, InstEmail) VALUES(0,:User,:e-mail)
 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID();

Powyższa instrukcja próbuje wstawić nowy rekord używając 0 (zero) dla kolumny id. Jeśli zero jest już używane jako klucz, to wykrywany jest duplikat i id jest aktualizowany tak, aby używał ostatnio wstawionego identyfikatora. Czyli, id o jeden przyrost wyższy niż ostatnio użyty.

W przypadku Firebirda, jeśli emulujesz klucze autoinkrementacji [1], powinno działać coś takiego:

INSERT INTO fpdev(UserName, InstEmail) VALUES(:User,:e-mail);(

Instrukcja wstawia wszystko poza kluczem podstawowym i pozwala Firebirdowi przed wyzwalaczem wstawiania użyć generatora/sekwencji do wstawienia wartości id dla Ciebie.

W przypadku instrukcji INSERT możesz chcieć użyć bieżących wartości pól wybranego rekordu. W przypadku instrukcji UPDATE warto użyć wartości pól, które były przed edycją w klauzuli WHERE. Jak wspomniano wcześniej, wartości pól przed edycją muszą być zapisane jako nazwa pola poprzedzona OLD_ (bezwzględnie wielkimi literami, przynajmniej w Lazarus v. 1.0). Weźmy na przykład to zapytanie:

UPDATE fpdev SET UserName=:User, InstEmail=:e-mail WHERE UserName=:OLD_User;

Powyższa instrukcja aktualizuje kolumny UserName i InstEmail wszystkich rekordów, w których User jest równy starej wartości User.

Czytelnikowi pozostawiamy ćwiczenie, jak użyć bieżących wartości pól i starych wartości pól w instrukcjach DELETE.

Zobacz także oficjalną dokumentację:

• dokumentacja InsertSQL
• dokumentacja UpdateSQL
• dokumentacja DeleteSQL

Parametry w TSQLQuery.SQL

W większości sytuacji właściwość SQL TSQLQuery będzie zawierać instrukcję SELECT, która najczęściej nie wymaga parametrów. Może je jednak zawierać. Pozwala to na bardzo łatwy i skuteczny sposób filtrowania Twoich rekordów.

Parametry mają następujące zalety:

• nie ma potrzeby formatowania danych jako tekst SQL, data itp. (tzn. nie trzeba pamiętać, jak formatować datę dla MySQL, która może różnić się od implementacji Firebird; nie ma potrzeby ucieczki danych tekstowych, takich jak O'Malley's SQL Przerażenie"
• prawdopodobnie zwiększona wydajność
• ochrona przed atakami typu SQL injection

Użycie parametrów może pomóc w wydajności bazy danych. Większość baz danych obsługuje gotowe zestawienia, co oznacza, że ​​zestawienie jest przygotowywane i buforowane w bazie danych. Przygotowana instrukcja może być użyta więcej niż jeden raz i nie wymaga parsowania i planowania zapytań za każdym razem, gdy jest używana, tylko parametry są zmieniane przy każdym użyciu. W sytuacjach, w których to samo stwierdzenie jest używane wiele razy (gdy różnią się tylko wartości parametrów), przygotowane zestawienia mogą znacznie poprawić wydajność. Dodatkowo ataki typu SQL injection można złagodzić za pomocą parametrów.

Właściwości InsertSQL, UpdateSQL i DeleteSQL mają wstępnie zdefiniowane parametry dla bieżących i starych wartości pól. Jednak właściwość SQL nie. Ale możesz tworzyć własne parametry we właściwości Params.

Przykład zapytania SELECT

Ten przykład pokazuje, jak wybrać dane za pomocą parametrów. Pokazuje również użycie aliasów (... AS ...) w SQL.

 sql_temp.sql.text := 'SELECT id AS ''ID'', UserName AS ''User'', InstEmail AS ''e-mail'' FROM fpdev WHERE InstEmail=:emailsearch;'
 ...
 //Spowoduje to utworzenie parametru o nazwie emailsearch.

 //Jeśli chcemy, możemy jawnie ustawić, jaki to jest typ parametry... co może być też konieczne, gdy FPC odgadnie źle:
 //sql_temp.params.parambyname('emailsearch').datatype:=ftWideString
 
 //Możemy teraz uzupełnić wartość parametru:
 sql_temp.params.parambyname('emailsearch').asstring := 'mvancanneyt@freepascal.org';
 ...
 //Następnie użyj swojego zwykłego sposobu na pobieranie danych,
 //opcjonalnie zmień wartość parametru i uruchom go ponownie

Przykład zapytania INSERT

Ten przykład pokazuje, jak wstawić nowy rekord do tabeli za pomocą parametrów:

 sql_temp.sql.text := 'insert into PRODUCTS (ITEMNR,DESCRIPTION) values (:OURITEMNR,:OURDESCRIPTION)'
 ...
 sql_temp.Params.ParamByName('OURITEMNR').AsString := 'XXXX';
 sql_temp.Params.ParamByName('OURDESCRIPTION').AsString := 'description';
 sql_temp.ExecSQL;
 SQLTransaction1.Commit; //lub ewentualnie CommitRetaining, w zależności od konfiguracji aplikacji

Innym sposobem na zrobienie tego jest coś takiego:

 tsqlquery1.appendrecord(['XXXX', 'description']) 
 tsqltransaction1.commit; //lub CommitRetaining

Zapytanie z funkcją Format

Korzystanie z zapytań parametrycznych jest preferowanym podejściem, ale w niektórych sytuacjach alternatywą może być funkcja Format. (patrz ostrzeżenie poniżej). Przykładowo, parametry nie mogą być używane podczas wykonywania instrukcji za pomocą procedury ExecuteDirect (oczywiście można równie dobrze użyć zapytania, aby uruchomić daną instrukcję SQL). Wtedy może się przydać to:

procedure InsertRecord
var
  aSQLText: string;
  aSQLCommand: string;
begin
  aSQLText:= 'INSERT INTO products(item_no, description) VALUES(%d, %s)';
  aSQLCommand:= Format(aSQLText, [strtoint(Edit1.Text), Edit2.Text]);
  aConnection.ExecuteDirect(aSQLCommand);
  aTransaction.Commit;
end;

Wartości zmiennych mogą się zmieniać, a wartości zapytań będą się zmieniać wraz z nimi, tak jak w przypadku zapytań parametrycznych.

Parametr %d jest używany dla liczb całkowitych, %s dla łańcuchów; itp. Szczegółowe informacje można znaleźć w dokumentacji funkcji Format.

Ostrzeżenie: Pamiętaj, że przy użyciu tej techniki możesz napotkać problemy z tekstem zawierającym ' i daty!

Uruchamianie własnego SQL i uzyskiwanie metadanych

Jeśli chcesz po prostu sprawdzić niektóre instrukcje SQL, rozwiązać problemy lub pobrać metadane (np. listę tabel) z bazy danych, możesz to zrobić z poziomu IDE. W swoim programie, z T*Connection, transakcją, obiektem Query itp. ustawionym w czasie projektowania, przejdź do właściwości SQL obiektu zapytania, a następnie kliknij przycisk ...

Zobaczysz okno z kodem SQL i możesz uruchomić kilka instrukcji, takich jak

SELECT * FROM EMPLOYEE

poprzez naciśniecie ikony play:

Możesz również uzyskać metadane: nazwy tabel, nazwy kolumn itp. (jeśli złącze sqldb to obsługuje, ale większość z nich obecnie to robi):

(See also: Database metadata#Lazarus TSQLQuery metadata tool) (Zobacz także: Database metadata#Lazarus Narzedzie metadanych TSQLQuery)

Rozwiązywanie problemów

Logowanie

Zobacz tutaj: SqlDBHowto#Troubleshooting: Logowanie TSQLConnection, aby uzyskać więcej informacji.

Słaba wydajność

• Upewnij się, że zapytania do bazy danych są zoptymalizowane (użyj odpowiednich indeksów itp.). Użyj do tego swoich narzędzi bazodanowych (np. zapewnienie planów zapytań).
• Zobacz #Out of memory errors poniżej, aby poznać możliwe ulepszenia wydajności podczas przechodzenia do przodu przez SQLQuery.

Błędy braku pamięci

TSQLQuery jest potomkiem BufDataset, zestawu danych, który buforuje otrzymane dane w pamięci. Jeśli pobierzesz wiele rekordów (np. podczas przeglądania ich w pętli w celu wyeksportowania), pamięć sterty może się zapełnić (z rekordami, przez które już przeszłaś pętlę) i wyeliminujesz błędy pamięci.

Chociaż ta sytuacja uległa poprawie w wersji rozwojowej FPC, obejściem jest wydanie do BufDataset komendy odrzucenia rekordów, które już zostały przeczytane, poprzez ustawienie właściwości UniDirectional na true przed otwarciem zapytania:

MySQLQuery.UniDirectional:=True;

Może to również poprawić wydajność.

Zbiór danych DataSet jest tylko do odczytu

Może się to zdarzyć, jeśli określisz zapytanie, o którym Ty wiesz, że można zaktualizować dane, ale FPC o tym nie wie.

Przykład:

SELECT p.dob, p.surname, p.sex FROM people p;

Parser SQL w FPC jest dość uproszczony i kiedy znajdzie przecinek lub spację w części FROM, bierze pod uwagę wiele tabel i ustawia zestaw danych tylko do odczytu. Dla bezpieczeństwa, aliasowanie tabel nie jest zwykle wykonywane, gdy w grę wchodzi tylko jedna tabela. Rozwiązanie: przepisz zapytanie lub określ własne zapytania InsertSQL, UpdateSQL i DeleteSQL.