Prev: installing knitro 6.0 in matlab R2009a
Next: How to compute neighboring regions of a region in an image? thank you
From: Sven on 8 Apr 2010 16:02
Hi all,

I would like to run a stored procedure from MATLAB, and provide a java object (in this case a CLOB-equivalent) as one of the parameters. Consider the following "hello world" Oracle procedure:

CREATE OR REPLACE PROCEDURE testProc (inClob IN VARCHAR2, outChar OUT VARCHAR2) IS
BEGIN
outChar := 'Hello World!';
END testProc;
/

Now the following MATLAB command runs successfully:
>> out1 = runstoredprocedure(conn, 'testProc',{'''blah'''},{java.sql.Types.VARCHAR});

The following, however, fails:
>> javaStr = java.lang.String('blah');
>> out2 = runstoredprocedure(conn, 'testProc',{javaStr},{java.sql.Types.VARCHAR});
??? Error using ==> horzcat
The following error occurred converting from char to opaque:
Error using ==> horzcat
Undefined function or method 'opaque' for input arguments of type 'char'.

Error in ==> database.runstoredprocedure at 56
spcall = [spcall inarg{i} ',']; %#ok, not sure how long spcall will be

Can somebody help me to upload a java object directly? I understand that this the above case my javaStr actually gets converted to a MATLAB char so that placing quotes around it will get it to upload as a string, but I would eventually like to create a CLOB object and upload that directly. If I simply wrap my string in quotes, it is uploaded as a VARCHAR object, and the following error occurs:
??? Java exception occurred:
java.sql.SQLException: ORA-06550: line 1, column 16:
PLS-00172: string literal too long


at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:330)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:287)

at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:744)

at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:217)

at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:968)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1189)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3369)

at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3453)

at oracle.jdbc.driver.OracleCallableStatement.executeUpdate(OracleCallableStatement.java:4421)


Error in ==> database.runstoredprocedure at 81
csmt.executeUpdate;

So in summary, my question is:
Can I provide a java object as one of the parameters to runstoredprocedure, and if so, how??

Thanks,
Sven.
From: Yair Altman on 10 Apr 2010 14:00
Re this specific case, your SP expects a VARCHAR2, which Matlab converts into an expectation for a Matlab string (char) object. Matlab's DB toolbox is not smart enough to understand that your java.lang.String object can easily be converted to a char using the char() function, so you should help it by char'ing yourself.

Re your general question about passing Java objects, I have never tried this but perhaps you can try passing them as a BLOB.

Yair Altman
http://UndocumentedMatlab.com
From: Sven on 14 Apr 2010 12:41
Thanks for the reply Yair. See below for some responses/questions.

> Re this specific case, your SP expects a VARCHAR2, which Matlab converts into an expectation for a Matlab string (char) object. Matlab's DB toolbox is not smart enough to understand that your java.lang.String object can easily be converted to a char using the char() function, so you should help it by char'ing yourself.

OOPS! In various testing and boiling down my problem to newsgroup-ready code, I made a typo with the procedure definition.
It was supposed to be "(inClob IN CLOB, outChar OUT VARCHAR2)" rather than "(inClob IN VARCHAR2..."

My problem basically boils down to the fact that I don't actually *want* to pass my argument as a MATLAB char() or any other native MATLAB format. My choice of CLOB is due to the fact that the text I want to pass is very long (above the size limit for Oracle's VARCHAR2). The act of char'ing my java.lang.String object means that it gets passed as a VARCHAR2 to the stored procedure and I run over this limit. I will make another reply to this post that addresses this question directly with accompanying code.


> Re your general question about passing Java objects, I have never tried this but perhaps you can try passing them as a BLOB.

This is essentially what I'm trying to do. Can you give an example of MATLAB code that "passes arguments as a BLOB" (or in fact, *any* non MATLAB-native variable) to a stored procedure or an insert/update statement?

Thanks,
Sven.
From: Sven on 14 Apr 2010 15:10
As I mentioned in my previous post, here is a simple example that I would like to *somehow* get to work:

Firstly, a simple oracle stored procedure that takes a CLOB as input:
------------------
CREATE OR REPLACE PROCEDURE testClob (inClob IN CLOB, clobSize OUT NUMBER) IS
BEGIN
SELECT length(inClob) into clobSize from dual;
END testClob;
/
--------------------

Next, to show that I can run this procedure from matlab passing a VARCHAR2-length string:

>> myout = runstoredprocedure(conn,'testClob',{['''' sprintf('%d',ones(1,32767)) '''']},{java.sql.Types.NUMERIC});
>> myout{1}
ans =
32767

As you can see, the length of the string is returned. Note that 32767 is the maximum length for a VARCHAR2 in oracle. Note however that the stored procedure takes in a CLOB, not a VARCHAR2. When calling directly from Oracle PL/SQL, I can send any size CLOB to this function without error. If I exceed this size with my input from MATLAB however, an error occurs:

>> myout = runstoredprocedure(conn,'testClob',{['''' sprintf('%d',ones(1,32768)) '''']},{java.sql.Types.NUMERIC})
??? Java exception occurred:
java.sql.SQLException: ORA-06550: line 1, column 16:
PLS-00172: string literal too long

Can anybody please show me how to get my test procedure to return when called from MATLAB with a return value greater than 32767?

As a step towards a solution, here is a simple stored procedure which BUILDS a CLOB:
---------------
CREATE OR REPLACE PROCEDURE buildClob (clobSize NUMBER, outClob OUT CLOB) IS
BEGIN
outClob := '';
FOR Lcntr IN 1..clobSize LOOP outClob := outClob || '1'; END LOOP;
END
/
-----------------

I can get a large CLOB *into* MATLAB quite simply as follows:
>> myoutCLOB = runstoredprocedure(conn,'buildClob',{50000},{java.sql.Types.CLOB})

myoutCLOB =
[1x1 oracle.sql.CLOB]

So now I have a CLOB ready to send to a stored procedure that accepts a CLOB. Much to my dismay, however, I simply can't find the syntax to call it. The following causes error:
>> myout = runstoredprocedure(conn,'testClob',myoutCLOB,{java.sql.Types.NUMERIC})
??? Error using ==> horzcat
The following error occurred converting from char to opaque:
Error using ==> horzcat
Undefined function or method 'opaque' for input arguments of type 'char'.

Error in ==> database.runstoredprocedure at 56
spcall = [spcall inarg{i} ',']; %#ok, not sure how long spcall will be


So... can anyone tell me whether what I'm trying to do is even possible? I've really been banging my head against a wall with this one and I'd appreciate any help you can give.

Thanks,
Sven.
From: Sven on 15 Apr 2010 11:26
Ok, I've gotten a little further by looking through the runstoredprocedure function, and modifying it a little to produce runstoredprocedure_generic(), which I'll copy at the bottom of this post. It's quite rudimentary at the moment, only working with STRING and NUMERIC types (and I only have Oracle to test against), but it allows me to send a large string to a stored procedure that takes a CLOB as its inParameter.

The problem with runstoredprocedure is that it builds a string to include all input parameters rather than binding them (as it does for output parameters). So that a call from runstoredprocedure looks something like:

'{call testClob(''param1 input string'',?)}'

Rather than the more generic

'{call testClob(?,?)}'

Note that with the first format, ALL input parameters are forced into a string before being sent. With the second format, you can use the "set" methods of the callable statement to individually bind different parameters.

So, I wrote a function that mimics runstoredprocedure but with a bit more generic input. I didn't have time to work out how to get "Ordinal binding" working, so I had to get a further input parameter containing the specific names of the stored procedure parameters, and use "Named binding" instead. If any Mathworks people are following this thread and have input, it would be great to hear from you either in this thread or by email as I have some more questions and problems I would like to overcome.

Anyway, the following now works for a stored procedure:
-------------
CREATE OR REPLACE PROCEDURE UMPIRE.testClob (inClob IN CLOB, clobSize OUT NUMBER) IS
BEGIN
SELECT length(inClob) into clobSize from dual;
END testClob;
/
------------
Can now be successfully called from MATLAB with a large (CLOB-sized) string as follows:
>> x = runstoredprocedure_generic(c,'testClob',{repmat('1',1,50000)},{java.sql.Types.VARCHAR,java.sql.Types.NUMERIC},{'inClob','clobSize'})
>> x{1}
ans =
50000

So without further ado, here's the runstoredprocedure_generic function:

function x = runstoredprocedure_generic(c,spcall,inarg,typeinout,namesinout)
%RUNSTOREDPROCEDURE_GENERIC Stored procedures with input and output parameters.
% X = RUNSTOREDPROCEDURE_GENERIC(C,SPCALL,INARG,TYPEINOUT,NAMESINOUT) calls a stored
% procedure given input parameters and returns output parameters. C is the database
% connection handle, SPCALL is the stored procedure to be run, INARG is a
% cell array containing the stored procedure's input parameters. TYPEINOUT
% is a list of data types of the input/output parameters, in the order of the stored procedure
% definition. Likewise namesinout are the procedure parameter names corresponding to the typeinout
% list.
%
% For example, the call syntax may appear as
%
% x = runstoredprocedure(c,'myproc',{2500,'Jones'},{java.sql.Types.NUMERIC,java.sql.Types.VARCHAR,java.sql.Types.NUMERIC},{'param1','param2'})
%
% which means that the stored procedure myproc will be run given the
% input parameters 2500 and 'Jones', and the stored procedure expects numeric
% and string input respectively. It will return an output parameter
% of type java.sql.Types.NUMERIC which could be any numeric Java data
% type.
% NOTE: The valid input types are currently limited to:
% - java.sql.Types.NUMERIC (can be used to indicate INTEGER/NUMBER/BOOLEAN/ETC). It will be
% passed to the database via setDouble(), and the database will deal with typing from there
% - java.sql.Types.VARCHAR (can be used to indicate CHAR/VARCHAR2/CLOB/ETC). It will be passed
% to the database as toString()
% ALSO NOTE: This function has ONLY been tested with Oracle
%
% If the TYPEIN parameter is unused, this function will simply shadow MATLAB'S database toolbox
% RUNSTOREDPROCEDURE, passing ONLY the name of the spcall, and the INARG/TYPEOUT parameters if
% they are set.
%
% For stored procedures that return resultsets, use the methods
% DATABASE/EXEC and CURSOR/FETCH to process the return data.
%
% See also RUNSTOREDPROCEDURE, EXEC, FETCH.

% Modified by Sven Holcombe.


%Ensure all parameters are set
assert(nargin==5,'runstoredprocedure_generic expects all 5 arguments to be set')
assert(length(typeinout)==length(namesinout),'Number of input/output arguments and their names must be consistent')

%Build input/output pairs of types and names
numin = length(inarg);
numout = length(typeinout) - numin;
typein = typeinout(1:numin);
typeout = typeinout(numin+1:end);
namesin = namesinout(1:numin);
namesout = namesinout(numin+1:end);

%Build the full stored procedure call based on the number of arguments provided
numParams = numin + numout;
paramsStr = repmat('?,',1,numParams);
paramsStr = paramsStr(1:end-1);
spcallFullStr = sprintf('{call %s(%s)}',spcall,paramsStr);

%Get JDBC connection Handle
h = c.Handle;

%Create callable statement
csmt = h.prepareCall(spcallFullStr);

%Register input parameters
for i = 1:numin
switch(typein{i})
case 12
csmt.setString(namesin{i},inarg{i})
case 2
csmt.setDouble(namesin{i},inarg{i})
otherwise
error('runstoredprocedure_generic:badInputType','Current implementation can only handle java.sql.Types.VARCHAR/NUMERIC')
end
end

%Register output parameters
for i = 1:numout
csmt.registerOutParameter(namesout{i},typeout{i});
end

%Execute callable statement, method depends on output parameters
if ~isempty(typeout)
csmt.executeUpdate;
else
try
x = csmt.execute;
catch exception
error('database:runstoredprocedure:returnedResultSet',...
'Procedure may return resultset. Use EXEC and FETCH');
end
return
end

%Return output parameters as native data types
x = cell(numout,1);
for i = 1:numout
x{i} = csmt.getObject(namesout{i});
end

%Close callable statement
close(csmt)
 | 
Pages: 1
Prev: installing knitro 6.0 in matlab R2009a
Next: How to compute neighboring regions of a region in an image? thank you