User Tools

Site Tools


Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
wiki:guides:authors:using_the_sql_wizard [2015/01/13 09:37]
Zoltan [The Write SQL Wizard]
wiki:guides:authors:using_the_sql_wizard [2016/03/26 11:51] (current)
Brian Collins
Line 1: Line 1:
 ====== Using the SQL Wizard ====== ====== Using the SQL Wizard ======
  
-After a new EASAP is created ​and the **New Application Wizard** ​is opened, on the second screen you can choose ​to use the SQL Wizard (pictured below).+After a new EASAP is created,  ​the **New Application Wizard** ​offers ​the **[[wiki:​guides:​authors:​using_the_sql_wizard#​Read SQL Wizard|Read SQL Wizard]]** and **[[wiki:​guides:​authors:​using_the_sql_wizard#​Write SQL Wizard|Write SQL Wizard]]** ​to aid the **Author** in creating an EASAP with SQL capability.
  
-It is divided into two separate modules, [[wiki:​guides:​authors:​using_the_sql_wizard#​Read SQL Wizard|Read ​SQL Wizard]] and [[wiki:​guides:​authors:​using_the_sql_wizard#​Write SQL Wizard|Write SQL Wizard]]. They allow you to create ​**Database** actions populated with children ​**Read** and **Write** actions respectively.+With these **SQL Wizards** the **Author** can automate the creation of **DATABASE** ACTION'​s and their respective Child **READ** and **WRITE** ACTION'​s.
  
-{{ :​wiki:​guides:​authors:​use_read_sql_wizard.jpg?​nolink |}} 
  
 ----- -----
-**Note: Please note that at this point, the SQL Wizard is still in active development and as a result, it has limited functionality ​for nowFor this reason, the rest of this page is going to be a step-by-step guide on how to produce expected results.**+**Note:** Please note that at this point, the **SQL Wizard** is still in active development and as a result, it presently ​has limited functionality. ​The rest of this page is a detailed ​step-by-step guide that will produce expected results.
 ----- -----
-**Note: ​Please note that at this point, the SQL Wizard ​is only capable to deal with operations ​involving ​a single table only.**+**Note:** Currently, the **SQL Wizard** only allows ​operations ​on a single table.
 ----- -----
  
 ===== The Read SQL Wizard ===== ===== The Read SQL Wizard =====
  
-After selecting the **Read SQL Wizard** and pressing **Finish**, the **New Application Wizard**+{{ :​wiki:​guides:​authors:​use_read_sql_wizard2c.png?​nolink |}}
  
-closes and the **Read SQL Wizard** opens.+Select the **Read SQL Wizard** and press **Finish**. The **New Application Wizard** ​closes and the **Read SQL Wizard** opens.
  
-The first screen called ​**Database Selection** ​lists all the databases available for use with EASAThe list will only contain databases that are set up with EASA on the web pages.+Select a database from those already configured on the **Database Selection** ​window**StockData** comes built-in so we use it in this example.
  
 ----- -----
-**Note: ​This can be done on the admin pages, thus you will need to follow the appropriate steps detailed in the [[wiki:​guides:​admins:​configuring_connections_to_external_databases|Administrator’s Pages]]. If you do not have acces to the admin pages and the list is missing the desired database, you will have to contact one of your EASA Administrators to connect the database to EASA.**+**Note:** To configure a database see [[wiki:​guides:​admins:​configuring_connections_to_external_databases]]. If you do not have access ​to the admin pages and the list is missing the desired database, you will have to contact one of your EASA Administrators to connect the database to EASA.
 ----- -----
  
-{{ :​wiki:​guides:​authors:​sql_wizard_database_select.jpg?nolink |}}+{{ :​wiki:​guides:​authors:​sql_wizard_database_select2b.png?nolink |}}
  
-After selecting the database and pressing the **Next** ​button, the **Field Selection** screen (pictured below) allows you to select the fields you want the wizard to work with. To do this, you must first select **Disable Relation Restriction**. Click **Next** again to proceed to the next screen.+Press  ​**Next**.
  
-{{ :​wiki:​guides:​authors:​sql_wizard_field_select.jpg?nolink |}}+In the **Field Selection** window select **Disable Relation Restriction**
  
-On the next screen (called **Add Filters**, pictured below) you can construct your query by adding rows of conditionsEach row has four columns:+Now select ​the fields to be used in a SQL query. ​
  
-  ​* Field +Click **Next**. 
-  * Operartor + 
-  * Value +{{ :​wiki:​guides:​authors:​sql_wizard_field_select2d.png?​nolink |}} 
-  * And/Or+ 
 +In the **Add Filters** window, below, construct a query by adding rows of conditions. Each row has four columns: 
 + 
 +  * **Field** 
 +  * **Operator** 
 +  ​* **Value** 
 +  ​* **And/Or**
  
 The **Field** column lists all items chosen on the **Field Selection** screen in the previous step. The **Operator** column lists options on how the field parameter should relate to the value parameter. The options are: The **Field** column lists all items chosen on the **Field Selection** screen in the previous step. The **Operator** column lists options on how the field parameter should relate to the value parameter. The options are:
  
-  * Greater than > +  * **>** -> Greater than  
-  * Lesser than +  * **<** -> Less than 
-  * Greater than or equal to >= +  * **> =** -> Equal or greater than 
-  * Lesser than or equal to <= +  * **< =** -> Equal or less than 
-  * Not equal to <> +  * **<>** -> Not equal 
-  * LIKE +  ​* **LIKE** 
-  * NOT +  ​* **NOT** 
-  * IN+  ​* **IN**
  
-The **Value** parameter is an input box instead of a drop-down list. You may enter the desired value in the data type that matches the field parameter. The final column called ​**And/Or** is only available if there are more than one row and is used to connect the rows with logical **AND** or **OR** ​parameters.+The **Value** parameter is an input box instead of a drop-down list. You may enter the desired value in the data type that matches the field parameter. The  **And/​Or** ​column ​is available if there is more than one filter ​and uses logical **AND** or **OR** ​on the filters to create the SQL query logic.
  
-{{ :​wiki:​guides:​authors:​sql_wizard_filters.jpg?nolink |}}+{{ :​wiki:​guides:​authors:​sql_wizard_filter2a.png?nolink |}}
  
 ----- -----
-**Note: As mentioned ​before, the SQL Wizard currently ​only support creating ​operations ​over a single table. Even though ​you can select ​multiple tables and carry out the rest of the wizardyou will receive ​an error message ​regarding ​the query created. ​There is a step in the wizard ​between Add Filters and Test Query called Join Selection if you selected ​multiple tables. It gives you a choice of methods for joining the tables, but will produce the same error. ​This feature ​is for future releases of EASA.**+**Note:** As mentioned ​above, the **SQL Wizard** currently ​supports ​operations ​on a single table. Even though multiple ​ tables ​may be initially selected ​and further steps in the **SQL Wizard** taken, an error message ​will be returned after the query is created. ​Additionally,​ the step in the **Wizard** ​between ​'Add Filters' ​and 'Test Query' ​called ​'Join Selection' ​if multiple tables ​were selected, but will produce the same error. ​Joining tables in the **SQL Wizard**  ​is a feature ​for future releases of EASA.
 ----- -----
  
-The final screen is called ​**Test Query** ​and allows you to preview ​the query results, the created query itself and make changes to it if desired to check the updated ​results. ​If you do change the query on this screen, you can update the results view by pressing ​**Test Updated Query.** Press **Finish** when you are happy with the results ​and the EASAP Builder tool opens with the newly created [[wiki:​guides:​authors:​objects:​database|Database]] and [[wiki:​guides:​authors:​objects:​read|Read]] objects.+The final **Test Query** ​window shows the query results. ​The query can be modified and **Test Updated Query** ​will return ​the new results.
  
-{{ :​wiki:​guides:​authors:​sql_wizard_tree.jpg?nolink |}}+{{ :​wiki:​guides:​authors:​sql_wizard_test_query2a.png?nolink |}} 
 + 
 +Press **Finish** when the query is correct and the EASAP **Builder** ​ opens with the newly created [[wiki:​guides:​authors:​objects:​database]] and [[wiki:​guides:​authors:​objects:​read]] objects. 
 + 
 +{{ :​wiki:​guides:​authors:​sql_wizard_tree2a.png?nolink |}}
  
 ===== The Write SQL Wizard ===== ===== The Write SQL Wizard =====
Line 65: Line 74:
 Using the **Write SQL Wizard** is very similar to using the **Read SQL Wizard**. The first screen, **Database Selection** is the same for both, listing all the databases available for use with EASA. Once again, the list will only contain databases that are set up with EASA on the web pages. Using the **Write SQL Wizard** is very similar to using the **Read SQL Wizard**. The first screen, **Database Selection** is the same for both, listing all the databases available for use with EASA. Once again, the list will only contain databases that are set up with EASA on the web pages.
  
-{{ :​wiki:​guides:​authors:​sql_wizard_select_database.jpg?nolink |}}+{{ :​wiki:​guides:​authors:​sql_wizard_write2a.png?nolink |}} 
  
 On the next screen you can choose the desired **Write Method**. The available options are: On the next screen you can choose the desired **Write Method**. The available options are:
  
-  * UPDATE +  ​* **UPDATE** 
-  * INSERT +  ​* **INSERT** 
-  * DELETE+  ​* **DELETE**
  
-Depending on your choice, the **Add Filters** screen will either be presented to you in a later step (UPDATE and DELETE) or not (INSERT).+In this example we choose ​**UPDATE**.
  
-{{ :​wiki:​guides:​authors:​sql_wizard_statement_type.jpg?nolink |}}+The **Field Selection** window appears, below. Choose **Disable Relation Restriction** before choosing fields
  
-The next screen is once again the **Field Selection** view and it works the same as in the **Read SQL Wizard**You have to choose **Disable Relation Restriction** before choosing the desired fields.+We will update ​the '​Industry'​ value for '​GOOG'​. 
 +{{ :​wiki:​guides:​authors:​sql_wizard_update2a.png?nolink |}}
  
-{{ :​wiki:​guides:​authors:​sql_wizard_select_fields.jpg?​nolink |}} 
  
-If you selected UPDATE or DELETE on the **Write Method** screen, the **Add Filters** screen appearsOnce again, you can construct your database action by adding rows of conditionsEach row has four columns:+The **Value** parameter is an input box instead of a drop-down listWe enter EXCHANGE.COMPANY=GOOG.
  
-  * Field +{{ :wiki:​guides:​authors:​sql_wizard_add_filters2c.png?nolink |}}
-  * Operartor +
-  * Value +
-  * And/Or +
- +
-The **Field** column lists all items chosen on the **Field Selection** screen in the previous step. The **Operator** column lists options on how the field parameter should relate to the value parameter. The options are: +
- +
-  * Greater than > +
-  * Lesser than < +
-  * Greater than or equal to >= +
-  * Lesser than or equal to <= +
-  * Not equal to <> +
-  * LIKE +
-  * NOT +
-  * IN +
- +
-The **Value** parameter is an input box instead of a drop-down list. You may enter the desired value in the data type that matches the field parameter.+
  
 The final column called **And/Or** is only available if there are more than one row and is used to connect the rows with logical **AND** or **OR** parameters. The final column called **And/Or** is only available if there are more than one row and is used to connect the rows with logical **AND** or **OR** parameters.
  
-{{ :​wiki:​guides:​authors:​sql_wizard_add_filtesr.jpg?​nolink |}} +Click **Next** and the **Statement Preview** ​window appears. The WHERE clause has been filled out for us but we can edit the SET part of the query. Here we delete '​EXCHANGE.COMPANY=?,' ​and edit the second SET to be: EXCHANGE.INDUSTRY='​Search Engine'​
- +
-The final screen is called ​**Statement Preview** and allows you to preview ​the constructed statement line by line.+
  
-{{ :​wiki:​guides:​authors:​sql_wizard_statement_preview.jpg?nolink |}}+Click **Finish** and the EASAP **Builder** opens with the newly created [[wiki:guides:​authors:​objects:​database]] and [[wiki:​guides:​authors:​objects:​write]] objects. The Text: parameter containing the complete query is also shown.
  
-When the statement is as desired, press **Finish** and the EASAP Builder opens.+{{ :​wiki:​guides:​authors:​sql_wizard_tree22a.png?nolink |}}
  
-{{ :​wiki:​guides:​authors:​sql_wizard_tree2.jpg?​nolink |}}+----
  
-The result of both wizards are created as an event processing structure with an [[wiki:​guides:​authors:​objects:​action_group|Action Group]] containing the objects and parameters recreating ​the database actions specified in the wizard.+Both **Read** and **Write Wizards** create in the **Builder**  ​an **[[wiki:​guides:​authors:​objects:​event processing|EVENT PROCESSING]]** ​structure with an **[[wiki:​guides:​authors:​objects:​action_group]]** containing the **[[wiki:​guides:​authors:​objects:database action]]**, **[[wiki:​guides:​authors:​objects:​database]]** ​and the SQL containing **[[wiki:​guides:​authors:​objects:​write]]** or **[[wiki:​guides:​authors:​objects:​read]]** objects.

Page Tools