# rawSQL (plugins.rawSQL) ## Overview The `rawSQL` plugin allows developers to execute raw SQL commands and manage advanced database interactions within Servoy. It is intended for scenarios where built-in APIs are insufficient and should be used cautiously to avoid data integrity or performance issues. This plugin supports SQL execution, stored procedure handling, cache management, and data change notifications. Developers can use `executeSQL` to run custom SQL queries or updates, and `executeStoredProcedure` to call stored procedures with input and output arguments. The `flushAllClientsCache` function ensures that database changes are propagated across clients, while `notifyDataChange` informs clients of record updates or deletions based on primary keys. Additionally, `getException` provides detailed error information for debugging failed operations. Careful usage of this plugin enables powerful database operations while maintaining system stability. ## Properties | Property | Description | | --------------------------------------- | -------------------------------------------------------------------------- | | `servoy.rawSQL.allowClientCacheFlushes` | In case of performance problem you might want to disable this (true/false) | ## Methods Summarized | Type | Name | Summary | | ---------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------- | | [Boolean](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/boolean) | [executeSQL(serverName, sql)](#executesql-servername-sql) | Execute any SQL, returns true if successful. | | [Boolean](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/boolean) | [executeSQL(serverName, sql, sql\_args)](#executesql-servername-sql-sql_args) | Execute any SQL, returns true if successful. | | [Promise](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/promise) | [executeSQLAsync(serverName, sql)](#executesqlasync-servername-sql) | Execute any SQL asynchronously. | | [Promise](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/promise) | [executeSQLAsync(serverName, sql, sql\_args)](#executesqlasync-servername-sql-sql_args) | Execute any SQL asynchronously with prepared statement parameters. | | [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) | [executeStoredProcedure(serverName, procedureDeclaration, arguments, maxNumberOfRowsToRetrieve)](#executestoredprocedure-servername-proceduredeclaration-arguments-maxnumberofrowstoretrieve) | Execute a stored procedure, return all created result sets. | | [JSDataSet](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jsdataset) | [executeStoredProcedure(serverName, procedureDeclaration, arguments, inOutDirectionality, maxNumberOfRowsToRetrieve)](#executestoredprocedure-servername-proceduredeclaration-arguments-inoutdirectionality-maxnumberofrowstoretrieve) | Execute a stored procedure. | | [Promise](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/promise) | [executeStoredProcedureAsync(serverName, procDecl, args, maxRows)](#executestoredprocedureasync-servername-procdecl-args-maxrows) | Execute a stored procedure asynchronously without explicit direction types. | | [Promise](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/promise) | [executeStoredProcedureAsync(serverName, procDecl, args, inOutTypes, maxRows)](#executestoredprocedureasync-servername-procdecl-args-inouttypes-maxrows) | Execute a stored procedure asynchronously with specified parameter directions and types. | | [Boolean](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/boolean) | [flushAllClientsCache(serverName, tableName)](#flushallclientscache-servername-tablename) | Flush cached database data. | | [Exception](https://github.com/Servoy/gitbook/blob/master/reference/servoycore/dev-api/exception.md) | [getException()](#getexception) | If the result from a function was false, it will return the exception object. | | [Boolean](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/boolean) | [notifyDataChange(serverName, tableName, pksDataset, action)](#notifydatachange-servername-tablename-pksdataset-action) | Notify clients about changes in records, based on pk(s). | ## Methods Detailed ### executeSQL(serverName, sql) Execute any SQL, returns true if successful. **Parameters** * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **serverName** the name of the server * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **sql** the sql query to execute **Returns:** [Boolean](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/boolean) True if the SQL execution was successful; false otherwise. **Sample** ```js /*************************************************************************** WARNING! You can cause data loss or serious data integrity compromises! You should have a THOROUGH understanding of both SQL and your backend database (and other interfaces that may use that backend) BEFORE YOU USE ANY OF THESE COMMANDS. You should also READ THE DOCUMENTATION BEFORE USING ANY OF THESE COMMANDS ****************************************************************************/ var country = 'NL' var done = plugins.rawSQL.executeSQL("example_data","update employees set country = ?", [country]) if (done) { //flush is required when changes are made in db plugins.rawSQL.flushAllClientsCache("example_data","employees") } else { var msg = plugins.rawSQL.getException().getMessage(); //see exception node for more info about the exception obj plugins.dialogs.showErrorDialog('Error', 'SQL exception: '+msg, 'Ok') } // Note that when this function is used to create a new table in the database, this table will only be seen by // the Servoy Application Server when the table name starts with 'temp_', otherwise a server restart is needed. ``` ### executeSQL(serverName, sql, sql\_args) Execute any SQL, returns true if successful. **Parameters** * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **serverName** the name of the server * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **sql** the sql query to execute * [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) **sql\_args** the arguments for the query **Returns:** [Boolean](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/boolean) True if the SQL execution was successful; false otherwise. **Sample** ```js /*************************************************************************** WARNING! You can cause data loss or serious data integrity compromises! You should have a THOROUGH understanding of both SQL and your backend database (and other interfaces that may use that backend) BEFORE YOU USE ANY OF THESE COMMANDS. You should also READ THE DOCUMENTATION BEFORE USING ANY OF THESE COMMANDS ****************************************************************************/ var country = 'NL' var done = plugins.rawSQL.executeSQL("example_data","employees","update employees set country = ?", [country]) if (done) { //flush is required when changes are made in db plugins.rawSQL.flushAllClientsCache("example_data","employees") } else { var msg = plugins.rawSQL.getException().getMessage(); //see exception node for more info about the exception obj plugins.dialogs.showErrorDialog('Error', 'SQL exception: '+msg, 'Ok') } // Note that when this function is used to create a new table in the database, this table will only be seen by // the Servoy Application Server when the table name starts with 'temp_', otherwise a server restart is needed. ``` ### executeSQLAsync(serverName, sql) Execute any SQL asynchronously. Returns a [Promise](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/promise) object that resolves to a [Boolean](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/boolean) indicating whether the SQL executed successfully, or rejects with an error message if it fails. Example using the \example\_data.employees\ datasource: ```js plugins.rawSQL.executeSQLAsync( "example_data", "UPDATE employees SET lastname = ? WHERE employeeid = ?", ["DE", 100] ) .then(function(success) { if (success) { application.output("Lastname updated successfully"); } else { application.output("Update returned false: " + plugins.rawSQL.getException().getMessage()); } }) .catch(function(errMsg) { application.output("Async SQL failed: " + errMsg); }); ``` **Parameters** * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **serverName** the logical DB server name (e.g. `example_data`) * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **sql** the SQL statement to execute **Returns:** [Promise](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/promise) a [Promise](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/promise) resolving to `true` on success or `false` on a non-exceptional failure ### executeSQLAsync(serverName, sql, sql\_args) Execute any SQL asynchronously with prepared statement parameters. Returns a [Promise](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/promise) object that resolves to a [Boolean](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/boolean) indicating success, or rejects with an error message if an exception occurs. Example inserting a new employee: ```js plugins.rawSQL.executeSQLAsync( "example_data", "UPDATE employees SET lastname = ?, firstname = ? WHERE employeeid = ?", ["Smith", "Jane", 1] ) .then(function(success) { if (success) { application.output("Employee updated successfully"); } else { application.output("Update returned false: " + plugins.rawSQL.getException().getMessage()); } }) .catch(function(errMsg) { application.output("Async SQL failed: " + errMsg); }); ``` **Parameters** * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **serverName** the logical DB server name (e.g. `example_data`) * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **sql** the SQL statement with placeholders * [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) **sql\_args** the array of parameter values **Returns:** [Promise](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/promise) a [Promise](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/promise) resolving to `true` on success or `false` on a non-exceptional failure ### executeStoredProcedure(serverName, procedureDeclaration, arguments, maxNumberOfRowsToRetrieve) Execute a stored procedure, return all created result sets. **Parameters** * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **serverName** ; * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **procedureDeclaration** ; * [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) **arguments** ; * [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **maxNumberOfRowsToRetrieve** ; **Returns:** [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) the result sets created by the procedure. **Sample** ```js /*************************************************************************** WARNING! You can cause data loss or serious data integrity compromises! You should have a THOROUGH understanding of both SQL and your backend database (and other interfaces that may use that backend) BEFORE YOU USE ANY OF THESE COMMANDS. You should also READ THE DOCUMENTATION BEFORE USING ANY OF THESE COMMANDS ****************************************************************************/ var maxReturnedRows = 10; //useful to limit number of rows var procedure_declaration = '{ get_unpaid_orders_and_their_customers(?) }' var args = [42] var datasets = plugins.rawSQL.executeStoredProcedure(databaseManager.getDataSourceServerName(controller.getDataSource()), procedure_declaration, args, maxReturnedRows); for (var i = 0; i < datasets.length; i++) { var ds = datasets[i] // process dataset } ``` ### executeStoredProcedure(serverName, procedureDeclaration, arguments, inOutDirectionality, maxNumberOfRowsToRetrieve) Execute a stored procedure. **Parameters** * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **serverName** ; * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **procedureDeclaration** ; * [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) **arguments** ; * [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) **inOutDirectionality** ; * [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **maxNumberOfRowsToRetrieve** ; **Returns:** [JSDataSet](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jsdataset) a dataset with output (in case of output data) or the last result set executed by the procedure. **Sample** ```js /*************************************************************************** WARNING! You can cause data loss or serious data integrity compromises! You should have a THOROUGH understanding of both SQL and your backend database (and other interfaces that may use that backend) BEFORE YOU USE ANY OF THESE COMMANDS. You should also READ THE DOCUMENTATION BEFORE USING ANY OF THESE COMMANDS ****************************************************************************/ var maxReturnedRows = 10; //useful to limit number of rows var procedure_declaration = '{?=calculate_interest_rate(?)}' // define the direction, a 0 for input data, a 1 for output data var typesArray = [1, 0]; // define the types and values, a value for input data, a sql-type for output data var args = [java.sql.Types.NUMERIC, 3000] // A dataset is returned, when no output-parameters defined, the last select-result in the procedure will be returned. // When one or more output-parameters are defined, the dataset will contain 1 row with the output data. var dataset = plugins.rawSQL.executeStoredProcedure(databaseManager.getDataSourceServerName(controller.getDataSource()), procedure_declaration, args, typesArray, maxReturnedRows); var interest_rate = dataset.getValue(1, 1); ``` ### executeStoredProcedureAsync(serverName, procDecl, args, maxRows) Execute a stored procedure asynchronously without explicit direction types. Returns a [Promise](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/promise) object that resolves to an array of `JSDataSet` containing all result sets, or rejects with an error message on failure. Example listing employees in a department: ```js plugins.rawSQL.executeStoredProcedureAsync( "example_data", "call increase(?)", [2], [0] 100 ) .then(function(datasets) { var ds = datasets[0]; application.output(ds); }) .catch(function(error) { application.output("Async stored procedure failed: " + error); }); ``` **Parameters** * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **serverName** the logical DB server name (e.g. `example_data`) * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **procDecl** the JDBC stored procedure declaration * [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) **args** the array of procedure arguments * [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **maxRows** the maximum number of rows to retrieve for a SELECT inside the procedure **Returns:** [Promise](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/promise) a [Promise](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/promise) resolving to an array of `JSDataSet` on success ### executeStoredProcedureAsync(serverName, procDecl, args, inOutTypes, maxRows) Execute a stored procedure asynchronously with specified parameter directions and types. Returns a [Promise](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/promise) object that resolves to a `JSDataSet` containing the result set, or rejects with an error message on failure. Example calculating age from birth year: ```js plugins.rawSQL.executeStoredProcedureAsync( "example_data", "call increase(?)", [2], 100 ) .then(function(datasets) { var ds = datasets[0]; application.output(ds); }) .catch(function(error) { application.output("Async stored procedure failed: " + error); }); ``` **Parameters** * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **serverName** the logical DB server name (e.g. `example_data`) * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **procDecl** the JDBC stored procedure call string * [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) **args** the array of input/output parameter values * [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) **inOutTypes** the SQL Types for input/output parameters * [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **maxRows** the maximum number of rows to fetch from a SELECT inside the procedure **Returns:** [Promise](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/promise) a [Promise](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/promise) resolving to a `JSDataSet` on success ### flushAllClientsCache(serverName, tableName) Flush cached database data. Use with extreme care, its affecting the performance of clients! This method will take into account tenants, just like databroadcast from user interface. **Parameters** * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **serverName** ; * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **tableName** ; **Returns:** [Boolean](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/boolean) True if the cache was successfully flushed; false otherwise. **Sample** ```js /*************************************************************************** WARNING! You can cause data loss or serious data integrity compromises! You should have a THOROUGH understanding of both SQL and your backend database (and other interfaces that may use that backend) BEFORE YOU USE ANY OF THESE COMMANDS. You should also READ THE DOCUMENTATION BEFORE USING ANY OF THESE COMMANDS ****************************************************************************/ var country = 'NL' var done = plugins.rawSQL.executeSQL("example_data","employees","update employees set country = ?", [country]) if (done) { //flush is required when changes are made in db plugins.rawSQL.flushAllClientsCache("example_data","employees") } else { var msg = plugins.rawSQL.getException().getMessage(); //see exception node for more info about the exception obj plugins.dialogs.showErrorDialog('Error', 'SQL exception: '+msg, 'Ok') } // Note that when this function is used to create a new table in the database, this table will only be seen by // the Servoy Application Server when the table name starts with 'temp_', otherwise a server restart is needed. ``` ### getException() If the result from a function was false, it will return the exception object. **Returns:** [Exception](https://github.com/Servoy/gitbook/blob/master/reference/servoycore/dev-api/exception.md) The exception object if the result from a function was false, or null if no exception occurred. **Sample** ```js /*************************************************************************** WARNING! You can cause data loss or serious data integrity compromises! You should have a THOROUGH understanding of both SQL and your backend database (and other interfaces that may use that backend) BEFORE YOU USE ANY OF THESE COMMANDS. You should also READ THE DOCUMENTATION BEFORE USING ANY OF THESE COMMANDS ****************************************************************************/ var country = 'NL' var done = plugins.rawSQL.executeSQL("example_data","employees","update employees set country = ?", [country]) if (done) { //flush is required when changes are made in db plugins.rawSQL.flushAllClientsCache("example_data","employees") } else { var msg = plugins.rawSQL.getException().getMessage(); //see exception node for more info about the exception obj plugins.dialogs.showErrorDialog('Error', 'SQL exception: '+msg, 'Ok') } // Note that when this function is used to create a new table in the database, this table will only be seen by // the Servoy Application Server when the table name starts with 'temp_', otherwise a server restart is needed. ``` ### notifyDataChange(serverName, tableName, pksDataset, action) Notify clients about changes in records, based on pk(s). Use with extreme care, its affecting the performance of clients! This method will take into account tenants, just like databroadcast from user interface. **Parameters** * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **serverName** ; * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **tableName** ; * [JSDataSet](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jsdataset) **pksDataset** ; * [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **action** ; **Returns:** [Boolean](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/boolean) True if the data change notification was successfully sent; false otherwise. **Sample** ```js /*************************************************************************** WARNING! You can cause data loss or serious data integrity compromises! You should have a THOROUGH understanding of both SQL and your backend database (and other interfaces that may use that backend) BEFORE YOU USE ANY OF THESE COMMANDS. You should also READ THE DOCUMENTATION BEFORE USING ANY OF THESE COMMANDS ****************************************************************************/ var action = SQL_ACTION_TYPES.DELETE_ACTION //pks deleted //var action = SQL_ACTION_TYPES.INSERT_ACTION //pks inserted //var action = SQL_ACTION_TYPES.UPDATE_ACTION //pks updates var pksdataset = databaseManager.convertToDataSet(new Array(12,15,16,21)) var ok = plugins.rawSQL.notifyDataChange(databaseManager.getDataSourceServerName(controller.getDataSource()), 'employees', pksdataset,action) ``` ***