MATLAB: Do I receive an error when I use RUNSTOREDPROCEDURE in the Database Toolbox 3.3 (R2007a)

Database Toolbox

When I try to run my stored procedure in my SQL database with the following command:

results = runstoredprocedure(conn, sp_name, parms_in, types_out)

where types_out is a cell array specifying the types of the output arguments:

types_out = {'varchar','decimal'}

I get the following error:

 ??? Java exception occurred: 
 java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]Invalid
 character value for cast specification

Best Answer

This error occurs if you have not specifed outputs arguments in your stored procedure.

If instead of output arguments your stored procedure is returning a result set, you can run it and retrieve the results using the EXEC and FETCH commands.

To see an example of using the EXEC command to run a stored procedure, type "doc exec" at the MATLAB command prompt.

Related Solutions

MATLAB: Am I unable to execute a SQL query which uses BEGIN and END statements with the MATLAB Database Toolbox 3.0.1 (R14SP1)

Neither the Oracle ODBC driver nor the Microsoft Oracle ODBC driver is able to execute statements of this syntax. These drivers typically run a stored procedure interactively. The introduction of a BEGIN/END block will cause them to fail. To work around this issue, create a stored procedure in the database and then run this procedure from the exec command.

You are also able to use input parameters for the stored procedure call. For

example,

e = exec(c,'{call sp_name(a,b,c)}')

where sp_name is the name of your stored procedure.

MATLAB: How to call a stored procedure with an OUT parameter from MATLAB using the Database Toolbox

This feature has been added in the Database Toolbox 3.2 (R2006b). You can use the RUNSTOREDPROCEDURE function. If you are using a previous version, read below for any workarounds.

You can retrieve the output value(s) as follows:

% Get one output.
x = runstoredprocedure(c,'myproc',{2500,'Jones'},{java.sql.Types.NUMERIC})
% Get two outputs.
x = runstoredprocedure(c,'myproc',{2500,'Jones'},{java.sql.Types.NUMERIC java.sql.Types.INTEGER})
% One integer output value.
%returnValue = runstoredprocedure(conn, 'test', {0}, {java.sql.Types.INTEGER})
% Two integer output values.
returnValues = runstoredprocedure(conn, 'test2', {0}, {java.sql.Types.INTEGER java.sql.Types.INTEGER})

To return data from a stored procedure, you will need to issue some commands from the MATLAB other than EXEC and FETCH commands.

The syntax for a stored procedure is:

 {?= call <procedure-name>[<arg1>,<arg2>, ...]}

 {call <procedure-name>[<arg1>,<arg2>, ...]}

The first case deals with a procedure that returns an OUT parameter and the second case shows no OUT parameter.

MATLAB (ODBC) example:

conn = database('source','username','password');
cs = conn.Handle.prepareCall('{?= call <procedure-name>[<arg1>,<arg2>, ...]}');

Assume this stored procedure returns a double

cs.registerOutParameter(2, java.sql.Types.DOUBLE, 3);
cs.executeQuery;
x = cs.getDouble(1);

The above code then returns a double value stored at index value 1 of the output parameters.

For more information about the above code, refer to the JDBC API documentation.

Best Answer

Related Solutions

MATLAB: Am I unable to execute a SQL query which uses BEGIN and END statements with the MATLAB Database Toolbox 3.0.1 (R14SP1)

MATLAB: How to call a stored procedure with an OUT parameter from MATLAB using the Database Toolbox

Related Question