== Working with Firebird Databases ==

== Using a Database==

Database User:
* Default user: ''SYSDBA''
* Default password: ''masterkey''

Path to Data:
* Format: ''server:path\to\data''
* Example: ''localhost:C:\Program Files\Audina\ezFIT4\data''

== IBExpert: Creating Database for use by Firebird (Embedded) ==

* Database > Create Database
* Database properties:
** Server: Local
** Database: D:\programming\audina\EzFIT4\data\ezfitdb.gdb
** Client Library: D:\proj\photorpt\data\fbembed.dll
** SQL Dialect: 1
** User Name: SYSDBA
** Password: masterkey
** Register Database After Creating (checkbox)
* Database properties (General) in Register Database screen:
** Server: Local
** Server Version: Firebird 1.5
** Database File: D:\programming\audina\EzFIT4\data\ezfitdb.gdb
** Database Alias: DBezFIT
** User Name: SYSDBA
** Password: masterkey
** Client Library: D:\programming\audina\EzFIT4\data\fbembed.dll

== IBExpert: Creating Database Link for use by InterBase 6.0 ==

* Database > Register Database (<shift><alt><R>)
* Database properties (General):
** Server: Local
** Server Version: 6.0 - 6.1
** Database File: D:\programming\audina\EzFIT4\data\ezfitdb.gdb
** Database Alias: DBezFIT
** User Name: SYSDBA
** Password: masterkey

== IBExpert: Creating Tables in an existing Database ==

* Create the main table. SQL Script for Main Table:
<code sql>
/******************************************************************************/
/****              Generated by IBExpert 6/13/2005 3:47:55 PM              ****/
/******************************************************************************/

SET NAMES none;



/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/


CREATE GENERATOR gen_reportid;

CREATE TABLE report (
    reportid    INTEGER NOT NULL,
    shortname   VARCHAR(20) NOT NULL,
    longname    VARCHAR(200) NOT NULL,
    dir         VARCHAR(200),
    createdate  DATE NOT NULL,
    intro       BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    body        BLOB SUB_TYPE 0 SEGMENT SIZE 80
);




/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE report ADD CONSTRAINT pk_reportid PRIMARY KEY (reportid);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX namebydatex ON report (shortname, createdate);
CREATE INDEX shortnamex ON report (shortname);


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/


SET TERM ^ ;


/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: SET_REPORTID */
CREATE TRIGGER set_reportid FOR report
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.reportid IS NULL) THEN
    NEW.reportid = GEN_ID(gen_reportid,1);
END
^


SET TERM ; ^



/******************************************************************************/
/****                              Privileges                              ****/
/******************************************************************************/
</code>

* Create secondary table. SQL Script for Secondary Table:
<code sql>
/******************************************************************************/
/****              Generated by IBExpert 6/13/2005 4:03:11 PM              ****/
/******************************************************************************/

SET NAMES none;



/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE photo (
    reportid    INTEGER NOT NULL,
    createdate  DATE NOT NULL,
    filename    VARCHAR(50) NOT NULL,
    dir         VARCHAR(200),
    category    VARCHAR(200),
    caption     VARCHAR(300)
);




/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE photo ADD CONSTRAINT pk_photo PRIMARY KEY (reportid, createdate, filename);


/******************************************************************************/
/****                             Foreign Keys                             ****/
/******************************************************************************/

ALTER TABLE photo ADD CONSTRAINT fk_photo_report FOREIGN KEY (reportid) 
    REFERENCES report (reportid) ON DELETE CASCADE ON UPDATE CASCADE;


/******************************************************************************/
/****                              Privileges                              ****/
/******************************************************************************/
</code>

* Create stored procedures. Stored Procedure to GetReportID:
<code sql>
SET TERM ^ ;

CREATE PROCEDURE P_GET_REPORTID 
RETURNS (
    REPORTID INTEGER)
AS
begin
   REPORTID = GEN_ID(gen_reportid, 1);
end
^

SET TERM ; ^

GRANT EXECUTE ON PROCEDURE P_GET_REPORTID TO SYSDBA;
</code>


== Controlling InterBase Service from Command Line ==

* Use command sc to perform "service control" on any process running under Windows.
* ''sc'' Parameter //Start//. This key indicates when the service should be started. Possible variables are as follows:

^ Value ^ Meaning ^ 
| 0x00   | Boot     |
| 0x01   | System   |
| 0x02   | Autoload |
| 0x03   | On demand (manual) |
| 0x04   | Disabled (filesystem drivers will load anyway) |

* Disabling InterBaseServer service to run automatically. In this case, set:
  <code>C:\> sc localhost config InterBaseServer start=demand</code>

* Alternatively, modify Windows Registry to disable service:
  <code>My Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\InterBaseServer\Start = 0x00000003</code>


== Frequently Asked Questions ==

=== How to check whether a table exists or not? ===

  SELECT RDB$RELATION_NAME FROM RDB$RELATIONS /* all tables will be shown    */
     WHERE RDB$RELATION_NAME = 'your_TABLE'   /* particular table is checked */