MySQL Primary key without squence insert error

Questions and answers regarding general SQL and backend databases

MySQL Primary key without squence insert error

Postby v.weimer » Wed Jan 17, 2018 9:19 am

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?
v.weimer
 
Posts: 14
Joined: Mon Sep 05, 2016 8:04 am

Re: MySQL Primary key without squence insert error

Postby jcompagner » Wed Jan 17, 2018 2:28 pm

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?
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8829
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: MySQL Primary key without squence insert error

Postby v.weimer » Thu Jan 18, 2018 8:54 am

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.

Code: Select all
/*
* @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;
}
v.weimer
 
Posts: 14
Joined: Mon Sep 05, 2016 8:04 am

Re: MySQL Primary key without squence insert error

Postby v.weimer » Thu Jan 18, 2018 12:31 pm

Thanks for your hint. I gues I found it.

I added
Code: Select all
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?

Code: Select all
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;
}
v.weimer
 
Posts: 14
Joined: Mon Sep 05, 2016 8:04 am

Re: MySQL Primary key without squence insert error

Postby jcompagner » Mon Jan 22, 2018 2:27 pm

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
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8829
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 9 guests

cron