DatabaseManager.ExecuteStoredProcedure

stephan · June 22, 2004, 9:24am

I’m having trouble passing a date to a store procedure in Oracle.

This is what I do in a java class:

CallableStatement call = conn.prepareCall("{call recalc(?,?)}");
call.setInt(1,8);
call.setTimestamp(2,new java.sql.Timestamp(new java.util.Date().getTime()));
call.executeQuery();

And this is the equivalent I’ve tried in Servoy:

var proc = "{call recalc(?,?)}";
var args = new Array(2);
args[0] = 8; // an int
var d = new Date();
args[1] = d.getTime(); // a date
var argsInput = new Array(2);
argsInput[0] = 1; // input
argsInput[1] = 1; // input
databaseManager.executeStoredProcedure(controller.getServerName(), proc, args,argsInput, 0);

The procedure is defined as:

CREATE OR REPLACE procedure recalc(justAnInt INT,justADate DATE) IS ...

The problem lies with the second argument. This is what I get when (as above) I try : args[1] = d.getTime(); // a date

Any idea on how to make this work?

java.sql.SQLException: Invalid column type
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:222)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:285)
        at oracle.jdbc.driver.OracleStatement.get_internal_type(OracleStatement.java:5465)
        at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:2
46)
        at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:3
71)
        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 com.servoy.j2db.persistence.datasource.n.invoke(Unknown Source)
        at $Proxy2.registerOutParameter(Unknown Source)
        at org.apache.commons.dbcp.DelegatingCallableStatement.registerOutParameter(DelegatingCallableSta
tement.java:164)
        at org.apache.commons.dbcp.DelegatingCallableStatement.registerOutParameter(DelegatingCallableSta
tement.java:164)
        at org.apache.commons.dbcp.DelegatingCallableStatement.registerOutParameter(DelegatingCallableSta
tement.java:164)
        at com.servoy.j2db.dataprocessing.SQLEngine.executeStoredProcedure(Unknown Source)
        at com.servoy.j2db.dataprocessing.JSDatabaseManager.js_executeStoredProcedure(Unknown Source)
        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.NativeJavaMethod.call(NativeJavaMethod.java:289)
        at org.mozilla.javascript.ScriptRuntime.call(ScriptRuntime.java:1232)
        at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:1940)
        at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:94)
        at com.servoy.j2db.scripting.e.call(Unknown Source)
        at com.servoy.j2db.develop.debugger.l.a(Unknown Source)
        at com.servoy.j2db.develop.debugger.l.executeFunction(Unknown Source)
        at com.servoy.j2db.FormPanel.a(Unknown Source)
        at com.servoy.j2db.FormPanel.actionPerformed(Unknown Source)
        at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
        at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(Unknown Source)
        at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
        at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
        at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
        at java.awt.Component.processMouseEvent(Unknown Source)
        at java.awt.Component.processEvent(Unknown Source)
        at java.awt.Container.processEvent(Unknown Source)
        at java.awt.Component.dispatchEventImpl(Unknown Source)
        at java.awt.Container.dispatchEventImpl(Unknown Source)
        at java.awt.Component.dispatchEvent(Unknown Source)
        at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
        at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
        at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
        at java.awt.Container.dispatchEventImpl(Unknown Source)
        at java.awt.Window.dispatchEventImpl(Unknown Source)
        at java.awt.Component.dispatchEvent(Unknown Source)
        at java.awt.EventQueue.dispatchEvent(Unknown Source)
        at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.run(Unknown Source)
java.sql.SQLException: Invalid column type
java.sql.SQLException: Invalid column type
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:222)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:285)
        at oracle.jdbc.driver.OracleStatement.get_internal_type(OracleStatement.java:5465)
        at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:2
46)
        at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:3
71)
        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 com.servoy.j2db.persistence.datasource.n.invoke(Unknown Source)
        at $Proxy2.registerOutParameter(Unknown Source)
        at org.apache.commons.dbcp.DelegatingCallableStatement.registerOutParameter(DelegatingCallableSta
tement.java:164)
        at org.apache.commons.dbcp.DelegatingCallableStatement.registerOutParameter(DelegatingCallableSta
tement.java:164)
        at org.apache.commons.dbcp.DelegatingCallableStatement.registerOutParameter(DelegatingCallableSta
tement.java:164)
        at com.servoy.j2db.dataprocessing.SQLEngine.executeStoredProcedure(Unknown Source)
        at com.servoy.j2db.dataprocessing.JSDatabaseManager.js_executeStoredProcedure(Unknown Source)
        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.NativeJavaMethod.call(NativeJavaMethod.java:289)
        at org.mozilla.javascript.ScriptRuntime.call(ScriptRuntime.java:1232)
        at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:1940)
        at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:94)
        at com.servoy.j2db.scripting.e.call(Unknown Source)
        at com.servoy.j2db.develop.debugger.l.a(Unknown Source)
        at com.servoy.j2db.develop.debugger.l.executeFunction(Unknown Source)
        at com.servoy.j2db.FormPanel.a(Unknown Source)
        at com.servoy.j2db.FormPanel.actionPerformed(Unknown Source)
        at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
        at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(Unknown Source)
        at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
        at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
        at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
        at java.awt.Component.processMouseEvent(Unknown Source)
        at java.awt.Component.processEvent(Unknown Source)
        at java.awt.Container.processEvent(Unknown Source)
        at java.awt.Component.dispatchEventImpl(Unknown Source)
        at java.awt.Container.dispatchEventImpl(Unknown Source)
        at java.awt.Component.dispatchEvent(Unknown Source)
        at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
        at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
        at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
        at java.awt.Container.dispatchEventImpl(Unknown Source)
        at java.awt.Window.dispatchEventImpl(Unknown Source)
        at java.awt.Component.dispatchEvent(Unknown Source)
        at java.awt.EventQueue.dispatchEvent(Unknown Source)
        at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.run(Unknown Source)
com.servoy.j2db.persistence.RepositoryException: java.sql.SQLException: Invalid column type Invalid colum
n type
com.servoy.j2db.persistence.RepositoryException: java.sql.SQLException: Invalid column type Invalid colum
n type
        at com.servoy.j2db.dataprocessing.SQLEngine.executeStoredProcedure(Unknown Source)
        at com.servoy.j2db.dataprocessing.JSDatabaseManager.js_executeStoredProcedure(Unknown Source)
        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.NativeJavaMethod.call(NativeJavaMethod.java:289)
        at org.mozilla.javascript.ScriptRuntime.call(ScriptRuntime.java:1232)
        at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:1940)
        at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:94)
        at com.servoy.j2db.scripting.e.call(Unknown Source)
        at com.servoy.j2db.develop.debugger.l.a(Unknown Source)
        at com.servoy.j2db.develop.debugger.l.executeFunction(Unknown Source)
        at com.servoy.j2db.FormPanel.a(Unknown Source)
        at com.servoy.j2db.FormPanel.actionPerformed(Unknown Source)
        at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
        at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(Unknown Source)
        at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
        at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
        at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
        at java.awt.Component.processMouseEvent(Unknown Source)
        at java.awt.Component.processEvent(Unknown Source)
        at java.awt.Container.processEvent(Unknown Source)
        at java.awt.Component.dispatchEventImpl(Unknown Source)
        at java.awt.Container.dispatchEventImpl(Unknown Source)
        at java.awt.Component.dispatchEvent(Unknown Source)
        at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
        at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
        at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
        at java.awt.Container.dispatchEventImpl(Unknown Source)
        at java.awt.Window.dispatchEventImpl(Unknown Source)
        at java.awt.Component.dispatchEvent(Unknown Source)
        at java.awt.EventQueue.dispatchEvent(Unknown Source)
        at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.run(Unknown Source)

I’ve tried other things (try to replace d.getTime() by other things) to try to solve the problem but I get a ClassCastException:

java.lang.ClassCastException
        at com.servoy.j2db.dataprocessing.SQLEngine.executeStoredProcedure(Unknown Source)
        at com.servoy.j2db.dataprocessing.JSDatabaseManager.js_executeStoredProcedure(Unknown Source)
        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.NativeJavaMethod.call(NativeJavaMethod.java:289)
        at org.mozilla.javascript.ScriptRuntime.call(ScriptRuntime.java:1232)
        at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:1940)
        at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:94)
        at com.servoy.j2db.scripting.e.call(Unknown Source)
        at com.servoy.j2db.develop.debugger.l.a(Unknown Source)
        at com.servoy.j2db.develop.debugger.l.executeFunction(Unknown Source)
        at com.servoy.j2db.FormPanel.a(Unknown Source)
        at com.servoy.j2db.FormPanel.actionPerformed(Unknown Source)
        at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
        at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(Unknown Source)
        at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
        at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
        at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
        at java.awt.Component.processMouseEvent(Unknown Source)
        at java.awt.Component.processEvent(Unknown Source)
        at java.awt.Container.processEvent(Unknown Source)
        at java.awt.Component.dispatchEventImpl(Unknown Source)
        at java.awt.Container.dispatchEventImpl(Unknown Source)
        at java.awt.Component.dispatchEvent(Unknown Source)
        at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
        at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
        at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
        at java.awt.Container.dispatchEventImpl(Unknown Source)
        at java.awt.Window.dispatchEventImpl(Unknown Source)
        at java.awt.Component.dispatchEvent(Unknown Source)
        at java.awt.EventQueue.dispatchEvent(Unknown Source)
        at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.run(Unknown Source)
java.lang.ClassCastException
java.lang.ClassCastException
        at com.servoy.j2db.dataprocessing.SQLEngine.executeStoredProcedure(Unknown Source)
        at com.servoy.j2db.dataprocessing.JSDatabaseManager.js_executeStoredProcedure(Unknown Source)
        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.NativeJavaMethod.call(NativeJavaMethod.java:289)
        at org.mozilla.javascript.ScriptRuntime.call(ScriptRuntime.java:1232)
        at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:1940)
        at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:94)
        at com.servoy.j2db.scripting.e.call(Unknown Source)
        at com.servoy.j2db.develop.debugger.l.a(Unknown Source)
        at com.servoy.j2db.develop.debugger.l.executeFunction(Unknown Source)
        at com.servoy.j2db.FormPanel.a(Unknown Source)
        at com.servoy.j2db.FormPanel.actionPerformed(Unknown Source)
        at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
        at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(Unknown Source)
        at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
        at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
        at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
        at java.awt.Component.processMouseEvent(Unknown Source)
        at java.awt.Component.processEvent(Unknown Source)
        at java.awt.Container.processEvent(Unknown Source)
        at java.awt.Component.dispatchEventImpl(Unknown Source)
        at java.awt.Container.dispatchEventImpl(Unknown Source)
        at java.awt.Component.dispatchEvent(Unknown Source)
        at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
        at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
        at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
        at java.awt.Container.dispatchEventImpl(Unknown Source)
        at java.awt.Window.dispatchEventImpl(Unknown Source)
        at java.awt.Component.dispatchEvent(Unknown Source)
        at java.awt.EventQueue.dispatchEvent(Unknown Source)
        at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.run(Unknown Source)
com.servoy.j2db.persistence.RepositoryException: java.lang.ClassCastException null
com.servoy.j2db.persistence.RepositoryException: java.lang.ClassCastException null
        at com.servoy.j2db.dataprocessing.SQLEngine.executeStoredProcedure(Unknown Source)
        at com.servoy.j2db.dataprocessing.JSDatabaseManager.js_executeStoredProcedure(Unknown Source)
        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.NativeJavaMethod.call(NativeJavaMethod.java:289)
        at org.mozilla.javascript.ScriptRuntime.call(ScriptRuntime.java:1232)
        at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:1940)
        at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:94)
        at com.servoy.j2db.scripting.e.call(Unknown Source)
        at com.servoy.j2db.develop.debugger.l.a(Unknown Source)
        at com.servoy.j2db.develop.debugger.l.executeFunction(Unknown Source)
        at com.servoy.j2db.FormPanel.a(Unknown Source)
        at com.servoy.j2db.FormPanel.actionPerformed(Unknown Source)
        at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
        at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(Unknown Source)
        at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
        at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
        at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
        at java.awt.Component.processMouseEvent(Unknown Source)
        at java.awt.Component.processEvent(Unknown Source)
        at java.awt.Container.processEvent(Unknown Source)
        at java.awt.Component.dispatchEventImpl(Unknown Source)
        at java.awt.Container.dispatchEventImpl(Unknown Source)
        at java.awt.Component.dispatchEvent(Unknown Source)
        at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
        at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
        at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
        at java.awt.Container.dispatchEventImpl(Unknown Source)
        at java.awt.Window.dispatchEventImpl(Unknown Source)
        at java.awt.Component.dispatchEvent(Unknown Source)
        at java.awt.EventQueue.dispatchEvent(Unknown Source)
        at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.run(Unknown Source)

stephan · June 22, 2004, 10:32am

(please read my post above to understand what I’m talking about)

Well, right now I’m not so sure that’s the second argument that’s causing the problem.

I’ve modified the store procedure to receive just one argument (an int) and I still get :
java.sql.SQLException: Invalid column type
or
java.lang.NullPointerException
at com.servoy.j2db.dataprocessing.SQLEngine.executeStoredProcedure(Unknown Source)
at com.servoy.j2db.dataprocessing.JSDatabaseManager.js_executeStoredProcedure(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

(The error depends on the attemp made)

In (unsuccesfull) attemps to make the damn thing work I’ve tried the following:

var proc = “{call recalc(?)}”;
var args = new Array(1);
args[0] = 19;
var argsInput = new Array(1);
argsInput[0] = java.sql.Types.INTEGER;
databaseManager.executeStoredProcedure(controller.getName(), proc, args,argsInput, 0);

var proc = “{call recalc(?)}”;
var args = new Array(1);
args[0] = 19;
var argsInput = new Array(1);
argsInput[0] = 4;
databaseManager.executeStoredProcedure(controller.getName(), proc, args,argsInput, 0);

var proc = “{call recalc(?)}”;
var args = new Array(1);
args[0] = 19;
var argsInput = new Array(1);
argsInput[0] = java.sql.Types.NUMERIC;
databaseManager.executeStoredProcedure(controller.getName(), proc, args,argsInput, 0);

var proc = “{call recalc(?)}”;
var args = new Array(1);
args[0] = 19;
var argsInput = new Array(1);
argsInput[0] = 2;
databaseManager.executeStoredProcedure(controller.getName(), proc, args,argsInput, 0);

var proc = “{call recalc(?)}”;
var args = new Array(1);
args[0] = 19;
var argsInput = new Array(1);
argsInput[0] = 1;
databaseManager.executeStoredProcedure(controller.getName(), proc, args,argsInput, 0);

Nothing work.
From a java class I have no problem executing the store procedure (this one and the one in the previous post).

jcompagner · June 28, 2004, 12:21pm

stephan
as far as i can see you do one thing wrong.

you do this:

var proc = “{call recalc(?)}”;
var args = new Array(1);
args[0] = 19;
var argsInput = new Array(1);
argsInput[0] = 2;
databaseManager.executeStoredProcedure(controller.getName(), proc, args,argsInput, 0);

but see a view post above you and you see that what you do there is wrong:

//define the types and direction, in this case a 0 for input data
var typesArray = new Array();
typesArray[0]=0;

The inOutArray doesn’t specify the TYPE of the parameter!! It does say what direction the param has.. So 0 is to the database (Input param) and 1 is from the database (output param)
The args array is then used what the input value is
like:
args[0] = ‘stephan’;
or if the arg is a output param then it is used for the type:
args[1] = java.sql.Types.NUMERIC;

stephan · June 28, 2004, 1:33pm

I think I’ve tried that too. Anyway I’ve tried it again.

This is the last thing I’ve tried (and I hope that the syntax is correct):

The store procedure definition is:

CREATE OR REPLACE procedure recalc(anId INT) IS ...

and the script in Servoy is:

var proc="{call recalc(?)}"; 
var args=new Array(1);
args[0]=19;
var typesArray=new Array(1);
typesArray[0]=0;
databaseManager.executeStoredProcedure(controller.getName(), proc, args,typesArray, 0);

I still get the NullPointerException in the log.
Do you see anything wrong this time?

java.lang.NullPointerException
        at com.servoy.j2db.dataprocessing.SQLEngine.executeStoredProcedure(Unknown Source)
        at com.servoy.j2db.dataprocessing.JSDatabaseManager.js_executeStoredProcedure(Unknown Source)
        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.NativeJavaMethod.call(NativeJavaMethod.java:289)
        at org.mozilla.javascript.ScriptRuntime.call(ScriptRuntime.java:1232)
        at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:1940)
        at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:94)
        at com.servoy.j2db.scripting.e.call(Unknown Source)
        at com.servoy.j2db.develop.debugger.l.a(Unknown Source)
.................

jcompagner · June 28, 2004, 6:40pm

i see you do controller.getName()
That is the form name. You have to specify the servername so you must do:

controller.getServerName()

stephan · June 29, 2004, 7:45am

I knew that I was doing something wrong! Thank you, that solved this problem!

I have another problem with passing a DATE.

How do you pass a date to oracle?

stephan · June 30, 2004, 2:19pm

I still don’t know how to pass a DATE to a store procedure in oracle but I’ve found an alternative.

I’m passing a VARCHAR2 to the store procedure and then I convert the string into a date.

For example in the select inside the proc I can do:
to_date(dateArg,‘dd/mm/yyyy hh24:mi:ss’)
or
to_date(concat(dateArg,’ 00:00:00’),‘dd/mm/yyyy hh24:mi:ss’)

jcompagner · July 1, 2004, 3:51pm

can you give me an example of a stored procedure that you have in oracle and how you are calling that one in servoy that uses a Date?

I think i know the problem (We have to send a java.sql.Date) instead of a ‘normal’ date. The problem is that you can’t test this right now because there is a bug in the javascript library that i need to fix first.

stephan · July 1, 2004, 6:08pm

Example of a stored procedure

CREATE TABLE USUENVIOS.test
(
	id_test	INT,
	a_date	DATE,
	CONSTRAINT PK_test PRIMARY KEY (id_test )
);

insert into test values(1,sysdate-3);
insert into test values(2,sysdate-2);
insert into test values(3,sysdate-1);

CREATE OR REPLACE procedure addDate(id INT, a_date_with_time DATE) IS
     cursor rs is
	   select id_test from test where id_test>id for update of a_date;
begin	
	 for rec in rs loop
	    update test set a_date=a_date+1 where id_test=rec.id_test;
	 end loop;
	 	 
	 commit;	 
end;
/

Then you do (in oracle) :
call adddate(-1,sysdate);

In Servoy (currently doesn’t works):

var proc="{call adddate(?,?)}";
var args=new Array(2);
args[0]=-1;
var d=new Date();
args[1]=d;
var argsInput = new Array(2);
argsInput[0]=0; // input
argsInput[1]=0; // input
databaseManager.executeStoredProcedure(controller.getServerName(), proc, args,argsInput, 0);

used procedureDeclaration {call adddate(?,?)}
questiondata[0]= -1.0
questiondata[1]= Thu Jul 01 20:07:10 CEST 2004
java.sql.SQLException: Invalid column type
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:222)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:285)
        at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:2443)
        at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:2566)
        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 com.servoy.j2db.persistence.datasource.n.invoke(Unknown Source)
        at $Proxy2.setObject(Unknown Source)
        at org.apache.commons.dbcp.DelegatingCallableStatement.setObject(DelegatingCallableStatement.java
:214)
        at org.apache.commons.dbcp.DelegatingCallableStatement.setObject(DelegatingCallableStatement.java
:214)
        at org.apache.commons.dbcp.DelegatingCallableStatement.setObject(DelegatingCallableStatement.java
:214)
        at com.servoy.j2db.dataprocessing.SQLEngine.executeStoredProcedure(Unknown Source)
        at com.servoy.j2db.dataprocessing.JSDatabaseManager.js_executeStoredProcedure(Unknown Source)
        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.NativeJavaMethod.call(NativeJavaMethod.java:289)
        at org.mozilla.javascript.ScriptRuntime.call(ScriptRuntime.java:1232)
        at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:1940)
        at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:94)
        at com.servoy.j2db.scripting.e.call(Unknown Source)
        at com.servoy.j2db.develop.debugger.l.a(Unknown Source)
....................................

One last thing. Please use a java.sql.Timestamp and not a java.sql.Date, the difference being that the Date only stores the date dd/MM/yyyy but not time. With a Timestamp, oracle receives both the date and time.

jcompagner · August 5, 2004, 9:52am

just to make sure, does it all work now in the latest release? (2.1rc2)

stephan · August 6, 2004, 2:35pm

I don’t know it <> works but I can tell you that the last example in my post works.

I’ll try to revisit past issues with Oracle and will post if I find something that doesn’t work.

Topic		Replies	Views
Getting Stored Procedure output Classic Servoy	6	4666	September 8, 2004
calling a Oracle stored function without parameters/argument Classic Servoy	4	3612	February 22, 2007
Stored procedures and functions Classic Servoy	6	4511	July 6, 2007
executeStoredProcedure() does not work Classic Servoy	5	4197	July 27, 2012
stored procedure not executing Classic Servoy	4	2739	August 27, 2004

DatabaseManager.ExecuteStoredProcedure

Related topics