Разработка приложений баз данных в среде Delphi. Часть 3. Interbase: Учебно-методическое пособие

  • Published on
    08-Dec-2016

  • View
    224

  • Download
    5

Embed Size (px)

Transcript

  • Delphi. III. InterBase

    -

    010501 (010200)

    , 2005

  • 2

    - 1 21.09.2005 .

    .. - - , . 4 / .

  • 3

    , , - InterBase. - Delphi [3] SQL. InterBase Borland -- : Novell NetWare, Windows , Linux, IBM, Hewlett-Packard, SUN, SGI . InterBase 10-20 , - 2 . 65535 , , - 132 . InterBase . Interbase - , - . Interbase , Oracle MS SQL, . Interbase ANSI SQL 92, Interbase - SQL . Interbase -. InterBase, Borland, . InterBase - (, FireBird, Jaffil) - , InterBase, - ibase.ru. Interbase - - - IBAdmin, IBManager, IBExpert .

  • 4

    1. InterBase

    1.1. InterBase InterBase FireBird, , . , FireBird 1.5 , , Firebird-1.5.2.4731-Win32. , ( , FireBird Windows ). FireBird Classic SuperServer. Classic , SuperServer . , . FireBird , . InterBas. . - , , , . -, Delphi Install InterBase Client. , gds32.dll Windows\System32. , .

    1.2.

    Interbase Console ( IBConsole) InterBase 6-7 . :

    SQL- . .

    IBConsole , SQL Explorer, Delphi. SQL Explorer BDE . , ( ), - IBExpert, , . - IBExpert - 2.5. - , ( Options Environment Option Interface Language), - (.. ) - .

  • 5

    1.3.

    , *.gdb ( InterBase) *.fdb ( FireBird)

    IBExpert

    , , . - , : Windows 98 ; Windows 2000/XP (Services), InterBase ( Firebird) Guardian InterBase Server. IBExpert DataBase - Create DataBase. Server Local, Database , :

    UserName SYSDBA, Password masterkey (, , ). SQL Dialect Dialect 3. SQL InterBase 6 FireBird . 1 , InterBase . . . IBExpert . . ( FireBird 1.5), , .

  • 6

    , InterBase . .

    DataBase Explorer NewSklad.

    , , ( , , , , , , , , ):

    . ,

  • 7

    . InterBase : Server_Name:Full_FileName, Server_Name , InterBase. NetBios, IP- . Full_FileName *.gdb, . : c1r214srv:d:\ib\4gr\MyBase\sklad.gdb. . , . , \\c1r214srv\MyBase\sklad.gdb. c1r214srv:H:\MyBase\sklad.gdb, H , . , D d:\ib, H. Full_FileName , . d:\ib\4gr\MyBase\sklad.gdb, . . IBExpert , c1r214srv . , , - , , - , SYSDBA masterkey (. . 1.7.2).

  • 8

    , :

    , H:\4gr\MyBase. , IBExpert DataBase -

    Register DataBase

    . InterBase : CharSet WIN1251 COLLATE PXW_CYRL .

  • 9

    Upper SQL . - , , UpCase ( ) CASEUDF, http://www.ibase.ru/ download/caseudf.zip - (. 3).

    1.4. , , (, -, SQL- ) . IBExpert , - Tables, New Table. Customers (. ). , ( ) ( - ). , PK.

    Ctrl+F9 DDL , , Commit . , IBExpert - (Commit), . , . , , - New Field, Ctrl+Alt+C ( ):

    http://www.ibase.ru/

  • 10

    IBExpert - . : . . - , : . . InterBase , , , , RDB$6. . Domains Database Explorer, CTRL+N ,

    . Varchar(40) not null, Address_Domain.

    1.5. ,

    InterBase - . ( ), . ,

  • 11

    , , IBExpert, Delphi, Web- .. . AutoIncrement Create Generator. , .

    Trigger Create Trigger, :

    , - . ( Procedure) , -, .

  • 12

    CREATE PROCEDURE SP_GEN_CUSTOMERS_ID RETURNS (ID INTEGER) AS BEGIN ID = GEN_ID(GEN_CUSTOMERS_ID, 1); SUSPEND; END , - ( Database Explorer). - - . (. ). , . - (After Insert ..) .

    . , , - . , - DDL. ( ).

    1.6. SQL-

    IBExpert SQL. SQL Editor ( F12) , , - .

  • 13

    Query Builder ( Tools) , , SELECT. Builder , Edit - , Result . - F9, , (NewSklad) :

    Script Executive SQL- . , , Case- ERWIN. sklad.sql - Create Table. Script Executive.

    F9. DataBase Explorer , -, , , .

  • 14

    1.7.

    InterBase SYSDBA . - masterkey. . () . SYSDBA , - . 31 , - , . - . 8 - , 32 . InterBase. 1.7.1. . - Tools User Manager. SYSDBA masterkey. ( ) Edit.

    . IBExpert - , -, . . !

  • 15

    1.7.2. User Manager Add

    4k4gr , (owner) . c 4k4gr . , . 1.7.3. , . ; , . , - PUBLIC, . (owner) , ( ). Grant (.) , , . SQL- GRANT - - , (. . 3.4), -. ( ), - GRANT, , , , , . SQL- REVOKE .

    SELECT DELETE INSERT UPDATE EXECUTE

  • 16

    REFERENCES , ,

    ALL SELECT, DELETE, INSERT, UPDATE, REFERENCES

    IBExpert. - Tools - Grant Manager. :

    , SYSDBA. - 4k4gr PUBLIC :

    - . -

  • 17

    Grant WITH GRANT OPTION. -, , . - , (. ). 1.7.4. . Windows 2000/XP. , . , , isc4.gdb, - . , . , , . , - . Manager Guest. Roles User Manager.

    Membership .

    With ADMIN OPTION , , , , , .. . Grant Manager, . . , ,

  • 18

    , SQL, IBExpert, ..

    1.8. UDF SQL, min(), max(), avg(), InterBase UDF (, ). UDF SQL. , Firebird UDF - . UDF (, Delphi, Fortran ..). , , DLL InterBase. UDF 10 , InterBase, . . ( ), . :

    Interbase Object Pascal Integer Integer Double precision Double Cstring Pchar Date Type IBGateTime=record

    Days: integer; Msec: cardinal; End;

    , UDF BY VALUE. , , . - , . . -: DECLARE EXTERNAL FUNCTION name [datatype | CSTRING() [,datatype | CSTRING()]] RETURNS {datatype [BY VALUE] | CSTRING()} [FREE_IT] ENTRY_POINT entryname MODULE_NAME modulename; Name UDF SQL-; -

    ,

  • 19

    ENRT_POINT Datatype

    RETURNS

    BY VALUE ,

    CSTRING( )

    FREE_IT UDF , ,

    entryname , ;

    modulename , , UDF

    : InterBase 6, UDF ..\InterBase\udf . UDF , SQL, . SQL-, - . UDF caseudf.dll. . , - DLL, Delphi. library caseudf; { Copyright Dmitry Kuzmenko, Epsylon Technologies } { e-mail: dima@demo.ru, web: http://ib.demo.ru } { , } uses Windows, SysUtils; function UpCase(CStr: PChar): PChar; cdecl; export; begin CharUpperBuff(CStr, Length(CStr)); Result:=CStr; end; { , } exports UpCase; begin isMultiThread:=True; end.

    mailto:dima@demo.ruhttp://ib.demo.ru

  • 20

    DataBase Explorer UDFs New DLL:

    UDF SQL, DDL, : DECLARE EXTERNAL FUNCTION UPCASE CSTRING(255) RETURNS CSTRING(255) ENTRY_POINT 'UpCase' MODULE_NAME 'CASEUDF.DLL' UDF *.dll , - SQL-, . UDF SQL Builder:

    2. - API . IBExpert, , , Delphi. -- , . , InterBase ADO, ADO.NET, ODBC, JDBC, BDE, DbExpress . Delphi ADO, BDE DbExpress. , BDE - . ADO Windows ADO- InterBase ( . www.ibase.ru).

    http://www.ibase.ru

  • 21

    DbExpress, Borland, , . Interbase Express (IBX), Delphi InterBase. FIBPlus - Devrace , - , .

    2.1. , Delphi, TDataSet, BDE- . , - , . IBX. IBDataBase, ( , , , ), - DataBase Component Editor, - .

    . , c1r214srv:D:\IB\4gr\MyBase\NewSklad.gdb DataBaseName .

  • 22

    LoginPrompt False, . - , , IBDataBase - , (. 3). , , . IBTransaction, - . IBDataBase IBTransaction property DefaultTransaction: TIBTransaction. , IBTransaction Transaction: TIBTransaction (IBQuery, IBTable .). -, - , . StartTransaction TIBTransaction Active:= True. -, , IBTransaction, , tbCust.Open. Commit ( ) RollBack () - , -. , - ( !). - IBTransaction property InTransaction: Boolean. , - , .. .

    2.2.

    2.2.1. TIBTable TIBTable , . - SELECT * FROM ,

  • 23

    . . TTable. - , (View). Delphi 7, - . 2.2.2. TIBQuery , TIBQuery, , , ReadOnly, True, . , TIBQuery, TIBUpdateSQL, , . TIBUpdateSQL . TIBUpdateSQL UpdateObject TIBQuery. Update SQL Editor - SQL- . 2.2.3. TIBDataSet IBTable, IBQuery IBUpdateSQL, . SQL- - SelectSQL TStrings (, - ). IBUpdateSQL , , UpdateSQL. SQL- - InsertSQL, ModifySQL, DeleteSQL. IBQuery IBDataSet . property GeneratorField: TIBGeneratorField, ( ). - , . 2.2.4. TIBSQL SQL- . , TDataSet, - Tomponent,

  • 24

    . - , , - , Next. ExecQuery.

    3.

    3.1. - . - , . , ( , ), Cust_ID Prod_ID. - , -. . , - , . Case- Erwin . (, IBExpert Database Designer).

    (Order) (Product), - , - . , , ..\Borland\Delphi7\Demos\Db\IBMastApp Delphi ( ..\Borland\Borland Shared\Data\mastsql.gdb).

    3.2.

    SQL - InterBase :

  • 25

    SET TERM ^; /* */ /* */ CREATE TABLE Customers ( Cust_ID INTEGER NOT NULL, CustName VARCHAR(20) NOT NULL, Address CHAR(40), PRIMARY KEY (Cust_ID) )^ CREATE TABLE Products ( Prod_ID INTEGER NOT NULL, ProductName VARCHAR(20) NOT NULL, Price DECIMAL(10,2) NOT NULL CHECK (Price > 0), /* */ Rest INTEGER NOT NULL CHECK (Rest >= 0), PRIMARY KEY (Prod_ID) )^ CREATE TABLE Orders ( Num INTEGER NOT NULL, Cust_ID INTEGER NOT NULL, Prod_ID INTEGER NOT NULL, ProdCount INTEGER NOT NULL CHECK (ProdCount > 0), Total NUMERIC(10,2) NOT NULL,/* ..*/ SaleDate DATE NOT NULL, PRIMARY KEY (Num), FOREIGN KEY (Cust_ID) REFERENCES Customers, FOREIGN KEY (Prod_ID) REFERENCES Products )^ /* */ CREATE GENERATOR GEN_CUSTOMERS_ID ^ SET GENERATOR GEN_CUSTOMERS_ID TO 0 ^ CREATE GENERATOR GEN_ORDERS_ID ^ SET GENERATOR GEN_ORDERS_ID TO 0 ^ CREATE GENERATOR GEN_PRODUCTS_ID ^ SET GENERATOR GEN_PRODUCTS_ID TO 0 ^ /* */ CREATE PROCEDURE SP_GEN_CUSTOMERS_ID RETURNS (ID INTEGER) AS BEGIN ID = GEN_ID(GEN_CUSTOMERS_ID, 1); SUSPEND; /* */ END ^ /* */ CREATE TRIGGER PRODUCTS_BI FOR PRODUCTS ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.PROD_ID IS NULL) THEN NEW.PROD_ID = GEN_ID(GEN_PRODUCTS_ID,1); END ^

  • 26

    CREATE TRIGGER PRODUCTS_AD0 FOR PRODUCTS ACTIVE AFTER DELETE POSITION 0 AS begin /* */ delete from Orders where orders.prod_id = products.prod_id; end ^ CREATE TRIGGER ORDERS_BI FOR ORDERS ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.NUM IS NULL) THEN NEW.NUM = GEN_ID(GEN_ORDERS_ID,1); END ^ COMMIT ^ SET TERM ; ^ Script Executive IBExpert. . , - .

    3.3. 3.3.1. ( File New Data Module) - . . ds - TDataSorce, id TIBDataSet, iq TIBQuery, . .

    1. idbSklad: TIBDatabase itaSklad: TIBTransaction. - idbSklad DataBase Editor

  • 27

    (..2.1), , .

    , , DataBase Editor . - . 2. iqInsert ,...

Recommended

View more >