01: /*
02: * Copyright (C) 2007 Rob Manning
03: * manningr@users.sourceforge.net
04: *
05: * This library is free software; you can redistribute it and/or
06: * modify it under the terms of the GNU Lesser General Public
07: * License as published by the Free Software Foundation; either
08: * version 2.1 of the License, or (at your option) any later version.
09: *
10: * This library is distributed in the hope that it will be useful,
11: * but WITHOUT ANY WARRANTY; without even the implied warranty of
12: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
13: * Lesser General Public License for more details.
14: *
15: * You should have received a copy of the GNU Lesser General Public
16: * License along with this library; if not, write to the Free Software
17: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
18: */
19: package test;
20:
21: import java.sql.CallableStatement;
22: import java.sql.Connection;
23: import java.sql.DriverManager;
24: import java.sql.ResultSet;
25: import java.sql.SQLException;
26: import java.sql.Statement;
27:
28: import net.sourceforge.squirrel_sql.client.ApplicationArguments;
29: import net.sourceforge.squirrel_sql.fw.sql.SQLUtilities;
30:
31: public class OracleSyntaxErrorOffsetTest {
32:
33: private static final String OFFSET_FUNCTION = "create or replace function SQUIRREL_GET_ERROR_OFFSET (query IN varchar2) "
34: + "return number authid current_user "
35: + "is "
36: + " l_theCursor integer default dbms_sql.open_cursor; "
37: + " l_status integer; "
38: + "begin "
39: + " begin "
40: + " dbms_sql.parse( l_theCursor, query, dbms_sql.native ); "
41: + " exception "
42: + " when others then l_status := dbms_sql.last_error_position; "
43: + " end; "
44: + " dbms_sql.close_cursor( l_theCursor ); "
45: + " return l_status; " + "end; ";
46:
47: private static void test(Connection con) throws Exception {
48: CallableStatement cstmt = null;
49: Statement stmt = null;
50: try {
51: stmt = con.createStatement();
52: System.out.println("Executing sql: " + OFFSET_FUNCTION);
53: int offsetResult = stmt.executeUpdate(OFFSET_FUNCTION);
54: System.out.println("Result: " + offsetResult);
55: String sql = "{?=call get_error_offset(?)}";
56: System.out.println("Executing sql: " + sql);
57: cstmt = con.prepareCall(sql);
58: cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
59: cstmt.setString(2, "select * from foobar");
60: cstmt.execute();
61: System.out.println("Offset=" + cstmt.getInt(1));
62:
63: } catch (SQLException e) {
64: e.printStackTrace();
65: } finally {
66: SQLUtilities.closeStatement(cstmt);
67: SQLUtilities.closeStatement(stmt);
68: }
69:
70: }
71:
72: /**
73: * @param args
74: */
75: public static void main(String[] args) throws Exception {
76: ApplicationArguments.initialize(new String[] {});
77: Class.forName("oracle.jdbc.OracleDriver");
78: String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:CSUITE";
79: Connection con = DriverManager.getConnection(jdbcUrl, "test",
80: "password");
81: test(con);
82: }
83:
84: }
|