How do you find out how many rows have been updated after an SQL Update including a not found.
If I run :
done = plugins.rawSQL.executeSQL( blah, blah, “Update tablex set …” )
I get a true if it runs ok, but I’ve no idea whether it updated anything (how many rows) or nothing at all?
Thanks.
The rawSQL plugin doesnt provide that information.
You can create a feature request for this.
Thanks, so theres no alternative means within Servoy aside form running a select count(*) before performing the update to get the count of how many rows will be updated. Obviously if it was zero you wouldn’t perform the update.
Inefficient, am suprised nobody has requested this before, imho its good practice to know whether the update had had a not found condition at the very least.
Ho humm.
Alternative is not using rawSQL, for this you could use the JSFoundSetUpdater.
Well, it’s not a best practice to update through the RawSQL plugin, so that is why I think no-one ever ran into it.
Paul
Had no idea it was not best practice to update via the plugin …
Anyways what I’m trying to do work out is best way of codign the update sql when I have to :
Not sure how you do this with foundset concept as the user is simply kicking off the request to read the file and under the covers a whole lot of table loads (inserts), selects and updates are occurring across multiple tables.
Without knowing the finer details of servoy I’m simply reading file to eof executing Update sql (as per plugin) as this seemed the most logical way of doing it.
Yummy:
Not sure how you do this with foundset concept as the user is simply kicking off the request to read the file and under the covers a whole lot of table loads (inserts), selects and updates are occurring across multiple tables.
Using foundsets is core to coding with Servoy. I agree though, not enough information is out there to make a smooth transition from other programming environments. Here’s some code examples to get you started:
1- Benoit’s importer module: http://www.servoyforge.net/projects/csvimporter
2- Simple foundset processing:
function WEB4com_temp_convert_internal_links() {
// find all web_block_data records with current site.id_site
var sql = 'SELECT web_block_data.id_block_data FROM web_block_data WHERE web_block_data.id_block IN \
(select web_block.id_block from web_block WHERE web_block.id_area IN \
(select web_area.id_area from web_area WHERE web_area.id_page IN \
(select web_page.id_page from web_page WHERE web_page.id_site = ?)))'
var params = [forms.WEB_0F_site.id_site]
var dataset = databaseManager.getDataSetByQuery("sutra_cms", sql, params, -1)
if (dataset.getMaxRowIndex() < 1 ) {
plugins.dialogs.showErrorDialog( "Error", "No data found")
return
}
var input = plugins.dialogs.showQuestionDialog( "", dataset.getMaxRowIndex() + " records will be modifed. Continue?", "Yes", "No")
if ( input == "Yes" ) {
// load a foundset with dataset
var fsBlockData = databaseManager.getFoundSet( "sutra_cms", "web_block_data")
fsBlockData.loadRecords(dataset)
// initialize variables used in loop
var regexp = new RegExp('index\\.jsp\\?id=(\\d{1,10})','gi')
var counter = 0
// loop through foundset and replace web_block_data.data_data links
for (var i = 1; i <= fsBlockData.getSize(); i++) {
var record = fsBlockData.getRecord(i)
// if not null
if ( record.data_data ) {
// if regexp, replace
if ( record.data_data.match(regexp) ) {
record.data_data = record.data_data.replace(regexp, '{DS:ID_$1}')
databaseManager.saveData(i)
counter++
}
}
}
plugins.dialogs.showInfoDialog( "Completed", counter + " records updated.")
}
}
3- Duplicating records and all related records:
function CODE_record_duplicate()
{
/*
* TITLE : CODE_record_duplicate
*
* MODULE : rsrc_CODE_sutra
*
* ABOUT : create a duplicate of record, and optionally all children
*
* INPUT : 1- a record from some foundset
* 2- array of relations to copy through
* 3- overwrite bool (from autoenter/relations/etc) with values from copying record
*
* OUTPUT : new parent record
*
* REQUIRES :
*
* USAGE : CODE_record_duplicate(record, [relationArray], [overwrite]) Duplicates a record and (optionally) all children
*
* MODIFIED : June 24, 2009 -- Troy Elliott, Data Mosaic
*
*/
//first, create object of relations
//under each relation, put all the child relations
//loop through it that way
//MEMO: need to somehow put this section in a Function of it's own
//running in Tano...strip out jsevents for now
if (utils.stringToNumber(application.getVersion()) >= 5) {
//cast Arguments to array
var Arguments = new Array()
for (var i = 0; i < arguments.length; i++) {
Arguments.push(arguments[i])
}
//reassign arguments without jsevents
arguments = Arguments.filter(globals.CODE_jsevent_remove)
}
var srcRecord = arguments[0]
var relationArray = arguments[1]
var overwriteOK = (arguments[2]) ? true : false
//object to store all relations
//tree required for construction
var relations =
tree = new Object()
var tree
//something was passed in
if (srcRecord) {
//if relations, convert array into object tree
if (relationArray && relationArray.length) {
relationArray.sort()
//split up compound relations
for (var i = 0; i < relationArray.length; i++) {
var item = relationArray[i]
//multiple levels of relations
if (utils.stringPatternCount(item,'.')) {
item = item.split('.')
}
//nothing to do, skip this iteration
else if (!item) {
continue
}
//one relation
else {
item = new Array(item)
}
//add all items to tree
for (var j = 0; j < item.length; j++) {
//no place holder for this object yet
if (!tree[item[j]]) {
tree[item[j]] = {
_relation_ : item[j]
}
//punch down position of this item
if (!tree.length) {
tree.length = 1
}
else {
tree.length++
}
//punch down name of this position
tree[tree.length - 1] = item[j]
}
//set tree to newly created item
tree = tree[item[j]]
}
//reset tree for next go round
tree = relations
}
}
//get foundset of source record
var serverName = srcRecord.foundset.getServerName()
var tableName = srcRecord.foundset.getTableName()
var fsThis = databaseManager.getFoundSet(serverName,tableName)
//create duplicate record and copy data
var destRecord = fsThis.getRecord(fsThis.newRecord(false,true))
databaseManager.copyMatchingColumns(srcRecord,destRecord,overwriteOK)
//go through relations and duplicate sub-records
for (var i = 0; i < relations.length; i++) {
//this relation has multiple levels of children
globals.CODE_record_duplicate_fx(srcRecord,destRecord,relations[relations[i]],overwriteOK)
}
databaseManager.saveData()
return destRecord
}
}
function CODE_record_duplicate_fx()
{
/*
* TITLE : CODE_record_duplicate_fx
*
* MODULE : rsrc_CODE_sutra
*
* ABOUT : create a duplicate of record, and optionally all children
*
* INPUT : 1- source record
* 2- destination record
* 3- object of relations
* 4- overwrite bool
*
* OUTPUT :
*
* REQUIRES :
*
* USAGE : CODE_record_duplicate_fx(sourceRecord, destinationRecord, subRelationArray, objectRecord, [overwrite]) Duplicates all children
*
* MODIFIED : June 24, 2009 -- Troy Elliott, Data Mosaic
*
*/
var srcRecord = arguments[0]
var destRecord = arguments[1]
var node = arguments[2]
var overwriteOK = (arguments[3]) ? true : false
var serverName = srcRecord.foundset.getServerName()
var tableName = srcRecord.foundset.getTableName()
var fsSource = eval('srcRecord.' + node._relation_)
var fsDest = eval('destRecord.' + node._relation_)
if (fsSource && utils.hasRecords(fsSource)) {
//go through children, call
for (var i = 1; i <= fsSource.getSize(); i++) {
//create duplicate record
var srcChild = fsSource.getRecord(i)
var destChild = fsDest.getRecord(fsDest.newRecord(false,true))
databaseManager.copyMatchingColumns(srcChild,destChild,overwriteOK)
//re-call this Function if there are more levels beneath
for (var j = 0; j < node.length; j++) {
globals.CODE_record_duplicate_fx(srcChild,destChild,node[node[j]],overwriteOK)
}
}
}
}
Thanks David, yep understand now …
Question as I don’t know how Servoy works under the covers surely to me on face value its more efficient to run a single update command which is then executed within the database and the database server (e.g. update tab x set col y = ‘blah’ where …) then executing updates using code controlled via Servoy ?
The examples I gave you deal mainly with changing values of a single foundset row then moving on to the next row and processing (or not based on logic). For each value change on a foundset row, Servoy sends out a sql update statement. (You can see the SQL servoy generates in the server performance tab.) This is the equivalent of writing a MySQL script that iterates through logic running many SQL statements in the process.
Servoy also has the equivalent of an update statement that is run once for many records. Check out the sample code of the Database Manager > JSFoundSetUpdater object. (Mentioned by Michel above.)
The way I read your initial issue is that you are using a Servoy method to read in the initial file data – apply some logic and iteration – and when you finally reach a point where you want to update the backend you issue a plugins.rawSQL statement. Instead you should modify a foundset or foundSetUpdater object at this point.
Servoy will be slower than hand coded SQL statements only if you can write better optimized SQL than what Servoy generates. So if you’re importing and processing large files, Servoy may not be the best tool for the job. If you do use Servoy, you will notice a big speed difference running this over the network vs running via a client on the server (batch processor good for this).
If the job requires a lot of logic and iteration (and debugging to get it right), you’re going to have to use some sort of scripting though and Servoy shines in this regard.