Child pages
  • rawSQL
Skip to end of metadata
Go to start of metadata


Server Property Summery
#servoy.rawSQL.allowClientCacheFlushes

Method Summery
Boolean #executeSQL(serverName, tableName, SQL, [arguments])
Execute any SQL, returns true if successful.
JSDataSet #executeStoredProcedure(serverName, procedureDeclaration, [arguments[], [IODirectionality[], maxNrReturnedRows)
Execute a stored procedure.
Boolean #flushAllClientsCache(serverName, tableName)
Flush cached database data.
ServoyException #getException()
If the result from a function was false, it will return the exception object.
Boolean #notifyDataChange(serverName, tableName, pksDataset, action)
Notify clients about changes in records, based on pk(s).

Server Property Details
servoy.rawSQL.allowClientCacheFlushes
In case of performance problem you might want to disable this (true/false)

Method Details
executeSQL

Boolean executeSQL (serverName, tableName, SQL, [arguments])

Execute any SQL, returns true if successful.
Parameters
serverName
tableName
SQL
[arguments]
Returns
Boolean
Sample
/****************************************************************************
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

Note that when server names have been switched (databasemanager.switchServer),the 
real server names must be used here, plugins.rawSQL is not transparent to switched servers.
****************************************************************************/
//Execute any SQL, returns true if successful.
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')
}
executeStoredProcedure

JSDataSet executeStoredProcedure (serverName, procedureDeclaration, [arguments[], [IODirectionality[], maxNrReturnedRows)

Execute a stored procedure.
Parameters
serverName
procedureDeclaration
[arguments[]
[IODirectionality[]
maxNrReturnedRows
Returns
JSDataSet
Sample
/****************************************************************************
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

Note that when server names have been switched (databasemanager.switchServer),the 
real server names must be used here, plugins.rawSQL is not transparent to switched servers.
****************************************************************************/
//Execute a stored procedure.
var maxReturnedRows = 10;//useful to limit number of rows
var procedure_declaration = '{?=calculate_interest_rate(?)}'
var args = new Array()
args[0] = java.sql.Types.NUMERIC
args[1] = 3000
//	define the types and direction, in this case a 0 for input data
var typesArray = new Array();
typesArray[0]=1;
typesArray[1]=0;
var dataset = plugins.rawSQL.executeStoredProcedure(controller.getServerName(), procedure_declaration, args, typesArray,maxReturnedRows);

//example to calc a strange total
global_total = 0;
for( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ )
{
		global_total = global_total + dataset.getValue(i,1);
}
flushAllClientsCache

Boolean flushAllClientsCache (serverName, tableName)

Flush cached database data. Use with extreme care, its affecting the performance of clients!
Parameters
serverName
tableName
Returns
Boolean
Sample
/****************************************************************************
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

Note that when server names have been switched (databasemanager.switchServer),the 
real server names must be used here, plugins.rawSQL is not transparent to switched servers.
****************************************************************************/
//Flush cached database data. Use with extreme care, its affecting the performance of clients!
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')
}
getException

ServoyException getException ()

If the result from a function was false, it will return the exception object.
Returns
ServoyException
Sample
 
notifyDataChange

Boolean 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!
Parameters
serverName
tableName
pksDataset
action
Returns
Boolean
Sample
/****************************************************************************
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

Note that when server names have been switched (databasemanager.switchServer),the 
real server names must be used here, plugins.rawSQL is not transparent to switched servers.
****************************************************************************/
//Notify clients about changes in records, based on pk(s). Use with extreme care, its affecting the performance of clients!
var action = 1 //pks deleted
//var action = 2 //pks inserted
//var action = 3 //pks updates
var pksdataset = databaseManager.convertToDataSet(new Array(12,15,16,21))
var ok = plugins.rawSQL.notifyDataChange(controller.getServerName(), 'employees',pksdataset,action)
  • No labels