Working With TSQLQuery/pl
│
English (en) │
español (es) │
français (fr) │
日本語 (ja) │
polski (pl) │
中文（中国大陆） (zh_CN) │
|
References:
Tutorials/practical articles:
Databases |
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:
- Upuść TSQLQuery na formularz/moduł danych i ustaw odpowiednie właściwości Database, Transaction i zapytanie SQL w SQL.
- Upuść komponent TDataSource i ustaw jego właściwość DataSet na instancję TSQLQuery.
- Upuść TDBGrid na formularzu i ustaw jego właściwość DataSource na instancję TDataSource.
- 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:
- zawierać jedną kolumnę PRIMARY KEY.
- 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ę:
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
Insert query example
This example shows how to insert a new record into the table using parameters:
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; //or possibly CommitRetaining, depending on how your application is set up
Another way of doing this is something like:
tsqlquery1.appendrecord(['XXXX', 'description'])
tsqltransaction1.commit; //or commitretaining
Query with Format function
Using parameterized queries is the preferred approach, but in some situations the format function can be an alternative. (see warning below). For example, parameters can't be used when you execute statements with the connection's ExecuteDirect procedure (of course, you can just as well use a query to run the SQL statement in question). Then this can come in handy:
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;
The values of the variables can change and the query values will change with them, just as with parameterized queries.
The parameter %d is used for integers, %s for strings; etc. See the documentation on the Format function for details.
Ostrzeżenie: Be aware that you may run into issues with text containing ' and dates using this technique!
Running your own SQL and getting metadata
If you want to just check some SQL statements, troubleshoot, or get metadata (e.g. list of tables) from the database, you can do so from within the IDE. In your program, with your T*Connection, transaction, query object etc set up at design-time, go into the SQL property for the query object, then click the ... button.
You'll see a window with SQL code, and you can run some statements like
SELECT * FROM EMPLOYEE
by pressing the play icon:
You can also get metadata: table names, column names etc (if the sqldb connector supports it but most of them do nowadays):
(See also: Database metadata#Lazarus TSQLQuery metadata tool)
Troubleshooting
Logging
See here: SqlDBHowto#Troubleshooting: TSQLConnection logging for more detail.
Poor performance
- Make sure your database queries are optimized (use proper indexes etc). Use your database tools (e.g. providing query plans) for this.
- See #Out of memory errors below for possible performance improvements when moving forward through an SQLQuery.
Error messages
Out of memory errors
TSQLQuery is a descendant of BufDataset, a dataset that buffers the data it receives into memory. If you retrieve a lot of records (e.g. when looping through them for an export), your heap memory may become full (with records you already looped through) and you will get out of memory errors.
Although this situation has improved in the FPC development version, a workaround is to tell bufdataset to discard the records you have already read by setting the Unidirectional property to true before opening the query:
MySQLQuery.UniDirectional:=True;
This may also improve performance.
Dataset is read-only
This may happen if you specify a query that you know is updatable but FPC doesn't.
Example:
select p.dob, p.surname, p.sex from people p;
The SQL parser in FPC is quite simplistic and when it finds a comma or space in the FROM part it considers multiple tables are involved and sets the dataset to read only. To its defense, aliasing tables is usually not done when only one table is involved.
Solution: rewrite the query or specify your own InsertSQL, UpdateSQL and DeleteSQL.
