RemoteExecuteSQL()

This function executes remote SQL query using ODBC data source. To work it needs a running AMODIT Cloud Agent. The idea is to use an agent (a Windows Service) to execute a querry on a remote computer, possibly behind a firewall, where a normal DB connection is not possible. This function writes the query to AMODIT database, then an agent gets the query by web service, runs it and returns result by web service. Finally AMODIT processes that result to fill in a form field.

Syntax

RemoteExecuteSQL("ODBC DSN", "SQL Query", ["Query Type"], ["FieldName"], ["ReturnScript"], ["AgentName"], ["FieldError"])

Function arguments

• DSN – (String) A valid ODBC connection string for an ODBC data source that must exist.
• query – (String) Actual SQL command. You can use: standard case fields (i.e. [CaseId], [CaseTitle], etc.), fields from main form provided in standard format (i.e. [DateFrom], [Description] etc.), fields from tables provided in specific format '[tableName.fieldName]’ (i.e. [Positions.Price] etc.) and for dictionary fields, when dictionary is imported from external source, its external identifier in format '[fieldName.id]’ (i.e. [Customer.id] etc.)
• Query Type – (String) [Optional] How to execute query and return result.
  „” – (empty) ExecuteScalar (returns a single result);
  „csv”, „json” – ExecuteReader (returns a table in either csv or json);
  „none” – ExecuteNonQuery (returns the number of affected rows).
• FieldName – (String) [Optional] Name of form field for query result.
• ReturnScript – (String) [Optional] AMODIT script to run after the query returns. „sqlresult” is replaced by result. Example: „[numer w SAP]=sqlresult; ForwardCase(\”archiwum\”, \”zamknięta\”); CloseCase();”
• AgentName – (String) [Optional] Name of agent to run the query. Leave it at „” if there is just one agent, otherwise it must be the same as agent name in configuration of that agent. This allows for having 2+ agents (for 2+ databases in different locations) and selecting the agent to run this query.
• FieldError – (String) [Optional] Optional form field for SQL errors. Errors are logged in systemlog anyway, but you may want to display error on the form or put it in a hidden field and later check in a rule, if that field is empty.

Return value

This function returns String.
Returns empty string.

Examples

Example 1

This will execute specified SQL command on specified ODBC data source.

RemoteExecuteSQL("Driver={ODBC Driver 18 for SQL Server};Server=myServerAddress;
Database=myDataBase;Uid=myUsername;Pwd=myPassword;",
"Insert into Invoice (CaseId,CaseTitle,CustomerId,Price,ProductId) 
values ([CaseId],[CaseTitle],[Customer.id],[Positions.Price],[Positions.Product.id])");

Example 2

This will execute specified SQL command on specified ODBC data source. It will then asynchronusly write the result to field „Rezultat” and execute the given script, substituting „sqlresult” for the result. The query will be executed only by agent „AMOD-SAP-Agent”. Any SQL errors will be written to form field „Error”, in addition to systemlog.

RemoteExecuteSQL("DSN=zebra", "SELECT Id FROM Invoice WHERE CaseId = [CaseId];", "" "Rezultat", 
"[numer w SAP]=sqlresult; ForwardCase(\"archiwum\", \"zamknięta\"); CloseCase();", "AMOD-SAP-Agent", "Error");

Example 3

Fields „Wojewodztwo” and „Wynik_csv” are set to query result, in this case „Mazowieckie”.

RemoteExecuteSQL("DSN=Amod_Dict", "SELECT WOJEWODZTWO FROM SL_WOJEWODZTWO WHERE ID_WOJ = [Wojew_id]", "", 
"Wojewodztwo", "[Wynik_csv] = \"sqlresult\";", "", "");

Example 4

This will write the contents of table SL_WOJEWODZTWO into field „Wynik_csv” as CSV. The first row consists of column names with data types. The separator ia a semicolon instead of comma. In this case the result is (there is more, but only 3 rows are shown):
ID_WOJ (nvarchar);WOJEWODZTWO (nvarchar);MSGID (nvarchar);DATA (datetime);PRZETWORZONE (char)
DSL;Dolnośląskie;;;T
K-P;Kujawsko-Pomorskie;;;T
LBL;Lubelskie;;;T

RemoteExecuteSQL("DSN=Amod_Dict", "SELECT * FROM SL_WOJEWODZTWO; ", "csv", "Wynik_csv", "", "", "");

Example 5

This will write the contents of table SL_WOJEWODZTWO into field „Wynik_csv” as JSON. In this case the result is (there is more, but only 2 items are shown) (it is condensed in this example, but normally it is better formatted):
[
{
„ID_WOJ” : „DSL”,
„WOJEWODZTWO” : „Dolnośląskie”,
„MSGID” : „”,
„DATA” : „”,
„PRZETWORZONE” : „T”
} ,
{
„ID_WOJ” : „K-P”,
„WOJEWODZTWO” : „Kujawsko-Pomorskie”,
„MSGID” : „”,
„DATA” : „”,
„PRZETWORZONE” : „T”
}
]

RemoteExecuteSQL("DSN=Amod_Dict", "SELECT * FROM SL_WOJEWODZTWO; ", "json", "Wynik_csv", "", "", "");

Example 6

Since release versions 240331(.71), 240630(.37) and 240930(.0) of AMODIT system it is possible to refer a few times to the same case form  field in SQL queries passed as argument to functions ExecuteSQL() and RemoteExecuteSQL().

RemoteExecuteSQL("SOME_CONN_STR", "SELECT * FROM some_table WHERE column1=[some_form_field] 
OR column2=[some_form_field]");