This document describes the use of the Bizweaver SQL Command tool.

This document is intended for developers of Bizweaver workflows.

The following pages contain additional information that may be helpful with the development of Bizweaver workflows using the SQLCommand tool.

• Global Connections

Global connections required to access a database(s) that is needed must be created before this tool can be used.

SQL Server versions 2008 and higher are supported as well as SAP HANA databases. The SQL connection setup process is described in the SQL Connections section.

1. Connection Sets a connection to a database is selected from the drop-down list.
   1. The SQL Command tool uses a previously defined SQL/HANA connection to connect to the desired database.
2. Command Type selected from the drop-down list.
   1. See the “SQL Command Types” section below for more information.
3. SQL Command Enter a SQL statement in this area.

Four SQL Server command types are supported:

• Reader
• Non-Query
• Post Execution Query
• Stored Procedure

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

1. This command is used for SELECT statements only.
   1. Variables representing values from previous steps can be used in the statement as needed. Press <F4> for a list of available variables.
   2. 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.

1. This command is used for UPDATE, INSERT, DELETE and STORED PROCEDURES with or without Parameters statements.
   1. Variables representing values from previous steps can be used in the statement as needed. Press <F4> for a list of available variables.
   2. Keep in mind that these statements will affect your data immediately. Use them with care.
   3. Non-Query cannot be test run

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.

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:

 EXEC Stored_Proc_Name parm1, parm2 

Variables representing values from previous steps can be used in the statement as needed. Press <F4> for a list of available variables.

1. 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.
2. 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.
3. 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:

'WorkFlow(GetData).Get({FIELD_ItemCode})'

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.

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.

Test view

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 Run link to execute the modified statement. This may be done as many times as needed.

In this example we can enter a valid value where the variable will be used to test the statement.

WHERE t0.CardCode = 'WorkFlow(Variables).Get(CardCode)'

• Strip HTML Tags From Input String