Troubleshooting Firebird Database

Note:  I will be using the employee database(/opt/firebird/examples/empbuild/employee.fdb) which is available by-default with the Firebird 2.5.7  in this post.

How to connect to a Firebird Database and test (using command-line)

1. Go to /opt/firebird/bin directory.
2. Run ./isql (Command-line tool for Firebird)

udara@Razorx1:/opt/firebird/bin$ ./isql
Use CONNECT or CREATE DATABASE to specify a database
SQL>

3. Run connect "/opt/firebird/examples/empbuild/employee.fdb"

SQL> connect "/opt/firebird/examples/empbuild/employee.fdb"
CON>

4. Provide the user/password to connect, note the semicolon (;) at the end.

CON> user 'SYSDBA' password 'admin';
Database:  "/opt/firebird/examples/empbuild/employee.fdb", User: SYSDBA
SQL>

5. Run the intended query,

SQL> select * from employee;

 EMP_NO FIRST_NAME      LAST_NAME            PHONE_EXT                 HIRE_DATE DEPT_NO JOB_CODE JOB_GRADE JOB_COUNTRY                    SALARY FULL_NAME                            
======= =============== ==================== ========= ========================= ======= ======== ========= =============== ===================== =====================================
      2 Robert          Nelson               250       1988-12-28 00:00:00.0000  600     VP               2 USA                         105900.00 Nelson, Robert                       
      4 Bruce           Young                233       1988-12-28 00:00:00.0000  621     Eng              2 USA                          97500.00 Young, Bruce                         
      5 Kim             Lambert              22        1989-02-06 00:00:00.0000  130     Eng              2 USA                         102750.00 Lambert, Kim                         
      8 Leslie          Johnson              410       1989-04-05 00:00:00.0000  180     Mktg             3 USA                          64635.00 Johnson, Leslie                      
      9 Phil            Forest               229       1989-04-17 00:00:00.0000  622     Mngr             3 USA                          75060.00 Forest, Phil                         
     11 K. J.           Weston               34        1990-01-17 00:00:00.0000  130     SRep             4 USA                          86292.94 Weston, K. J.                        
     12 Terri           Lee                  256       1990-05-01 00:00:00.0000  000     Admin            4 USA                          53793.00 Lee, Terri   

How to enable trace configuration

1. Open /opt/firebird/fbtrace.conf in your favorite text editor and update relevant configuration.

How to trace the database

1. Go to /opt/firebird/bin directory.
2. Run ./fbtracemgr with proper parameter set.

udara@Razorx1:/opt/firebird/bin$ ./fbtracemgr -SE service_mgr -START -NAME fire -CONFIG ../fbtrace.conf -USER SYSDBA -PASS admin

Sample trace output for the select * from employee;query.

2017-02-22T11:58:23.2200 (1980:0x7f85df7ae678) TRACE_INIT
    SESSION_4 fire
   

2017-02-22T11:58:23.2210 (1980:0x7f85df7ae678) PREPARE_STATEMENT
    /opt/firebird/examples/empbuild/employee.fdb (ATT_63, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
    /opt/firebird/bin/isql:7445
        (TRA_264, READ_COMMITTED | NO_REC_VERSION | WAIT | READ_WRITE)

Statement 128:
-------------------------------------------------------------------------------
select * from employee
      0 ms

2017-02-22T11:58:23.2210 (1980:0x7f85df7ae678) FREE_STATEMENT
    /opt/firebird/examples/empbuild/employee.fdb (ATT_63, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
    /opt/firebird/bin/isql:7445

Statement 127:
-------------------------------------------------------------------------------
select * from employee

2017-02-22T11:58:23.2220 (1980:0x7f85df7ae678) EXECUTE_STATEMENT_START
    /opt/firebird/examples/empbuild/employee.fdb (ATT_63, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
    /opt/firebird/bin/isql:7445
        (TRA_263, CONCURRENCY | WAIT | READ_WRITE)

Statement 128:
-------------------------------------------------------------------------------
select * from employee

2017-02-22T11:58:23.2260 (1980:0x7f85df7ae678) CLOSE_CURSOR
    /opt/firebird/examples/empbuild/employee.fdb (ATT_63, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
    /opt/firebird/bin/isql:7445

Statement 128:
-------------------------------------------------------------------------------
select * from employee

Firebird trace utility

Firebird Trace utility.
Usage: fbtracemgr <action> [<parameters>]

Actions:
  -STA[RT]                              Start trace session
  -STO[P]                               Stop trace session
  -SU[SPEND]                            Suspend trace session
  -R[ESUME]                             Resume trace session
  -L[IST]                               List existing trace sessions

Action parameters:
  -N[AME]    <string>                   Session name
  -I[D]      <number>                   Session ID
  -C[ONFIG]  <string>                   Trace configuration file name

Connection parameters:
  -SE[RVICE]  <string>                  Service name
  -U[SER]     <string>                  User name
  -P[ASSWORD] <string>                  Password
  -FE[TCH]    <string>                  Fetch password from file
  -T[RUSTED]  <string>                  Force trusted authentication

Examples:
  fbtracemgr -SE remote_host:service_mgr -USER SYSDBA -PASS masterkey -LIST
  fbtracemgr -SE service_mgr -START -NAME my_trace -CONFIG my_cfg.txt
  fbtracemgr -SE service_mgr -SUSPEND -ID 2
  fbtracemgr -SE service_mgr -RESUME -ID 2
  fbtracemgr -SE service_mgr -STOP -ID 4

Firebird sample with WSO2 DSS

I'm going to provide instructions to showcase how we can use Firebird data-source with WSO2 DSS.

Note:
I will be using the employee database(/opt/firebird/examples/empbuild/employee.fdb) which is available by-default with the Firebird 2.5.7  in this post.

Following are the Firebird/JDBC driver and WSO2 DSS versions which I have tried.

• Firebird: 2.5.X
• JDBC Driver: jaybird-full-2.2.12
• WSO2 DSS: 3.5.1

Steps:

1. Download the JDBC driver for Firebird and copy into the <DSS_HOME>/repository/components/lib/ directory.
2. Start the DSS node.(or restart if it's already started)
3. Add a new Data Service with the following configuration.

Datasource type: RDBMS

Database Engine: Generic

Driver Class: org.firebirdsql.jdbc.FBDriver

URL: jdbc:firebirdsql://localhost:3050//opt/firebird/examples/empbuild/employee.fdb

* Provide Host, Port, Database Path, User Name, Password accordingly.

Create and test the Datasource connection.

Save and proceed next, to add the query

select EMP_NO,FIRST_NAME,
LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,SALARY from employee where EMP_NO=:EMP_NO

Add Input/Output mappings accordingly.

Save and proceed next, to add the operation.

 

Save and finish.

Now if you go to the Home> Manage> Services> List> firebirdTest> Edit Data Source(XML Edit) you can see the following Data Service definition.

<data disableStreaming="true" name="firebirdTest" transports="http https local">
   <config enableOData="false" id="emp">
      <property name="driverClassName">org.firebirdsql.jdbc.FBDriver</property>
      <property name="url">jdbc:firebirdsql://localhost:3050//opt/firebird/examples/empbuild/employee.fdb</property>
      <property name="username">SYSDBA</property>
      <property name="password">admin</property>
   </config>
   <query id="select" useConfig="emp">
      <sql>select EMP_NO,FIRST_NAME,&#xd;LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,SALARY from employee where EMP_NO=:EMP_NO</sql>
      <result element="Entries" rowName="Entry">
         <element column="EMP_NO" name="EMP_NO" xsdType="string"/>
         <element column="FIRST_NAME" name="FIRST_NAME" xsdType="string"/>
         <element column="LAST_NAME" name="LAST_NAME" xsdType="string"/>
         <element column="PHONE_EXT" name="PHONE_EXT" xsdType="string"/>
         <element column="HIRE_DATE" name="HIRE_DATE" xsdType="string"/>
         <element column="DEPT_NO" name="DEPT_NO" xsdType="string"/>
         <element column="SALARY" name="SALARY" xsdType="string"/>
      </result>
      <param name="EMP_NO" sqlType="STRING"/>
   </query>
   <operation disableStreaming="true" name="getEmp">
      <call-query href="select">
         <with-param name="EMP_NO" query-param="EMP_NO"/>
      </call-query>
   </operation>
</data>

How to test the data-service(Try-it tool)

Go to Home> Manage> Services> List> firebirdTest> Try this service.

Provide the EMP_NO and press send.

Further information on query/input and output mappings/operation can be found here.