| Function | With the CallSQL-statement you can use SQL functions for GuiXT table variables. |
|
Examples More examples and explanations |
CallSQL "DELETE FROM V[items] WHERE remove = 'X' "
In the table variable V[items], all rows where column "remove" contains "X" are deleted. CallSQL customerinfo = _ "SELECT c.account, c.name, c.city, co.countryname " && _ "FROM V[customers] AS c LEFT OUTER JOIN V[countries] AS co " && _ "ON c.country = co.country" The specified columns from V[customers] are copied into the table variable V[customerinfo], and the matching country name "countryname" from the table variable V[countries] is added as well. |
| Format |
CallSQL
"SQL statement"
CallSQL varname = "SQL statement" Both notations execute the SQL statement, where GuiXT table variables are referenced as table names using the notation V[varname]. If a result variable is specified, the result of the statement is assigned to the result variable, which is usually also a table variable. If a structure variable is specified as the result variable, only the first row of the result set is read and assigned to the structure variable. A simple variable (not a structure or table) can only be specified in conjunction with "SELECT count(*) FROM ...". GuiXT uses SQLite as its SQL engine, whose syntax largely conforms to the SQL standard. Please note that for your own column names, such as "order", names reserved in SQL must be enclosed in square brackets. Example:
CallSQL selorders = "SELECT [order] FROM V[orders] WHERE selected = 'X' " With Q[ok] you can check whether the SQL statement was executed. Further information can then be found in the GuiXT system fields (see below). |
| Options | mode="createFromResult"
The result variable is automatically created with CreateTable using the column names of the SQL statement's result set. A separate CreateTable beforehand is therefore not required. mode="appendRows" The rows of the result set are appended to the specified table variable. mode="updateCells" The values of the result set replace the corresponding cell contents of the table variable. This lets you, for example, fill a single column of a table variable in a targeted way. mode="" The default mode, if not specified or left empty, is filling the result variable as described above. Here, the column names of the SQL result table are matched to the columns of the result variable by name. All other columns remain empty. |
| Special SQL Functions | All SQL functions available in SQLite, such as upper(), concat(), replace, ... can be used via CallSQL . A list of available functions can be found at https://sqlite.org/lang_corefunc.html. In addition, the following special GuiXT functions are available, which can be used in the SQL statement in the same way as the standard SQLite functions. A combination of SQLite functions with GuiXT functions is possible anywhere in the SQL statement. guixtValue(varname, format) The function has two parameters: the variable name and the desired format. The GuiXT variable with the specified name is evaluated and, if specified, converted into the SQL format:
... WHERE company = '&V[companyname]'
When directly inserting the variable value via '&V[...]', the character ' is not doubled within the string, which can result in an invalid SQL statement. Instead, use: ... WHERE company = guixtValue('companyname')This way, the variable value is not inserted as a literal, but is only passed to SQLite when the SQL statement is interpreted. guixtConvert(value, format) The function has two parameters: the value to be converted and the desired format. The following formats are possible:
The function can be called with two, three, or four parameters. The first parameter is the name of a "process" script; this is called by GuiXT in the same way as with the "process" keyword. 1-3 values are passed as parameters, and the return value of the process function is returned to SQL as the function value. Example: "SELECT vbeln,posnr,matnr..." Result: 3974 000010 TG12 1804 000010 MZ-FG-C990 1804 000020 EWMS4-20"SELECT vbeln,GuiXTProcess('no_leading_zeros', posnr) as posnr,matnr..." Result: 3974 10 TG12 1804 10 MZ-FG-C990 1804 20 EWMS4-20For each value in the "posnr" column, the GuiXT script "process_no_leading_zeros.txt" is called during SQL processing:
GuiXT
|
| System fields | V[_sqlrowcount] Number of rows returned V[_sqlchangecount] Number of rows changed V[_sqlerrortext] Error text in case of an error, e.g. incorrect SQL syntax. The text is also displayed in the Script Editor. V[_sqlstatement] The SQL statement (only in case of an error). |
| Tips & Tricks | |
| Component | GuiXT + Controls |