MySQL Primary key without squence insert error

Classic Servoy
1

Hello everybody,

scenario:
I have two databases with the same tables A and B. Only differents is that the primary key in table A is a database identity and in table B the primary key has no sequence. I set the sequence type of table B to none of the primary key column.

Aim:
What I would like to do is to copy the data from table A to table B with the exact same primary keys. This job runs once a day and the data rows in Table A are sequencial but could have gaps in the squence due to deleting and reentering data.

Error:
ERROR com.servoy.j2db.util.Debug - Error executing sql: insert into B (id, txt) values (?, ?) with params: [1 ,type: java.lang.Integer, ‘Test’ ,type: java.lang.String]
I get this error for each new record I try to insert.

Information:
When I insert that SQL with a SQL-Tool ist simply works.
When I use a different sequnecy type it seams to work, but not realy the new record does not have the same primary key value. It gets it from the sequence.

Anything I can do?

2

what error is it that you really get?
Because from Servoy wise it shouldn’t matter what sequence is on it or not
as long as you give the value your self which you seem to do

How do you really insert that code?

3

Hi,

this is the function I use to copy all fields from source to destnation and try to save the destination.
It should insert a new record, if needed or simply update an exist one.

/*
 * @param pQuelle {JSFoundset} source foundset
 * @param pZiel {JSFoundset} destination foundset
*/
function syncAlleDatensatzeVonNach(pQuelle, pZiel) {
	var vErgebnisliste = [];
	pQuelle.loadAllRecords();
        // itterate over the source Foundset. pQuelle
	pQuelle.forEach(function(pQuellelement, pIndex) {
			var vPkQuellelement = pQuellelement.getPKs();
                        // Try to load the destination record by primary key.
			if (pZiel.loadRecords(vPkQuellelement)) {
                                // create new record, if needed
				var vZeilelement = pZiel.getSelectedRecord();
				if (!vZeilelement) {
					vZeilelement = pZiel.getRecord(pZiel.newRecord());
				}
                                // copy all fields from source to destination. This includes the primary key to, because both tables do have the same solumns
				if (databaseManager.copyMatchingFields(pQuellelement,vZeilelement)) {
                                        // Save data to the database explicit.
					if (!databaseManager.saveData(vZeilelement)) {						
						// Error - database save operation not possible
						vErgebnisliste.push({
							pk : vPkQuellelement,
							fehler : AUFZAEHLUNG_SYNC_FEHLER.SPEICHERN
						});
					}
				} else {
					// Error - Field copy not possible
					vErgebnisliste.push({	
						pk : vPkQuellelement,
						fehler : AUFZAEHLUNG_SYNC_FEHLER.FELDER_KOPIEREN
					});
				}
			}
	});
	application.output(vErgebnisliste);

	return vErgebnisliste;
}
4

Thanks for your hint. I gues I found it.

I added

databaseManager.getTable(pQuelle).getRowIdentifierColumnNames().forEach(function(pEle, pIdx) {
						vZeilelement[pEle] = pQuellelement[pEle];
					});

below to set the pk explicitly after copyMatchingFileds. (See below) This seams to work. Is this behavior by design?

function syncAlleDatensatzeVonNach(pQuelle, pZiel) {
	var vErgebnisliste = [];
	pQuelle.loadAllRecords();
	pQuelle.forEach(function(pQuellelement, pIndex) {
			var vPkQuellelement = pQuellelement.getPKs();			
			if (pZiel.loadRecords(vPkQuellelement)) {
				var vZeilelement = pZiel.getSelectedRecord();
				if (!vZeilelement) {
					vZeilelement = pZiel.getRecord(pZiel.newRecord());
				}
				if (databaseManager.copyMatchingFields(pQuellelement,vZeilelement)) {
                                        // { New part 
					databaseManager.getTable(pQuelle).getRowIdentifierColumnNames().forEach(function(pEle, pIdx) {
						vZeilelement[pEle] = pQuellelement[pEle];
					});					
                                        // } New part 
					if (!databaseManager.saveData(vZeilelement)) {						
						// fehler beim Speichern
						vErgebnisliste.push({
							pk : vPkQuellelement,
							fehler : AUFZAEHLUNG_SYNC_FEHLER.SPEICHERN
						});
					}
				} else {
					// fehler beim Kopieren der felder
					vErgebnisliste.push({	
						pk : vPkQuellelement,
						fehler : AUFZAEHLUNG_SYNC_FEHLER.FELDER_KOPIEREN
					});
				}
			}
	});
	application.output(vErgebnisliste);

	return vErgebnisliste;
}
5

As far as i can see is that IF the destination record already has a value set in the PK (row_ident column(s)) then we will not overwrite it.
That is a feature…
So not sure what the destination record value is before you call copyMatchingFields