Page History

{hidden}
DO NOT EDIT THE CONTENT OF THIS PAGE DIRECTLY, UNLESS YOU KNOW WHAT YOU'RE DOING.
		THE STRUCTURE OF THE CONTENT IS VITAL IN BEING ABLE TO EXTRACT CHANGES FROM THE PAGE AND MERGE THEM BACK INTO SERVOY SOURCE{hidden}
{sub-section:description|text=}{sub-section}\\ 

{table:id=|class=servoy sSummerysSummary}{colgroup}{column:padding=0px|width=80px}{column}{column}{column}{colgroup}{tr:style=height: 30px;}{th:colspan=2}Server Property SummerySummary{th}{tr}{tbody}{tr}{td}{td}{td}[#servoy.rawSQL.allowClientCacheFlushes]
{td}{tr}{tbody}{table}\\ 

{table:id=|class=servoy sSummerysSummary}{colgroup}{column:padding=0px|width=80px}{column}{column}{column}{colgroup}{tr:style=height: 30px;}{th:colspan=2}Method SummerySummary{th}{tr}{tbody}{tr}{td}[Boolean]{td}{td}[#executeSQL]\(serverName, tableName, SQL, \[arguments\])
Execute any SQL, returns true if successful.{td}{tr}{tbody}{tbody}{tr}{td}[JSDataSet]{td}{td}[#executeStoredProcedure]\(serverName, procedureDeclaration, \[arguments[\], \[IODirectionality[\], maxNrReturnedRows)
Execute a stored procedure.{td}{tr}{tbody}{tbody}{tr}{td}[Boolean]{td}{td}[#flushAllClientsCache]\(serverName, tableName)
Flush cached database data.{td}{tr}{tbody}{tbody}{tr}{td}[ServoyException]{td}{td}[#getException]\()
If the result from a function was false, it will return the exception object.{td}{tr}{tbody}{tbody}{tr}{td}[Boolean]{td}{td}[#notifyDataChange]\(serverName, tableName, pksDataset, action)
Notify clients about changes in records, based on pk(s).{td}{tr}{tbody}{table}\\ 

{table:id=serverProperty|class=servoy sDetail}{colgroup}{column:padding=0px|width=100%}{column}{colgroup}{tr:style=height: 30px;}{th:colspan=1}Server Property Details{th}{tr}{tbody:id=servoy.rawSQL.allowClientCacheFlushes|class=node}{tr:id=name}{td}h6.servoy.rawSQL.allowClientCacheFlushes{td}{tr}{tr:id=des}{td}{sub-section:servoy.rawSQL.allowClientCacheFlushes_des|text=|trigger=button}{sub-section}{sub-section:servoy.rawSQL.allowClientCacheFlushes_des|trigger=none|class=sIndent}In case of performance problem you might want to disable this (true/false){sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=snc}{td}*Since*\\{sub-section:servoy.rawSQL.allowClientCacheFlushes_snc|text=|trigger=button}{sub-section}{sub-section:servoy.rawSQL.allowClientCacheFlushes_snc|trigger=none|class=sIndent} Replace with version info{sub-section}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=prs}{td}*Parameters*\\{sub-section:servoy.rawSQL.allowClientCacheFlushes_prs|text=|trigger=button}{sub-section}{sub-section:servoy.rawSQL.allowClientCacheFlushes_prs|trigger=none|class=sIndent}{sub-section}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=ret}{td}*Returns*\\{sub-section:servoy.rawSQL.allowClientCacheFlushes_ret|text=|trigger=button}{sub-section}{sub-section:servoy.rawSQL.allowClientCacheFlushes_ret|trigger=none|class=sIndent}{sub-section}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=see}{td}*Also see*\\{sub-section:servoy.rawSQL.allowClientCacheFlushes_see|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:servoy.rawSQL.allowClientCacheFlushes_see|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=link}{td}*External links*\\{sub-section:servoy.rawSQL.allowClientCacheFlushes_link|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:servoy.rawSQL.allowClientCacheFlushes_link|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=sam}{td}*Sample*\\{sub-section:servoy.rawSQL.allowClientCacheFlushes_sam|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:servoy.rawSQL.allowClientCacheFlushes_sam|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{table}\\ 

{table:id=function|class=servoy sDetail}{colgroup}{column:padding=0px|width=100%}{column}{colgroup}{tr:style=height: 30px;}{th:colspan=1}Method Details{th}{tr}{tbody:id=executeSQL|class=node}{tr:id=name}{td}h6.executeSQL{td}{tr}{tr:id=sig}{td}{span:style=float: left; margin-right: 5px;}[Boolean]{span}{span:id=iets|style=float: left; font-weight: bold;}executeSQL{span}{span:id=iets|style=float: left;}\(serverName, tableName, SQL, \[arguments\]){span}{td}{tr}{tr:id=des}{td}{sub-section:executeSQL_des|text=|trigger=button}{sub-section}{sub-section:executeSQL_des|trigger=none|class=sIndent}Execute any SQL, returns true if successful.{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=snc}{td}*Since*\\{sub-section:executeSQL_snc|text=|trigger=button}{sub-section}{sub-section:executeSQL_snc|trigger=none|class=sIndent} Replace with version info{sub-section}{td}{tr}{builder-show}{tr:id=prs}{td}*Parameters*\\{sub-section:executeSQL_prs|text=|trigger=button}{sub-section}{sub-section:executeSQL_prs|trigger=none|class=sIndent}serverName
tableName
SQL
\[arguments\]
{sub-section}{td}{tr}{tr:id=ret}{td}*Returns*\\{sub-section:executeSQL_ret|text=|trigger=button}{sub-section}{sub-section:executeSQL_ret|trigger=none|class=sIndent}[Boolean]{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=see}{td}*Also see*\\{sub-section:executeSQL_see|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:executeSQL_see|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=link}{td}*External links*\\{sub-section:executeSQL_link|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:executeSQL_link|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{tr:id=sam}{td}*Sample*\\{sub-section:executeSQL_sam|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:executeSQL_sam|trigger=none}{code:language=javascript}
/****************************************************************************
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')
}
{code}{sub-section}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=executeStoredProcedure|class=node}{tr:id=name}{td}h6.executeStoredProcedure{td}{tr}{tr:id=sig}{td}{span:style=float: left; margin-right: 5px;}[JSDataSet]{span}{span:id=iets|style=float: left; font-weight: bold;}executeStoredProcedure{span}{span:id=iets|style=float: left;}\(serverName, procedureDeclaration, \[arguments[\], \[IODirectionality[\], maxNrReturnedRows){span}{td}{tr}{tr:id=des}{td}{sub-section:executeStoredProcedure_des|text=|trigger=button}{sub-section}{sub-section:executeStoredProcedure_des|trigger=none|class=sIndent}Execute a stored procedure.{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=snc}{td}*Since*\\{sub-section:executeStoredProcedure_snc|text=|trigger=button}{sub-section}{sub-section:executeStoredProcedure_snc|trigger=none|class=sIndent} Replace with version info{sub-section}{td}{tr}{builder-show}{tr:id=prs}{td}*Parameters*\\{sub-section:executeStoredProcedure_prs|text=|trigger=button}{sub-section}{sub-section:executeStoredProcedure_prs|trigger=none|class=sIndent}serverName
procedureDeclaration
\[arguments\[\]
\[IODirectionality\[\]
maxNrReturnedRows
{sub-section}{td}{tr}{tr:id=ret}{td}*Returns*\\{sub-section:executeStoredProcedure_ret|text=|trigger=button}{sub-section}{sub-section:executeStoredProcedure_ret|trigger=none|class=sIndent}[JSDataSet]{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=see}{td}*Also see*\\{sub-section:executeStoredProcedure_see|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:executeStoredProcedure_see|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=link}{td}*External links*\\{sub-section:executeStoredProcedure_link|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:executeStoredProcedure_link|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{tr:id=sam}{td}*Sample*\\{sub-section:executeStoredProcedure_sam|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:executeStoredProcedure_sam|trigger=none}{code:language=javascript}
/****************************************************************************
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// argsdefine =the new Array()
args[0] = java.sql.Types.NUMERIC
args[1] = 3000
//	direction, a 0 for input data, a 1 for output data
var typesArray = [1, 0];
// define the types and directionvalues, in this case a 0value for input data, vara typesArraysql-type =for new Array();
typesArray[0]=1;
typesArray[1]=0;
var datasetoutput data
var args = plugins[java.rawSQLsql.executeStoredProcedure(controller.getServerName(), procedure_declaration, args, typesArray,maxReturnedRows);
Types.NUMERIC, 3000]
//example toA calcdataset ais strangereturned, totalwhen global_total = 0;
for( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ )
{
		global_total = global_total + dataset.getValue(i,1);
}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(controller.getServerName(), procedure_declaration, args, typesArray, maxReturnedRows);
var interest_rate = dataset.getValue(1, 1);
{code}{sub-section}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=flushAllClientsCache|class=node}{tr:id=name}{td}h6.flushAllClientsCache{td}{tr}{tr:id=sig}{td}{span:style=float: left; margin-right: 5px;}[Boolean]{span}{span:id=iets|style=float: left; font-weight: bold;}flushAllClientsCache{span}{span:id=iets|style=float: left;}\(serverName, tableName){span}{td}{tr}{tr:id=des}{td}{sub-section:flushAllClientsCache_des|text=|trigger=button}{sub-section}{sub-section:flushAllClientsCache_des|trigger=none|class=sIndent}Flush cached database data. Use with extreme care, its affecting the performance of clients!{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=snc}{td}*Since*\\{sub-section:flushAllClientsCache_snc|text=|trigger=button}{sub-section}{sub-section:flushAllClientsCache_snc|trigger=none|class=sIndent} Replace with version info{sub-section}{td}{tr}{builder-show}{tr:id=prs}{td}*Parameters*\\{sub-section:flushAllClientsCache_prs|text=|trigger=button}{sub-section}{sub-section:flushAllClientsCache_prs|trigger=none|class=sIndent}serverName
tableName
{sub-section}{td}{tr}{tr:id=ret}{td}*Returns*\\{sub-section:flushAllClientsCache_ret|text=|trigger=button}{sub-section}{sub-section:flushAllClientsCache_ret|trigger=none|class=sIndent}[Boolean]{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=see}{td}*Also see*\\{sub-section:flushAllClientsCache_see|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:flushAllClientsCache_see|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=link}{td}*External links*\\{sub-section:flushAllClientsCache_link|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:flushAllClientsCache_link|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{tr:id=sam}{td}*Sample*\\{sub-section:flushAllClientsCache_sam|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:flushAllClientsCache_sam|trigger=none}{code:language=javascript}
/****************************************************************************
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')
}
{code}{sub-section}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=getException|class=node}{tr:id=name}{td}h6.getException{td}{tr}{tr:id=sig}{td}{span:style=float: left; margin-right: 5px;}[ServoyException]{span}{span:id=iets|style=float: left; font-weight: bold;}getException{span}{span:id=iets|style=float: left;}\(){span}{td}{tr}{tr:id=des}{td}{sub-section:getException_des|text=|trigger=button}{sub-section}{sub-section:getException_des|trigger=none|class=sIndent}If the result from a function was false, it will return the exception object.{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=snc}{td}*Since*\\{sub-section:getException_snc|text=|trigger=button}{sub-section}{sub-section:getException_snc|trigger=none|class=sIndent} Replace with version info{sub-section}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=prs}{td}*Parameters*\\{sub-section:getException_prs|text=|trigger=button}{sub-section}{sub-section:getException_prs|trigger=none|class=sIndent}{sub-section}{td}{tr}{builder-show}{tr:id=ret}{td}*Returns*\\{sub-section:getException_ret|text=|trigger=button}{sub-section}{sub-section:getException_ret|trigger=none|class=sIndent}[ServoyException]{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=see}{td}*Also see*\\{sub-section:getException_see|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:getException_see|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=link}{td}*External links*\\{sub-section:getException_link|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:getException_link|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{tr:id=sam}{td}*Sample*\\{sub-section:getException_sam|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:getException_sam|trigger=none}{builder-show:permission=edit}{code:language=javascript}

{code}{builder-show}{|trigger=none}{code:language=javascript}
/****************************************************************************
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.
****************************************************************************/

// If the result from a function was false, it will return the exception object.
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')
}
{code}{sub-section}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=notifyDataChange|class=node}{tr:id=name}{td}h6.notifyDataChange{td}{tr}{tr:id=sig}{td}{span:style=float: left; margin-right: 5px;}[Boolean]{span}{span:id=iets|style=float: left; font-weight: bold;}notifyDataChange{span}{span:id=iets|style=float: left;}\(serverName, tableName, pksDataset, action){span}{td}{tr}{tr:id=des}{td}{sub-section:notifyDataChange_des|text=|trigger=button}{sub-section}{sub-section:notifyDataChange_des|trigger=none|class=sIndent}Notify clients about changes in records, based on pk(s). Use with extreme care, its affecting the performance of clients!{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=snc}{td}*Since*\\{sub-section:notifyDataChange_snc|text=|trigger=button}{sub-section}{sub-section:notifyDataChange_snc|trigger=none|class=sIndent} Replace with version info{sub-section}{td}{tr}{builder-show}{tr:id=prs}{td}*Parameters*\\{sub-section:notifyDataChange_prs|text=|trigger=button}{sub-section}{sub-section:notifyDataChange_prs|trigger=none|class=sIndent}serverName
tableName
pksDataset
action
{sub-section}{td}{tr}{tr:id=ret}{td}*Returns*\\{sub-section:notifyDataChange_ret|text=|trigger=button}{sub-section}{sub-section:notifyDataChange_ret|trigger=none|class=sIndent}[Boolean]{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=see}{td}*Also see*\\{sub-section:notifyDataChange_see|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:notifyDataChange_see|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=link}{td}*External links*\\{sub-section:notifyDataChange_link|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:notifyDataChange_link|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{tr:id=sam}{td}*Sample*\\{sub-section:notifyDataChange_sam|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:notifyDataChange_sam|trigger=none}{code:language=javascript}
/****************************************************************************
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 = 1SQL_ACTION_TYPES.DELETE_ACTION //pks deleted
//var action = 2SQL_ACTION_TYPES.INSERT_ACTION //pks inserted
//var action = 3SQL_ACTION_TYPES.UPDATE_ACTION //pks updates
var pksdataset = databaseManager.convertToDataSet(new Array(12,15,16,21))
var ok = plugins.rawSQL.notifyDataChange(controller.getServerName(), 'employees', pksdataset,action)
{code}{sub-section}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{table}