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

  • Published on
    08-Dec-2016

  • View
    214

  • Download
    2

Embed Size (px)

Transcript

  • Delphi. II.

    - (010200)

    2003

  • 2 - 6 26.05.03 . : .., . . , . 4 / 4 / .

  • 3 , I Delphi , - . Delphi 6 Interbase 5.5-6.0. Delphi [3] SQL. 1, 4, . 2.1-2.3 .. , . 2.4-2.5, 3 . . . - InterBase. InterBase . InterBase, Borland , :

    . InterBase - : Novell NetWare Windows 2000 Server, IBM, Hewlett-Packard, SUN, SGI . InterBase ANSI SQL 92, ( ) Windows, Unix.

    InterBase , , .

    IB 10-20 , 2 . 65535 , 132 .

    (Oracle, DB2) . InterBase Case- , . InterBase , Magnavox , , , .

  • 4 1. InterBase

    1.1. Interbase Interactive SQL Interbase Windows Interactive SQL ( ISQL) InterBase . :

    SQL- .

    Server Manager. WISQL , , . WISQL , InterBase . InterBase 6-7 IBConsole. , . 1.1.1. , Interbase (, 4k4gr) . , Interbase Server Manager (. . 2.1). , InterBase c1r214srv, d:\ib\4k . d:\ib V: . , V:\4k\folder. ISQL File | Create DataBase. ( Windows), Local Engine , (SYSDBA) (masterkey - ). , InterBase Server Manager. Windows- Network Protocol TCP/IP NetBEUI, Novell- Novell SPX.

  • 5

    , (D:\ib\4k\folder\sklad.gdb), (, V:\4k\folder\sklad.gdb) . , D:\ib\4k\folder sklad.gdb, . . 1.1.2. InterBase , , (, , SQL- ) . ISQL "File|Connect to Database", . , , CREATE TABLE. *.gdb. ISQL SQL: SQL Statement; - "File|Run an ISQL Script..." (). ( .sql.) SQL- , . SQL- . , , . 4.2. Interbase Delphi SQL Explorer.

  • 6 . InterBase : DEFAULT CHARACTER SET WIN1251 COLLATE PXW_CYRL , LANGUAGE DRIVER = Pdox ANSI Cyrillic BDE. UPPER SQL. , ( !) , UPPER UDF- CASEUDF, http://www.ibase.ru/ download/caseudf.zip (. .2.5). 1.1.3. ISQL : , , .. View Extract. , .4 Sklad, "Extract|SQL Metadata for Table" Cust. ISQL Output , , : /* Extract Table CUST */ /* Table: CUST, Owner: RUD */ CREATE TABLE CUST (NC SMALLINT NOT NULL, NAME VARCHAR(20), ADDRESS VARCHAR(20), CONSTRAINT CUSTPRIMARYKEY1 PRIMARY KEY (NC));

    1.2. SQL Explorer InterBase SQL Explorer , . 1.2.1. . New, New Database Alias IntrBase. , Interbase1 IbSklad, Server Name c1r214srv:d:\ib\4gr\folder\sklad.gdb, User Name - 4k4gr. : ( TCP/IP), \\\ ( NetBEUI). Local Engine ( ), .

    http://www.ibase.ru/

  • 7

    , File Apply. 1.2.2. + ( ). , 4k4gr . 1.2.3. Tables New ( ). Table1, Detal. Detal , Columns New ( ), Column1 nd, integer, Nulls No ( nd ).

  • 8 , Detal File Apply. , :

    1.2.4. . Primary Key - New, Columns New nd.

    1.2.5. . Generators - New, G1, 0.

  • 9

    , SQL- Text. ( Apply). 1.2.6. . Detal Triggers New. By_nd, Definition Type Before insert, Text Begin End new.nd=gen_id(g1,1);

    . , , .

  • 10 Procedures , , .

    1.3. InterBase , - , , , *.dbf, Paradox, Access. InterBase, , , ? ( ) TBatchMove, . , , . TBatchMove: Source ( ), , Destination , , Mode - . batCopy, Destination Source . Execute. - datapump.exe ( TBatchMove), BDE. , , . InterBase . , . 2. -

    2.1. 2.1.1. . . , . - . 2.1.2. . :

  • 11

    . -,

    CHECK

    ,

    SQL- UDF,

    ,

    TDataBase. DataSet, , .

    TQuery TTable. WHERE , , , .

    2.2. 2.2.1. , . , . SQL . (Insert, Edit ..) TDBGrid. Post Cancel. . SQL (SET TRANSACTION, COMMIT, ROLLBACK), TDataBase [2] . . StartTransaction , , , . . Commit ( ) RollBack ( , ). IsTransaction Boolean ,

  • 12 ( !). , ( StartTransaction) . try except. ( ) , ( Detal) ( CD): if Otpusk
  • 13 1 : 2 : 1 : 2 : , ( DirtyRead ). RepeatableRead . (Repeatable) , . , 2 . , , .. . ReadCommited. 2.2.3. (), . , - - ApplyUpdates. - CommitUpdates - CancelUpdates. , read only (. [3]). : dbSklad.StartTransaction; // try qrDetal.ApplyUdates; // qrCD.ApplyUpdates; // dbSklad.Commit; // qrDetal.CommitUdates; qrCD.CommitUpdates; // except dbSklad.RollBack; // qrDetal.CancelUdates; // qrCD.CancelUpdates; // ShowMessage(' !'); end;

    2.3. IBExpress , Delphi Interbase, Interbase ( InterBase Express, IBX). , BDE.

  • 14 IBTable, IBTable, IBTable BDE- . BDE IBX. , , . . 2.3.1. IBX.

    IBDataBase, , DataBase Component Editor,

    , c1r214srv:D:\IB\IB_SKLAD2.GDB, DataBaseName. !

    , IBQuery, IBUpdateSQL ( , , ReadOnly).

    IBDataSet, IBQuery IBUpdateSQL ( ). SQL- SelectSQL TStrings ( ). IBUpdateSQL , , UpdateSQL [3]. SQL- InsertSQL, ModifySQL, DeleteSQL.

  • 15 IBQuery,

    IBDataSet, IBTable ( GeneratorField: TIBGeneratorField). .

    .

    2.3.2. IBDataBase, IBTransaction, . IBDataBase IBTransaction DefaultTransaction: TIBTransaction. , IBTransaction Transaction: TIBTransaction (IBQuery, IBTable .). - , , . IBTransaction StartTransaction, Commit RollBack, , , .2.2.1, . StartTransaction Active True. , ( !). , , , IBTransaction. . 2.2.1 ( ), StartTransaction - . 4. Commit RollBack , . ( InTransaction: Boolean), , , OtpForm . BDE- IBX- .

    2.4. (Views) , .

  • 16 , (view). , , . , (View) - , , , "" . , , . . , , , , . , . : CREATE VIEW [([, ])] AS < select>; . , . , SELECT. , job employee.gdb ( , InterBase) 8 job_code, job_grade, job_country, job_title, min_salary, max_salary, job_requirement, language_req. ( , ) CREATE VIEW JOB_SALARY_RANGES AS SELECT JOB_CODE, MIN_SALARY, MAX_SALARY FROM JOB; : 1. . . 2. . , , , . 3. . , . , , , , . 4. . , .

  • 17 , , . , ( , ). . , TQuery (. [1], [3, .39]). Delphi , TableName SQL, TQuery.

    2.5. , SQL- , , min(), max(), avg(), InterBase UDF ( , ). (DDL Windows), InterBase, UDF . UDF (, Pascal, 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()]]

  • 18 RETURNS {datatype [BY VALUE] | CSTRING()} [FREE_IT] ENTRY_POINT entryname MODULE_NAME modulename; Name UDF SQL-;

    , ENRT_POINT

    Datatype RETURNS BY VALUE ,

    CSTRING() FREE_IT UDF ,

    entryname ,

    modulename , ,

    UDF ( ) DECLARE EXTERNAL FUNCTION UDF, , . , , . . , , lib InterBase (, C:\Program Files\InterBase\lib) Windows. InterBase 6, UDF ..\InterBase\udf ! UDF , SQL, . SQL-, . InterBase UDF- ib_udf.dll ( ..\InterBase\udf ). UDF . , , examples. . ib_udf.dll . 3. InterBase

    3.1. InterBase ( ) , ) ,

  • 19 InterBase , ) . isc4.gdb ..\Interbase. : User name , Password UID ,

    GID , Full name , ( ). . ( , , ) gsec Windows- IB Server Manager, .

    3.2. Tasks|User Security IB Server Manager, InterBase Security dialog box , :

    . ( Add User).

    ( Modify User). : , .

    . Delete, .

    . SYSDBA , . InterBase SYSDBA masterkey. . () . SYSDBA , SQL- , , shutdown . :

    SYSDBA, ,

  • 20 ( SYSDBA ).

    31 , , .

    . 8 , 32 .

    , InterBase, , :

    . , FINANCE . .

    , .

    SQL- : CREATE DATABASE 'c1r214srv:d:\ib\folder\ibSklad.gdb' USER

    "SVETA" PASSWORD "privet" , SVETA c1r214srv privet, SVETA (owner) IB_Sklad.gdb.

    3.3. . ; , . , PUBLIC, . . (owner) , ( ). Grant (.) , , . - , . SQL- GRANT - , (. . 3.4), .

  • 21 ( ), GRANT, , , , , . SQL- REVOKE . GRANT ( . Language Reference , InterBase): GRANT { ON [Table] {tablename | viewname} TO { | | GROUP Unix_group} | TO {PUBLIC | role_grantee_list}}; : = {ALL [PRIVILEGES] | } ={ SELECT | DELETE | INSERT | UPDATE [(col [, col ]) ] | REFERENCES [(col [, col ...])] [, ...]}} = { PROCEDURE procname | TRIGGER trigname | VIEW viewname | PUBLIC

    [, ]} ={ [USER] username | rolename | Unix_user} [, ...] [WITH GRANT OPTION] = rolename [, rolename] = [USER] username [, [USER] username ]

    [WITH ADMIN OPTION]

    SELECT DELETE INSERT UPDATE EXECUTE

    REFERENCES , ,

    ALL SELECT, DELETE, INSERT, UPDATE, REFERENCES

    : GRANT ALL ON detal TO Vasya - Detal Vasya. GRANT UPDATE (ost) ON detal TO Vasya - Ost.

  • 22

    3.4. . , . CREATE ROLE . GRANT . , GRANT, . , , , WISQL. : CREATE ROLE admins // admins GRANT ALL,EXECUTE ON cust TO admins // GRANT admins TO sveta, vasya // - CONNECT 'c1r214srv:d:\ib\folder\ibSklad.gdb' USER 'SVETA'

    PASSWORD 'privet' ROLE 'admins'

    3.5. InterBase Server Manager InterBase. :

    ; ; "" ; / ; .

    . 3.5.1. , Server Manager, , . :

    . / " " - , . .

    , . , , .

    .

  • 23 . .

    3.5.2. Borland InterBase - . , , . "Back Version" ( ) " " -...

Recommended

View more >