To comment on the following update, log in, then open the issue:
http://www.openoffice.org/issues/show_bug.cgi?id=43548
Issue #:|43548
Summary:|Call to Oracle Stored Procedure causes Nasty Crash in
|OOO.
Component:|Database access
Version:|OOo 1.1.4
Platform:|All
URL:|
OS/Version:|Windows XP
Status:|UNCONFIRMED
Status whiteboard:|
Keywords:|
Resolution:|
Issue type:|DEFECT
Priority:|P3
Subcomponent:|none
Assigned to:|msc
Reported by:|ynaras
------- Additional comments from [EMAIL PROTECTED] Fri Feb 25 08:19:22 -0800
2005 -------
<Center><B>PLEASE HELP!</B></Center>
Hello All,
I have a program in java that uses jdbc to make a call to a stored procedure.
The program works perfectly in java. When I port the code to OOO macro it
causes a nasty crash in OOO calc. Here are detailed steps to reproduce the
problem:
Environment needed:
- Oracle database running and listening.
- OOO installed
- Oracle jdbc driver (ex: classes111.jar) installed under program folder of
openoffice installation folder. These jar files will need to be referenced from
classpath setting of Tools -> Options.
1. First create the following the following stored procedure in Oracle:
Code:
create or replace procedure Echo(txtInput IN OUT VARCHAR2) is
begin
txtInput := 'Hello ' || txtInput;
end Echo;
2. Next test the following code in java to ensure it works in java(please make
sure you compile and run with class path pointing to the same oracle jdbc class
files that ooo points to). If you do it correctly, it will work like a charm.
Code:
import java.sql.*;
public class StProcExample {
public static void main(String[] args)
throws SQLException {
int ret_code;
Connection conn = null;
try {
//Load and register Oracle driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
//Establish a connection
conn = DriverManager.getConnection
("jdbc:oracle:thin:@oraclehost.mydomain.com:1521:cssr", "cssr", "cssr");
String s_deptno = "ddd";
CallableStatement pstmt = conn.prepareCall("{call echo(?)}");
pstmt.setString(1, s_deptno);
// pstmt.registerOutParameter(2, Types.INTEGER);
pstmt.registerOutParameter(1, Types.VARCHAR);
// pstmt.registerOutParameter(4, Types.FLOAT);
pstmt.executeUpdate();
// int o_empno = pstmt.getInt(2);
String o_ename = pstmt.getString(1);
// float o_sal = pstmt.getFloat(4);
System.out.print("Input: "+s_deptno+"; Output: "+o_ename);
pstmt.close();
conn.close();
} catch (SQLException e) {ret_code = e.getErrorCode();
System.err.println(ret_code + e.getMessage()); conn.close();}
}
}
3. Now start a new OpenOffice calc and Start a new macro and paste the
following code in it, When you step through it, it will crash when the code
touches "prepareCall( strSql )". Please ensure you have a OO data source set up
called CSSR:
Code:
Sub ExecuteStoredProcedure
Dim oCallableStatement as Object 'com.sun.star.sdbc.CallableStatement
If (Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools")) Then
GlobalScope.BasicLibraries.LoadLibrary("Tools")
End If
oDBConnection = ConnectToDatabase( "CSSR", "CSSR", "CSSR" )
strSql = "{call Echo(?)}"
oCallableStatement = oDBConnection.prepareCall( strSql )
' oCallableStatement.registerOutParameter( 1, OracleTypes. )
oCallableStatement.setString(1, "Yogesh")
oCallableStatement.Execute()
ResultSet = oCallableStatement.getString( 2 )
Print ResultSet
End Sub
PS: This piece of OOO is not documented properly anywhere. Once I get this to
work, I am willing to contribute documentation and user manual for this part of
openoffice.
Any help will be greatly appreciated, Thanks,
Yogesh.
---------------------------------------------------------------------
Please do not reply to this automatically generated notification from
Issue Tracker. Please log onto the website and enter your comments.
http://qa.openoffice.org/issue_handling/project_issues.html#notification
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
