FBSQL - Frontend Backend SQL

100% pure SQL | 100% pure REST

What is FBSQL?
FBSQL is a server that transparently yet secure connects your frontend to the remote databases.

Is it really two-tier architecture?
Well, yes and no. Look at the FBSQL as two-tier architecture with third tier under the hood.

Which databases are supported?
FBSQL supports any JDBC compliant databases like SQLite, MySQL, PostgreSQL, Oracle, Microsoft SQL Server, IBM Db2 etc. All you need is JDBC driver for your database.

What about security?
FBSQL security is based on the principle of least privilege (POLP). By default, FBSQL does not accept incoming connections. It also rejects all SQL statements that were not declared explicitly. Pre-execution triggers can be used to verify and modify input parameters before execution or completely reject execution by custom conditions. Users must implement their own authentication and authorization mechanism.

What about performance?
FBSQL was designed with performance in mind and supports out of the box connection pooling, results prefetching, ETag-optimized communication and response compression.

We wish to improve the documentation. Any help is welcome!

Tutorial

Getting started
FBSQL distributions
Installation
Command line interface (CLI)
Init script
Configuration and fine tuning
Authentication
Authorization
Expose our database to frontend
Execute SQL statements
Triggers, event notification, parameters checking
Reseult set formats
Database agnostic stored procedures
Schedule periodic jobs
Binary data
Date and Time
Database event notification
Mocking with FBSQL
Frontend debug tool
Did you know?

Commands

CONNECT TO
SWITCH TO
DECLARE STATEMENT
DECLARE PROCEDURE
SCHEDULE
INCLUDE

Functions

REMOTE_USER()
REMOTE_ROLE()
REMOTE_SESSION_ID()
REMOTE_SESSION_CREATION_TIME()
REMOTE_SESSION_LAST_ACCESSED_TIME()
REMOTE_USER_INFO()
IN_ROLE()
GET_COOKIE()
GET_HTTP_SESSION_ATTRIBUTE_AS_CHAR()
GET_HTTP_SESSION_ATTRIBUTE_AS_INTEGER()
GET_HTTP_HEADER_AS_CHAR()
GET_HTTP_HEADER_AS_DATE()
GET_HTTP_HEADER_AS_INTEGER()

Constants

FBSQL_REMOTE_USER
FBSQL_REMOTE_ROLE
FBSQL_REMOTE_SESSION_ID
FBSQL_REMOTE_SESSION_CREATION_TIME
FBSQL_REMOTE_SESSION_LAST_ACCESSED_TIME
FBSQL_REMOTE_USER_INFO

Client API

Connection

Constructor

prepareStatement
addDatabaseEventListener
fireMockDatabaseEvent

PreparedStatement

executeQuery
executeUpdate
setResultSetFormat
setMockFunction

Frontend debug tool

logExecuteQuery
logExecuteUpdate
logDatabaseEvents

Appendices

Getting started

Backend:

Install FBSQL:

Download the latest FBSQL Server release: fbsql-server-2.3.4-linux-x86-64.zip
Unzip the downloaded file on your machine:


fbsql-server-2.3.4-linux-x86-64 ─┐
                                 ├─ fbsql              - Server executable (Linux-x86-64)
                                 ├─ fbsql.min.js       - Client for browser
                                 ├─ fbsql-debug.min.js - Frontend debug tool
                                 ├─ README             - Release information 
                                 └─ LICENSES           - Third party licenses

Put the initialization script init.sql into the ~/fbsql/config/init directory:

/*
 * init.sql
 *
 * Initialization script executes on FBSQL startup,
 * connects to database instance and performs (optionally)
 * any operations that you want to be executed at the server start up time.
 *
 * To be executed at startup init scripts must have the name "init.sql"
 * or have any other name that ends with ".init.sql". E.g.: "my.init.sql"
 *
 * Put your init scripts somewhere under "<FBSQL_HOME>/fbsql/config/init" directory.
 */

           CONNECT TO 'jdbc:sqlite:hello_world_db'
UNDECLARED STATEMENTS ALLOW
 INCOMING CONNECTIONS ALLOW
                   AS HelloWorldExample;

DROP TABLE IF EXISTS EMPLOYEES;

CREATE TABLE EMPLOYEES (
    EMPLOYEE_ID   CHAR(4)     PRIMARY KEY,
    EMPLOYEE_NAME VARCHAR(50) NOT NULL
);

INSERT INTO EMPLOYEES (EMPLOYEE_ID, EMPLOYEE_NAME) VALUES('B342', 'Bill ');
INSERT INTO EMPLOYEES (EMPLOYEE_ID, EMPLOYEE_NAME) VALUES('D455', 'Dan  ');
INSERT INTO EMPLOYEES (EMPLOYEE_ID, EMPLOYEE_NAME) VALUES('J231', 'John ');
INSERT INTO EMPLOYEES (EMPLOYEE_ID, EMPLOYEE_NAME) VALUES('W123', 'World');

Start FBSQL server:
- Go to the appropriate subdirectory of the FBSQL installation
- Run the startup command from command line:
```
./fbsql --start
```

Frontend:

<!DOCTYPE html>
<html lang="en">
    <head>
        <script src="fbsql.min.js"></script>
    </head>
    <body>
        <script type="text/javascript">
            const conn = new Connection("http://localhost:8080/db/HelloWorldExample");
            const ps   = conn.prepareStatement("SELECT EMPLOYEE_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = :id");
            ps.executeQuery({id: "W123"})
            .then(resultSet => alert("Hello, " + resultSet[0].EMPLOYEE_NAME + "!")); // Hello, World!
        </script>
    </body>
</html>

Result:

FBSQL distributions

FBSQL Server is «all included» zero-config distribution for these who want complete backend solution including popular embedded databases and their JDBC drivers, connection engine and application server. It's best choice if you want to give try to FBSQL.

FBSQL Server Min is minimalist distribution without embedded databases, JDBC drivers and debugging tools. Good choice for production usage.

FBSQL Servlet targeted for these who want run FBSQL on own application server infrastructure.

If you are having troubles choosing a distribution, you probably need the FBSQL Server.

FBSQL distributions overview

	FBSQL Server	FBSQL Server Min	FBSQL Servlet
FBSQL engine (servlet)	✓	✓	✓
JavaScript client API	✓	✓	✓
Frontend debug tool	✓
Java Runtime Environment (JRE)	✓	✓
Servlet container	✓	✓
Command line interface	✓	✓
Embedded SQLite, H2, HSQLDB, Apache Derby	✓
JDBC drivers for embedded databases	✓

Installation

FBSQL Server

Download the latest FBSQL Server release: fbsql-server-2.3.4-linux-x86-64.zip
Unzip the downloaded file on your machine:


fbsql-server-2.3.4-linux-x86-64 ─┐
                                 ├─ fbsql              - Server executable (Linux-x86-64)
                                 ├─ fbsql.min.js       - Client for browser
                                 ├─ fbsql-debug.min.js - Frontend debug tool
                                 ├─ README             - Release information 
                                 └─ LICENSES           - Third party licenses

FBSQL Server Min

Download the latest FBSQL Server Min release: fbsql-server-min-2.3.4-linux-x86-64.zip
Unzip the downloaded file on your machine:


fbsql-server-min-2.3.4-linux-x86-64 ─┐
                                     ├─ fbsql        - Server executable (Linux-x86-64)
                                     ├─ fbsql.min.js - Client for browser
                                     ├─ README       - Release information 
                                     └─ LICENSES     - Third party licenses

FBSQL Servlet

Download the latest FBSQL Servlet release: fbsql-war-2.3.4.zip
Unzip the downloaded file on your machine:


fbsql-war-2.3.4 ─┐
                 ├─ fbsql.war    - FBSQL servlet
                 ├─ fbsql-min.js - Client for browser
                 ├─ README       - Release information 
                 └─ LICENSES     - Third party licenses

Deploy fbsql.war to your application server infrastructure.

Command line interface (CLI)

Command line:


Usage:                                          
                                                
fbsql [options]                                 
                                                
Options:                                        
         help    - print help                   
         version - print version                
         start   - start server (in basckground)
         stop    - stop server                  
         run     - run server (in foreground)   

Examples:
  fbsql start
  fbsql stop
  fbsql run

Command line options:

Option	Description
`help`	Prints the command line interface help
`version`	Prints FBSQL version, build timestamp and release information
`start`	Starts FBSQL server as background process
`stop`	Stops FBSQL server background process started with `start` option
`run`	Starts FBSQL server in foreground

Configuration and fine tuning

After FBSQL starts it automatically copy all the nessesary configuration files to FBSQL home directory (by default user home directory). You can change the default FBSQL home directory location by specifying FBSQL_HOME operating system environment variable. FBSQL home directory contains init scripts, logs and all configuration files related to application server and JVM.

Due to security reasons all changes you do in FBSQL home directory are immutable across FBSQL server running session. Configuration changes will loaded after FBSQL server starts next time.

Anatomy of FBSQL home directory:

<FBSQL_HOME> ─┐ ......................................................│-> FBSQL home directory
              │                                                       │
              └─ fbsql ─┐                                             │
                        │                                             │
                        ├─ config                                     │
                        │  │                                          │
                        │  ├─ init ...................................│-> init scripts
                        │  │                                          │
                        │  ├─ jre ....................................│-> JVM conf
                        │  │  └─ conf                                 │
                        │  │     ├─ logging.properties                │
                        │  │     ├─ management                        │
                        │  │     │  ├─ jmxremote.access               │
                        │  │     │  ├─ jmxremote.password.template    │
                        │  │     │  └─ management.properties          │
                        │  │     ├─ net.properties                    │
                        │  │     ├─ sdp                               │
                        │  │     │  └─ sdp.conf.template              │
                        │  │     ├─ security                          │
                        │  │     │  ├─ java.policy                    │
                        │  │     │  ├─ java.security                  │
                        │  │     │  └─ policy                         │
                        │  │     │     ├─ limited                     │
                        │  │     │     │  ├─ default_local.policy     │
                        │  │     │     │  ├─ default_US_export.policy │
                        │  │     │     │  └─ exempt_local.policy      │
                        │  │     │     ├─ README.txt                  │
                        │  │     │     └─ unlimited                   │
                        │  │     │        ├─ default_local.policy     │
                        │  │     │        └─ default_US_export.policy │
                        │  │     └─ sound.properties                  │
                        │  │                                          │
                        │  └─ tomcat .................................│-> Apache Tomcat conf
                        │     ├─ bin                                  │
                        │     │  └─ setenv.sh                         │
                        │     ├─ conf                                 │
                        │     │  ├─ catalina.policy                   │
                        │     │  ├─ catalina.properties               │
                        │     │  ├─ context.xml                       │
                        │     │  ├─ jaspic-providers.xml              │
                        │     │  ├─ jaspic-providers.xsd              │
                        │     │  ├─ logging.properties                │
                        │     │  ├─ server.xml                        │
                        │     │  ├─ tomcat-users.xml                  │
                        │     │  ├─ tomcat-users.xsd                  │
                        │     │  └─ web.xml                           │
                        │     └─ webapps                              │
                        │        └─ ROOT                              │
                        │           │                                 │
                        │           └─ WEB-INF .......................│-> servlet conf
                        │              ├─ classes                     │ 
                        │              ├─ lib ........................│-> JDBC drivers
                        │              └─ web.xml                     │
                        │                                             │
                        └─ logs ......................................│-> Logs dir

Setup JDBC drivers:
Install the JDBC drivers for your database, and set them up so they are in your classpath. The easiest way to do this is to copy all the necessary JDBC drivers jars into <FBSQL_HOME>/fbsql/config/tomcat/webapps/ROOT/WEB-INF/lib directory.

Setup custom classes, resources and jars:
To setup custom classes, resources and jars copy:

all the necessary jars into <FBSQL_HOME>/fbsql/config/tomcat/webapps/ROOT/WEB-INF/lib directory.
all the necessary classes and resources into <FBSQL_HOME>/fbsql/config/tomcat/webapps/ROOT/WEB-INF/classes directory.

Setup CORS policy:
To setup your CORS policy please modify value of CORS_ALLOW_ORIGIN parameter in <FBSQL_HOME>/fbsql/config/tomcat/webapps/ROOT/WEB-INF/web.xml file:

...

<init-param>
	<!--
	https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Access-Control-Allow-Origin">HTTP Access-Control-Allow-Origin
 	-->
	<description>"Access-Control-Allow-Origin" HTTP header value</description>
	<param-name>CORS_ALLOW_ORIGIN</param-name>
	<param-value></param-value>
</init-param>

...

Setup static frontend files:
FBSQL server (optionally) can host your static frontend files. To setup your static content (.html, .js, *.css etc.) please copy appropriate files into <FBSQL_HOME>/fbsql/config/tomcat/webapps/ROOT directory.

Init script

Initialization script executes on FBSQL startup, connects to database instance and performs (optionally) any operations that you want to be executed at the server start up time.

To be executed at startup init scripts must have the name "init.sql" or have any other name that ends with ".init.sql". E.g.: "my.init.sql".

Put your init scripts somewhere (directory <FBSQL_HOME>/fbsql/config/init can have arbitrary hierarchy structure) under <FBSQL_HOME>/fbsql/config/init directory.

Authentication

In this chapter we will learn how to add simple authentication to our CONNECT TO statement.
We are going to authenticate our users by username and password.

Backend:

/*
 * init.sql
 *
 * Initialization script executes on FBSQL startup,
 * connects to database instance and performs (optionally)
 * any operations that you want to be executed at the server start up time.
 *
 * To be executed at startup init scripts must have the name "init.sql"
 * or have any other name that ends with ".init.sql". E.g.: "my.init.sql"
 *
 * Put your init scripts somewhere under "<FBSQL_HOME>/fbsql/config/init" directory.
 */

                  CONNECT TO 'jdbc:sqlite:authentication_example_db'
EXPOSE UNDECLARED STATEMENTS
  ALLOW INCOMING CONNECTIONS IF EXISTS (
                                        SELECT TRUE
                                          FROM USERS U
                                         WHERE USERNAME=:user AND PASSWORD=:password
                                       )
                          AS AuthenticationExample;


/*
 * Authenticaton. Implement your own authentication logic here!
 *
 * WARNING!
 * In this example we are store passwords as plain text only for educational purposes.
 * It's strongly not recommended way to store passwords in production environment.
 * Passwords should be hashed.
 */

/* Users */
DROP TABLE IF EXISTS USERS;
CREATE TABLE USERS (
    USERNAME VARCHAR(15) PRIMARY KEY,
    PASSWORD VARCHAR(15) NOT NULL
);
INSERT INTO USERS (USERNAME, PASSWORD) VALUES('john',  'secret'   );
INSERT INTO USERS (USERNAME, PASSWORD) VALUES('tim',   'secret123');
INSERT INTO USERS (USERNAME, PASSWORD) VALUES('jerry', 'secret456');

Frontend:

<!DOCTYPE html>
<html lang="en">
    <head>
        <script src="fbsql.min.js"></script>
    </head>
    <body>
        <script type="text/javascript">
            const conn = new Connection("http://localhost:8080/db/AuthenticationExample", "john", "secret");
            const ps   = conn.prepareStatement("SELECT 'Hello, World!' AS greeting");
            ps.executeQuery()
            .then(resultSet => alert(resultSet[0].greeting));
        </script>
    </body>
</html>

Authorization

In this chapter we will learn how to add simple role-based authorization to our CONNECT TO statement.
We are going to authenticate our users by username and password, and authorize by role.

Backend:

/*
 * init.sql
 *
 * Initialization script executes on FBSQL startup,
 * connects to database instance and performs (optionally)
 * any operations that you want to be executed at the server start up time.
 *
 * To be executed at startup init scripts must have the name "init.sql"
 * or have any other name that ends with ".init.sql". E.g.: "my.init.sql"
 *
 * Put your init scripts somewhere under "<FBSQL_HOME>/fbsql/config/init" directory.
 */

                  CONNECT TO 'jdbc:sqlite:role_based_auth_db'
EXPOSE UNDECLARED STATEMENTS
  ALLOW INCOMING CONNECTIONS IF EXISTS (
                                        SELECT TRUE
                                          FROM USERS U
                                         WHERE USERNAME=:user AND PASSWORD=:password AND EXISTS (
                                               SELECT TRUE
                                                 FROM USER_ROLES R
                                                WHERE U.USERNAME=R.USERNAME AND R.ROLE=:role
                                               )
                                       )
                          AS RoleBasedAuthorizationExample;


/*
 * Authenticaton and authorization. Implement your own authentication/authorization logic here!
 *
 * WARNING!
 * In this example we are store passwords as plain text only for educational purposes.
 * It's strongly not recommended way to store passwords in production environment.
 * Passwords should be hashed.
 */

/* Users */
DROP TABLE IF EXISTS USERS;
CREATE TABLE USERS (
    USERNAME VARCHAR(15) PRIMARY KEY,
    PASSWORD VARCHAR(15) NOT NULL
);
INSERT INTO USERS (USERNAME, PASSWORD) VALUES('john',  'secret'   );
INSERT INTO USERS (USERNAME, PASSWORD) VALUES('tim',   'secret123');
INSERT INTO USERS (USERNAME, PASSWORD) VALUES('jerry', 'secret456');

/* Roles */
DROP TABLE IF EXISTS USER_ROLES;
CREATE TABLE USER_ROLES (
    USERNAME VARCHAR(15) NOT NULL,
    ROLE     VARCHAR(15) NOT NULL,
    PRIMARY KEY (USERNAME, ROLE),
    FOREIGN KEY (USERNAME) REFERENCES USERS(USERNAME)
);
INSERT INTO USER_ROLES (USERNAME, ROLE) VALUES('john',  'manager'      );
INSERT INTO USER_ROLES (USERNAME, ROLE) VALUES('tim',   'programmer'   );
INSERT INTO USER_ROLES (USERNAME, ROLE) VALUES('jerry', 'administrator');

Frontend:

<!DOCTYPE html>
<html lang="en">
    <head>
        <script src="fbsql.min.js"></script>
    </head>
    <body>
        <script type="text/javascript">
            const conn = new Connection("http://localhost:8080/db/RoleBasedAuthorizationExample", "john", "secret", "manager");
            const ps   = conn.prepareStatement("SELECT 'Hello, World!' AS greeting");
            ps.executeQuery()
            .then(resultSet => alert(resultSet[0].greeting));
        </script>
    </body>
</html>

Expose our database to frontend

In this chapter we will learn how to secure our backend with DECLARE STATEMENT command and reference statements by name.

Backend:

/*
 * init.sql
 *
 * Initialization script executes on FBSQL startup,
 * connects to database instance and performs (optionally)
 * any operations that you want to be executed at the server start up time.
 *
 * To be executed at startup init scripts must have the name "init.sql"
 * or have any other name that ends with ".init.sql". E.g.: "my.init.sql"
 *
 * Put your init scripts somewhere under "<FBSQL_HOME>/fbsql/config/init" directory.
 */

                CONNECT TO 'jdbc:sqlite:declare_statement_example_db'
ALLOW INCOMING CONNECTIONS
                        AS DeclareStatementExample;

DROP TABLE IF EXISTS EMPLOYEES;

CREATE TABLE EMPLOYEES (
    EMPLOYEE_ID   CHAR(4)     PRIMARY KEY,
    EMPLOYEE_NAME VARCHAR(50) NOT NULL
);

INSERT INTO EMPLOYEES (EMPLOYEE_ID, EMPLOYEE_NAME) VALUES('B342', 'Bill ');
INSERT INTO EMPLOYEES (EMPLOYEE_ID, EMPLOYEE_NAME) VALUES('D455', 'Dan  ');
INSERT INTO EMPLOYEES (EMPLOYEE_ID, EMPLOYEE_NAME) VALUES('J231', 'John ');
INSERT INTO EMPLOYEES (EMPLOYEE_ID, EMPLOYEE_NAME) VALUES('W123', 'World');

DECLARE STATEMENT (SELECT EMPLOYEE_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = :id)
      COMPRESSION BEST COMPRESSION
               AS myStatement;

Frontend:

<!DOCTYPE html>
<html lang="en">
    <head>
        <script src="fbsql.min.js"></script>
        <script src="fbsql-debug.min.js"></script>
    </head>
    <body>
        <script type="text/javascript">
            const conn = new Connection("http://localhost:8080/db/DeclareStatementExample");

            /* Allowed! Statement source was provided */
            const ps1   = conn.prepareStatement("SELECT EMPLOYEE_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = :id");
            logExecuteQuery(ps1, {id: "W123"});

            /* Allowed! Statement alias name was provided */
            const ps2   = conn.prepareStatement("#myStatement");
            logExecuteQuery(ps2, {id: "W123"});

            /* Rejected! Undeclared statement. */
            const ps3 = conn.prepareStatement("SELECT EMPLOYEE_NAME FROM EMPLOYEES);
            logExecuteQuery(ps3);
        </script>
    </body>
</html>

Execute SQL statements

In this chapter we will learn how to execute SQL statements from frontend JavaScript by using executeQuery() and executeUpdate() methods.

Backend:

/*
 * init.sql
 *
 * Initialization script executes on FBSQL startup,
 * connects to database instance and performs (optionally)
 * any operations that you want to be executed at the server start up time.
 *
 * To be executed at startup init scripts must have the name "init.sql"
 * or have any other name that ends with ".init.sql". E.g.: "my.init.sql"
 *
 * Put your init scripts somewhere under "<FBSQL_HOME>/fbsql/config/init" directory.
 */

                  CONNECT TO 'jdbc:sqlite:execute_query_and_execute_update_example_db'
EXPOSE UNDECLARED STATEMENTS
  ALLOW INCOMING CONNECTIONS
                             AS ExecuteQueryAndExecuteUpdateExample;

DROP TABLE IF EXISTS COUNTRIES;
CREATE TABLE COUNTRIES (
    COUNTRY_ID   CHAR(2)     PRIMARY KEY,
    COUNTRY_NAME VARCHAR(50) NOT NULL
);

INSERT INTO COUNTRIES (COUNTRY_ID, COUNTRY_NAME) VALUES('AU', 'Australia');
INSERT INTO COUNTRIES (COUNTRY_ID, COUNTRY_NAME) VALUES('DE', 'Germany'  );
INSERT INTO COUNTRIES (COUNTRY_ID, COUNTRY_NAME) VALUES('IN', 'India'    );

Frontend:

Example of non-parametrized execution:

<!DOCTYPE html>
<html lang="en">
    <head>
        <script src="fbsql.min.js"></script>
    </head>
    <body>
        <script type="text/javascript">
            const conn = new Connection('http://localhost:8080/db/ExecuteQueryAndExecuteUpdateExample');

            /* Select all records */
            const ps1 = conn.prepareStatement("SELECT * FROM COUNTRIES");

            /* Update one record */
            const ps2 = conn.prepareStatement("UPDATE COUNTRIES SET COUNTRY_NAME = 'Federal Republic of Germany' WHERE COUNTRY_ID = 'DE'");


            ps1.executeQuery()
            .then(resultSet => {
                console.log(resultSet);
                /*
                 *   Output:
                 *   
                 *   [
                 *       {
                 *           "COUNTRY_ID": "AU",
                 *           "COUNTRY_NAME": "Australia"
                 *       },
                 *       {
                 *           "COUNTRY_ID": "DE",
                 *           "COUNTRY_NAME": "Germany"
                 *       },
                 *       {
                 *           "COUNTRY_ID": "IN",
                 *           "COUNTRY_NAME": "India"
                 *       }
                 *   ]
                 */
                 return ps2.executeUpdate();
            })
            .then(result => {
                console.log(result);
                /*
                 *   Output:
                 *   
                 *   {
                 *       "rowCount": 1,      // one record updated
                 *       "generatedKeys": []
                 *   }
                 */
                return ps1.executeQuery();
            })
            .then(resultSet => {
                console.log(resultSet);
                /*
                 *   Output:
                 *   
                 *   [
                 *       {
                 *           "COUNTRY_ID": "AU",
                 *           "COUNTRY_NAME": "Australia"
                 *       },
                 *       {
                 *           "COUNTRY_ID": "DE",
                 *           "COUNTRY_NAME": "Federal Republic of Germany"
                 *       },
                 *       {
                 *           "COUNTRY_ID": "IN",
                 *           "COUNTRY_NAME": "India"
                 *       }
                 *   ]
                 */
            });
        </script>
    </body>
</html>

Example of parametrized execution:

<!DOCTYPE html>
<html lang="en">
    <head>
        <script src="fbsql.min.js"></script>
    </head>
    <body>
        <script type="text/javascript">
            const conn = new Connection('http://localhost:8080/db/ExecuteQueryAndExecuteUpdateExample');

            /* Select all records */
            const ps1 = conn.prepareStatement("SELECT * FROM COUNTRIES");

            /* Update one record */
            const ps2 = conn.prepareStatement("UPDATE COUNTRIES SET COUNTRY_NAME = :name WHERE COUNTRY_ID = :id");


            ps1.executeQuery()
            .then(resultSet => {
                console.log(resultSet);
                /*
                 *   Output:
                 *   
                 *   [
                 *       {
                 *           "COUNTRY_ID": "AU",
                 *           "COUNTRY_NAME": "Australia"
                 *       },
                 *       {
                 *           "COUNTRY_ID": "DE",
                 *           "COUNTRY_NAME": "Germany"
                 *       },
                 *       {
                 *           "COUNTRY_ID": "IN",
                 *           "COUNTRY_NAME": "India"
                 *       }
                 *   ]
                 */
                 return ps2.executeUpdate({id: 'DE', name: 'Federal Republic of Germany'});
            })
            .then(result => {
                console.log(result);
                /*
                 *   Output:
                 *   
                 *   {
                 *       "rowCount": 1,      // one record updated
                 *       "generatedKeys": []
                 *   }
                 */
                return ps1.executeQuery();
            })
            .then(resultSet => {
                console.log(resultSet);
                /*
                 *   Output:
                 *   
                 *   [
                 *       {
                 *           "COUNTRY_ID": "AU",
                 *           "COUNTRY_NAME": "Australia"
                 *       },
                 *       {
                 *           "COUNTRY_ID": "DE",
                 *           "COUNTRY_NAME": "Federal Republic of Germany"
                 *       },
                 *       {
                 *           "COUNTRY_ID": "IN",
                 *           "COUNTRY_NAME": "India"
                 *       }
                 *   ]
                 */
            });
        </script>
    </body>
</html>

Example of batch execution:

<!DOCTYPE html>
<html lang="en">
    <head>
        <script src="fbsql.min.js"></script>
    </head>
    <body>
        <script type="text/javascript">
            const conn = new Connection('http://localhost:8080/db/ExecuteQueryAndExecuteUpdateExample');

            /* Select all records */
            const ps1 = conn.prepareStatement("SELECT * FROM COUNTRIES");

            /* Update one record */
            const ps2 = conn.prepareStatement("UPDATE COUNTRIES SET COUNTRY_NAME = :name WHERE COUNTRY_ID = :id");


            ps1.executeQuery()
            .then(resultSet => {
                console.log(resultSet);
                /*
                 *   Output:
                 *   
                 *   [
                 *       {
                 *           "COUNTRY_ID": "AU",
                 *           "COUNTRY_NAME": "Australia"
                 *       },
                 *       {
                 *           "COUNTRY_ID": "DE",
                 *           "COUNTRY_NAME": "Germany"
                 *       },
                 *       {
                 *           "COUNTRY_ID": "IN",
                 *           "COUNTRY_NAME": "India"
                 *       }
                 *   ]
                 */
                 return ps2.executeUpdate(
                     [
                         {id: 'DE', name: 'Federal Republic of Germany'},
                         {id: 'IN', name: 'Republic of India'          }
                     ]
                     
                 );
            })
            .then(result => {
                console.log(result);
                /*
                 *   Output:
                 *   
                 *   {
                 *       "rowCount": 2,      // two records updated
                 *       "generatedKeys": []
                 *   }
                 */
                return ps1.executeQuery();
            })
            .then(resultSet => {
                console.log(resultSet);
                /*
                 *   Output:
                 *   
                 *   [
                 *       {
                 *           "COUNTRY_ID": "AU",
                 *           "COUNTRY_NAME": "Australia"
                 *       },
                 *       {
                 *           "COUNTRY_ID": "DE",
                 *           "COUNTRY_NAME": "Federal Republic of Germany"
                 *       },
                 *       {
                 *           "COUNTRY_ID": "IN",
                 *           "COUNTRY_NAME": "Republic of India"
                 *       }
                 *   ]
                 */
            });
        </script>
    </body>
</html>

Triggers, event notification, parameters checking

FBSQL allow check and/or modify parameters of any SQL statement before execution. This can be achieved by using TRIGGER BEFORE clause of DECLARE STATEMENT command.

TRIGGER BEFORE procedure executes before the native SQL statement execution. Procedure must return string with JSON parameters object. If JSON parameters object is NULL or exception occurs execution will be rejected.

TRIGGER AFTER procedure executes after the native SQL statement execution. Procedure may return string with JSON event object. If JSON event object is not NULL and no exception occurs event object will delivered to subscribers on client side. Please refer to client's Connection#addDatabaseEventListener() method for information about how to catch database events on frontend side.

Backend:

/*
 * init.sql
 *
 * Initialization script executes on FBSQL startup,
 * connects to database instance and performs (optionally)
 * any operations that you want to be executed at the server start up time.
 *
 * To be executed at startup init scripts must have the name "init.sql"
 * or have any other name that ends with ".init.sql". E.g.: "my.init.sql"
 *
 * Put your init scripts somewhere under "<FBSQL_HOME>/fbsql/config/init" directory.
 */

           CONNECT TO 'jdbc:sqlite:TriggersExample'
 INCOMING CONNECTIONS ALLOW
UNDECLARED STATEMENTS ALLOW
                   AS 'TriggersExample';

DROP TABLE IF EXISTS COUNTRIES;
CREATE TABLE COUNTRIES (
    COUNTRY_ID   CHAR(2)     NOT NULL PRIMARY KEY,
    COUNTRY_NAME VARCHAR(50) NOT NULL
);

/* Declare Java procedure */
DECLARE PROCEDURE MY_CHECK_PARAMS
             TYPE JAVA
          OPTIONS '{ "class": "org.fbsql_examples.TriggerExample", "method": "myCheckParams" }';

/* Declare Java procedure */
DECLARE PROCEDURE MY_NOTIFY
             TYPE JAVA
          OPTIONS '{ "class": "org.fbsql_examples.TriggerExample", "method": "myNotify" }';

DECLARE STATEMENT (INSERT INTO COUNTRIES (COUNTRY_ID, COUNTRY_NAME) VALUES (:countryId, :countryName))
TRIGGER BEFORE MY_CHECK_PARAMS
TRIGGER AFTER MY_NOTIFY 
AS myInsertStatement;

org.fbsql_examples.TriggerExample

package org.fbsql_examples;

import java.sql.Connection;
import java.util.Locale;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.fbsql.tools.JsonRhinoUtils;

public class TriggerExample {

	/**
	 * 
	 * @param request
	 * @param response
	 * @param connection
	 * @param instanceName
	 * @param userInfoJson
	 * @param statementInfoJson
	 * @return
	 * @throws Exception
	 */
	public static String myCheckParams(HttpServletRequest request, HttpServletResponse response, Connection connection, String instanceName, String userInfoJson, String statementInfoJson) throws Exception {
		Map<String, Object> map        = (Map<String, Object>) JsonRhinoUtils.asMap(statementInfoJson);
		Map<String, Object> parameters = (Map<String, Object>) map.get("parameters");
		String              countryId  = (String) parameters.get("countryId");
		countryId = countryId.trim().toUpperCase(Locale.ENGLISH);
		String countryName = (String) parameters.get("countryName");
		if (countryName == null)
			throw new Exception("Country name required");
		countryName = countryName.trim();
		return "{\"countryId\":\"" + countryId + "\",\"countryName\":\"" + countryName + "\"}";
	}

	/**
	 * 
	 * @param request
	 * @param response
	 * @param connection
	 * @param userInfoJson
	 * @param selfUserInfoJson
	 * @param statementInfoJson
	 * @param executionResultJson
	 * @return
	 */
	public static String myNotify(HttpServletRequest request, HttpServletResponse response, Connection connection, String userInfoJson, String selfUserInfoJson, String statementInfoJson, String executionResultJson) {
		Map<String, Object> map        = (Map<String, Object>) JsonRhinoUtils.asMap(statementInfoJson);
		Map<String, Object> parameters = (Map<String, Object>) map.get("parameters");
		String              countryId  = (String) parameters.get("countryId");
		countryId = countryId.trim().toUpperCase(Locale.ENGLISH);
		String countryName = (String) parameters.get("countryName");
		return "{\"message\":\"New country added\",\"countryId\":\"" + countryId + "\",\"countryName\":\"" + countryName + "\"}";
	}

}

Frontend:

<!DOCTYPE html>
<html lang="en">
    <head>
        <script src="fbsql.min.js"></script>
        <script src="fbsql-debug.min.js"></script>
    </head>
    <body>
        <script type="text/javascript">
            const conn = new Connection('TriggersExample');
            logDatabaseEvents(conn);

            const psSelect = conn.prepareStatement("SELECT * FROM COUNTRIES");

            /* delete all records */
            const psDeleteAll = conn.prepareStatement("DELETE FROM COUNTRIES");

            /* add new records */
            const psInsert = conn.prepareStatement("INSERT INTO COUNTRIES (COUNTRY_ID, COUNTRY_NAME) VALUES (:countryId, :countryName)");

            logExecuteUpdate(psDeleteAll) // clear our table
            .then(result => {
            	// parameter 'countryId' will be checked and corrected (trimmed and uppercased) by "before" trigger
                return logExecuteUpdate(psInsert,
                    [
                      {countryId: '  us ', countryName: 'United States'},
            		    {countryId: '  au ', countryName: 'Australia'    },
                    ]
                );
            })
            .then(result => {
                return logExecuteQuery(psSelect)
            })
            .then(resultSet => {
                console.log(resultSet);
                /*
                 * [
                 *      {"COUNTRY_ID": "US", "COUNTRY_NAME": "United States"},
                 *      {"COUNTRY_ID": "AU", "COUNTRY_NAME": "Australia"},
                 * ]
                 */
            });
        </script>
    </body>
</html>

Reseult set formats

In this chapter we will learn how to receive result set in various formats by using PreparedStatement#setResultSetFormat() method.

You can control the result set format by choosing one from the available formats:

Available formats:

PreparedStatement.FORMAT_ARRAY_OF_OBJECTS
Arrays with records as plain JSON objects. The default.

PreparedStatement.FORMAT_ARRAY_OF_ARRAYS
2D-array

PreparedStatement.FORMAT_OBJECT_OF_ARRAYS
Pivot object with column names as keys and arrays of cell values as values

Database agnostic stored procedures

In this chapter we will learn how to write and use database agnostic stored procedures written in JavaScript or JVM languages, Operating System programs and URLs.

Procedures can be called from init.sql and/or frontend.

Opposite to native, FBSQL stored procedures and functions are executed before native statement execution. Procedure/function result is calculated and procedure/function call source replaced with calculated value.
E.g.
SELECT CALC_PRICE(4) AS PRICE FROM MYTABLE
will translated to:
SELECT 28 AS PRICE FROM MYTABLE

Backend:

/*
 * init.sql
 *
 * Initialization script executes on FBSQL startup,
 * connects to database instance and performs (optionally)
 * any operations that you want to be executed at the server start up time.
 *
 * To be executed at startup init scripts must have the name "init.sql"
 * or have any other name that ends with ".init.sql". E.g.: "my.init.sql"
 *
 * Put your init scripts somewhere under "<FBSQL_HOME>/fbsql/config/init" directory.
 */

           CONNECT TO 'jdbc:sqlite:declare_procedure_example_db'
 INCOMING CONNECTIONS ALLOW
UNDECLARED STATEMENTS ALLOW
                   AS DeclareProcedureExample;

/* Declare Java procedure */
DECLARE PROCEDURE GET_EMPLOYEES
             TYPE JAVA
          OPTIONS '{ "class": "org.fbsql_examples.DeclareStatementExample", "method": "getEmployees" }';

/* Declare JavaScript procedure */
DECLARE PROCEDURE GET_ITEMS
             TYPE JS
          OPTIONS '{ "file": "items.js", "function": "getItems" }';

/* Declare operating system command as procedure */
DECLARE PROCEDURE GET_COUNTRIES
             TYPE EXEC
          OPTIONS '{ "file": "countries.sh" }';

/* Declare URL as procedure */
DECLARE PROCEDURE GET_ANIMALS
             TYPE URL
          OPTIONS '{ "url": "animals.json" }';

StoredProcedures.java

package org.fbsql.examples;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.HashMap;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.h2.tools.SimpleResultSet;

public class StoredProcedures {

	/**
	 * Simple stored procedure that extracts employees by condition from mock "database" and return ResultSet
	 *
	 * @param request    javax.servlet.http.HttpServletRequest is mandatory parameter that receive each stored procedure
	 * @param connection java.sql.Connection is mandatory parameter that receive each stored procedure
	 * @param namePrefix Some user defined parameter
	 * @return
	 */
	public static ResultSet getEmployees(HttpServletRequest request, Connection connection, String nameStartsWith) {

		/**
		 * Our "database"
		 */
		Map<Integer, String> employees = new HashMap<>();
		employees.put(123, "John");
		employees.put(152, "Loren");
		employees.put(451, "Lisa");
		employees.put(481, "Ivan");
		employees.put(508, "Donald");
		employees.put(611, "Carlos");
		employees.put(751, "Lora");

		/**
		 * ResultSet
		 */
		SimpleResultSet rs = new SimpleResultSet();
		rs.addColumn("ID",   Types.INTEGER,  10, 0);
		rs.addColumn("NAME", Types.VARCHAR, 255, 0);

		/*
		 * Select data by condition
		 */
		for (Map.Entry<Integer, String> employee : employees.entrySet()) {
			int    id   = employee.getKey();
			String name = employee.getValue();
			if (name.startsWith(nameStartsWith)) // match our condition
				rs.addRow(id, name);
		}
		return rs;
	}
}

items.js

function getItems(request, response, connection, instanceName) {
	return [
		{id: "B123", name: "TV EK-234" },
		{id: "C120", name: "Phone M-12"},
	];
}

animals.json

[
	{"id": "L123", "name": "Leon" },
	{"id": "Z120", "name": "Zebra"}
]

countries.sh

cat <<EOF
[
	{"id": "au", name: "Australia"},
	{"id": "it", name: "Italy"    }
]
EOF

Frontend:

<!DOCTYPE html>
<html lang="en">
    <head>
        <script src="fbsql.min.js"></script>
        <script src="fbsql-debug.min.js"></script>
    </head>
    <body>
        <script type="text/javascript">
            const conn = new Connection('http://localhost:8080/db/DeclareProcedureExample');

            const ps1 = conn.prepareStatement("CALL GET_EMPLOYEES(:nameStartsWith)");
            logExecuteQuery(ps1, {nameStartsWith: "Lo"})
            .then(resultSet => console.log(resultSet));

            const ps2 = conn.prepareStatement("CALL GET_COUNTRIES()");
            logExecuteQuery(ps2)
            .then(resultSet => console.log(resultSet));

            const ps3 = conn.prepareStatement("CALL GET_ITEMS()");
            logExecuteQuery(ps3)
            .then(resultSet => console.log(resultSet));

            const ps4 = conn.prepareStatement("CALL GET_ANIMALS()");
            logExecuteQuery(ps4)
            .then(resultSet => console.log(resultSet));

            </script>
    </body>
</html>

Schedule periodic jobs

FBSQL has own scheduler to run periodic jobs. Stored procedures can be scheduled to run according cron expressions, which are able to create firing schedules such as: “At 8:00am every Monday through Friday” or “At 1:30am every last Friday of the month”.

Backend:

/*
 * init.sql
 *
 * Initialization script executes on FBSQL startup,
 * connects to database instance and performs (optionally)
 * any operations that you want to be executed at the server start up time.
 *
 * To be executed at startup init scripts must have the name "init.sql"
 * or have any other name that ends with ".init.sql". E.g.: "my.init.sql"
 *
 * Put your init scripts somewhere under "<FBSQL_HOME>/fbsql/config/init" directory.
 */

          CONNECT TO 'jdbc:schedule_statement_example_db'
INCOMING CONNECTIONS ALLOW
                  AS ScheduleStatementExample;

DECLARE PROCEDURE MY_PERIODIC_JOB TYPE JAVA
          OPTIONS '{"class": "org.fbsql_examples.StroredProcedures", "method": "myPeriodicJob" }';

SCHEDULE MY_PERIODIC_JOB AT "0/5 * * * * ?";

StroredProcedures.java

package org.fbsql_examples;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Timestamp;

public class StroredProcedures {

	/**
	 *
	 * @param connection
	 * @param instanceName
	 * @param cronExpression
	 * @return
	 * @throws SQLException
	 */
	public static String myPeriodicJob(Connection connection, String instanceName, String cronExpression) throws SQLException {
		String json = "{\"instanceName\": \"" + instanceName + "\", \"databaseProductName\": \"" + connection.getMetaData().getDatabaseProductName() + "\", \"cronExpression\": \"" + cronExpression + "\", \"timestamp\": \"" + new Timestamp(System.currentTimeMillis()) + "\"}";
		System.out.println("Periodic job. Event: " + json);
		return json;
	}

}

Frontend:

Example 1
Catch database events and print them on console:

<!DOCTYPE html>
<html lang="en">
    <head>
        <script src="fbsql.min.js"></script>
    </head>
    <body>
        <script type="text/javascript">
            const conn = new Connection('http://localhost:8080/db/ScheduleStatementExample');
            conn.addDatabaseEventListener(event => console.log(event));
        </script>
    </body>
</html>

Example 2
Catch database events with debug tool:

<!DOCTYPE html>
<html lang="en">
    <head>
        <script src="fbsql.min.js"></script>
        <script src="fbsql-debug.min.js"></script>
    </head>
    <body>
        <script type="text/javascript">
            const conn = new Connection('http://localhost:8080/db/ScheduleStatementExample');
            logDatabaseEvents(conn);
        </script>
    </body>
</html>

Binary data

In this chapter we will learn how to work with BINARY, VARBINARY, LONGVARBINARY and BLOB types.

Backend:

/*
 * init.sql
 *
 * Initialization script executes on FBSQL startup,
 * connects to database instance and performs (optionally)
 * any operations that you want to be executed at the server start up time.
 *
 * To be executed at startup init scripts must have the name "init.sql"
 * or have any other name that ends with ".init.sql". E.g.: "my.init.sql"
 *
 * Put your init scripts somewhere under "<FBSQL_HOME>/fbsql/config/init" directory.
 */

           CONNECT TO 'jdbc:h2:~/fbsql/data/BlobExample;AUTO_SERVER=TRUE;'
                 USER 'SA'
             PASSWORD ''
UNDECLARED STATEMENTS ALLOW
 INCOMING CONNECTIONS ALLOW
                   AS BlobAndVarbinaryExample;

DROP TABLE IF EXISTS COUNTRIES;
CREATE TABLE COUNTRIES (
    COUNTRY_ID     CHAR(2)     PRIMARY KEY,
    COUNTRY_NAME   VARCHAR(50) NOT NULL,
    COUNTRY_FLAG_1 BLOB,
    COUNTRY_FLAG_2 VARBINARY(5000)
);

INSERT INTO COUNTRIES (COUNTRY_ID, COUNTRY_NAME) VALUES('AU', 'Australia');
INSERT INTO COUNTRIES (COUNTRY_ID, COUNTRY_NAME) VALUES('IT', 'Italy');

Frontend:

BLOB

<!DOCTYPE html>
<html lang="en">
    <head>
        <script src="fbsql.min.js"></script>
    </head>

    <body>
        <h3>BLOB DEMO</h3>
        <div>Please choose image file</div>
        <br>
        <img id="myImage"><br>
        <input id="myInput" type="file" accept="image/*">

        <script type="text/javascript">
            let myImage = document.getElementById("myImage");
            let myInput = document.getElementById("myInput");

            const conn = new Connection('http://localhost:8080/db/BlobAndVarbinaryExample');
            let psSelect = conn.prepareStatement("SELECT COUNTRY_FLAG_1 FROM COUNTRIES WHERE COUNTRY_ID = 'AU'");
            let psUpdate = conn.prepareStatement("UPDATE COUNTRIES SET COUNTRY_FLAG_1 = :country_flag WHERE COUNTRY_ID = 'AU'");

            /* Load image from database */
            psSelect.executeQuery()
            .then(resultSet => {
                let base64data = resultSet[0].COUNTRY_FLAG_1;
                if (base64data != null)
                    myImage.src = 'data:;base64,' + base64data;
            });

            /* Select new image */
            myInput.onchange = function(event) {
                var input = event.target;
                var reader = new FileReader();
                reader.onload = function() {
                    /* Update image */
                    psUpdate.executeUpdate({country_flag: reader.result})
                    .then(result => {
                        /* Load image from database */
                        return psSelect.executeQuery();
                    })
                    .then(resultSet => {
                        console.log(`${reader.result.byteLength} byte(s) stored in database as BLOB and readed back.`);
                        let base64data = resultSet[0].COUNTRY_FLAG_1;
                        if (base64data != null)
                            myImage.src = 'data:;base64,' + base64data;
                    });
                };
                reader.readAsArrayBuffer(input.files[0]);
            };
        </script>
    </body>
</html>

VARBINARY

<!DOCTYPE html>
<html lang="en">
    <head>
        <script src="fbsql.min.js"></script>
    </head>

    <body>
        <h3>VARBINARY DEMO</h3>
        <div>Please choose image file</div>
        <br>
        <img id="myImage"><br>
        <input id="myInput" type="file" accept="image/*">

        <script type="text/javascript">
            let myImage = document.getElementById("myImage");
            let myInput = document.getElementById("myInput");

            const conn = new Connection('http://localhost:8080/db/BlobAndVarbinaryExample');
            let psSelect = conn.prepareStatement("SELECT COUNTRY_FLAG_2 FROM COUNTRIES WHERE COUNTRY_ID = 'AU'");
            let psUpdate = conn.prepareStatement("UPDATE COUNTRIES SET COUNTRY_FLAG_2 = :country_flag WHERE COUNTRY_ID = 'AU'");

            /* Load image from database */
            psSelect.executeQuery()
            .then(resultSet => {
                let base64data = resultSet[0].COUNTRY_FLAG_2;
                if (base64data != null)
                    myImage.src = 'data:;base64,' + base64data;
            });

            /* Select new image */
            myInput.onchange = function(event) {
                var input = event.target;
                var reader = new FileReader();
                reader.onload = function() {
                    /* Update image */
                    psUpdate.executeUpdate({country_flag: reader.result})
                    .then(result => {
                        /* Load image from database */
                        return psSelect.executeQuery();
                    })
                    .then(resultSet => {
                        console.log(`${reader.result.byteLength} byte(s) stored in database as VARBINARY and readed back.`);
                        let base64data = resultSet[0].COUNTRY_FLAG_2;
                        if (base64data != null)
                            myImage.src = 'data:;base64,' + base64data;
                    });
                };
                reader.readAsArrayBuffer(input.files[0]);
            };
        </script>
    </body>
</html>

Date and Time

In this chapter we will learn how to work with DATE, TIME and TIMESTAMP types.

Backend:

/*
 * init.sql
 *
 * Initialization script executes on FBSQL startup,
 * connects to database instance and performs (optionally)
 * any operations that you want to be executed at the server start up time.
 *
 * To be executed at startup init scripts must have the name "init.sql"
 * or have any other name that ends with ".init.sql". E.g.: "my.init.sql"
 *
 * Put your init scripts somewhere under "<FBSQL_HOME>/fbsql/config/init" directory.
 */

                  CONNECT TO 'jdbc:sqlite:date_and_time_example_db'
EXPOSE UNDECLARED STATEMENTS
  ALLOW INCOMING CONNECTIONS
                             AS DateAndTimeExample;


DROP TABLE IF EXISTS CITIES;
CREATE TABLE CITIES (
    CITY_ID   CHAR(2)     PRIMARY KEY,
    CITY_NAME VARCHAR(50) NOT NULL,
    CITY_DATE DATE,
    CITY_TIME TIME,
    CITY_TIMESTAMP TIMESTAMP
);

INSERT INTO CITIES (CITY_ID, CITY_NAME, CITY_DATE, CITY_TIME, CITY_TIMESTAMP)
     VALUES        ('NY', 'New York', '2014-12-27', '17:45:53', '2014-12-27 17:45:53');

Frontend:

<!DOCTYPE html>
<html lang="en">
    <head>
        <script src="http://localhost:8080/fbsql.min.js"></script>
    </head>

    <body>
        <button id="myInput">Update</button>

        <script type="text/javascript">
            let myInput = document.getElementById("DateAndTimeExample");

            const conn = new Connection('http://localhost:8080/db/DateAndTimeExample');
            let psSelect = conn.prepareStatement("SELECT * FROM CITIES WHERE CITY_ID = 'NY'");
            let psUpdate = conn.prepareStatement("UPDATE CITIES SET CITY_DATE = :city_date, CITY_TIME = :cǐty_time, CITY_TIMESTAMP = :city_timestamp WHERE CITY_ID = 'NY'");

            psSelect.executeQuery()
            .then(resultSet => {
                console.log('*** read from database ***');
                console.log(`CITY_DATE: ${resultSet[0].CITY_DATE}`);
                console.log(`CITY_TIME: ${resultSet[0].CITY_TIME}`);
                console.log(`CITY_TIMESTAMP: ${resultSet[0].CITY_TIMESTAMP}`);
            });

            myInput.onclick = function(event) {
                var input = event.target;
                let date = new Date();
                console.log('*** update database ***');
                psUpdate.executeUpdate({city_date: date, city_time: date, city_timestamp: date})
                .then(result => {
                    /* Load image from database */
                    return psSelect.executeQuery();
                })
                .then(resultSet => {
                    console.log('*** read updated values ***');
                    console.log(`CITY_DATE: ${resultSet[0].CITY_DATE}`);
                    console.log(`CITY_TIME: ${resultSet[0].CITY_TIME}`);
                    console.log(`CITY_TIMESTAMP: ${resultSet[0].CITY_TIMESTAMP}`);
                });
            };
        </script>
    </body>
</html>

Database event notification

On server side:
FBSQL supports two forms of database event notification:

By using TRIGGER AFTER clause of DECLARE STATEMENT command.
If trigger procedure ends without excepltion and returns JSON object, JSON object will be transmitted to subscribed clients as event object.
See also: DECLARE STATEMENT

By SCHEDULE command.
If scheduled procedure ends without exception and returns JSON object, JSON object will be transmitted to subscribed clients as event object.
See also: SCHEDULE, Schedule periodic jobs

On client side:
To catch database events on client side you need to add database event listener(s) to your connection object:

const conn = new Connection('http://localhost:8080/db/MyDatbase');
conn.addDatabaseEventListener(listener);

function listener(event) {
    console.log(event);
}

Catch database events and print them on console:

<!DOCTYPE html>
<html lang="en">
    <head>
        <script src="fbsql.min.js"></script>
    </head>
    <body>
        <script type="text/javascript">
            const conn = new Connection('http://localhost:8080/db/ScheduleStatementExample');
            conn.addDatabaseEventListener(event => console.log(event));
        </script>
    </body>
</html>

Catch database events with debug tool:

<!DOCTYPE html>
<html lang="en">
    <head>
        <script src="fbsql.min.js"></script>
        <script src="fbsql-debug.min.js"></script>
    </head>
    <body>
        <script type="text/javascript">
            const conn = new Connection('http://localhost:8080/db/ScheduleStatementExample');
            logDatabaseEvents(conn);
        </script>
    </body>
</html>

Mocking with FBSQL

You can simulate FBSQL database interactions with mock functions. Mock functions does not need connection to backend and can be used completly offline (without FBSQL).

Mock executeQuery() / executeUpdate() methods

client-server version (without mock functions):

const conn = new Connection('http://localhost:8080/db/MyDatabase');
const ps = conn.prepareStatement("SELECT * FROM COUNTRIES");
ps.executeQuery()
.then(resultSet => {
   console.log(resultSet);
   /*
    *   Output:
    *   
    *   [
    *       {
    *           "COUNTRY_ID": "AU",
    *           "COUNTRY_NAME": "Australia"
    *       },
    *       {
    *           "COUNTRY_ID": "DE",
    *           "COUNTRY_NAME": "Germany"
    *       },
    *       {
    *           "COUNTRY_ID": "IN",
    *           "COUNTRY_NAME": "India"
    *       }
    *   ]
    */
);

client-only version (with mock functions):

const mockFunc = function() {
    return [
               {
                   "COUNTRY_ID": "AU",
                   "COUNTRY_NAME": "Australia"
               },
               {
                   "COUNTRY_ID": "DE",
                   "COUNTRY_NAME": "Germany"
               },
               {
                   "COUNTRY_ID": "IN",
                   "COUNTRY_NAME": "India"
               }
           ];
}

const conn = new Connection('http://localhost:8080/db/MyDatabase');
//
// URL does not required when you use only mock functions.
// So, you can just write:
// const conn = new Connection();
//

// query will not transmitted to server
const ps = conn.prepareStatement("SELECT * FROM COUNTRIES");
ps.setMockFunction(mockFunc);
ps.executeQuery()
.then(resultSet => {
   console.log(resultSet);
  /*
   *   Output:
   *   
   *   [
   *       {
   *           "COUNTRY_ID": "AU",
   *           "COUNTRY_NAME": "Australia"
   *       },
   *       {
   *           "COUNTRY_ID": "DE",
   *           "COUNTRY_NAME": "Germany"
   *       },
   *       {
   *           "COUNTRY_ID": "IN",
   *           "COUNTRY_NAME": "India"
   *       }
   *   ]
   */
);

Fire mock database events in client environment

You can fire mock database events by using fireMockDatabaseEvent() method of Connection object.

<!DOCTYPE html>
<html lang="en">
    <head>
        <script src="fbsql.min.js"></script>
    </head>
    <body>
        <button id="myFireButton">Fire Event</button>
        <script type="text/javascript">
            const conn = new Connection('http://localhost:8080/db/ScheduleStatementExample');
            conn.addDatabaseEventListener(event => console.log(event));
            
            const mockEventFunc = function() {
                return {message: "price changed", price: 24.90, time = new Date()};
            }

            const myFireButton = document.getElementById('myFireButton');
            myFireButton.addEventListener('click', function() {
                conn.fireMockDatabaseEvent(mockEventFunc);
            });
        </script>
    </body>
</html>

Frontend debug tool

The frontend debug tool allow you execute queries, updates and listen for database events. Major difference with calling debug tool functions and corresponding FBSQL methods is debug tool automatically outputs verbose debugging information to console and browser window.
To use frontend debug tool just add fbsql-debug.min.js script after fbsql.min.js in <head> section of your HTML:

<!DOCTYPE html>
<html lang="en">
    <head>
        <script src="fbsql.min.js"></script>
        <script src="fbsql-debug.min.js"></script>
    </head>
    ...
</html>

Functions:

logExecuteQuery - helper function for corresponding executeQuery method of PreparedStatement.
Parameters are:
- PreparedStatement - PreparedStatement object of which we want execute query.
- Object - parameters JSON object. E.g. {nameStartsWith: "Samsu"}
logExecuteUpdate - helper function for corresponding executeUpdate method of PreparedStatement.
Parameters are:
- PreparedStatement - PreparedStatement object of which we want execute update.
- Object - parameters JSON object. E.g. {id: "LG-1134", newPrice: 950.00}
logDatabaseEvents - helper function for corresponding addDatabaseEventListener method of Connection.
Parameters are:
- Connection - Connection object of which we want listen for database events.

Example of calling debug tool functions:

<!DOCTYPE html>
<html lang="en">
    <head>
        <script src="fbsql.min.js"></script>
        <script src="fbsql-debug.min.js"></script>
    </head>
    <body>
        <script type="text/javascript">
            const conn = new Connection("http://localhost:8080/db/MyExample");

            /* Log database events for connection 'conn' */
            logDatabaseEvents(conn);

            const ps1   = conn.prepareStatement("SELECT EMPLOYEE_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = :id");
            /* Log execute query for PreparedStatement 'ps1' */
            logExecuteQuery(ps1, {id: "J123"});

            const ps2   = conn.prepareStatement("UPDATE EMPLOYEES SET EMPLOYEE_NAME = :name WHERE EMPLOYEE_ID = :id");
            /* Log execute update for PreparedStatement 'ps2' */
            logExecuteUpdate(ps2, {id: "J123", name: "John"});

        </script>
    </body>
</html>

Did you know?

One of cool FBSQL features is, you can reference statements by their names
Instead of using SQL statement body in client code, just declare statement name with DECLARE STATEMENT command in your init.sql script and reference statement by name.

See also: DECLARE STATEMENT

FBSQL server is a sweet home for all of your static stuff
FBSQL server (optionally) can host your static frontend files. To setup your static content (*.html, *.js, *.css etc.) please copy appropriate files into <FBSQL_HOME>/fbsql/config/tomcat/webapps/ROOT directory.

With FBSQL mock methods you can fast prototype your application
FBSQL have mock functions that allow you create application prototype even you still no decided about concrete database.

Debug tool allow you access the FBSQL backend before your web-application UI is ready
The frontend debug tool allow you execute queries, updates and listen for database events. Major difference with calling debug tool functions and corresponding FBSQL methods is debug tool automatically outputs verbose debugging information to console and browser window.

FBSQL break your database applicative limits by providing custom stored procedures
Custom stored procedures are database and language agnostic, can be written in a variety of programming languages such as Java, JavaScript, Python etc. Just output your result set data as JSON array of objects to stdout (standard output).

You can use self-hosted FBSQL browser client and debug tool
Both of scripts located in FBSQL Server root directory.

<!DOCTYPE html>
<html lang="en">
    <head>
        <!-- self-hosted FBSQL scripts -->
        <script src="http://localhost:8080/fbsql.min.js"></script>
        <script src="http://localhost:8080/fbsql-debug.min.js"></script>
    </head>
    <body>
        <script type="text/javascript">
            //
            // Because we use self-hosted client we can use connection name instead of URL:
            // const conn = new Connection("http://localhost:8080/db/MyExample");
            //
            const conn = new Connection("MyExample");
            ...
        </script>
    </body>
</html>

COMMANDS

CONNECT TO

connect_to_stmt
 : CONNECT TO jdbc_url
   (
    ( USER user ) |
    ( PASSWORD password ) |
    ( PROPERTIES jdbc_connection_properties ) |
    ( DRIVER jdbc_driver_class_name ) |
    ( LIB jar_file ( ',' jar_file )* ) |
    (
     CONNECTION POOL ( ( MIN connection_pool_size_min ) | ( MAX connection_pool_size_max ) )+ 
    ) |
    (
     UNDECLARED STATEMENTS ( ALLOW | REJECT )+ 
    ) |
    (
     INCOMING CONNECTIONS ( ALLOW | REJECT )+ ( IF EXISTS '(' native_sql ')' )?
    ) |
   ( AS? connection_alias )
   )*
 ;

Connects FBSQL to database through JDBC URL.

Clause	Required	Description
`CONNECT TO`	✓	JDBC URL. Please refer to your JDBC driver vendor documentation for more information.
`USER`		Database user name.
`PASSWORD`		Database user password.
`PROPERTIES`		File that contains JDBC connection properties. File must to be in Java properties format. Please refer to your JDBC driver vendor documentation for more information.
`DRIVER`		JDBC driver class name.
`LIB`		JDBC driver libraries. By default FBSQL search for JDBC driver libraries in directory: `.../fbsql/config/tomcat/webapps/ROOT/WEB-INF/lib`
`CONNECTION` `POOL` `MIN`		Connection pool minimum size. Default value is: `1`.
`CONNECTION` `POOL` `MAX`		Connection pool maximum size. Default value is: `100`.
`UNDECLARED` `STATEMENTS`		Allow/reject undeclared statements. Default value is: `REJECT`.
`INCOMING` `CONNECTIONS`		Allow/reject incomming connections. Default value is: `REJECT`. `INCOMING CONNECTIONS` can be used with `IF EXISTS` (native_sql) clause to provide authentication/authorization mechanism. native_sql can contain `:user`, `:password` and `:role` parameters.
`AS`		The alias name for this connection. Incoming connections can not be established if the alias name was not specified.

SWITCH TO

switch_to_stmt
::= SWITCH TO connection_alias
 ;

Switch the current connection to another. All statements in init script followed after SWITCH TO command are belongs to the specified connection.
See also: CONNECT TO

Examples:

/* Switch to MySQLite connection */
SWITCH TO MySQLite;

/* SQLite statements */
...
...

/* Switch to MyOracle connection */
SWITCH TO MyOracle;

/* Oracle statements */
...
...

DECLARE STATEMENT

declare_statement_stmt
 : DECLARE STATEMENT
   '(' native_sql ')'
   (
    STATIC |
    ( COMPRESSION compression_level ) |
    ( ROLES '(' role_name ( ',' role_name )* ')' ) |
    ( TRIGGER BEFORE trigger_before_procedure_name ) |
    ( TRIGGER AFTER trigger_after_procedure_name ) |
    ( AS? statement_alias )
   )*
 ;

Declare native SQL statement. This command allows you to use native SQL statements on frontend side. If the ROLES clause was specified, the statement execution will be available only for roles specified in roles list.

Clause	Required	Description
`DECLARE STATEMENT`	✓	Native SQL statement you would like to expose to frontend.
`STATIC`		This clause used to mark native SQL statement results as immutable. Such statements are executed once when FBSQL server starts. Results compressed and cashed in server memory for fast access without further database interaction.
`COMPRESSION`		Sets compression level for results. Available compression levels are: `BEST COMPRESSION` - compressed with best compression strategy, `BEST SPEED` - compressed with best compression speed strategy, `NO COMPRESSION` - no compression (the default)
`ROLES`		Roles list (comma separated). Restrict statement usage to users that are not specified in the roles list.
`TRIGGER BEFORE`		Procedure that executed before the native SQL statement execution. Procedure must return string with JSON parameters object. If JSON parameters object is NULL or exception occurs execution will be rejected.
`TRIGGER AFTER`		Procedure that executed after the native SQL statement execution. Procedure may return string with the arbitrary JSON event object. If the JSON object is not NULL the database event will fired. Please refer to client's `Connection#addDatabaseEventListener()` method for information about how to catch database events on frontend side.
`AS`		The alias name for this statement. If specified, you can use this name on frontend side instead SQL statement source code.

Examples:

/* Declare the immutable query using STATIC clause */ 
DECLARE STATEMENT (SELECT * FROM COUNRIES)
           STATIC
      COMPRESSION BEST COMPRESSION
               AS CountriesList;

/*
 * On frontend you can use both forms:
 *
 * preparedStatement.executeQuery("SELECT * FROM COUNRIES")
 * preparedStatement.executeQuery("#CountriesList")
 *
 */

DECLARE STATEMENT (SELECT * FROM ORDERS WHERE ORDER_ID = :id)
      COMPRESSION BEST COMPRESSION
            ROLES ('admin', 'manager')
               AS OrdersById;
/*
 * On frontend you can use both forms:
 *
 * preparedStatement.executeQuery("SELECT * FROM ORDERS WHERE ORDER_ID = :id")
 * preparedStatement.executeQuery("#OrdersById")
 *
 */

DECLARE PROCEDURE

declare_procedure_stmt
 : DECLARE PROCEDURE procedure_name TYPE
   (
   	JAVA |
   	JS   |
   	EXEC |
   	URL
   )*
   (
    OPTIONS json |
    OPTIONS FILE json_file
   )?
 ;

Declare non-native stored procedure. This command allows you to custom non-native stored procedures available on both backend and frontend sides. If the ROLES clause was specified, the statement execution will be available only for roles specified in roles list.

Clause	Required	Description
`DECLARE PROCEDURE`	✓	Stored procedure name.
`TYPE`	✓	Stored procedure type.
`OPTIONS`		Options for specified `TYPE`. Options is JSON string represents options object for specified type.
`OPTIONS FILE`		Options file. Options file contains source of options object for specified type.

Type	Description	Options
`JAVA`	Java language (or any JVM compatible languages)	`class` - Java class name `method` - Method name
`JS`	JavaScript/ECMAScript language	`file` - JavaScript file name `function` - Function name
`EXEC`	Operating system executable	`file` - OS executable file name
`URL`	Resource URL	`url` - Resource URL (`"http://"`, `"https://"`, `"file://"` are supported) or local file (absolute and relative path supported)

Examples:

DECLARE PROCEDURE MY_PROC
             TYPE JAVA
          OPTIONS '{"class": "org.fbsql_examples.StoredProcedures", "method": "getCustomers"}';

DECLARE PROCEDURE GET_COUNTRIES
             TYPE JS
          OPTIONS '{"file": "/home/john/scripts/procedures.js", "function": "getCountries"}';

DECLARE PROCEDURE SEND_SMS
             TYPE EXEC
          OPTIONS '{"file": "/home/john/send-sms"}';

DECLARE PROCEDURE GET_ITEMS
             TYPE URL
          OPTIONS '{"url": "https://example.com/api/items"}';

DECLARE PROCEDURE GET_ITEMS
             TYPE URL
     OPTIONS FILE '/home/john/my-options.json';

See also: Database agnostic stored procedures

SCHEDULE

schedule_stmt
 : SCHEDULE procedure_name AT cron_expression
 ;

Schedule periodic jobs. FBSQL has own scheduler to run periodic jobs. Stored procedures can be scheduled to run according cron expressions, which are able to create firing schedules such as: “At 8:00am every Monday through Friday” or “At 1:30am every last Friday of the month”.

Clause	Required	Description
`SCHEDULE`	✓	Stored procedure name.
`AT`	✓	cron expression.

Most of information about cron was taken from Cron Trigger Tutorial

cron is a UNIX tool that has been around for a long time, so its scheduling capabilities are powerful and proven.

Format

A cron expression is a string comprised of 6 or 7 fields separated by white space. Fields can contain any of the allowed values, along with various combinations of the allowed special characters for that field. The fields are as follows:

Field Name	Mandatory	Allowed Values	Allowed Special Characters
Seconds	YES	0-59	, - * /
Minutes	YES	0-59	, - * /
Hours	YES	0-23	, - * /
Day of month	YES	1-31	, - * ? / L W
Month	YES	1-12 or JAN-DEC	, - * /
Day of week	YES	1-7 or SUN-SAT	, - * ? / L #
Year	NO	empty, 1970-2099	, - * /

So cron expressions can be as simple as this: * * * * ? *

or more complex, like this: 0/5 14,18,3-39,52 * ? JAN,MAR,SEP MON-FRI 2002-2010

Special characters

* (“all values”) - used to select all values within a field. For example, “*” in the minute field means “every minute”.
? (“no specific value”) - useful when you need to specify something in one of the two fields in which the character is allowed, but not the other. For example, if I want my trigger to fire on a particular day of the month (say, the 10th), but don’t care what day of the week that happens to be, I would put “10” in the day-of-month field, and “?” in the day-of-week field. See the examples below for clarification.
- - used to specify ranges. For example, “10-12” in the hour field means “the hours 10, 11 and 12”.
, - used to specify additional values. For example, “MON,WED,FRI” in the day-of-week field means “the days Monday, Wednesday, and Friday”.
/ - used to specify increments. For example, “0/15” in the seconds field means “the seconds 0, 15, 30, and 45”. And “5/15” in the seconds field means “the seconds 5, 20, 35, and 50”. You can also specify ‘/’ after the ‘’ character - in this case ‘’ is equivalent to having ‘0’ before the ‘/’. ‘1/3’ in the day-of-month field means “fire every 3 days starting on the first day of the month”.
L (“last”) - has different meaning in each of the two fields in which it is allowed. For example, the value “L” in the day-of-month field means “the last day of the month” - day 31 for January, day 28 for February on non-leap years. If used in the day-of-week field by itself, it simply means “7” or “SAT”. But if used in the day-of-week field after another value, it means “the last xxx day of the month” - for example “6L” means “the last friday of the month”. You can also specify an offset from the last day of the month, such as “L-3” which would mean the third-to-last day of the calendar month. When using the ‘L’ option, it is important not to specify lists, or ranges of values, as you’ll get confusing/unexpected results.
W (“weekday”) - used to specify the weekday (Monday-Friday) nearest the given day. As an example, if you were to specify “15W” as the value for the day-of-month field, the meaning is: “the nearest weekday to the 15th of the month”. So if the 15th is a Saturday, the trigger will fire on Friday the 14th. If the 15th is a Sunday, the trigger will fire on Monday the 16th. If the 15th is a Tuesday, then it will fire on Tuesday the 15th. However if you specify “1W” as the value for day-of-month, and the 1st is a Saturday, the trigger will fire on Monday the 3rd, as it will not ‘jump’ over the boundary of a month’s days. The ‘W’ character can only be specified when the day-of-month is a single day, not a range or list of days.

The 'L' and 'W' characters can also be combined in the day-of-month field to yield 'LW', which translates to *"last weekday of the month"*.

# - used to specify “the nth” XXX day of the month. For example, the value of “6#3” in the day-of-week field means “the third Friday of the month” (day 6 = Friday and “#3” = the 3rd one in the month). Other examples: “2#1” = the first Monday of the month and “4#5” = the fifth Wednesday of the month. Note that if you specify “#5” and there is not 5 of the given day-of-week in the month, then no firing will occur that month.

The legal characters and the names of months and days of the week are not case sensitive. MON is the same as mon.

Examples

Here are some full examples:

Expression	Meaning
`0 0 12 * * ?`	Fire at 12pm (noon) every day
`0 15 10 ? * *`	Fire at 10:15am every day
`0 15 10 * * ?`	Fire at 10:15am every day
`0 15 10 * * ? *`	Fire at 10:15am every day
`0 15 10 * * ? 2005`	Fire at 10:15am every day during the year 2005
`0 * 14 * * ?`	Fire every minute starting at 2pm and ending at 2:59pm, every day
`0 0/5 14 * * ?`	Fire every 5 minutes starting at 2pm and ending at 2:55pm, every day
`0 0/5 14,18 * * ?`	Fire every 5 minutes starting at 2pm and ending at 2:55pm, AND fire every 5 minutes starting at 6pm and ending at 6:55pm, every day
`0 0-5 14 * * ?`	Fire every minute starting at 2pm and ending at 2:05pm, every day
`0 10,44 14 ? 3 WED`	Fire at 2:10pm and at 2:44pm every Wednesday in the month of March.
`0 15 10 ? * MON-FRI`	Fire at 10:15am every Monday, Tuesday, Wednesday, Thursday and Friday
`0 15 10 15 * ?`	Fire at 10:15am on the 15th day of every month
`0 15 10 L * ?`	Fire at 10:15am on the last day of every month
`0 15 10 L-2 * ?`	Fire at 10:15am on the 2nd-to-last last day of every month
`0 15 10 ? * 6L`	Fire at 10:15am on the last Friday of every month
`0 15 10 ? * 6L`	Fire at 10:15am on the last Friday of every month
`0 15 10 ? * 6L 2002-2005`	Fire at 10:15am on every last friday of every month during the years 2002, 2003, 2004 and 2005
`0 15 10 ? * 6#3`	Fire at 10:15am on the third Friday of every month
`0 0 12 1/5 * ?`	Fire at 12pm (noon) every 5 days every month, starting on the first day of the month.
`0 11 11 11 11 ?`	Fire every November 11th at 11:11am.

Pay attention to the effects of '?' and '*' in the day-of-week and day-of-month fields!

Notes

Support for specifying both a day-of-week and a day-of-month value is not complete (you must currently use the ‘?’ character in one of these fields).
Be careful when setting fire times between the hours of the morning when “daylight savings” changes occur in your locale (for US locales, this would typically be the hour before and after 2:00 AM - because the time shift can cause a skip or a repeat depending on whether the time moves back or jumps forward. You may find this wikipedia entry helpful in determining the specifics to your locale:
https://secure.wikimedia.org/wikipedia/en/wiki/Daylight_saving_time_around_the_world

INCLUDE

include_script_file_stmt
 : INCLUDE sql_script_file ( ',' sql_script_file )*
 ;

Include single or multiple SQL script file(s) into this SQL script.

Examples:

/* Include single file */
INCLUDE 'my.sql';

/* Include multiple files */
INCLUDE 'a.sql', 'b.sql', '/home/john/scripts/c.sql';

FUNCTIONS

REMOTE_USER()

Return remote user name as CHARACTER type. Remote user name transmitted to server from client's Connection object.

Example:


REMOTE_USER()

REMOTE_ROLE()

Return remote user role as CHARACTER type. Remote user role transmitted to server from client's Connection object.

Example:


REMOTE_ROLE()

REMOTE_SESSION_ID()

Return remote session id as CHARACTER type.

Example:


REMOTE_SESSION_ID()

REMOTE_SESSION_CREATION_TIME()

Return remote session creation time as INTEGER type.

Example:


REMOTE_SESSION_CREATION_TIME()

REMOTE_SESSION_LAST_ACCESSED_TIME()

Return remote session last accessed time as INTEGER type.

Example:


REMOTE_SESSION_LAST_ACCESSED_TIME()

REMOTE_USER_INFO()

Return verbose client information as CHARACTER type in form of JSON object.

Example:


REMOTE_USER_INFO()

IN_ROLE(character)

Checks if remote user in role provided as parameter as BOOLEAN type.

Example:


IN_ROLE('manager')

GET_COOKIE(character)

Get cookie value as CHARACTER type.

Example:


GET_COOKIE('customer_id')

GET_HTTP_SESSION_ATTRIBUTE_AS_CHAR(character)

Get HTTP session attribute as CHARACTER type.

Example:


GET_HTTP_SESSION_ATTRIBUTE_AS_CHAR('my_entry')

GET_HTTP_SESSION_ATTRIBUTE_AS_INTEGER(character)

Get HTTP session attribute as INTEGER type.

Example:


GET_HTTP_SESSION_ATTRIBUTE_AS_INTEGER('my_entry')

GET_HTTP_HEADER_AS_CHAR(character)

Get HTTP request header value as CHARACTER type.

Example:


GET_HTTP_HEADER_AS_CHAR('Accept-Charset')

GET_HTTP_HEADER_AS_DATE(character)

Get HTTP request header value as DATE type.

Example:


GET_HTTP_HEADER_AS_DATE('Date')

GET_HTTP_HEADER_AS_INTEGER(character)

Get HTTP request header value as INTEGER type.

Example:


GET_HTTP_HEADER_AS_INTEGER('Content-Length')

CONSTANTS

FBSQL_REMOTE_USER

Return remote user name as CHARACTER type. Remote user name transmitted to server from client's Connection object.

Example:


FBSQL_REMOTE_USER

FBSQL_REMOTE_ROLE

Return remote user role as CHARACTER type. Remote user role transmitted to server from client's Connection object.

Example:


FBSQL_REMOTE_ROLE

FBSQL_REMOTE_SESSION_ID

Return remote session id as CHARACTER type.

Example:


FBSQL_REMOTE_SESSION_ID

FBSQL_REMOTE_SESSION_CREATION_TIME

Return remote session creation time as INTEGER type.

Example:


FBSQL_REMOTE_SESSION_CREATION_TIME

FBSQL_REMOTE_SESSION_LAST_ACCESSED_TIME

Return remote session last accessed time as INTEGER type.

Example:


FBSQL_REMOTE_SESSION_LAST_ACCESSED_TIME

FBSQL_REMOTE_USER_INFO

Return verbose client information as CHARACTER type in form of JSON object.

Example:


FBSQL_REMOTE_USER_INFO

Client API

Connection

Method prepareStatement()

Creates a PreparedStatement object for sending parameterized SQL statements to the database. A SQL statement stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

Method addDatabaseEventListener()

The Connection method addDatabaseEventListener() sets up a function that will be called whenever the database event is delivered to the client.

Example:

const conn = new Connection("http://localhost:8080/db/MyDatabase");
conn.addDatabaseEventListener(event => console.log(event));

Method fireMockDatabaseEvent()

Fires a custom database event on client.

Example:

const conn = new Connection("http://localhost:8080/db/MyDatabase");
conn.addDatabaseEventListener(event => console.log(event));

const mockEventFunc = function() {
    return {message: "price changed", price: 24.90, time = new Date()};
}

const myButton = document.getElementById('myButton');
myButton.addEventListener('click', function() {
    conn.fireMockDatabaseEvent(mockEventFunc);
});

PreparedStatement

Method executeQuery()

Executes the SQL query in this PreparedStatement object and returns the result set array generated by the query. Returns: a result set array that contains the data produced by the query; never null.

Example:

[
    {
        "COUNTRY_ID": "AU",
        "COUNTRY_NAME": "Australia"
    },
    {
        "COUNTRY_ID": "DE",
        "COUNTRY_NAME": "Germany"
    },
    {
        "COUNTRY_ID": "IN",
        "COUNTRY_NAME": "India"
    }
]

Method executeUpdate()

Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement. Returns: execution result information (row count and generated keys for SQL Data Manipulation Language (DML) statements) in form JSON object. rowCount = 0 for SQL statements that return nothing.

Example:

{
    "rowCount": 2,
    "generatedKeys": [
                       {
                           "EMPLOYEE_ID": 123
                       },
                       {
                           "EMPLOYEE_ID": 124
                       }
                   ]
}

Method setResultSetFormat()

Sets the result set format.

Available formats:

PreparedStatement.FORMAT_ARRAY_OF_OBJECTS
Arrays with records as plain JSON objects. The default.

PreparedStatement.FORMAT_ARRAY_OF_ARRAYS
2D-array

PreparedStatement.FORMAT_OBJECT_OF_ARRAYS
Pivot object with column names as keys and arrays of cell values as values

Method setMockFunction()

Sets custom mock function.
Examples:

const mockFunc = function() {
    return [
               {
                   "COUNTRY_ID": "AU",
                   "COUNTRY_NAME": "Australia"
               },
               {
                   "COUNTRY_ID": "DE",
                   "COUNTRY_NAME": "Germany"
               },
               {
                   "COUNTRY_ID": "IN",
                   "COUNTRY_NAME": "India"
               }
           ];
}

const conn = new Connection('http://localhost:8080/db/MyDatabase');
//
// URL does not required when you use only mock functions.
// So, you can just write:
// const conn = new Connection();
//

// query will not transmitted to server
const ps = conn.prepareStatement("SELECT * FROM COUNTRIES");
ps.setMockFunction(mockFunc);
ps.executeQuery()
.then(resultSet => {
   console.log(resultSet);
  /*
   *   Output:
   *   
   *   [
   *       {
   *           "COUNTRY_ID": "AU",
   *           "COUNTRY_NAME": "Australia"
   *       },
   *       {
   *           "COUNTRY_ID": "DE",
   *           "COUNTRY_NAME": "Germany"
   *       },
   *       {
   *           "COUNTRY_ID": "IN",
   *           "COUNTRY_NAME": "India"
   *       }
   *   ]
   */
);

Frontend debug tool

Frontend debug tool fbsql-debug.min.js is a client side script contains three helper functions that you can use to test execution of your SQL statements. To use frontend debug tool just add fbsql-debug.min.js script after fbsql.min.js in <head> section of your HTML:

<!DOCTYPE html>
<html lang="en">
    <head>
        <script src="fbsql.min.js"></script>
        <script src="fbsql-debug.min.js"></script>
    </head>
    ...
</html>

Function logExecuteQuery()

logExecuteQuery() - helper function for corresponding executeQuery method of PreparedStatement.
Parameters are:

PreparedStatement - PreparedStatement object of which we want execute query.
Object - parameters JSON object. E.g. {nameStartsWith: "Samsu"}

Function logExecuteUpdate()

logExecuteUpdate() - helper function for corresponding executeUpdate method of PreparedStatement.
Parameters are:

PreparedStatement - PreparedStatement object of which we want execute update.
Object - parameters JSON object. E.g. {id: "LG-1134", newPrice: 950.00}

Function logDatabaseEvents()

logDatabaseEvents() - helper function for corresponding addDatabaseEventListener method of Connection.
Parameters are:

Connection - Connection object of which we want listen for database events.

Cheap and reliable Node.js hosting starts at $3/month, and $1/month static HTML hosting

fbsql / fbsql

Programming Languages

Labels

Projects that are alternatives of or similar to fbsql

FBSQL - Frontend Backend SQL

Getting started

FBSQL distributions

Installation

Command line interface (CLI)

Configuration and fine tuning

Init script

Authentication

Authorization

Expose our database to frontend

Execute SQL statements

Triggers, event notification, parameters checking

Reseult set formats

Database agnostic stored procedures

Schedule periodic jobs

Binary data

Date and Time

Database event notification

Mocking with FBSQL

Frontend debug tool

Did you know?

COMMANDS

CONNECT TO

SWITCH TO

DECLARE STATEMENT

DECLARE PROCEDURE

SCHEDULE

Format

Special characters

Examples

Notes

INCLUDE

FUNCTIONS

REMOTE_USER()

REMOTE_ROLE()

REMOTE_SESSION_ID()

REMOTE_SESSION_CREATION_TIME()

REMOTE_SESSION_LAST_ACCESSED_TIME()

REMOTE_USER_INFO()

IN_ROLE(character)

GET_COOKIE(character)

GET_HTTP_SESSION_ATTRIBUTE_AS_CHAR(character)

GET_HTTP_SESSION_ATTRIBUTE_AS_INTEGER(character)

GET_HTTP_HEADER_AS_CHAR(character)

GET_HTTP_HEADER_AS_DATE(character)

GET_HTTP_HEADER_AS_INTEGER(character)

CONSTANTS

FBSQL_REMOTE_USER

FBSQL_REMOTE_ROLE

FBSQL_REMOTE_SESSION_ID

FBSQL_REMOTE_SESSION_CREATION_TIME

FBSQL_REMOTE_SESSION_LAST_ACCESSED_TIME

FBSQL_REMOTE_USER_INFO

Client API

Connection

Method prepareStatement()

Method addDatabaseEventListener()

Method fireMockDatabaseEvent()

PreparedStatement

Method executeQuery()

Method executeUpdate()

Method setResultSetFormat()

Method setMockFunction()

Frontend debug tool

Function logExecuteQuery()

Function logExecuteUpdate()

Function logDatabaseEvents()