SQLJExamples class

This section displays the SQLJExamples class used to illustrate SQLJ stored procedures and functions. They are also in $SYBASE/$SYBASE_ASE/sample/JavaSql-R2. (UNIX) or %SYBASE%\Ase-12_5\sample\JavaSql-R2 (Windows NT).

import java.lang.*;

import java.sql.*;

import java.math.*;

static String _url = “jdbc:default:connection”;

public class SQLExamples {

	public static int region(String s) 
			throws SQLException {
		s = s.trim();
		if (s.equals(“MN”) || s.equals(“VT”) ||
			s.equals(“NH”) ) return 1;
		if (s.equals(“FL”) || s.equals(“GA”) ||
			s.equals(“AL”) ) return 2;
		if (s.equals(“CA”) || s.equals(“AZ”) ||
			s.equals(“NV”) ) return 3;
		else throw new SQLException
			(“Invalid state code”, “X2001”);	}

	public static void correctStates
		(String oldSpelling, String newSpelling)
		throws SQLException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			Class.forName
				(”sybase.asejdbc.ASEDriver”);
			conn = DriverManager.getConnection(_url);
		} 
		catch (Exception e) {
			System.err.println(e.getMessage() + 
				“:error in connection”);
		}
		try {
			pstmt = conn.prepareStatement
				(“UPDATE sales_emps SET state = ? 
				WHERE state = ?”);
			pstmt.setString(1, newSpelling);
			pstmt.setString(2, oldSpelling);
			pstmt.executeUpdate();
		}
		catch (SQLException e) {
			System.err.println(“SQLException: “ +
			e.getErrorCode() + e.getMessage());
		}

	}
	public static String job(int jc)
			throws SQLException {
		if (jc==1) return “Admin”;

 		else if (jc==2) return “Sales”;
		else if (jc==3) return “Clerk”;
		else return “unknown jobcode”;
	}

	public static String job(int jc)
			throws SQLException {
	if (jc==1) return “Admin”;

 	else if (jc==2) return “Sales”;
	else if (jc==3) return “Clerk”;
	else return “unknown jobcode”;
	}

	public static void bestTwoEmps(String[] n1,
		String[] id1, int[] r1, 
		BigDecimal[] s1, String[] n2,
		String[] id2, int[] r2, BigDecimal[] s2,
		int regionParm) throws SQLException {

	n1[0] = “****”; 	
	id1[0] = ““; 
	r1[0] = 0;
	s1[0] = new BigDecimal(0):
	n2[0] = “****”;
	id2[0] = ““;
	r2[0] = 0;
	s2[0] = new BigDecimal(0);


	try {
		Connection conn = DriverManager.getConnection
			(“jdbc:default:connection”);
		java.sql.PreparedStatement stmt =
			conn.prepareStatement(“SELECT name, id,”
			+ “region_of(state) as region, sales FROM”
			+ “sales_emps WHERE”
			+ “region_of(state)>? AND”
			+ “sales IS NOT NULL ORDER BY sales DESC”);
		stmt.setInteger(1, regionParm);
		ResultSet r = stmt.executeQuery();

		if(r.next()) {
			n1[0] = r.getString(“name”);
			id1[0] = r.getString(“id”);
			r1[0] = r.getInt(“region”);

			s1[0] = r.getBigDecimal(“sales”);
		}
		else return;

		if(r.next()) {
			n2[0] = r.getString(“name”);
			id2[0] = r.getString(“id”);
			r2[0] = r.getInt(“region”);
			s2[0] = r.getBigDecimal(“sales”);
		}
		else return;
	} 
	catch (SQLException e) {
		System.err.println(“SQLException: “ +
			e.getErrorCode() + e.getMessage());
		}
	}

	public static void orderedEmps
			(int regionParm, ResultSet[] rs) throws
			SQLException {


		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			Class.forName
				(“sybase.asejdbc.ASEDriver”);
			Connection conn =
				DriverManager.getConnection
				(“jdbc:default:connection”);
		} 
		catch (Exception e) {
			System.err.println(e.getMessage() 
				+ “:error in connection”);
		}

		try {
			java.sql.PreparedStatement 
				stmt = conn.prepareStatement 
				(“SELECT name, region_of(state)”
				“as region, sales FROM sales_emps”
				“WHERE region_of(state) > ? AND” 
				“sales IS NOT NULL” 
				“ORDER BY sales DESC”);
			stmt.setInt(1, regionParm);
			rs[0] = stmt.executeQuery();
			return;
		}
		catch (SQLException e) {
			System.err.println(“SQLException:” 
			+ e.getErrorCode() + e.getMessage());
		}
		return;
	} 	return;
	}
}