Execute SQL Instruction
Verb: executeSql
Executes a SQL instruction and returns info regarding the command's execution.
Syntax
executeSql --connection(String) --command(String) --results(ExecuteSqlResults) (Boolean)=isnull (String)=value (DataTable)=table (Numeric)=rows (Numeric)=columns
Inputs
| Script | Designer | Required | AcceptedTypes | Description |
|---|---|---|---|---|
| --connection | Connection string | Required | Text | Database string connection.
It only supports SQL Server connections. |
| --command | Instruction | Required | Text | SQL instruction that should be executed. |
| --results | Type of result | Required | ExecuteSqlResults | Type of result that should be returned. Options:
|
Outputs
| Script | Designer | AcceptedTypes | Description |
|---|---|---|---|
| isnull | Null | Boolean | Returns "Null" if the Database is empty. |
| value | Result | Text | Returns a value, in case the Type of result is defined as Scalar. |
| table | Data Table | Data Table | Returns the generated data table, in case the Type of resultis defined as Data Table. |
| rows | Rows | Number | Returns the number of affected rows in the data table, if any. |
| columns | Columns | Number | Returns the amount of affected coumns in the data table, if any. |
Example
The Execute SQL Instruction inserts a table in the Database, returning the table and the number of affected rows and columns.
defVar --name connectionString --type String --value "Data Source=127.0.0.1;Initial Catalog=test;User ID=root; Password=\"\";\r\n"
defVar --name sqlCommand --type String --value "INSERT INTO book (price,title,author,publishingcompany,pagesnumber,language) \n VALUES (59.99,\'Hamlet\',\'William Shakespeare\',\'New Horizon\',300,\'EN\');"
defVar --name returnedTable --type DataTable
defVar --name tableRows --type Numeric
defVar --name tableColumns --type Numeric
executeSql --connection "${connectionString}" --command "${sqlCommand}" --results "DataTable" returnedTable=table tableRows=rows tableColumns=columns
Remarks
This command is obsolete, use the MySQL Connection, ODBC Connection, Oracle Connection, SQLite Connection or Connect to SQL Server to connect to a database, and then use the Run SQL Command to execute an SQL instruction.