Batchprocessor sql error

[briese-it]

Hello,
I created a batchprocessor. Within the startup function I start some cronjobs. In this example the function "importPersDocs" was started.
The problem is, that the cronjobs are executed but on creating/saving new records I get an "roll back error" (...roll back...). Other functions to move files etc are working fine.
For testing I started the function "importPersDocs" within my solution and without batchprocessor and that's working fine.

I got the following errors in the server-log when batchprocessor is running:

Code: Select all

2015-08-21 10:50:00,315 ERROR [pool-7-thread-1] com.servoy.j2db.util.Debug - Error executing sql: insert into leda_doku (main_id, sub_id, p_nr, info, arch_dat, ch_dat, [uuid], original, endung, servoy_se, rec_del, created_on, modified_on, created_by_uid, modified_by_uid, created_by_name, modified_by_name) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) select scope_identity() with params: [4 ,type: java.lang.Integer, 18 ,type: java.lang.Integer, 9091 ,type: java.lang.Long, NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, 2015-08-21 10:50:00.222 ,type: java.sql.Timestamp, NullValue with type: 93 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, '081097CF-18B2-44A9-8314-8C0471159C2A' ,type: java.lang.String, '9091_0404.pdf' ,type: java.lang.String, 'pdf' ,type: java.lang.String, NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, NullValue with type: -7 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, 2015-08-21 10:50:00.222 ,type: java.sql.Timestamp, NullValue with type: 93 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, '' ,type: java.lang.String, NullValue with type: 1 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue] [823F1882-BFC8-4C0F-97DB-7FD280EC2358 br_boss_batchprocessor]
com.microsoft.sqlserver.jdbc.SQLServerException: Fehler beim Konvertieren einer Zeichenfolge in 'uniqueidentifier'.
   at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
   at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
   at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
   at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
   at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
   at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:332)
   at sun.reflect.GeneratedMethodAccessor39.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at com.servoy.j2db.datasource.Zg.invoke(Zg.java:10)
   at com.sun.proxy.$Proxy0.execute(Unknown Source)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
   at com.servoy.j2db.server.dataprocessing.Zw.Za(Zw.java:443)
   at com.servoy.j2db.server.dataprocessing.Zw.performUpdates(Zw.java:210)
   at com.servoy.j2db.dataprocessing.EditRecordList.stopEditing(EditRecordList.java:631)
   at com.servoy.j2db.dataprocessing.EditRecordList.stopEditing(EditRecordList.java:246)
   at com.servoy.j2db.dataprocessing.JSDatabaseManager.saveData(JSDatabaseManager.java:2548)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:158)
   at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:312)
   at org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:1774)
   at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:837)
   at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:158)
   at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:406)
   at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3204)
   at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:156)
   at com.servoy.j2db.scripting.ScriptEngine.executeFunction(ScriptEngine.java:628)
   at com.servoy.j2db.plugins.ClientPluginAccessProvider$MethodExecutor.run(ClientPluginAccessProvider.java:558)
   at com.servoy.j2db.server.headlessclient.SessionClient.invokeAndWait(SessionClient.java:1318)
   at com.servoy.j2db.server.headlessclient.SessionClient.doInvokeLater(SessionClient.java:1308)
   at com.servoy.j2db.ClientState.invokeLater(ClientState.java:1720)
   at com.servoy.j2db.plugins.ClientPluginAccessProvider$1.run(ClientPluginAccessProvider.java:468)
   at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
   at java.lang.Thread.run(Unknown Source)
2015-08-21 10:50:00,362 ERROR [pool-7-thread-1] com.servoy.j2db.util.Debug - Error executing sql: insert into leda_doku (main_id, sub_id, p_nr, info, arch_dat, ch_dat, [uuid], original, endung, servoy_se, rec_del, created_on, modified_on, created_by_uid, modified_by_uid, created_by_name, modified_by_name) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) select scope_identity() with params: [4 ,type: java.lang.Integer, 18 ,type: java.lang.Integer, 9091 ,type: java.lang.Long, NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, 2015-08-21 10:50:00.222 ,type: java.sql.Timestamp, NullValue with type: 93 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, '081097CF-18B2-44A9-8314-8C0471159C2A' ,type: java.lang.String, '9091_0404.pdf' ,type: java.lang.String, 'pdf' ,type: java.lang.String, NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, NullValue with type: -7 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, 2015-08-21 10:50:00.222 ,type: java.sql.Timestamp, NullValue with type: 93 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, '' ,type: java.lang.String, NullValue with type: 1 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue], script stacktrace:
   at br_boss_batchprocessor/scopes/brBossBatchprocesser$process/importPersDocs:867 (importPersDocs)
[823F1882-BFC8-4C0F-97DB-7FD280EC2358 br_boss_batchprocessor]
2015-08-21 10:50:00,378 ERROR [pool-7-thread-1] com.servoy.j2db.util.Debug - Error executing sql: null with params: <null> [823F1882-BFC8-4C0F-97DB-7FD280EC2358 br_boss_batchprocessor]
java.sql.SQLException: must rollback
   at com.servoy.j2db.datasource.Zo.startTransaction(Zo.java:118)
   at com.servoy.j2db.server.dataprocessing.Zw.Za(Zw.java:178)
   at com.servoy.j2db.server.dataprocessing.Zw.performUpdates(Zw.java:210)
   at com.servoy.j2db.dataprocessing.EditRecordList.stopEditing(EditRecordList.java:631)
   at com.servoy.j2db.dataprocessing.EditRecordList.stopEditing(EditRecordList.java:234)
   at com.servoy.j2db.dataprocessing.FoundSetManager.commitTransaction(FoundSetManager.java:1959)
   at com.servoy.j2db.dataprocessing.JSDatabaseManager.js_commitTransaction(JSDatabaseManager.java:2903)
   at com.servoy.j2db.dataprocessing.JSDatabaseManager.js_commitTransaction(JSDatabaseManager.java:2916)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:158)
   at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:312)
   at org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:1774)
   at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:837)
   at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:158)
   at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:406)
   at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3204)
   at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:156)
   at com.servoy.j2db.scripting.ScriptEngine.executeFunction(ScriptEngine.java:628)
   at com.servoy.j2db.plugins.ClientPluginAccessProvider$MethodExecutor.run(ClientPluginAccessProvider.java:558)
   at com.servoy.j2db.server.headlessclient.SessionClient.invokeAndWait(SessionClient.java:1318)
   at com.servoy.j2db.server.headlessclient.SessionClient.doInvokeLater(SessionClient.java:1308)
   at com.servoy.j2db.ClientState.invokeLater(ClientState.java:1720)
   at com.servoy.j2db.plugins.ClientPluginAccessProvider$1.run(ClientPluginAccessProvider.java:468)
   at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
   at java.lang.Thread.run(Unknown Source)
2015-08-21 10:50:00,378 ERROR [pool-7-thread-1] com.servoy.j2db.util.Debug - Error executing sql: null with params: <null>, script stacktrace:
   at br_boss_batchprocessor/scopes/brBossBatchprocesser$process/importPersDocs:880 (importPersDocs)
[823F1882-BFC8-4C0F-97DB-7FD280EC2358 br_boss_batchprocessor]
2015-08-21 10:55:00,038 ERROR [pool-7-thread-1] com.servoy.j2db.util.Debug - Throwable [823F1882-BFC8-4C0F-97DB-7FD280EC2358 br_boss_batchprocessor]
com.servoy.j2db.persistence.RepositoryException: java.sql.SQLException: must rollback must rollback

   at com.servoy.j2db.server.dataprocessing.Zw.endTransactions(Zw.java:1007)
   at com.servoy.j2db.dataprocessing.GlobalTransaction.commit(GlobalTransaction.java:192)
   at com.servoy.j2db.dataprocessing.FoundSetManager.commitTransaction(FoundSetManager.java:1963)
   at com.servoy.j2db.dataprocessing.JSDatabaseManager.js_commitTransaction(JSDatabaseManager.java:2903)
   at com.servoy.j2db.dataprocessing.JSDatabaseManager.js_commitTransaction(JSDatabaseManager.java:2916)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:158)
   at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:312)
   at org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:1774)
   at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:837)
   at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:158)
   at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:406)
   at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3204)
   at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:156)
   at com.servoy.j2db.scripting.ScriptEngine.executeFunction(ScriptEngine.java:628)
   at com.servoy.j2db.plugins.ClientPluginAccessProvider$MethodExecutor.run(ClientPluginAccessProvider.java:558)
   at com.servoy.j2db.server.headlessclient.SessionClient.invokeAndWait(SessionClient.java:1318)
   at com.servoy.j2db.server.headlessclient.SessionClient.doInvokeLater(SessionClient.java:1308)
   at com.servoy.j2db.ClientState.invokeLater(ClientState.java:1720)
   at com.servoy.j2db.plugins.ClientPluginAccessProvider$1.run(ClientPluginAccessProvider.java:468)
   at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
   at java.lang.Thread.run(Unknown Source)
2015-08-21 10:55:00,038 ERROR [pool-7-thread-1] com.servoy.j2db.util.Debug - Throwable, script stacktrace:
   at br_boss_batchprocessor/scopes/brBossBatchprocesser$process/importPersDocs:880 (importPersDocs)
[823F1882-BFC8-4C0F-97DB-7FD280EC2358 br_boss_batchprocessor]



Startup function for batchProcessor:

Code: Select all

...
scheduler.addCronJob( 'Import_Pers_Docs', "0 0/5 * * * ?", importPersDocs );
...

function "importPersDocs" which creates the sql error:

Code: Select all

var filesArray = plugins.file.getFolderContents(scopes.bossGlobals_serverPaths.crewPersDocsImportFilePath);
if(filesArray.length >0) {
   var fsCrewPersDoku = datasources.db.boss_sql.leda_doku.getFoundSet();
   var fsCrewPersDokuTypes = datasources.db.boss_sql.leda_doku_sub.getFoundSet();
   fsCrewPersDokuTypes.loadAllRecords();
   fsCrewPersDoku.loadAllRecords();
   var fsPersDaten = datasources.db.boss_sql.pdaten.getFoundSet();
   fsPersDaten.loadAllRecords();
   
   databaseManager.startTransaction();
   for (var index = 0; index < filesArray.length; index++) {
      var data = filesArray[index];
      if(data.canRead()) {
         var dataName = data.getName();
         var endung = scopes.bossGlobals$fileOperations.getFileExtension(dataName);
         var endPNr = dataName.search("_");
         if (endPNr != -1) {
            var importPNr = dataName.substr(0,endPNr);
            var importType = dataName.substr(endPNr+1,dataName.length-endung.length-endPNr-2);
            var searchAdditional = importType.search("_");
            if (searchAdditional != -1) {
               importType = importType.substr(0,searchAdditional);
            }
            var searchAdditional2 = importType.search("[\(]");
            if (searchAdditional2 != -1) {
               importType = importType.substr(0,searchAdditional2);
            }
            var searchAdditional3 = importType.search("-");
            if (searchAdditional3 != -1) {
               importType = importType.substr(0,searchAdditional3);
            }
            var searchAdditional4 = importType.search(" ");
            if (searchAdditional4 != -1) {
               importType = importType.substr(0,searchAdditional4);
            }
            if(importType.length!=4) { continue;};
            if(fsCrewPersDokuTypes.find()) {
               fsCrewPersDokuTypes.docno = importType;
               fsCrewPersDokuTypes.search()
               if(fsCrewPersDokuTypes.getSize()>0) {
                  if (fsPersDaten.find()) {
                     fsPersDaten.p_nr = importPNr;
                     fsPersDaten.search();
                     if (fsPersDaten && fsPersDaten.getSize()>0) {
                        fsCrewPersDoku.newRecord();
                        fsCrewPersDoku.p_nr       = utils.stringToNumber(importPNr);
                        fsCrewPersDoku.main_id       = fsCrewPersDokuTypes.main_id_nr;
                        fsCrewPersDoku.sub_id       = fsCrewPersDokuTypes.id_nr;
                        fsCrewPersDoku.arch_dat    = new Date( );
                        fsCrewPersDoku.leda_doku_to_leda_doku_status.newRecord()
                        fsCrewPersDoku.leda_doku_to_leda_doku_status.doc_id_nr    = fsCrewPersDoku.id_nr;
                        fsCrewPersDoku.leda_doku_to_leda_doku_status.datum       = new Date( );
                        fsCrewPersDoku.leda_doku_to_leda_doku_status.status    = 1;
                        fsCrewPersDoku.original    = dataName;
                        fsCrewPersDoku.endung      = endung;
                        // DocImport
                        var sourceFile = scopes.bossGlobals_serverPaths.crewPersDocsImportFilePath + dataName;
                        var newServerDirectory = scopes.bossGlobals_serverPaths.crewingDocuImportPath + "\\"  + importPNr + "\\";
                        var newFileName = fsCrewPersDoku.uuid.toString();
                        plugins.file.moveFile(sourceFile,newServerDirectory + newFileName + "." + endung)
                        databaseManager.saveData(fsCrewPersDoku.getSelectedRecord());
                     } else {
                        application.output("P_Nr: " + importPNr + " not found in database");
                     }
                  }   
               } else {
                  application.output("Type: " + importType + " not found in database")
               }
            }                     
         }
      }
   }
   databaseManager.commitTransaction();
}

[mboegem]

com.microsoft.sqlserver.jdbc.SQLServerException: Fehler beim Konvertieren einer Zeichenfolge in 'uniqueidentifier'.

Seems like a pretty clear description.
You have the query

Code: Select all

insert into leda_doku (main_id, sub_id, p_nr, info, arch_dat, ch_dat, [uuid], original, endung, servoy_se, rec_del, created_on, modified_on, created_by_uid, modified_by_uid, created_by_name, modified_by_name) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)


You have the arguments

Code: Select all

[4 ,type: java.lang.Integer, 18 ,type: java.lang.Integer, 9091 ,type: java.lang.Long, NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, 2015-08-21 10:50:00.222 ,type: java.sql.Timestamp, NullValue with type: 93 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, '081097CF-18B2-44A9-8314-8C0471159C2A' ,type: java.lang.String, '9091_0404.pdf' ,type: java.lang.String, 'pdf' ,type: java.lang.String, NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, NullValue with type: -7 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, 2015-08-21 10:50:00.222 ,type: java.sql.Timestamp, NullValue with type: 93 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, '' ,type: java.lang.String, NullValue with type: 1 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue]


Did you try to execute the query in a sql tool connected to the same database?

Also look whether the argument data types match your column types

[briese-it]

Hey Marc,
for me it's not clear because it works when I start the same function, with the same data, on a form button in my solution. Therefore I think that something is going wrong within the batch processor.

[mboegem]

Hi Michael,

Sounds like you're passing correct arguments from your solution and not from the batch-processor.
So where is that difference coming from?

Also, pls test the query directly on sql-server with the arguments provided and see if that also fails.
Fail = difference in arguments and you need to find out what (and why) that difference exists between batch-processor/solution
Pass = Servoy headless client is doing weird things with the query, sounds like a bug.

This is really about eliminating possibilities, hard job but that's part of programming

[briese-it]

Hey,
the only difference is that there is no user and user-uuid given in the headless client for the batch-processor. Maybe that's the problem because I use the automatic system values for creation user uid etc. on the tables .
I have a user and password to start the batch processor but in the "logged in client list" on the server is no user and user-id shown. See attached screenshot.
Do I have to authenticate this user within my batchprocessor function to get the user-uuid?

[mboegem]

I have a user and password to start the batch processor but in the "logged in client list" on the server is no user and user-id shown.

Do you have the 'mustAuthenticate' property of the batch solution checked?

Do I have to authenticate this user within my batchprocessor function to get the user-uuid?

AFAIK there's no need to perform additional steps.

[briese-it]

Hello,
must authenticate is not checked for the batch solution because it's a module of our main solution which has it activated.
Do you think I have to activate it, too?

[mboegem]

must authenticate is not checked for the batch solution because it's a module of our main solution which has it activated.

when you start the batch solution as a headless client it's not a module anymore, but the main solution.
I'm not sure if it will even use the username/password when you don't have 'mustAuthenticate' set to true.

When using it as a module the 'mustAuthenticate' is ignored.

Try and see if this will solve your problem.

[briese-it]

Hey Marc.
That's it.
Activated authentication for the batchprocessor-module and everything is fine. Records are created.
But it's a little bit cunfusing because moving and deleting files was possible without authentication. Only database related things are not allowed.

Many thankks

[mboegem]

Great, 1 issue down!