SQL ICE

 

 

User Guide

Version 2.2.0

 

 

 

by

Business Integration Technology, Inc.

 


 

Document date:  August 1, 2013

 

Table of Contents

1. Overview

2.  Introduction

3.  Getting Started

3.1.  AWS Marketplace

3.2.  Create Instance

3.3.  Configure

3.3.1.  Log In

3.3.2.  Change Admin Password

3.3.3.  Log Out

4.  User Interface

4.1.  Queries Tab

4.2.  Upload/Download Tab

4.3.  Connections Tab

4.4.  Users Tab

4.5.  My Account Tab

4.6.  Logout Tab

5.  My Data Management

5.2.  My Data

5.3.  Creating an Application Group

6.  Creating Your Own Database Application

6.1.  Create Query

6.2.  Parameters

6.3.  Other Query SQL

6.4.  Publishing Your Query

6.5.  Examples

7.  Advanced Features

7.1.  Upload/Download

7.1.1.  CSV Upload

7.1.1.1.  Add CSV to the Insert Query

7.1.1.2.  Verify Format of CSV Column Headings

7.1.1.3.  Create the Upload CSV Logic

7.1.1.4.  Upload the CSV file

7.1.2.  CSV Download

A.  Appendix

A.1.  Contact BIT

 

 


 

1. Overview

This User Guide provides a broad overview of the SQL ICE™ software product.

It begins with a high-level introduction of the functionality and its primary usage.

Then installation procedures are explained in detail, followed by directions for starting up the application and logging in.

Next, the user interface is reviewed.

Finally, comprehensive, step-by-step instructions are provided, along with screen shots and examples, which explain how to accomplish specific functions.
 

2. Introduction

This installation contains all of the sophisticated functions of SQL ICE™ that require advanced or expert knowledge of SQL.

For information on the other products and services we offer, such as Managed File Transfer or B2B Integration, please Contact Us or visit our main website at http://businessintegrationtechnology.com.

SQL ICE™ is revolutionary...

If you know SQL, you can build web applications, using the SQL you already know.

With SQL ICE™, you eliminate the "request-prioritize-approve-allocate" process involved in the typical application development cycle.

In your current organization:

·        How long does it take to build and deploy a new report?

·        Are reports available on the Web?

·        Can your application participate in Service-Oriented Architecture (SOA)?

·        Is enterprise application integration (EAI) a challenge?

If your application is implemented on a relational database and you know SQL and the application's schema, then SQL ICE™ can:

·        Connect to your existing/legacy database(s) and write queries to generate reports or manage database maintenance

·        Build complete internet applications, with insert, update, delete, even branching logic

·        Publish reports on the Web literally in minutes, using only SQL and your knowledge of the database

·        Extract data for spreadsheet analysis in .csv files

·        Enter new data to legacy applications – without access to the application code

·        Enable Web service integration for both new and legacy applications

·        Convert the relational data to XML and expose it as a Web service for SOA and EAI

·        Create JSON objects for sophisticated Web application development

The SQL ICE installation package includes an Apache Derby database that you can use to create your own databases. SQL ICE also supports the following databases:

·        MySQL

·        Derby

·        SQL Server

·        Oracle

·        any other database for which a jdbc driver exists

Please visit SQL ICE for more information, or contact us via email or phone:  314-635-6351, for detailed information on the many uses for SQL ICE™.


 

3. Getting Started

After completing this section, you will have a fully functioning installation of SQL ICE™.

The instructions for using the Amazon AWS Marketplace to create your own instance in Amazon Web Services’ Elastic Compute Cloud (EC2) are covered.  Procedures for starting up the application, logging in and out, and shutting it down are then discussed. 

3.1.  AWS Marketplace

Open a web browser and navigate to:  The BIT page at the AWS Marketplace. Select the SQL ICE product and click 'Continue'.

3.2.  Create Instance

Log in (or create an AWS account) to your account and select 'Launch with 1-Click'. After the instance is created, click on 'Your Software'. Once the instance is up and running, click on 'Access Software'.

3.3.  Configure

3.3.1.  Log In

From a web browser navigate to:  http://(your instance URI)/sqlice.

              

The admin user has full administrative privileges, including creating and maintaining user accounts, and access to all user functions.

username

password

admin

password

 

After a successful login, you will see a screen similar to the one below.

3.3.2.  Change Admin Password

It is very important to change the admin user password as soon as possible.  Click on the My Account tab.  Enter the current password and your new password in the input boxes.

3.3.3.  Log Out

To log out, click the tab farthest to the right, Logout.

4.  User Interface

This section of the User Guide offers a high-level explanation for each of the tabs across the top of the user interface.  More detailed instructions for specific functionality are found in the tutorials and examples that are found in later sections of the User Guide.

4.1. Queries Tab

After a successful login, the first screen the user sees is the Queries tab.  The Queries tab displays the list of queries that the user can access and run.  We call these Applications. The list of Applications that the user sees is the result of a SQL ICE query.

              

To see the query example that creates this list, click Query in the breadcrumb navigation, then click Edit for Web Display.

 

 

 

 

This is the SQL that creates the list of Applications on the Queries tab.  The Admin user can edit this query.

4.2. Upload/Download Tab

The Upload/Download tab is used to create ETL (Extract, Transform, Load logic.  ETL is a database process that involves:

·        Extracting data from outside sources

·        Transforming it to fit operational needs

·        Loading it into a destination database, data warehouse, or data mart

You can create a statement to download data from a database as a CSV file or as XML.  You can also create an ETL to upload data from a source file, CSV or XML.  In addition, you can create a “pull/push” ETL that uploads data from a source, executes some transform logic mid-process, then pushes the transformed data to a destination.

Some instructions for this feature are provided in the Advanced Features section.

4.3. Connections Tab

The Connections tab is used to create and maintain your database connections.  The SQL ICE installation package includes an Apache Derby database, so any new databases you create are Derby databases.  However, you can use SQL ICE to connect to external databases as well, and the following databases are supported:

·        MySQL

·        Derby

·        SQL Server

·        Oracle

·        any other database for which a jdbc driver exists

To create a new connection, click the Connections tab and then click New Connection.

              

Let’s say you’re running SAP and want to connect to your Oracle database.  Enter a Connection Name like “SAP”, then select “Oracle” in the Connection Type drop-down box, and then click Next.

              

Enter a valid Username and Password, and the appropriate Host and Database names for the database you want to access.  Click Finish.

              

You will see your new connection listed on the screen.

              

You can now write or modify queries to point to this database by selecting it as the Data Source in the query profile screen.

              

SQL ICE is a powerful tool to connect to all your databases using JDBC drivers. But most drivers are not open source and BIT cannot distribute them. To make your new SQL ICE Connection work with any external database (except Derby), you will need to obtain the driver and copy it to the SQL ICE lib folder in your SQL ICE installation. This is typically something like C:\SQLICE 2.0.2\apache-tomcat-6.0.32\webapps\sqlice\WEB-INF\lib or /usr/share/tomcat6/webapps/sqlice/WEB-INF/lib (depending on your operating system). The driver will be a jar file with a name like mysql-connector-java-5.1.8-bin.jar  (for mySQL).

 If you do not provide the driver and you define a connection that requires the driver, your SQL ICE startup will fail. If this happens, just copy the needed driver to the lib folder/directory as specified above and startup SQL ICE again.

4.4. Users Tab

The Users tab is where you create and maintain user IDs. You can add new users, and for existing users you can change user name or password, and mark as active or inactive.

4.5.  My Account Tab

The My Account tab is where the user that is currently logged in can change their password.

4.6. Logout Tab

When the Logout tab is clicked, the user is immediately logged out and presented with the login screen.  There is no pop-up message to confirm that the user really wants to log out.
 

5.  My Data Management

Click My Data Management to begin.

              

The My Data Management element is where you create and maintain your selection codes.

5.2.  My Data

5.3.  Creating an Application Group

Once you have completed defining and creating your database application, you can create an Application group so that the pages you created are grouped together under their own link on the Queries tab.

This step is not required, but makes your application more user-friendly.

We will use the “Shopping Lists” example for illustration.

Click My Data.

Click the edit icon for the first item, Item Catalog.

              

Click Edit Attributes, then click Add Attribute, and then add an Application Attribute of “Shopping Lists”

              

              

              

              

We now have “Shopping Lists” listed as an Application on the Queries tab.

              

Item Catalog can still be found under My Data, but now it can also be found under Shopping Lists.  If you no longer want it to appear under My Data, you can delete that Attribute.

 

This completes the example “Shopping Lists”.
 

6.  Creating Your Own Database Application

SQL ICE™ provides a complete Internet computing environment for creating Web database applications that access existing databases and database servers outside the SQL ICE™ framework. The Admin user creates SQL ICE™ queries that can perform all the operations necessary for a shared database application.

6.1. Create Query

The admin user creates new queries by clicking the New Query’ link at the bottom of any list of queries.

This will open the Query Editor for a new Query.

Enter the Query Name you want users to see on the Application Menu, a valid Query URL, and select a Data Source from the defined Connections. When you are ready to test your Query, click the Active checkbox to make the Query runnable.

When you click the Add SQL button, you will be taken to the SQL editor to enter your SQL statements. The default Query Output Type is ‘Web Display’ for interactive end-user applications. The SQL statement may be a Select, Insert, Update, Create, Delete, Drop, or many other statements. The syntax is dependent on you’re the syntax of the database specified in the Data Source and its corresponding Connection.

For instance, you may have a Customers table and want to display all Customers with a name starting with a particular string. In many SQL dialects, your statement would look something like this:

Click the ‘Save SQL’ button to return to the Query Editor.

6.2. Parameters

Then click ‘Add/Edit Params’ to enter the definition of the ‘Name’ parameter.

The definition for Name looks like this:

There are many kinds of parameters and built-in parameter generators.

Click ‘Save Query’ and you will be returned to the Query Editor. You can test your Query by clicking on the ‘Web Display’ link and continue to edit it by clicking on the ‘Edit’ link next to the ‘Web Display’ link. You may want to add Web Display Header Web Display Footer queries,

6.3. Other Query SQL

Any SQL ICE query may have an optional 'Authorize' SQL statement that runs in the context of the target database and enforces whatever control the SQL ICE administrator chooses to enforce. For SQL ICE internal queries, the Authorize SQL usually only checks whether the user is an Admin or not (available through the built-in "Is User Query Admin" parameter generator). For your production queries, you can construct a query that enforces whatever security your target database or application requires.

6.4. Publishing Your Query

We you are satisfied with your Query, you will likely want to add an Application Attribute by clicking on the ‘Edit Attributes’ link. This will make your Query appear on Application Menus.

A typical application will have from a handful to many dozens of Queries with Inserts, Updates, Deletes, and Selects to maintain the data. One of the powerful features of SQL ICE™ is the ability to craft HTML formatting and especially HTML links in your SQL to create rich applications including menus, images, and even JavaScript.

6.5.  Examples

Your SQL ICE™ distribution includes two sample applications to demonstrate some of the feature available for Query designers. The ‘Hello’ application is a very simple single-table application that shows how to do pagination and create navigation between the list, add, modify, and delete functions. There is a much more representative application from both the ‘Demo’ and ‘Invoicing’ links. The ‘Invoicing’ link exposes only the queries an end-user would see and the ‘Demo’ link is for Admin users that need to understand how the functionality is implemented.
 

 

7. Advanced Features

This section contains information about some of the more advanced features of this version of SQL ICE™.

7.1.  Upload/Download

The Upload/Download tab provides the capability to upload data from a CSV file into your database application.  You can also download data from your application as a CSV file.

7.1.1.  CSV Upload

There are 4 main steps you must complete in order to upload a CSV file, or a spreadsheet saved as a CSV file, into a database:

1.      Add CSV to the Insert query

2.      Verify the format of the CSV column headings

3.      Create the Upload CSV logic

4.      Upload the CSV file

7.1.1.1. Add CSV to the Insert Query

In order to populate a table from a CSV file, you need to create and Insert query and add CSV to this Insert query.

From the Queries tab, click any application, and filter for the Insert [Table] query; e.g., Insert Stores.

              

Click the edit icon, then click the Edit link for Web Display.

              

-             

Highlight and copy all of the SQL statements.

              

Click the browser back button, and then click New SQL.

              

Select “CSV” as the Output Type, paste in the SQL statements that you copied, and click Save SQL.

              

Click Edit Attributes, then click Add Attribute, and then add an Application Attribute of “Upload/Download” to make this query visible in the Upload/Download tab.

              

              

              

              

Click back 3 times until you are back to the Query edit screen.  Click Add/Edit Params, for use in the next step.

              

7.1.1.2.  Verify Format of CSV Column Headings

The column headings in your spreadsheet or CSV file must match EXACTLY the values in the Query Parameters (Params) – UPPERCASE_NO_SPACES.  You must make the necessary changes before uploading.

Open your CSV file so you can edit the column headings if needed.

Refer back to the Query Parameters (Params), and note the values in the Name field.

              

Make the necessary changes and save the CSV file.

              

7.1.1.3. Create the Upload CSV Logic

Click the Upload/Download tab, then click the link Upload/Download, and click Add CSV Upload/Download.

Leave Source Query ID empty, in Destination Query ID, select “SQL Ice Database - Insert [Table]”; e.g., SQL Ice Database - Insert Stores, and give it a name such as “Upload Stores”, then click Web Display to save it.

              

 

7.1.1.4. Upload the CSV file

Click the Upload link.

Click Browse and find your CSV file, then click upload.

              

You should see "All records successful".

              

View that table to verify that your data was inserted correctly.

 

7.1.2.  CSV Download

Create a Select query for the table in the CSV SQL type, run the query and click the CSV button.

Then just click Open or Save in the File Download dialog box.

              


 

A.  Appendix

A.1.  Contact BIT

Business Integration Technology, Inc.

1308 Papin Street

St. Louis, MO 63103

info@BusinessIntegrationTechnology.com

Phone number is 314-635-6351

Fax number is 314-601-3092