{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:class=servoy sSummery}{colgroup}{column:width=80px}{column}{column}{column}{colgroup}{tr:style=height: 30px;}{th:colspan=2}Method Summary{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:class=servoy sDetail}{colgroup}{column:width=100%}{column}{colgroup}{tr:style=height: 30px;}{th:colspan=1}Method Details{th}{tr}{tbody:id=2EC4FAF8-1DB7-470D-9F85-492988713296}{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:2EC4FAF8-1DB7-470D-9F85-492988713296_des|text=|trigger=button}{sub-section}{sub-section:2EC4FAF8-1DB7-470D-9F85-492988713296_des|trigger=none|class=sIndent}
Cache
index
true
refresh
100d
showRefresh
true
id
doc
title
Refresh page
showDate
true
retry
Enable
servoy sReturnTypes2100%height: 30px;2Supported ClientssWordListSmartClientsWordListWebClientsWordListNGClientservoy sSummary12%30%58%height: 30px;3Methods SummaryExecute any SQL, returns true if successful.Execute any SQL, returns true if successful.Execute a stored procedure, return all created result sets.Execute a stored procedure.Flush cached database data.If the result from a function was false, it will return the exception object.Notify clients about changes in records, based on pk(s).functionservoy sDetail2100%height:30px2Methods DetailsexecuteSQLname
executeSQL(serverName, sql)
dessIndent
Execute any SQL, returns true if successful.
{sub-section}{td}{tr}{tr:id=prs}{td}*Parameters*\\{sub-section:2EC4FAF8-1DB7-470D-9F85-492988713296_prs|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:2EC4FAF8-1DB7-470D-9F85-492988713296_prs|trigger=none}serverName
tableName
SQL
\[arguments\]
{sub-section}{div}{td}{tr}{tr:id=ret}{td}*Returns*\\{sub-section:2EC4FAF8-1DB7-470D-9F85-492988713296_ret|text=|trigger=button}{sub-section}{sub-section:2EC4FAF8-1DB7-470D-9F85-492988713296_ret|trigger=none|class=sIndent}[Boolean]{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=see}{td}*Also see*\\{sub-section:2EC4FAF8-1DB7-470D-9F85-492988713296_see|text=|trigger=button}{sub-section}{sub-section:2EC4FAF8-1DB7-470D-9F85-492988713296_see|class=sIndent|trigger=none}{sub-section}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=link}{td}*External links*\\{sub-section:2EC4FAF8-1DB7-470D-9F85-492988713296_see|text=|trigger=button}{sub-section}{sub-section:2EC4FAF8-1DB7-470D-9F85-492988713296_link|class=sIndent|trigger=none}{sub-section}{td}{tr}{builder-show}{tr:id=sam}{td}*Sample*\\{sub-section:2EC4FAF8-1DB7-470D-9F85-492988713296_sam|text=|trigger=button}{sub-section}{sub-section:2EC4FAF8-1DB7-470D-9F85-492988713296_sam|class=sIndent|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.
****************************************************************************/
//prs
Parameters
sIndentserverNamethe name of the serversqlthe sql query to executeret
Returns
sIndentclients
Supported Clients
sIndentSmartClient,WebClient,NGClientsam
Sample
sIdentjavascriptlastDetailRowexecuteSQLname
executeSQL(serverName, sql, sql_args)
dessIndent
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}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=B8809448-D690-45C4-922F-9D7B824812D2}{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:B8809448-D690-45C4-922F-9D7B824812D2_des|text=|trigger=button}{sub-section}{sub-section:B8809448-D690-45C4-922F-9D7B824812D2_des|trigger=none|class=sIndent}prs
Parameters
sIndentserverNamethe name of the serversqlthe sql query to executesql_argsthe arguments for the queryret
.{sub-section}{td}{tr}{tr:id=prs}{td}*Parameters*\\{sub-section:B8809448-D690-45C4-922F-9D7B824812D2_prs|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:B8809448-D690-45C4-922F-9D7B824812D2_prs|trigger=none}serverName
procedureDeclaration
\[arguments[\]
\[IODirectionality[\]
maxNrReturnedRows
{sub-section}{div}{td}{tr}{tr:id=ret}{td}*Returns*\\{sub-section:B8809448-D690-45C4-922F-9D7B824812D2_ret|text=|trigger=button}{sub-section}{sub-section:B8809448-D690-45C4-922F-9D7B824812D2_ret|trigger=none|class=sIndent}[JSDataSet]{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=see}{td}*Also see*\\{sub-section:B8809448-D690-45C4-922F-9D7B824812D2_see|text=|trigger=button}{sub-section}{sub-section:B8809448-D690-45C4-922F-9D7B824812D2_see|class=sIndent|trigger=none}{sub-section}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=link}{td}*External links*\\{sub-section:B8809448-D690-45C4-922F-9D7B824812D2_see|text=|trigger=button}{sub-section}{sub-section:B8809448-D690-45C4-922F-9D7B824812D2_link|class=sIndent|trigger=none}{sub-section}{td}{tr}{builder-show}{tr:id=sam}{td}*Sample*\\{sub-section:B8809448-D690-45C4-922F-9D7B824812D2_sam|text=|trigger=button}{sub-section}{sub-section:B8809448-D690-45C4-922F-9D7B824812D2_sam|class=sIndent|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 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);
}
{code}{sub-section}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=E821394D-CFC0-4F2F-905F-C8F61BD2CEB8}{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:E821394D-CFC0-4F2F-905F-C8F61BD2CEB8_des|text=|trigger=button}{sub-section}{sub-section:E821394D-CFC0-4F2F-905F-C8F61BD2CEB8_des|trigger=none|class=sIndent}
Flush cached database data. Use with extreme care, its affecting the performance of clients!
{sub-section}{td}{tr}{tr:id=prs}{td}*Parameters*\\{sub-section:E821394D-CFC0-4F2F-905F-C8F61BD2CEB8_prs|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:E821394D-CFC0-4F2F-905F-C8F61BD2CEB8_prs|trigger=none}serverName
tableName
{sub-section}{div}{td}{tr}{tr:id=ret}{td}*Returns*\\{sub-section:E821394D-CFC0-4F2F-905F-C8F61BD2CEB8_ret|text=|trigger=button}{sub-section}{sub-section:E821394D-CFC0-4F2F-905F-C8F61BD2CEB8_ret|trigger=none|class=sIndent}[Boolean]{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=see}{td}*Also see*\\{sub-section:E821394D-CFC0-4F2F-905F-C8F61BD2CEB8_see|text=|trigger=button}{sub-section}{sub-section:E821394D-CFC0-4F2F-905F-C8F61BD2CEB8_see|class=sIndent|trigger=none}{sub-section}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=link}{td}*External links*\\{sub-section:E821394D-CFC0-4F2F-905F-C8F61BD2CEB8_see|text=|trigger=button}{sub-section}{sub-section:E821394D-CFC0-4F2F-905F-C8F61BD2CEB8_link|class=sIndent|trigger=none}{sub-section}{td}{tr}{builder-show}{tr:id=sam}{td}*Sample*\\{sub-section:E821394D-CFC0-4F2F-905F-C8F61BD2CEB8_sam|text=|trigger=button}{sub-section}{sub-section:E821394D-CFC0-4F2F-905F-C8F61BD2CEB8_sam|class=sIndent|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}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=8E16A6E2-9EFD-40AB-A2AA-8414EABCE450}{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:8E16A6E2-9EFD-40AB-A2AA-8414EABCE450_des|text=|trigger=button}{sub-section}{sub-section:8E16A6E2-9EFD-40AB-A2AA-8414EABCE450_des|trigger=none|class=sIndent}prs
Parameters
sIndentserverName ;tableName ;ret
Returns
sIndentclients
Supported Clients
sIndentSmartClient,WebClient,NGClientsam
Sample
sIdentjavascriptlastDetailRowgetExceptionname
getException()
dessIndent
If the result from a function was false, it will return the exception object.
Notify clients about changes in records, based on pk(s). Use with extreme care, its affecting the performance of clients!
{sub-section}{td}{tr}{tr:id=prs}{td}*Parameters*\\{sub-section:D7D70595-CE43-4EBB-BCF4-F307C37AECF4_prs|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:D7D70595-CE43-4EBB-BCF4-F307C37AECF4_prs|trigger=none}serverName
tableName
pksDataset
action
{sub-section}{div}{td}{tr}{tr:id=ret}{td}*Returns*\\{sub-section:D7D70595-CE43-4EBB-BCF4-F307C37AECF4_ret|text=|trigger=button}{sub-section}{sub-section:D7D70595-CE43-4EBB-BCF4-F307C37AECF4_ret|trigger=none|class=sIndent}[Boolean]{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=see}{td}*Also see*\\{sub-section:D7D70595-CE43-4EBB-BCF4-F307C37AECF4_see|text=|trigger=button}{sub-section}{sub-section:D7D70595-CE43-4EBB-BCF4-F307C37AECF4_see|class=sIndent|trigger=none}{sub-section}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=link}{td}*External links*\\{sub-section:D7D70595-CE43-4EBB-BCF4-F307C37AECF4_see|text=|trigger=button}{sub-section}{sub-section:D7D70595-CE43-4EBB-BCF4-F307C37AECF4_link|class=sIndent|trigger=none}{sub-section}{td}{tr}{builder-show}{tr:id=sam}{td}*Sample*\\{sub-section:D7D70595-CE43-4EBB-BCF4-F307C37AECF4_sam|text=|trigger=button}{sub-section}{sub-section:D7D70595-CE43-4EBB-BCF4-F307C37AECF4_sam|class=sIndent|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 = 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)
{code}{sub-section}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{table}prs