Differences

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

--- bw:sql_command_tool [2019/12/09 13:31]
akoehler [Passing Parameters using Stored Procedure (SQL Server only)]
+++ bw:sql_command_tool [2022/08/02 10:35] (current)
akoehler [SQL System Variables]
@@ Line 16: / Line 16: @@
 The following pages contain additional information that may be helpful with the development of Bizweaver workflows using the SQLCommand tool.
-  * [[bw:global_connections|Global Connections]]
+  * **[[bw:global_connections|Global Connections]]**
 ====== Before You Begin ======
@@ Line 45: / Line 45: @@
   * **//[[bw:sql_command_tool#Non-Query (SQL Server only)|Non-Query]]//**
   * **//[[bw:sql_command_tool#Post Execution Query (SQL Server only)|Post Execution Query]]//**
-  * **//[[bw:sql_command_tool#Stored Procedure (SQL Server only)|Post Execution Query]]//**
+  * **//[[bw:sql_command_tool#Stored Procedure (SQL Server only)|Stored Procedure]]//**
 ===== 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 image shows the same SELECT statement as illustrated above, but with a variable in the WHERE clause.
 {{  :bw:sqlimage2.png?411x72  }}
+<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, and DELETE statements.
+  - This command is used for **UPDATE, INSERT, DELETE and STORED PROCEDURES** with or without Parameters 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**.
+    - Non-Query cannot be test run
 <WRAP center round important>
-**For SAP Business One Users**: Updates to standard SAP Business One tables should not be done directly using insert, update, or delete. These updates should be done using the **//[[bw:sap_business_one_integration_for_sql_server_databases#Mapper Tool|Mapper]]//** & **//[[bw:sap_business_one_integration_for_sql_server_databases#SBO Update|SBO Updater]]//** tools.
+**For SAP Business One Users**: Updates to standard SAP Business One tables should not be done directly using insert, update, or delete. These updates should be done using the **//[[bw:sap_business_one_integration_using_di#Mapper Tool|Mapper]]//** & **//[[bw:sap_business_one_integration_using_di#SBO Update|SBO Updater]]//** tools.
 </WRAP>
@@ Line 86: / Line 91: @@
 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.
-==== Stored Procedures (SQL Server only) ====
+====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:
@@ Line 132: / Line 137: @@
 </WRAP>
-======SQL Tools======
+====== 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======
   * [[bw:sql tools|Strip HTML Tags From Input String]]

Wiki

User Tools

Site Tools

Differences

Page Tools