Delphi database programming 1

Delphi Database Programming 1

Creating databases and tables:

Limitations on creation of tables:
- various database formats have differing limitations thus to ensure compatibility and migration between databases, it is strongly recommended you restrict yourself to these limitations
- field names:
  - must not include spaces or the characters:
  - must not use keywords such as "Date" (this causes errors in SQL statements)
  - length <
Creation of database:
- Paradox databases:
  - a database is just a Window's directory, and a BDE alias is a pointer to that directory
- Other databases:
  - cannot create databases within Delphi & thus usually require the specific server's utility to create databases:
    - Sybase: Sybase Central
    - MS Access: MS Access
Creation of tables:
- basic table creation / data copy mechanisms:
  - Create new table:
    - via server utility:
      - Sybase: Sybase Central
      - MS Access: MS Access
    - via BDE aliases:
      - Paradox/dbase tables via Borland's Database Desktop or Paradox front end
      - within Delphi:
        
        programmatically using TTable.createTable methodology
        
        via TQuery SQL language
        
        via TClientDataset:
        
        use fields editor to define fields (consider using Data Dictionary)
        
        then can upsize using TBatchmove
    - via ADO:
      - within Delphi 5 or higher:
  - Copy table structure from another table (ie. upsizing):
    - Via BDE aliases:
      - Within Delphi, use TBatchMove component if table exists in other format
      - Delphi Desktop version is supplied with Database Desktop which can copy a table to SQL format
      - Delphi C/S version is supplied with Datapump:
        
        allows more finesse than TBatchmove
  - metadata features such as indices, foreign keys, check constraints, stored procedures, triggers, etc requires use of either:
    - Delphi TQuery SQL script
    - Server database utilities (eg. Sybase Central)
- table design:
  - normalisation - avoidance of repetitious data
  - ensure field size smallest needed
  - ensure field names are user-friendly & avoid SQL key words such as "Date"
Upsizing databases to client server:
- create a server database
- create table(s) within server database taking into account:
  - data type differences:
    - Sybase SQL Anywhere vs Sybase System 11:
      - NUMERIC has different default precision & scale settings
      - System 11 MONEY requires a user-defined NUMERIC(20,4) allowing NULL in SQL Anywhere
      - SQL Anywhere does not support NCHAR as it supports multi-byte character sets within the CHAR datatype
  - data security and integrity
  - transaction control
  - data access rights
    - even when migrating b/n Sybase SQL Anywhere & Sybase Series 11, you need to recreate your users, groups & permissions
    - System 11 uses 5 security roles vs SQL Anywhere of 3
    - System 11 has 2 levels of user (server logins, db users); SQL Anywhere only has db user level;
    - System 11 allows users to belong to only 1 group other than PUBLIC
  - data validation
  - locking differences:
    - System 11: supports levels 0,1,3 isolation levels & page-level locking
    - SQL Anywhere: supports all 4 isolation levels & row-level locking => avoid level 2 & row-locking
  - field naming constraints
- may need to modify SQL, trigger & stored procedure statements:
  - System 11 allows access to multiple databases from 1 SQL whereas SQL Anywhere doesn't, this is because the 2 platforms handle servers & databases differently:
    - SQL Server implements a master database that stores information about all the individual databases on the system.
    - System 11, there is a fixed relationship where each db is defined for one & only one server
    - SQL Anywhere, any server can start and use any db
  - Default engine behaviour:
    - SQL command delimiter: SQL Anywhere = semi-colon; System 11 = "Go";
    - can't use username as "DBO" in SQL server
    - String comparisons:
      - System 11 ignores trailing blanks in comparisons whereas SQL Anywhere doesn't
      - SQL Anywhere is case insensitive by default, System 11 has no default but is often case sensitive
    - SQL Anywhere has restrictive name space indices
    - System 11 has restrictive name space for trigger names
    - System 11 does not differentiate b/n quote types & treats them both as constraints whereas SQL Anywhere treats full quotes as delimited identifiers and single quotes as constraints
    - transaction Mx:
      - can SET CHAINED ON in System 11 but SQL Anywhere does not have option for unchained mode at all
  - SQL dialects:
    - try and stick with ANSI standards as other extensions will need to be re-written:
      - SQL Anywhere uses Watcom SQL and Sybase Transact-SQL
      - System 11 uses Sybase Transact SQL

Accessing databases from Delphi:

create the database and tables within it
ensure BDE is installed on system and registry settings point to its location
if using ODBC:
- install 32bit ODBC drivers
- configure 32bit ODBC via Control Panel applet
- configure BDE via BDE Administrator:
  - add ODBC driver
  - add new alias that uses the ODBC driver and points to desired database as set up in ODBC
for local file-based databases using native links:
- paradox, dbase tables are accessed via the STANDARD driver
- ASCII tables are accessed via the ASCII driver
- access files are accessed via access driver which then accesses the JET engine which needs to be present too
for SQL files using native links:
- ensure Borland SQL links are installed
for remote databases, ensure network is connected, for troubleshooting TCP/IP networks:
- is server's client-side connection properly configured?:
  - is TCP/IP communications software installed? is the proper version of WINSOCK.DLL installed?
  - is Client for MS Networks installed?
  - is Connect to NT on logon checked, and is domain name set eg. whcn
  - can you ping the server?
- are the DLL's for your connection and database drivers in the search path?
in Delphi app:
- need the following components (on a form or data module):
  - TDatabase (not needed for local tables):
    - set which database to access:
      - set AliasName to the BDE alias, or,
      - set DriverName to the BDE driver AND
    - optionally, set login parameters (user name & password) in the params property
    - optionally, uncheck the display of a login prompt
    - set databasename to any local alias so that TDatasets can connect to it
    - set connected = true to attempt connection to the database
  - a TDataset component:
    - common to all:
      - set databaseName to either:
        
        the TDatabase.DatabaseName property if a TDatabase component is used
        
        a BDE alias
        
        a directory path if local table is being accessed
      - optionally:
        
        once tablename or SQL is set:
        
        can create persistent TFields as well as create in-memory calculated fields and set their properties via double clicking on Tdataset component then right-click & select Add Fields or New Field to create a calculated field
        
        ensure all posted records are immediately saved to physical table rather than waiting until BDE unloads it from memory (otherwise, new data may be lost if computer crashes prior to correct shutdown procedure):
        
        in AfterPost method write:
        
        DBISaveChanges((Dataset as TwwTable).Handle);
        
        add "BDE" to uses clause
    - TTable (equivalent to Select * from tablename):
      - set TableName
      - optionally set an index to use
      - set Active = true to open table
    - TQuery:
      - set SQL strings, conforming to ANSI '92 SQL specifications
      - optionally:
        
        create a parameterised query using the where xxx = :parameter syntax and either:
        
        setting the datasource property to access the parameter from another datasource component, thereby linking datasources, or,
        
        set params property at design time, or:
        
        programmatically, can use either:
        
        ParamByName('parameter').asString := Edit1.text
        
        Params[0].asString := Edit1.text
        
        consider calling the prepare method prior to opening if intending to run same dynamic query multiple times as this will speed up queries but use up some database resources, remember to call unprepare method when query no longer needed
        
        set unidirectional = true to speed up queries if no need for backward navigation through result set
      - run the query by either:
        
        set Active = true to run a static query
        
        call ExecSQL method to run a query that does not return a result set, eg. INSERT
        
        call Open method to execute a query that produces a result set of records which by default is read only and can only be updated by either:
        
        set RequestLive = true, for this to be possible, the following must apply to SQL:
        
        involve only a single table
        
        omit the ORDER BY clause
        
        omit aggregate functions such as SUM, AVG
        
        omit calculated fields in select list
        
        WHERE clause must only contain scalar constants
        
        if Sybase table, table must have a unique index
        
        NB. if this fails then result set will be read only for local tables, but if query is a SQL passthrough query, then an error code will be returned
        
        use cached updates:
        
        add a TUpdateSQL component
        
        enter the SQL update statement for the result set to the update component's ModifySQL, InsertSQL or DeleteSQL properties
        
        set the query component's CachedUpdate property to true
    - TStoredProc component to run a SQL stored procedure
      - similar procedures as for TQuery component but instead of setting SQL property, you need to set the StoredProcName property and the Params property
  - a TDatasource component to allow user access to the dataset via GUI controls:
    - set Dataset property to the name of the TDataset component
  - user visible data-aware controls:
    - set Datasource property to TDatasource component's name
    - eg:
      - DBNavigator provides table navigation buttons such as first, prior, next, last, insert, delete
      - for field controls, set Fieldname
      - for lookup controls, set LookupTable and lookupField properties as well as may need to set Selected property
      - if a InfoPower TwwDBGrid is used, double click on it to create persistent columns allowing you to edit display properties including displayed name, size, picture mask for validation of input, edit control type such as checkbox, lookup dialog box, etc
      - filterDialog for further filtering of table
      - locateDialog for searching table
    - optionally:
      - if using more than one TDataset which the user accesses, ensure DBNavigator, IncrementalSearch, LocateDialog controls are pointing to the last accessed TDatasource control via adding code to each GUI DB-aware control's OnEnter event:
        
        DBNavigator1.datasource := (Sender as TDBControl).datasource;
      - similarly, if use a tabbed notebook with different TDataset's accessed on each page, add code to its OnChange event so that DBNavigator, etc points to the correct TDatasource:
        
        var grid: TwwDBGrid;
        
        begin
        
        case TabbedNotebook1.pageIndex of
        
        0: grid := wwDBGrid1; {assuming the grid on pageIndex 0 is called wwDBGrid1}
        
        end;
        
        wwDBGrid1OnEnter(grid); {assumes you have created an OnEnter event as above}
        
        end;