Znajdziesz to bez trudu. Nie używam komponentów wizualnych bazodanowych bo trudno je konfigurować. Używam komponentów wizualnych niebazodanowych i z nich przepisuję do zapytania SQL z parametrami. Tu jest dokumentacja o podłaczaniu DBExpress i ClientDataSet:
należy dać do pliku dpr deklarację uses crtl, MidasLib
a do katalogu programu u użytkownika dać plik dbexpint.dll.
dla biblioteki dbexpres w TSQLConnection.params ustawić:
ServerCharSet = win1250
LocaleCode = 1045
Database = nazwa pliku bazy danych umieszczonego w katalogu aplikacji
User_Name = sysdba
Password = aktualne hasło (konwalik)
SQLDialect = 3
Zabezpieczenie przed kasowaniem kaskadowym przy użyciu przeszukiwania w Delphi zamiast w SQL. Najczęściej stosuje się to z ClientDataSet bo wtedy nie trzeba rozpoczynać transakcji dodatkowym kodem, gdyż transakcja jest rozpoczynana automatycznie.
taLEASE.IndexName:=‘Propery_Number’;
If taLEASE.FindKey([taPROPERTYProperty_Number.AsInteger]) then
raise EDatabaseError.Create(‘Property_Number is in use - delete
failed’);
Ten fragment programu zabezpiecza przed usunięciem wierszy tabeli PROPERTY
które posiadają odwołanie do wierszy w tabeli LEASE.
ApplyUpdate() will not return an exception. You need to test the result of
ApplyUpdates manually:
if YourCDS.changecount > 0 then
try
If SQLClientDataset.ApplyUpdates(0)>0 Then
Raise Exception.Create('Error');
except
showmessage('Nie można zapisać z powodu współdzielenia');
SQLClientDataset.CancelUpdates;
SQLClientDataset.refresh
end
do SQLConnection.params trzeba wpisać
ServerCharSet=win1250
LocaleCode=1045
Normally you don't use lookup fields with
C/S or datasnap applications. If you need to include a description for
some code number, such as a part description for a part number, you do
it with a join query.
Change your SELECT to something like the following
Pole DEALER_NAME jest użyte jako lookup ale w inny sposób
SELECT P.*, D.DEALER_NAME
FROM PRODUCT P JOIN DEALER D
ON (P.DEALER_ID = D.DEALER_ID)
Set all of the ProviderFlags for the DEALER_NAME field object (dla TSQLQuery) to
false (tak samo należy zrobić dla wszystkich pól z tabeli lookup które są wymienione w instrukcji select) . Create an OnGetTableName event handler for the DataSetProvider
that contains
TableName := 'PRODUCT'; //Tu dajemy nazwę tabeli nie będącej tabelą lookup
=========
Drop a SQLConnection component from the
dbExpress page of the component palette on the
data module.
4. Name the SQLConnection component
EmployeeConnection and set its DriverName
property to InterBase.
5. Open the property editor for the Params property and
set the database parameter to the path to the sample
InterBase EMPLOYEE.GDB database. On most
installations, this will be c:\program
files\Borland\interbase\examples\dat
abase\employee.gdb.
6. Change the UserName and Password parameters if
you require different values to connect to your
InterBase server.
7. Set the LoginPrompt property to false so you will not
be prompted for a username and password each time
you run the program.
8. Set the Connected property to true to test your
connection, and then set it to false again.
The SQLConnection component also provides the
StartTransaction, Commit, and Rollback methods for explicit
transaction control. If you need to execute SQL statements that
do not return a result set, you can use the Execute or
ExecuteDirect methods of the SQLConnection component. No
dataset component is required.
Because the SQLQuery returns a read only unidirectional result
set, properties and methods relating to the Borland Database
Engine or to editing data are missing.
- Drop SQLQuery components on the
data module.
- Set the SQLConnection property to
EmployeeConnection.
- Name the EmployeeQry, the second.
- Set the SQL property of EmployeeQry to:
SELECT * FROM EMPLOYEE
WHERE DEPT_NO = :DEPT_NO
ORDER BY LAST_NAME
- Return to the EmployeeQry, open the Params
property editor and set the value of the DEPT_NO
parameter to XXX. Since this is an invalid value, it
causes no records to be displayed until the user has
entered a valid department number.
- Double-click EmployeeQry to open the Fields
Editor and add field objects for all fields.
- Select the EMP_NO field, expand the
ProviderFlags property, and set pfInKey to
true. Setting the pfInKey flag identifies the
EMP_NO field as the primary key. The
DataSetProvider (that you will add later) needs this
information to construct the SQL statements to apply
changes to the database.
- Select the FULL_NAME field; expand its
ProfiderFlags property, and set pfInUpdate and
pfInWhere to false. (prawdopodobnie to samo należy nastawić w podłączonym ClientDataSet) FULL_NAME is a computed
field, so it should not be updated or included in the
WHERE clause of any SQL statements generated by
the DataSetProvider.
- Set the Active property of EmployeeQry to true.
- Set the Active property of EmployeeQry to false.
- Set the Connected property of EmployeeConnection
to false.
Jeśli pola są automatycznie wypełniane przez trigery na serwerze (jak autoincrement) lub obliczane (calculated), to należy dla nich ustawić required:=false
If you need more flexibility than the SimpleDataSet offers, drop
a SQLQuery, DataSetProvider, and ClientDataSet on a form
or data module. Set the Connection property of the SQLQuery.
Set the DataSet property of the DataSetProvider to connect it
to the SQLQuery. Set the ProviderName property of the
ClientDataSet to connect it to the DataSetProvider. Select all
three components and choose Component | Create
Component Template from the main menu. Supply a class
name and palette page for your new template. Now, you can
drop three separate components on a data module as easily as
you can drop a single component.
If you need bi-directional scrolling and the ability to update data,
you will need to use the DataSetProvider and ClientDataSet
components.
- Add a DataSetProvider from the Data Access page of
the component palette.
- Set its DataSet property to EmployeeQry and its
Name property to EmployeeProv.
The DataSetProvider UpdateMode property lets you control
how the provider determines if another user has updated a
record you are trying to update since you read the record. When
the provider generates SQL statements to update the database
each UPDATE and DELETE statement includes a WHERE clause
to identify the record. If UpdateMode is set to upWhereAll,
the original value of every non-Blob field in the record is
included in the WHERE clause unless the field’s pfInWhere
provider flag is false. This means that your UPDATE or
DELETE will fail if another user has changed any of these
fields. Setting UpdateMode to upWhereChanged means that
only fields you have changed will be included in the WHERE
clause. Setting UpdateMode to upWhereKeyOnly will generate a
WHERE clause that contains just the primary key fields.
The provider’s Options property includes many flags that let you
control the provide/resolve process. If the
poCascadeDeletes option is true, the provider will not
generate SQL statements to delete the detail records for a master
record you have deleted. The provider assumes the database
server supports cascaded deletes and will delete the detail
records automatically. The poCascadeUpdates option provides
the same feature for changes to the master table’s primary key.
Ustawienie obu tych opcji na true jest wymagane żeby kasowanie i uaktualnianie
kaskadowe ustawione na serwerze bazy danych działało.
If the SQLQuery that supplies data to the provider has an
ORDER BY clause in its SQL statement, and you want the
records to retain that order in the ClientDataSet, set the
poRetainServerOrder flag to true.
If you want to be able to change the SQL statement in the SQLQuery by changing
the CommandText property of the ClientDataSet, set the
poAllowCommandText option to true.
If you create a BeforeUpdateRecord event handler for the
DataSetProvider, and the event handler may change the value of
a field in a record before the database is updated, set the
poPropogateChanges option to true. The provider will
now send any changes back to the ClientDataSet to update the
records it holds in memory.
The BeforeUpdateRecord event handler also gives you a place to
examine each record, before it is updated, and change any field
values. You can even block the update entirely by raising an
exception. This makes BeforeUpdateRecord a good place to
enforce business rules.
Drop ClientDataSet on the data module in the sample
application.
2. Set the ProviderName property to
EmployeeProv and set the Name property to
EmployeeCds.
3. Double-click EmployeeCds to open the Fields
Editor and add all field objects.
4. Right-click EmployeeCds and choose Fetch Params
from the context menu so the ClientDataSet will
update its parameter list to match the EmployeeQry
component.
7. Drop DataSource component on the data
module and name it EmployeeSrc.
8. Set its DataSet property to EmployeeCds.
You should now be able to set the EmployeeCds component Active property to true.
Assign a semicolon-delimited list of field names to the
IndexFieldNames property to sort the records in the
ClientDataSet in ascending order by those fields
(wielkość liter w nazwie pola nie ma znaczenia). To sort in
descending order or for faster performance on very large
datasets, create an index on the fields you want to sort by and
assign the index name to the IndexName property of the
ClientDataSet.
The PacketRecords property lets your control the number of
records that the provider fetches from the server at one time.
The default value of –1 tells the provider to fetch all records
returned by the source dataset’s SQL statement. Normally, this is
fine, but if you need to process a very large number of records,
you may need to retrieve them in small groups.