Differences

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

--- bw2:sql_command_tool [2020/12/15 12:18]
akoehler [Related Documents]
+++ bw2:sql_command_tool [2023/12/29 14:09] (current)
wgates [Reader (SQL Server and HANA)]
@@ Line 1: / Line 1: @@
-====== Introduction (SQLCommand Tool) ======
+====== Introduction (SQL/HANA Tool) ======
 This document describes the use of the Bizweaver SQL Command tool.
@@ Line 14: / Line 14: @@
 ====== Related Documents ======
-The following pages contain additional information that may be helpful with the development of Bizweaver workflows using the SQLCommand tool.
+The following pages contain additional information that may help develop Bizweaver workflows using the SQLCommand tool.
   * **[[bw2:global_connections|Global Connections]]**
@@ Line 28: / Line 28: @@
 ====== Basic Tool Setup ======
-{{  :bw:sqlimage1.png?384x184  }}
+{{ :bw2:bizweaversqltool1.png |}}
   - **Connection** Sets a connection to a database is selected from the drop-down list.
@@ Line 51: / Line 51: @@
 Only the **Reader** function is supported for HANA databases at this time.
-Standard SQL Server or HANA syntax is used for the commands. The basic syntax of the SQL statement is validated when the [**OK**] button is clicked. If the statement is not valid an error message is displayed but the step is saved as-is
+Standard SQL Server or HANA syntax is used for the commands. The SQL statement's basic syntax is validated when the [**OK**] button is clicked. If the statement is not valid, an error message is displayed, but the step is saved as-is.
 ===== Reader (SQL Server and HANA) =====
@@ Line 59: / Line 59: @@
     - When variables are used, they are subject to standard syntax rules regarding text vs. numeric values. This means that text variables MUST be enclosed in single quotes.
-The following image shows same SELECT statement as illustrated above, but with a variable in the WHERE clause.
+The following shows the same SELECT statement as illustrated above but with a variable in the WHERE clause.
-{{  :bw:sqlimage2.png?411x72  }}
+<code sql>
+SELECT
+	CardCode,
+	CardName
+FROM ocrd
+WHERE CardCode = 'WorkFlow(Variables).Get(CardType)'
+</code>
+<WRAP center round important>
+When saving a workflow or opening the SQL/HANA tool or the workflow, the reader command type will validate the written query. This executes the query against the connected database. **There is the ability to have Non-query commands written using the Reader command type. This will execute the statement against the database. If a Create, Update, Insert, Truncate or Delete (any SQQL/HANA statement that affects a change to any data) is written, the command will execute and be run against the connected database if the workflow is saved or the tool or workflow is opened**
+</WRAP>
 ===== Non-Query (SQL Server only) =====
-  - This command is used for **UPDATE, INSERT, DELETE and STORED PROCEDURES** with or without Parameters statements.
+  - This command is used for **UPDATE, INSERT, DELETE, and STORED PROCEDURES** with or without Parameter statements.
     - Variables representing values from previous steps can be used in the statement as needed. Press <F4> for a list of available variables.
     - Keep in mind that these statements will affect your data immediately. **Use them with care**.
@@ Line 77: / Line 87: @@
 <WRAP center round info>
-This option is included for compatibility with older versions of Bizweaver, and is no longer used. The same thing can be accomplished using a “Non-query” operation as illustrated below. The SQL statement will be the same.
+This option is included for compatibility with older versions of Bizweaver and is no longer used. The same thing can be accomplished using a “Non-query” operation, as illustrated below. The SQL statement will be the same.
 </WRAP>
-{{  :bw:sqlimage3.png?384x288  }}
+{{ :bw2:bizweaversqltool2.png |}}
 =====Stored Procedure (SQL Server only) =====
-This command can be used to execute a SQL Stored Procedure that does not require parameters. In this case the “exec” statement is not used. All that is required is the name of the procedure. In most cases, it is probably easier to simply always use the Non-Query option to avoid confusion. Use of the Non-Query option is described above.
+This command can be used to execute a SQL Stored Procedure that does not require parameters. In this case, the “exec” statement is not used. All that is required is the name of the procedure. In most cases, it is probably easier to simply always use the Non-Query option to avoid confusion. The use of the Non-Query option is described above.
 ====Passing Parameters using  Stored Procedures (SQL Server only) ====
-While there is a “Stored Procedure” command in the SQL Command tool drop down menu, its use is currently limited as it does not support the use of input parameters. To address this, the **Non-Query** command can also be used to execute stored procedures that include parameters for execution. In this case the “exec” statement must be included in the command. The parameters are passed using standard SQL Server syntax. For example, assume a stored procedure that includes two parameters. The SQL statement would be:
+While there is a “Stored Procedure” command in the SQL Command tool drop-down menu, its use is currently limited as it does not support the use of input parameters. To address this, the **Non-Query** command can also be used to execute stored procedures that include parameters for execution. In this case, the “exec” statement must be included in the command. The parameters are passed using standard SQL Server syntax. For example, assume a stored procedure that includes two parameters. The SQL statement would be:
 <code sql> exec Stored_Proc_Name parm1, parm2 </code>
@@ Line 99: / Line 109: @@
   - Any “calculated” value in a SQL statement must have an alias assigned for it to be available in Bizweaver. This applies to numeric calculations, concatenated text values, CASE values, and any other instance where the value is not a column name in a table. Standard syntax rules apply for assigning alias names.
-  - When using the {RetKey} variable in a SQL Command statement, it should always be enclosed in single quotes. If this is not done the validation process will raise an error.
+  - When using the {RetKey} variable in a SQL Command statement, it should always be enclosed in single quotes. If this is not done, the validation process will raise an error.
-  - When variables are used in a SQL statement, the normal rules regarding quotes apply. This means that if the value represented by the variable is a string, the variable in the statement should be enclosed in single quotes as well. For example, the value ItemCode is selected in a Reader step named “GetData”. When this value is used in a subsequent step the associated variable is enclosed in single quotes as shown here:
+  - When variables are used in a SQL statement, the normal rules regarding quotes apply. This means that if the value represented by the variable is a string, the variable in the statement should be enclosed in single quotes as well. For example, the value ItemCode is selected in a Reader step named “GetData”. When this value is used in a subsequent step, the associated variable is enclosed in single quotes as shown here:
  <code>'WorkFlow(GetData).Get({FIELD_ItemCode})'</code>
 ====== SQL Test Run Option ======
-Bizweaver version 2.3 introduces the **Test Run** option in the SQLCommand tool.  This option allows you to test SQL functions without running through the entire workflow.  It also allows you to test SQL steps where the input involves variables from previous steps.  The example here uses the following SQL statement.
+Bizweaver version 2.3 introduces the **Execute** option in the SQLCommand tool.  This option allows you to test SQL functions without running through the entire workflow.  It also allows you to test SQL steps where the input involves variables from previous steps.  The example here uses the following SQL statement.
-{{ :bw:sqlcommandtooltestrunstep1.png?500 |}}
+{{ :bw2:bizweaversqltool3.png |}}
-To invoke this option click on the "Test Run" link in the lower-right corner of the tool window.  The display changes to the test view.
+To invoke this option, click on the "Execute" link in the lower-right corner of the tool window.  The display changes to the test view.
-{{ :bw:testrunstep2.png?nolink |}}
+{{ :bw2:bizweaversqltool4.png |}}
 Test view
-{{ :bw:sqlcommandtooltestrunstep3.png?nolink&500 |}}
+{{ :bw2:bizweaversqltool5.png |}}
-The [Result] tab displays the results of the SQL statement.  The statement can be modified in this tab to add/change the statement as needed for testing.  This includes adding/changing variable values.  Click the green <color #22b14c>Run</color> link to execute the modified statement.  This may be done as many times as needed.
+The [Result] tab displays the results of the SQL statement.  The statement can be modified in this tab, but selecting Execute again will revert all changes to the original query.
-{{ :bw:runstep4.png?nolink |}}
-In this example we can enter a valid value where the variable will be used to test the statement.
 <code sql>
@@ Line 128: / Line 134: @@
 <WRAP center round important 60%>
-Changes in the [Result] tab **DO NOT** automatically carry back to the [SQL] tab.  If you make changes in the [Result] tab that need to be in the "real" SQL statement, you must add them manually. Once the test is modified you should copy the code and then exit the test view and past them in the SQL section for best results.
+Changes in the [Result] tab **DO NOT** automatically carry back to the [SQL] tab.  If you make changes in the [Result] tab that needs to be in the "real" SQL statement, you must add them manually. Once the test is modified, you should copy the code and then exit the test view and past them in the SQL section for the best results.
-Click the green <color #22b14c>Run</color> link to execute tests.  If you click the "Test Run" link again the results will revert to the statement in the [SQL] tab.
 </WRAP>
+====== SQL System Variables ======
+^ Name           ^ Description         ^
+| CommandType       | The command type chosen in the SQL tool ex. Reader, Non-Query      |
+| DataSourceFields |The fields pulled from the data source will only show up when executing the query in the SQL tool and not during a test run|
+| DataSourceResultsTable         | The results from executing the query in the workflow. Will only show up when executing the query in the SQL tool and not during the test run      |
+| QueryString         | The current query that is being executed     |
+| SQLCommand        | Used for an older version of Bizweaver no longer returns a value    |
+| SQLResultsCount         | The total amount of records that were retrieved during a query       |
+|StepMessage       | During a failure the reason for failure will be populated here     |
+| StepStatus        | True or False     |
 ======SQL Tips======
-  * [[bw2:sql tools|Strip HTML Tags From Input String]]
+  * **[[bw2:sql tools|Strip HTML Tags From Input String]]**

Wiki

User Tools

Site Tools

Differences

Page Tools