Write modified MSSQL record back to FoxPro DBF table

Classic Servoy
1

Hello, I have a Servoy Framework program, that uses an MS SQL table. If I modify a record, I need to write back the modified record to the matching FoxPro DBF table. I tried it using the following function.:

function dc_save( _event, _triggerForm, _popup ) {
	
	var isSaved = -1,
		/** @type {QBSelect<db:/boss_db/kunden>} */ 
		customerDbfQuery,
		/** @type {JSFoundSet<db:/boss_db/kunden>} */ 
		customerDbfFs,
		/** @type {JSRecord<db:/boss_db/kunden>} */ 
		customerDbfRec,
		customerDbfFsSize = 0,
		customerRec,
		/** @type {String[]} */
		dataproviders = [ ],
		dataprovider = '',
		dataproviderIndex = 0;
	
	_super.dc_save( _event, _triggerForm, _popup );
	
//	if ( isSaved === 1 ) {
		customerDbfQuery = databaseManager.createSelect( 'db:/boss_db/kunden' );
		customerDbfQuery.where.add( customerDbfQuery.columns.id_nr.eq( id_nr ) );
		customerDbfFs = databaseManager.getFoundSet( customerDbfQuery );
		customerDbfFsSize = customerDbfFs.getSize( );
		
		databaseManager.startTransaction( );

		if ( customerDbfFsSize === 0 ) {
			
			// TODO
			
		} else {
			customerDbfRec = customerDbfFs.getRecord( 1 );
			
			application.output( customerDbfRec );
			
			customerRec = foundset.getSelectedRecord( );
			dataproviders = foundset.alldataproviders;
			
			for ( dataproviderIndex = 0; dataproviderIndex < dataproviders.length; dataproviderIndex++ ) {
				dataprovider = dataproviders[ dataproviderIndex ];
				// TODO filter out row ident
				if ( customerDbfRec[ dataprovider ] && customerRec[ dataprovider ] ) {
//					application.output( dataprovider + ': DBF value: ' + customerDbfRec[ dataprovider ] + ' - SQL value: ' + customerRec[ dataprovider ] );
					if ( customerDbfRec[ dataprovider ] !== customerRec[ dataprovider ] ) {
						customerDbfRec[ dataprovider ] = customerRec[ dataprovider ];	
					}
				}
			}
			
			application.output( customerDbfRec );

//			if ( !databaseManager.saveData( customerDbfRec ) ) {
//				application.output( 'Could not save back to DBF table.', LOGGINGLEVEL.ERROR );
//			}
			
			if ( !databaseManager.commitTransaction() ) {
				databaseManager.rollbackTransaction( );
			}
		}
		
//	}
	
	return isSaved;
}

databaseManager.saveData( customerDbfRec ) does not seem to work, because I get the ouput: “Could not save back to DBF table.”. databaseManager.rollbackTransaction( ) seems also not to work, because the changed values are not in the DBF table, but no databaseManager.rollbackTransaction( ) was triggered.

How can I save the new / changed record back to a FoxPro DBF table?

2

I also tried it directly using SQL.:

	sql = 'update kunden set firma = ? where id_nr = ?';
	var done = plugins.rawSQL.executeSQL( 'boss_db', 'kunden', sql, [ firma, id_nr ] );
	
	application.output( 'done: ' + done );

If I step over line var done = plugins.rawSQL.executeSQL( ‘boss_db’, ‘kunden’, sql, [ firma, id_nr ] );, the debugger seems to be at the end, but doesn’t get the application.output( 'done: ’ + done ).

Why I don’t get the output? Cause after stepping over var done = plugins.rawSQL.executeSQL( ‘boss_db’, ‘kunden’, sql, [ firma, id_nr ] );, I again can click the edit button in the Smart Client.

3

so you just copy over the change that is made in the mssql db over to a record in of the “same” dbf table?
that should work just fine.

what is the error in the log that is created by “could not save”

4

jcompagner:
so you just copy over the change that is made in the mssql db over to a record in of the “same” dbf table?
that should work just fine.

what is the error in the log that is created by “could not save”

Hello Johan, I already solved it. I use a newer DBF driver and now it seems to work. But could explain, why the debugger was suspended, I get no output and I can click again the “edit” button in Smart Client?