Thursday 26 July 2012

iPhone - Free 3G tethering


Applications like MyWi and PDANet are not allowed on the Apple App Store due to getting around user's data network data usage plans (Which networks charge a lot for!)

Check out this app, very simple, lightweight and free!

http://www.junefabrics.com/desktop/

Tuesday 17 July 2012

JDBC - Calling an Oracle Stored Procedure with CURSOR output


This article explains how to call an Oracle Stored Procedure that returns a Cursor as an output parameter with Java using JDBC.


Things to note:
- JDBC begins it's index's at 1, not 0. I.e. The CallableStatement object's methods such as: getObject must have a parameter with a value of at least 1.
- Once the Connection or CallableStatement objects are closed, the ResultSet will be empty. This is because it only exists as long as the database connection is open. Ensure you take your results before these objects are destroyed.
- oracle.jdbc.OracleCallableStatement and java.sql.CallableStatement offer the same outcome in this example. Therefore, I have opted to use the java.sql library.
- The '?' within the prepareCall parameter is a place holder for a parameter. This is populated (in this example) using the registerOutParameter method, declaring it's expected type.


Oracle Stored Procedure
Code Snippet
  1. CREATE OR REPLACE PROCEDURE PACKAGENAME.GET_ACCOUNTS
  2. (
  3.     l_cursor OUT sys_refcursor
  4. )
  5.     OPEN l_cursor FOR SELECT ACCOUNT_NAME FROM Accounts ORDER BY ACCOUNT_NAME;
  6. END GET_ACCOUNTS;
End of Code Snippet


Java Code
Code Snippet
  1. // Establishes and returns a new connection to the database
  2. private Connection GetConnection() throws SQLException
  3. {
  4.     // Load and register Oracle driver
  5.     DriverManager.registerDriver(new OracleDriver());
  6.    
  7.     // Establish a connection
  8.     return DriverManager.getConnection("jdbc:oracle:thin:@SERVER:PORT:SID", "USERNAME", "PASSWORD");
  9. }
  10.  
  11.  
  12. // Gets a collection of all accounts within the database
  13. public Collection<Account> GetAccounts() throws SQLException
  14. {  
  15.     Connection conn = null;
  16.     CallableStatement cstmt   = null;
  17.     Collection<Account> accounts = new ArrayList<Account>();
  18.  
  19.     try
  20.     {
  21.         // Connect
  22.         conn = this.GetConnection();
  23.  
  24.         // Get ResultSet
  25.         cstmt = conn.prepareCall("{call PACKAGENAME.GET_ACCOUNTS(?)}");   // '?' is a template for our Cursor OUT parameter
  26.         cstmt.registerOutParameter(1, OracleTypes.CURSOR);
  27.         cstmt.execute();
  28.  
  29.         // Index starts at 1, so we want our first result, which is the Cursor
  30.         ResultSet rs = (ResultSet) cstmt.getObject(1);
  31.  
  32.         // process results one row at a time
  33.         while(rs.next())
  34.         {
  35.             Account acc = new Account(rs.getString(1)); // Account name is the first (and only) result in our Cursor.
  36.             accounts.add(acc); // Add account to our collection of accounts to return
  37.         }
  38.     }
  39.     catch (SQLException e)
  40.     {
  41.         System.err.println(e.getErrorCode() + e.getMessage());
  42.     }
  43.         catch(Exception e)
  44.         {
  45.             e.printStackTrace();
  46.         }
  47.     finally
  48.     {
  49.         conn.close();
  50.         cstmt.close();
  51.     }
  52.    
  53.     return accounts; // Return collection of accounts to caller
  54. }
  55.  
  56.  
  57.  
  58. // Account POJO
  59. public class Account
  60. {
  61.     private String accountName;
  62.  
  63.     public Account(String accName)
  64.     {
  65.         this.accountName = accName;
  66.     }
  67.    
  68.     public String getAccountName() {
  69.         return this.accountName;
  70.     }
  71.  
  72.     public void setAccountName(String accountName) {
  73.         this.accountName = accountName;
  74.     }
  75. }
End of Code Snippet

java.lang.IllegalAccessError: oracle/jdbc/driver/OracleCallableStatement


I received this error when attempting to cast the result of a Connection object's prepareCall method.

Solution
Try altering the application to not refer to oracle.jdbc.driver.OracleCallableStatement. It should be oracle.jdbc.OracleCallableStatement.

Reason
For Oracle 9i onwards you should use oracle.jdbc.OracleDriver rather than oracle.jdbc.driver.OracleDriver as Oracle have stated that oracle.jdbc.driver.OracleDriver is deprecated and support for this driver class will be discontinued in the next major release.