SQL Command

SQL — Execute an SQL query.

Synopsis

SQL CONNECT data_source_name

SQL DISCONNECT data_source_name

SQL ACTION data_source_name COMMAND command

SQL ACTION data_source_name INSERT table_name RECORD record_name

SQL ACTION data_source_name UPDATE table_name RECORD record_name

SQL QUERY data_source_name COMMAND command DATAMODEL data_model_name

SQL QUERY data_source_name COMMAND command TO TEXT text_name

Introduction

The SQL command executes an SQL query on a relational database. There are three types of queries:

CONNECT/DISCONNECT: This SQL statements opens/closes the data source to a data provider.
ACTION: This is non-query type of SQL statement which typically is an UPDATE or an INSERT command, whereby the database will be modified and the returning value will be the number of rows affected. The number of rows affected can be retrieved by calling the built-in function SQLRETURN(). As a shortcut/typing saving measure for inserting or updating a complete record/row variation, subcommands INSERT and UPDATE are available expanding record details.
QUERY: This is typically a SELECT statement, whereby a data model will be returned containing the selected values.

All SQL commands will return a status. As soon as something goes wrong the status will have a value other than 0. The status can be retrieved by calling the built-in function SQLSTATUS().

Arguments

`data_source_name`	The name of the data source as defined in the development environment.
`command`	A valid SQL command. The external column names of the database can be referenced directly, or the external column names, as linked to the data dictionary, can be referenced using a reference prefix D-. ie D-datadictionary_name. Likewise, external table names can be referenced using a reference prefix T-.
`data_model_name`	The name of a valid and existing data model object.
`text_name`	The name of a valid and existing text object. All the returning rows will be dumped at once to the text object, so it does not need a FOREACH and data model to retrieve all data.
`table_name`	The name of a valid and existing table object. The external name of the table will be used in the data provider.
`record_name`	The name of a valid and existing record object. All data dictionaries in the record will be expanded to meet the requirements of a valid sql statement.

Example

. . .
. . .
SQL ACTION Order COMMAND "update Cust set Name = 'Johnson' where Cust_Id=" L-Cust_Id
. . .
SQL ACTION Order COMMAND "update Cust set " EXPANDUPDATE(Cust) " where Cust_Id=" L-Cust_Id
. . .
SQL ACTION Order COMMAND "insert into " T-Country " values ('61',Australia')"
. . .
SQL ACTION Order COMMAND "insert into " T-Country " values (" EXPANDINSERT(Country) ")"
. . .
SQL QUERY Order COMMAND "select * from Cust" DATAMODEL Cust
. . .
SQL QUERY Order COMMAND "select " D-Cust_Id  " from " T-Cust DATAMODEL Cust
. . . . . .
SQL QUERY Order COMMAND "select * from Cust" TO TEXT AllCust 
. . .
. . .

Related Commands

DATAMODEL FOREACH TRANSACTION

ProLinga