Child pages
  • rawSQL

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Divcache
Div
iddescription
HTML Table
id
classservoy sSummary
Colgroup Tag
Col
width80px
Col
Table Head (thead)
Table Row (tr)
styleheight: 30px;
Table Head (th)
colspan2
Server Property Summary
Table Row (tr)
Table Cell (td)
 
Table Cell (td)
servoy.rawSQL.allowClientCacheFlushes
HTML Table
id
classservoy sSummary
Colgroup Tag
Col
width80px
Col
Table Head (thead)
Table Row (tr)
styleheight: 30px;
Table Head (th)
colspan2
Method Summary
Table Row (tr)
Table Cell (td)
Boolean
Table Cell (td)executeSQL(serverName, tableName, sql)
styleindexdisplay:none

DO NOT EDIT THE CONTENT OF THIS PAGE DIRECTLY (EXCEPT INSIDE THE DIV BELOW WITH ID=DESCRIPTION), UNLESS YOU KNOW WHAT YOU'RE DOING.
THE STRUCTURE OF THE CONTENT IS VITAL IN BEING ABLE TO AUTO UPDATE THE CONTENT THROUGH THE DOC GENERATOR.

true
refresh100d
showRefreshtrue
iddoc
titleRefresh page
showDatetrue
retryEnable
idserverPropertyclass

servoy sReturnTypes2100%height: 30px;2Supported ClientssWordListSmartClientsWordListWebClientsWordListNGClient

Table Cell (td)
Boolean
Table Cell (td)executeSQL(serverName, tableName, sql, sql_args)
servoy sSummary12%30%58%height: 30px;3Methods SummaryExecute any SQL, returns true if successful. Table Row (tr)Execute any SQL, returns true if successful. Table Row (tr)
Table Cell (td)
JSDataSet
Table Cell (td)executeStoredProcedure(serverName, procedureDeclaration, arguments, inOutDirectionality, maxNumberOfRowsToRetrieve)
Execute a stored procedure, return all created result sets.Execute a stored procedure. Table Row (tr)
Table Cell (td)
Boolean
Table Cell (td)flushAllClientsCache(serverName, tableName)
Table Cell (td)getException()
Flush cached database data. Table Row (tr)
Table Cell (td)
ServoyException
Table Cell (td)
Boolean
Table Cell (td)notifyDataChange(serverName, tableName, pksDataset, action)
If the result from a function was false, it will return the exception object. Table Row (tr)Notify clients about changes in records, based on pk(s). HTML Table

servoy sDetail

Colgroup Tag
Col
colspan2
width100%
Col
Table Head (thead)
Table Row (tr)
styleheight: 30px;
Table Head (th)
colspan2
Server Property Details
Table Body (tbody)
idservoy.rawSQL.allowClientCacheFlushes
Table Row (tr)
idname
Table Cell (td)

servoy.rawSQL.allowClientCacheFlushes

Table Row (tr)
classlastDetailRow
Table Cell (td)
 
HTML Tableid

functionclass Colgroup Tag Colcolspanservoy sDetail2width100%

Col
Table Head (thead) Table Row (tr)styleheight:30px;
Table Head (th)
colspan2
Method Details
Table Body (tbody)
idexecuteSQL-String_String_String
Table Row (tr)
idname
Table Cell (td)

executeSQL

Table Row (tr)
idsig
Table Cell (td)
Span
stylemargin-right: 5px;
Boolean
Span
stylefont-weight: bold;
executeSQL
Spanid2Methods DetailsexecuteSQLname

executeSQL(serverName, tableName, sql)

Table Row (tr)des Table Cell (td) DivclasssIndent
Execute any SQL, returns true if successful.
Table Row (tr)idtd
prsclass

Parameters

DivsIndent{String} serverName - the name of the server
{String} tableName - the name of the table
{String} sql - the sql query to execute Table Row (tr)idtdret

Returns

divsIndentclients

classid

Supported Clients

sIndent
Boolean Table Row (tr)SmartClient,WebClient,NGClientsam

Table Cell (td)

Table Row (tr)
classlastDetailRow
Table Cell (td)
 
Table Body (tbody)
idexecuteSQL-String_String_String_ObjectArray
Table Row (tr)
idname
Table Cell (td)

executeSQL

Table Row (tr)
idsig
Table Cell (td)
Span
stylemargin-right: 5px;
Boolean
Span
stylefont-weight: bold;
executeSQL
Span

Sample

Div
classsIndent
Code Block
languagejavascript
/****************************************************************************
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.
****************************************************************************/

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.
idsIdentjavascriptlastDetailRowexecuteSQLname

executeSQL(serverName, tableName, sql, sql_args)

Table Row (tr) Divclassdes Table Cell (td)idsIndent
Execute any SQL, returns true if successful.
Table Row (tr)tdprsclass

Parameters

DivsIndent{String} serverName - the name of the server
{String} tableName - the name of the table
{String} sql - the sql query to execute
{Object[]} sql_args - Table Row (tr)idthe arguments for the queryret

Table Cell (td)

Returns

divsIndentclients

classid

Supported Clients

sIndent
Boolean Table Row (tr)SmartClient,WebClient,NGClientsam

Table Cell (td) Table Row (tr)

idsig
Table Cell (td)
Span
stylemargin-right: 5px;
JSDataSet
Span
stylefont-weight: bold;
executeStoredProcedure
Span

Sample

Div
classsIndent
Code Block
languagejavascript
/****************************************************************************
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.
****************************************************************************/

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.
Table Row (tr)
classlastDetailRow
Table Cell (td)
 
Table Body (tbody)
idexecuteStoredProcedure-String_String_ObjectArray_NumberArray_Number
Table Row (tr)
idname
Table Cell (td)

executeStoredProcedure

sIdentjavascriptlastDetailRowexecuteStoredProcedurename

executeStoredProcedure(serverName, procedureDeclaration, arguments, maxNumberOfRowsToRetrieve)

dessIndent
Execute a stored procedure, return all created result sets.
prs

Parameters

sIndentserverName ;procedureDeclaration ;arguments ;maxNumberOfRowsToRetrieve ;
ret

Returns

sIndent
clients

Supported Clients

sIndentSmartClient,WebClient,NGClient
sam

Sample

sIdentjavascript
lastDetailRow
executeStoredProcedurename

executeStoredProcedure(serverName, procedureDeclaration, arguments, inOutDirectionality, maxNumberOfRowsToRetrieve)

Table Row (tr)idclass

des Table Cell (td) DividsIndent
Execute a stored procedure.
Table Row (tr)prs

Table Cell (td)class

Parameters

DivsIndent{String} serverName
{String} procedureDeclaration
{Object[]} arguments
{Number[]} inOutDirectionality
{Number} maxNumberOfRowsToRetrieve
Table Row (tr)
idret
Table Cell (td)

Returns

Div
classsIndent
JSDataSet
Table Row (tr)
idsam
Table Cell (td)

Sample

Div
classsIndent
Code Block
languagejavascript
/****************************************************************************
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.
****************************************************************************/

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);
Table Row (tr)
classlastDetailRow
Table Cell (td)
 
Table Body (tbody)
idflushAllClientsCache-String_String
Table Row (tr)
idname
Table Cell (td)

flushAllClientsCache

Table Row (tr)
idsig
Table Cell (td)
Span
stylemargin-right: 5px;
Boolean
Span
stylefont-weight: bold;
flushAllClientsCache
Span
(serverName, tableName)
Table Row (tr)
iddes
Table Cell (td)
Div
classsIndent
Flush cached database data. Use with extreme care, its affecting the performance of clients!
Table Row (tr)
idprs
Table Cell (td)

Parameters

Div
classsIndent
{String} serverName
{String} tableName
Table Row (tr)
idret
Table Cell (td)

Returns

Div
classsIndent
Boolean
Table Row (tr)
idsam
Table Cell (td)

Sample

Div
classsIndent
Code Block
languagejavascript
/****************************************************************************
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.
****************************************************************************/

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.
Table Row (tr)
classlastDetailRow
Table Cell (td)
 
Table Body (tbody)
idgetException
Table Row (tr)
idname
Table Cell (td)

getException

Table Row (tr)
idsig
Table Cell (td)
Span
stylemargin-right: 5px;
ServoyException
Span
stylefont-weight: bold;
getException
Span
()
Table Row (tr)
iddes
Table Cell (td)
Div
classsIndent
If the result from a function was false, it will return the exception object.
Table Row (tr)
idret
Table Cell (td)

Returns

Div
classsIndent
ServoyException
Table Row (tr)
idsam
Table Cell (td)

Sample

Div
classsIndent
Code Block
languagejavascript
/****************************************************************************
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.
****************************************************************************/

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.
Table Row (tr)
classlastDetailRow
Table Cell (td)
 
Table Body (tbody)
idnotifyDataChange-String_String_JSDataSet_Number
Table Row (tr)
idname
Table Cell (td)

notifyDataChange

Table Row (tr)
idsig
Table Cell (td)
Span
stylemargin-right: 5px;
Boolean
Span
stylefont-weight: bold;
notifyDataChange
SpanserverName ;procedureDeclaration ;arguments ;inOutDirectionality ;maxNumberOfRowsToRetrieve ;ret

Returns

sIndent
clients

Supported Clients

sIndentSmartClient,WebClient,NGClient
sam

Sample

sIdentjavascript
lastDetailRow
flushAllClientsCachename

flushAllClientsCache(serverName, tableName)

dessIndent
Flush cached database data. Use with extreme care, its affecting the performance of clients!
prs

Parameters

sIndentserverName ;tableName ;
ret

Returns

sIndent
clients

Supported Clients

sIndentSmartClient,WebClient,NGClient
sam

Sample

sIdentjavascript
lastDetailRow
getExceptionname

getException()

dessIndent
If the result from a function was false, it will return the exception object.
ret

Returns

sIndent
clients

Supported Clients

sIndentSmartClient,WebClient,NGClient
sam

Sample

sIdentjavascript
lastDetailRow
notifyDataChangename

notifyDataChange(serverName, tableName, pksDataset, action)

Table Row (tr)id Divclass

des Table Cell (td)sIndent
Notify clients about changes in records, based on pk(s). Use with extreme care, its affecting the performance of clients!
tr
idprs

Table Cell (td)

Parameters

Divclass
Table Row (tr)
idret
Table Cell (td)

Returns

Div
classsIndent
Boolean
Table Row (tr)
idsam
Table Cell (td)

Sample

Div
classsIndent
Code Block
languagejavascript
/****************************************************************************
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.
****************************************************************************/

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)
Table Row (tr)
classlastDetailRow
Table Cell (td) 

sIndent{String} serverName
{String} tableName
{JSDataSet} pksDataset
{Number} actionserverName ;tableName ;pksDataset ;action ;ret

Returns

sIndent
clients

Supported Clients

sIndentSmartClient,WebClient,NGClient
sam

Sample

sIdentjavascript
lastDetailRow