additional fields in log-Table

Classic Servoy
1

Hi,
I’m using a MS SQL-DB and the logging from Servoy.
I’ve additional fields in the log-table.

Is there any way to to add a function which is called when the log-records are written?
I found that all log-records are written after leaving a record.

I created a function to populate the addtional fields.
Within that function is use: “var done = plugins.rawSQL.executeSQL(“winxam”,“log”,_query1)”

There is no EVENT “when a record is left” (opposite of onRecordSelection)
So I call my function in the very beginning of onRecordSelection() See sample below.

I there a better way to do it?

Harro

function onRecordSelection(event) {
	if (globals.table_name_previous && globals.pk_previous_record ) {
		// application.output("letzte Tabelle im Zugriff: "+ globals.table_name_previous+ " PK: "+globals.pk_previous_record)
		databaseManager.saveData()
		globals.UpdateLogTab(globals.table_name_previous,globals.pk_previous_record)
	}
	globals.table_name_previous = controller.getName()
	/** * @type {String} */
	var strpk    = lngid.toString()
	var lenpk    = strpk.length
	var strlenpk = lenpk.toString()
	lenpk        = lenpk.toString()
	globals.pk_previous_record = strlenpk + "." + strpk + ";"
2

Have you looked a the table events? The afterRecordUpdate might be a nice event for it. That way you don’t have to attach a method to each form that uses the table.

[attachment=0]table_events.png[/attachment]

table_events.png
table_events.png223×223 2.62 KB

3

Hi,
thanks :)

I looked on the wrong place, I searched only on the form.

It works as expected. Nice :)

Harro

4

Great! 8)

5

Hi,
I think my approach from yesterday with “plugins.rawSQL.executeSQL()” is not really good.
The “rawSQL” SQL-Statement runs “outside” from Servoy.

Now I try to read data with “getDataSetByQuery”, it works.

I read all records in a FOR loop.
But, I don’t know how to update the records.

Thanks for Info and help
Harro

function TestUpdateLog_PRO() {
	var _query1 = null
	_query1     = _query1 + "select " 
	_query1     = _query1 + " log.prospect_i "
	_query1     = _query1 + ",log.table_name " 
	_query1     = _query1 + ",log.tabelle    " 
	_query1     = _query1 + ",log.SEK_KEY    "
	_query1     = _query1 + ",log.INS_DATE   " 
	_query1     = _query1 + ",log.INS_User   "
	_query1     = _query1 + ",log.UPD_USER   "
	_query1     = _query1 + ",log.DEL_USER   "
	_query1     = _query1 + ",d_pro.prospect_i as pro_prospect_i "
	_query1     = _query1 + "from log "
	_query1     = _query1 + "left join D_PRO on (D_PRO.lngid = cast(dbo.UDF_intLngID(pk_data) as integer)) " 
	_query1     = _query1 + "where  table_name = 'd_pro' "
	_query1     = _query1 + "and log.pk_data = '4.3634;' "
	application.output("PI  log: "+ _query1)
	var _args   = null;
	var _dsLOG  = databaseManager.getDataSetByQuery("winxam", _query1, _args, -1);
	application.output("Anzahl records: "+_dsLOG.getMaxRowIndex())
	for (var i = 1; i <= _dsLOG.getMaxRowIndex(); i++) {
		_dsLOG.rowIndex = i;	
		// update fields
		_dsLOG.tabelle  = _dsLOG.table_name.substr(2);
		_dsLOG.sek_key  = 0;
		_dsLOG.ins_date = _dsLOG.event_time;
		_dsLOG.ins_user = 'Servoy';
		_dsLOG.upd_user = '_';
		_dsLOG.del_user = '_';
	}
}
6

I haven’t used it yet but take a look at the databaseManager.addTrackingInfo() method. It looks like this is designed for what you want to do but I’m not sure how and if it works correctly in this particular scenario.

http://wiki.servoy.com/display/public/DOCS/Database+Manager#DatabaseManager-addTrackingInfo

7

in general it seems to be nice.

But … log files are written from changes in 6 different Tables.
I want to update in the log-Table information from those tables in relation from the value of table_name.

My impession is that databaseManager.addTrackingInfo(‘new_field’,‘Hello’)
writes to a field a specific value. Maybe a value from variable.
I think I cannot solve my problem with that.

Perhaps you have the answer to my question in the post before.
I read all related log-records with “getDataSetByQuery”

Now I want to update the additional fields in the logtable.
I don’t know how.

Thanks Harro

8

Hi Harro,

The automatic log-entries by Servoy are also made outside of the eventmodel so I think there is no harm in using the rawSQL plugin here. There are so many ways to update data that it is kind of hard to tell you which one you should use. It also depends on the amount of modified records.

An efficient approach for updating multiple records is to fill a foundset with the records you want to update (databaseManager.getFoundset(…)) and then use the foundsetUpdater to loop through it and update the individual records. Like this:

var count = 0;
var fsUpdater = databaseManager.getFoundSetUpdater(foundset)
while(fsUpdater.next())
{
	fsUpdater.setColumn('myColumn', myValue);
}