How to set up an External SQL Connection in EasyBadge

These instructions detail how to set up an External SQL Connection in EasyBadge.

EasyBadge can connect to and external databases table or view using its ‘External Connection’ feature.

Before we set up the connection it is important to understand how EasyBadge software works.

EasyBadge always needs its own database instance creating before you set up the connection. Once the EasyBadge Database is set up you set up the external connection from

within this database and EasyBadge syncs the External data into its own table mirroring the source data table or view. The reason for this is it enables more control

over the advanced database and print functions used in EasyBadge.

EasyBadge provides methods for using cached query expressions so that remote data is only retrieved once from external database connections. What this means, is that rather than running a query to retrieve each individual field i.e. 7 queries for 7 fields, you can set EasyBadge to run one query that returns multiple fields, hence potentially speeding up the data retrieval.

The following pre-requisites  are required before setting up the external connection.

• You have a source data table or view to connect to (eg SQL server database ) and the EasyBadge user has access to this.

• Any drivers for the external database (SQL , MySQL or Oracle drivers) are already installed and working.
• You have created an EasyBadge database instance which holds all the fields you need to sync, Please refer to the Configuring a custom database help topic.
• For easy of set up it is recommended any fields in the EasyBadge database have the exact same name as the source table or view.
• If you want to link to staff or student photos which are named uniquely  in a network folder it is recommended you set up and Image on Disk field which is details in the help section.

Creating the External Connection.

1.  Click the Menu button top left 

2. Click Modify Database

3. Click External Connections

4. Click the ‘Create new Connection’ icon.

5. Click ‘SQL Server’ Icon.

6. Enter the name of the server in ‘Server’ and the name of you EasyBadge database in ‘Database Name’, Then Click’ Test’ to check the connection. You should receive ‘Test Successful’, Then Click OK. 

                If your test was unsuccessful  you need to check you SQL privileges with your SQL Administrator.

7.  click Add Query

8. Name this first query ‘Load’ as below. In ‘SQL’ insert the SQL  query that will load the data using SELECT and FROM statements. In this example the table being loaded from is call IMAGEBASE. Then tick ‘Synchronisation Query’ and click test to check it works.

9. Repeat step 7 and 8 but this time name the query ‘Sync’. This query uses SELECT, FROM and WHERE Statements. In ‘Test’ you can validate a unique entry in the database.

Once you have complete this form click OK.

10. Now Click the Test button, on the query results page in the comments column click each different line and select map this field. The comment line will change to ‘Data is being used’, then click OK.

11. Now from the Main EasyBadge view click the Menu button top left 

12. Next click “import & Export and “Synchronise Load” to trigger the Synchronisation of  the query.

13. Once the Sync is complete you will see the below dialogue box and you can click “Close”.

14.  If you need to you can adjust the Database Synchronisation settings by clicking the Menu button > Modify Database > Database settings to view this dialogue box.

The SQL data should now be synced into the EasyBadge SQL table ready for the user.