Floreant POS – Database – ORO POS – Point of Sale

Floreant POS – Database

Intro

Floreant POS ships with Apache Derby database with sample menu items. This database is an embedded database and cannot be accessed from another computer. If you need to connect more computers to one central database you need to set up a database server.

Server options

We can use one of the 3 database systems.

  • Apache Derby Server
    3.5MB small footprint database suitable for small stores.
  • Postgresql 9.5
    We highly recommend this database for multi-terminal deployment.
  • MySQL 5.6
    Very popular database but apparently it has lot more issues.

Here we will discuss on Derby server. For Postgresql or MySQL you can find online instructions and when done set terminals in the same way.
 Please note the recommended DB versions.

Derby Server Start

If you are in the windows operating system enter database/derby-server and click on start-server.bat . In case you are in Linux or mac you should run start-server.sh

When it starts you will find a terminal window running and waiting for a database connection.

Connect Terminals

Terminals can be connected with LAN or WIFI. However, in a busy restaurant, we would recommend LAN.  Make sure all terminals are connected and they can see server system.

Every database has a port number and that must be open in Firewall.

? We do not use standard derby port,  we use 51527 

Our next job is to set the following information for every terminal. Here are our default settings.

  • DB type: apache derby server
  • Server: localhost
  • DB name: posdb (change this value as per your configuration)
  • User: app
  • Password: sa

In a multi-terminal system, you should put the server IP address here. You can use ipconfig command in windows and ifconfig in Linux.  In the following screenshot, you can find how we find the server IP addresses. 

 

Configure Terminal

As you have found server IP you have to apply that in terminal configuration. In the above screenshot, we find our database server address is 192.168.1.66.  After setting the test connection, save and restart.

 

 

 

Recreate Database

If you ever need to reset the full system you can recreate the database.

⚡ It will wipe out your existing data. Shut down POS and take backup of the database by copying database folder.

  • Click on “CREATE DATABASE SCHEMA”.
  • If you need sample data say yes.

You will be prompted on success and you are all set.

? This is a known issue that Derby often fails to remove existing data and recreate the database. Delete the database folder and recreating DB should solve the issue. You can also create a database in a different name.

 Create a separate database for training purposes.

Database Access

If you are a power user you can directly access the database with DBeaver SQL client.
As our system uses hibernate, we would NOT recommend inserting or updating directly to the database.

Create a Connection to Access Data

Follow the steps below to add credentials and other required connection properties.

  1. In the Databases menu, click New Connection.
  2. In the Create new connection wizard that results, select the driver.
  3. On the next page of the wizard, click the driver properties tab.
  4. Enter values for authentication credentials

    From Menu click on Database and select the new connection

    Database Schema

    Download latest ER Diagram from here.

     

    Query on Database

    You can now query information from the tables exposed by the connection: Right-click a Table and then click Edit Table. The data is available on the Data tab.

     

 

 

Disclaimer: The information and media included on this website are not legal documents. Whilst we make every attempt to ensure the accuracy and reliability of the information contained in the documents stored, served and accessed on this site, this information should not be relied upon as a substitute for formal advice from our company. Features described in this document are subject to change without any notice.