Batchprocessor sql error

briese-it · August 21, 2015, 9:01am

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:

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:

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

function “importPersDocs” which creates the sql error:

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 · August 21, 2015, 12:14pm

briese-it:
com.microsoft.sqlserver.jdbc.SQLServerException: Fehler beim Konvertieren einer Zeichenfolge in ‘uniqueidentifier’.

Seems like a pretty clear description.
You have the query

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

[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 · August 21, 2015, 12:27pm

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 · August 21, 2015, 1:57pm

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 · August 24, 2015, 6:44am

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 · August 24, 2015, 8:26am

briese-it:
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?

briese-it:
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 · August 24, 2015, 9:07am

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 · August 24, 2015, 9:48am

briese-it:
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 · August 24, 2015, 10:39am

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 · August 24, 2015, 11:35am

Great, 1 issue down! :-)

Topic		Replies	Views
Help with server log error Classic Servoy	0	1380	March 3, 2009
Batch processor log file garbage on startup Classic Servoy	3	3712	December 18, 2006
Error executing sql: insert into client_stats Questions	4	4274	April 14, 2023
java.sql.SQLException: must rollback Classic Servoy	10	5856	September 25, 2010
Trying to executeStoredProcedure on VFP Classic Servoy	9	14392	June 28, 2014

Batchprocessor sql error

Related topics