public static String getVersion(Connection conn) throws SQLException {
String method = "getVersion";
int location = 1000;
try {
location = 2000;
String value = "";
location = 2100;
Statement stmt = conn.createStatement();
String strSQL =
"SELECT CASE "
+ "WHEN POSITION ('HAWQ 2.0.1' in version) > 0 THEN 'HAWQ_2_0_1' "
+ "WHEN POSITION ('HAWQ 2.0.0' in version) > 0 THEN 'HAWQ_2_0_0' "
+ "WHEN POSITION ('HAWQ 1' in version) > 0 THEN 'HAWQ_1' "
+ "WHEN POSITION ('HAWQ' in version) = 0 AND POSITION ('Greenplum Database' IN version) > 0 THEN 'GPDB' "
+ "ELSE 'OTHER' END "
+ "FROM version()";
if (debug) Logger.printMsg("Getting Variable: " + strSQL);
location = 2200;
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
value = rs.getString(1);
}
location = 2300;
return value;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static void dropExternalReplTable(
Connection conn,
String sourceType,
String targetSchema,
String targetTable,
String sourceTable)
throws SQLException {
String method = "dropExternalReplTable";
int location = 1000;
try {
location = 2000;
String replTargetSchema = externalSchema;
location = 2100;
String replTargetTable = getStageTableName(targetSchema, targetTable);
location = 2200;
String replSourceTable = getReplTableName(sourceType, sourceTable);
location = 2315;
dropExternalTable(conn, replTargetSchema, replTargetTable);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static String getVariable(Connection conn, String name) throws SQLException {
String method = "getVariable";
int location = 1000;
try {
location = 2000;
String value = "";
location = 2100;
Statement stmt = conn.createStatement();
String strSQL = "SELECT os.fn_get_variable('" + name + "')";
if (debug) Logger.printMsg("Getting Variable: " + strSQL);
location = 2200;
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
value = rs.getString(1);
}
location = 2300;
return value;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static void failJobs(Connection conn) throws SQLException {
String method = "failJobs";
int location = 1000;
try {
Statement stmt = conn.createStatement();
String strSQL =
"INSERT INTO os.ao_queue (queue_id, status, queue_date, start_date, end_date, "
+ "error_message, num_rows, id, refresh_type, target_schema_name, target_table_name, target_append_only, "
+ "target_compressed, target_row_orientation, source_type, source_server_name, source_instance_name, "
+ "source_port, source_database_name, source_schema_name, source_table_name, source_user_name, "
+ "source_pass, column_name, sql_text, snapshot) "
+ "SELECT queue_id, 'failed' as status, queue_date, start_date, now() as end_date, "
+ "'Outsourcer stop requested' as error_message, num_rows, id, refresh_type, target_schema_name, "
+ "target_table_name, target_append_only, target_compressed, target_row_orientation, source_type, "
+ "source_server_name, source_instance_name, source_port, source_database_name, source_schema_name, "
+ "source_table_name, source_user_name, source_pass, column_name, sql_text, snapshot "
+ "FROM os.queue WHERE status = 'queued'";
stmt.executeUpdate(strSQL);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static void customStartAll(Connection conn) throws SQLException {
String method = "customStartAll";
int location = 1000;
try {
Statement stmt = conn.createStatement();
int id = 0;
int gpfdistPort = 0;
String strSQL = "SELECT id\n";
strSQL += "FROM os.custom_sql";
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
id = rs.getInt(1);
gpfdistPort = GpfdistRunner.customStart(OSProperties.osHome);
strSQL = "INSERT INTO os.ao_custom_sql\n";
strSQL +=
"(id, table_name, columns, column_datatypes, sql_text, source_type, source_server_name, source_instance_name, source_port, source_database_name, source_user_name, source_pass, gpfdist_port)\n";
strSQL +=
"SELECT id, table_name, columns, column_datatypes, sql_text, source_type, source_server_name, source_instance_name, source_port, source_database_name, source_user_name, source_pass, "
+ gpfdistPort
+ "\n";
strSQL += "FROM os.custom_sql\n";
strSQL += "WHERE id = " + id;
stmt.executeUpdate(strSQL);
}
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static void emailAlert(Connection conn, String errorMsg) throws SQLException {
String method = "emailAlert";
int location = 1000;
try {
Statement stmt = conn.createStatement();
String strSQL = "SELECT gp_elog('" + errorMsg + "',true)";
ResultSet rs = stmt.executeQuery(strSQL);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static int insertReplTable(Connection conn, String targetSchema, String targetTable)
throws SQLException {
String method = "insertReplTable";
int location = 1000;
int numRows = 0;
try {
location = 2000;
String replTargetSchema = externalSchema;
location = 2100;
String replTargetTable = getStageTableName(targetSchema, targetTable);
location = 2200;
String externalTable = getExternalTableName(replTargetSchema, replTargetTable);
location = 2305;
// truncate the stage table before loading
truncateTable(conn, replTargetSchema, replTargetTable);
location = 2400;
Statement stmt = conn.createStatement();
location = 2500;
String strSQL =
"INSERT INTO \""
+ replTargetSchema
+ "\".\""
+ replTargetTable
+ "\" \n"
+ "SELECT * FROM \""
+ externalSchema
+ "\".\""
+ externalTable
+ "\"";
if (debug) Logger.printMsg("Executing SQL: " + strSQL);
location = 2600;
numRows = stmt.executeUpdate(strSQL);
location = 2700;
return numRows;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static boolean checkStageTable(Connection conn, String targetSchema, String targetTable)
throws SQLException {
String method = "checkStageTable";
int location = 1000;
try {
location = 2000;
boolean found = false;
String stageTable = getStageTableName(targetSchema, targetTable);
location = 2100;
String strSQL =
"SELECT NULL \n"
+ "FROM INFORMATION_SCHEMA.TABLES \n"
+ "WHERE table_schema = '"
+ externalSchema
+ "' \n"
+ " AND table_name = '"
+ stageTable
+ "'";
if (debug) Logger.printMsg("Executing sql: " + strSQL);
location = 2200;
Statement stmt = conn.createStatement();
location = 2310;
ResultSet rs = stmt.executeQuery(strSQL);
location = 2400;
while (rs.next()) {
found = true;
}
location = 2500;
return found;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static String getMax(Connection conn, String schema, String table, String columnName)
throws SQLException {
String method = "getMax";
int location = 1000;
try {
location = 2000;
String strSQL =
"SELECT MAX(\""
+ columnName.toLowerCase()
+ "\") \n"
+ "FROM \""
+ schema
+ "\".\""
+ table
+ "\"";
if (debug) Logger.printMsg("Executing sql: " + strSQL);
String max = "-1";
location = 2100;
Statement stmt = conn.createStatement();
location = 2200;
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
max = rs.getString(1);
}
location = 2313;
if (max == null) {
max = "-1";
}
return max;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static void truncateTable(Connection conn, String schema, String table)
throws SQLException {
String method = "truncateTable";
int location = 1000;
try {
location = 2000;
Statement stmt = conn.createStatement();
location = 2100;
String strSQL = "truncate table \"" + schema + "\".\"" + table + "\"";
if (debug) Logger.printMsg("Truncating table: " + strSQL);
location = 2200;
stmt.executeUpdate(strSQL);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static String getArchMax(
Connection conn, String targetSchema, String targetTable, String columnName)
throws SQLException {
String method = "getArchMax";
int location = 1000;
try {
location = 2000;
String archTable = getArchTableName(targetSchema, targetTable);
location = 2100;
String max = getMax(conn, externalSchema, archTable, columnName);
location = 2200;
return max;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static void createReplExternalTable(
Connection conn,
String osServer,
String refreshType,
String targetSchema,
String targetTable,
String sourceType,
String sourceTable,
String maxId,
int queueId,
int jobPort)
throws SQLException {
String method = "createReplExternalTable";
int location = 1000;
try {
location = 2000;
String replTargetSchema = externalSchema;
location = 2100;
String replTargetTable = getStageTableName(targetSchema, targetTable);
location = 2200;
String replSourceTable = getReplTableName(sourceType, sourceTable);
location = 2308;
createExternalTable(
conn,
osServer,
refreshType,
replSourceTable,
replTargetSchema,
replTargetTable,
maxId,
queueId,
jobPort);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public RunnableTraitementEntree(Socket s) {
CSocket = s;
try {
dis = new DataInputStream(new BufferedInputStream(CSocket.getInputStream()));
dos = new DataOutputStream(new BufferedOutputStream(CSocket.getOutputStream()));
} catch (IOException e) {
System.err.println("RunnableTraitement : Host non trouvé : " + e);
}
beanOracle = new BeanBDAccess();
try {
beanOracle.connexionOracle("localhost", 1521, "TRAFIC", "TRAFIC", "XE");
} catch (ClassNotFoundException ex) {
System.err.println("Class not found " + ex.getMessage());
} catch (SQLException ex) {
System.err.println("SQL Exception (oracle)" + ex.getMessage());
} catch (connexionException ex) {
System.err.println(ex.getNumException() + " -- " + ex.getMessage());
}
}
public static void cancelJobs(Connection conn) throws SQLException {
String method = "cancelJobs";
int location = 1000;
try {
List<String> jobIdList = new ArrayList<String>();
Statement stmt = conn.createStatement();
String strSQL = "SELECT id FROM os.queue WHERE status = 'processing'";
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
jobIdList.add(rs.getString(1));
}
for (String jobId : jobIdList) {
strSQL = "SELECT os.fn_cancel_job(" + jobId + ")";
rs = stmt.executeQuery(strSQL);
}
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static void setupReplicationTables(
Connection conn, String targetSchema, String targetTable, String columnName)
throws SQLException {
String method = "setupReplicationTables";
int location = 1000;
try {
location = 2000;
String archTable = getArchTableName(targetSchema, targetTable);
location = 2100;
String stageTable = getStageTableName(targetSchema, targetTable);
location = 2200;
String strSQL =
"SELECT os.fn_replication_setup('"
+ targetSchema
+ "', '"
+ targetTable
+ "', '"
+ externalSchema
+ "', '"
+ stageTable
+ "', '"
+ archTable
+ "', '"
+ columnName
+ "')";
location = 2312;
Statement stmt = conn.createStatement();
location = 2400;
stmt.executeQuery(strSQL);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static void executeReplication(
Connection conn, String targetSchema, String targetTable, String appendColumnName)
throws SQLException {
String method = "executeReplication";
int location = 1000;
try {
location = 2000;
Statement stmt = conn.createStatement();
location = 2100;
String externalTable = getExternalTableName(targetSchema, targetTable);
location = 2200;
String stageTable = getStageTableName(targetSchema, targetTable);
location = 2301;
String strSQL =
"SELECT os.fn_replication('"
+ targetSchema
+ "', '"
+ targetTable
+ "', '"
+ externalSchema
+ "', '"
+ stageTable
+ "', '"
+ appendColumnName
+ "');";
if (debug) Logger.printMsg("Executing function: " + strSQL);
location = 2400;
stmt.executeQuery(strSQL);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static void dropExternalTable(Connection conn, String targetSchema, String targetTable)
throws SQLException {
String method = "dropExternalTable";
int location = 1000;
try {
location = 2000;
String externalTable = getExternalTableName(targetSchema, targetTable);
location = 2100;
Statement stmt = conn.createStatement();
location = 2200;
String strSQL =
"DROP EXTERNAL TABLE IF EXISTS \"" + externalSchema + "\".\"" + externalTable + "\"";
if (debug) Logger.printMsg("Dropping External Table (if exists): " + strSQL);
location = 2303;
stmt.executeUpdate(strSQL);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
/**
* Method declaration
*
* @return
*/
private Channel init() {
try {
mSocket.setTcpNoDelay(true);
mInput = new DataInputStream(new BufferedInputStream(mSocket.getInputStream()));
mOutput = new DataOutputStream(new BufferedOutputStream(mSocket.getOutputStream()));
String user = mInput.readUTF();
String password = mInput.readUTF();
Channel c;
try {
mServer.trace(mThread + ":trying to connect user " + user);
return mDatabase.connect(user, password);
} catch (SQLException e) {
write(new Result(e.getMessage()).getBytes());
}
} catch (Exception e) {
}
return null;
}
private static void executeExt(Connection gpConn, int connectionId, String selectSQL)
throws Exception {
String method = "executeExt";
int location = 1000;
String sourceType = "";
String sourceServer = "";
String sourceInstance = "";
int sourcePort = 0;
String sourceDatabase = "";
String sourceUser = "";
String sourcePass = "";
String strSQL = "";
Connection conn = null;
int fetchSize = 10;
try {
location = 3000;
ResultSet rs;
Statement stmt;
location = 3010;
strSQL = GP.getExtConnectionDetails(gpConn, connectionId);
location = 3020;
stmt = gpConn.createStatement();
location = 3030;
rs = stmt.executeQuery(strSQL);
while (rs.next()) {
sourceType = rs.getString(1);
sourceServer = rs.getString(2);
sourceInstance = rs.getString(3);
sourcePort = rs.getInt(4);
sourceDatabase = rs.getString(5);
sourceUser = rs.getString(6);
sourcePass = rs.getString(7);
}
location = 3090;
if (sourceType.equals("sqlserver")) {
location = 3100;
conn = CommonDB.connectSQLServer(sourceServer, sourceInstance, sourceUser, sourcePass);
location = 3300;
// execute the SQL Statement
CommonDB.outputData(conn, selectSQL);
location = 3400;
conn.close();
} else if (sourceType.equals("oracle")) {
location = 4000;
fetchSize = Integer.parseInt(GP.getVariable(gpConn, "oFetchSize"));
location = 4100;
conn =
CommonDB.connectOracle(
sourceServer, sourceDatabase, sourcePort, sourceUser, sourcePass, fetchSize);
location = 4200;
// execute the SQL Statement
CommonDB.outputData(conn, selectSQL);
location = 4400;
conn.close();
}
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
} finally {
if (conn != null) conn.close();
}
}
private static void executeOS(
String sourceType,
String sourceServer,
String sourceInstance,
int sourcePort,
String sourceDatabase,
String sourceSchema,
String sourceTable,
String refreshType,
String appendColumnName,
int appendColumnMax,
Connection gpConn,
int queueId)
throws Exception {
String method = "executeOS";
int location = 1000;
String sourceUser = "";
String sourcePass = "";
String strSQL = "";
int fetchSize = 10;
Connection conn = null;
try {
location = 3000;
ResultSet rs;
Statement stmt;
location = 3010;
strSQL = GP.getQueueDetails(gpConn, queueId);
location = 3020;
stmt = gpConn.createStatement();
location = 3040;
rs = stmt.executeQuery(strSQL);
while (rs.next()) {
sourceUser = rs.getString(1);
sourcePass = rs.getString(2);
}
location = 3090;
if (sourceType.equals("sqlserver")) {
location = 3100;
conn = CommonDB.connectSQLServer(sourceServer, sourceInstance, sourceUser, sourcePass);
location = 3200;
// create SQL statement for selecting data
strSQL =
SQLServer.getSQLForData(
conn,
sourceDatabase,
sourceSchema,
sourceTable,
refreshType,
appendColumnName,
appendColumnMax);
location = 3300;
// execute the SQL Statement
CommonDB.outputData(conn, strSQL);
location = 3400;
conn.close();
} else if (sourceType.equals("oracle")) {
location = 4000;
fetchSize = Integer.parseInt(GP.getVariable(gpConn, "oFetchSize"));
location = 4100;
conn =
CommonDB.connectOracle(
sourceServer, sourceDatabase, sourcePort, sourceUser, sourcePass, fetchSize);
location = 4200;
// execute the SQL Statement
strSQL =
Oracle.getSQLForData(
conn, sourceSchema, sourceTable, refreshType, appendColumnName, appendColumnMax);
location = 4300;
// execute the SQL Statement
CommonDB.outputData(conn, strSQL);
location = 4400;
conn.close();
}
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
} finally {
if (conn != null) conn.close();
}
}
public static void createExternalTable(
Connection conn,
String osServer,
String refreshType,
String sourceTable,
String targetSchema,
String targetTable,
String maxId,
int queueId,
int jobPort)
throws SQLException {
String method = "createExternalTable";
int location = 1000;
try {
location = 2000;
String externalTable = getExternalTableName(targetSchema, targetTable);
location = 2100;
Statement stmt = conn.createStatement();
String createSQL =
"CREATE EXTERNAL TABLE \"" + externalSchema + "\".\"" + externalTable + "\" \n (";
location = 2309;
String strSQL =
"SELECT c.column_name, \n"
+ " CASE WHEN c.data_type = 'character' THEN c.data_type || '(' || c.character_maximum_length || ')' ELSE c.data_type END AS data_type \n"
+ "FROM INFORMATION_SCHEMA.COLUMNS c \n"
+ "WHERE table_schema = '"
+ targetSchema
+ "' \n"
+ " AND table_name = '"
+ targetTable
+ "' \n"
+ "ORDER BY ordinal_position";
location = 2400;
ResultSet rs = stmt.executeQuery(strSQL);
location = 2500;
while (rs.next()) {
location = 2600;
if (rs.getRow() == 1) {
location = 2700;
createSQL = createSQL + "\"" + rs.getString(1) + "\" " + rs.getString(2);
} else {
location = 2800;
createSQL = createSQL + ", \n \"" + rs.getString(1) + "\" " + rs.getString(2);
}
}
location = 2900;
createSQL = createSQL + ") \n";
////////////////////////////////////////////
// Create location for External Table
////////////////////////////////////////////
location = 3000;
// replace space in the maxId because this could now be a date
maxId = maxId.replace(" ", "SPACE");
location = 3100;
String extLocation =
"LOCATION ('gpfdist://"
+ osServer
+ ":"
+ jobPort
+ "/config.properties+"
+ queueId
+ "+"
+ maxId
+ "+"
+ refreshType
+ "+"
+ sourceTable
+ "#transform=externaldata"
+ "')";
location = 3400;
extLocation = extLocation + "\n" + "FORMAT 'TEXT' (delimiter '|' null 'null' escape '\\\\')";
////////////////////////////////////////////
// Add createSQL with Java Command to exec.
////////////////////////////////////////////
location = 3500;
createSQL = createSQL + extLocation;
////////////////////////////////////////////
// Create new external web table
////////////////////////////////////////////
location = 4000;
if (debug) Logger.printMsg("Creating External Table: " + createSQL);
stmt.executeUpdate(createSQL);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
} catch (Exception e) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + e.getMessage() + ")");
}
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
ResultSet rs = null;
try {
// SET UP Context environment, to look for Data Pooling Resource
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
DataSource ds = (DataSource) envCtx.lookup("jdbc/TestDB");
Connection dbcon = ds.getConnection();
// ########### SEARCH INPUT PARAMETERS, EXECUTE search
// #####################################################
Vector<String> params = new Vector<String>();
params.add(request.getParameter("fname"));
params.add(request.getParameter("lname"));
params.add(request.getParameter("title"));
params.add(request.getParameter("year"));
params.add(request.getParameter("director"));
List<Movie> movies = new ArrayList<Movie>();
movies = SQLServices.getMovies(params.toArray(new String[params.size()]), dbcon);
// ########## SET DEFAULT SESSION() PARAMETERS ####################################
request.getSession().removeAttribute("movies");
request.getSession().removeAttribute("linkedListMovies");
request.getSession().removeAttribute("hasPaged");
request.getSession().setAttribute("hasPaged", "no");
request.getSession().setAttribute("movies", movies);
request.getSession().setAttribute("currentIndex", "0");
request.getSession().setAttribute("defaultN", "5");
// ########## IF MOVIES FROM SEARCH NON-EMPTY ###########################################
List<String> fields = Movie.fieldNames();
int count = 1;
if (!movies.isEmpty()) {
request.setAttribute("movies", movies);
for (String field : fields) {
request.setAttribute("f" + count++, field);
}
request.getRequestDispatcher("../movieList.jsp").forward(request, response);
} else {
out.println("<html><head><title>error</title></head>");
out.println("<body><h1>could not find any movies, try your search again.</h1>");
out.println("<p> we are terribly sorry, please go back. </p>");
out.println("<table border>");
out.println("</table>");
}
dbcon.close();
} catch (SQLException ex) {
while (ex != null) {
System.out.println("SQL Exception: " + ex.getMessage());
ex = ex.getNextException();
}
} catch (java.lang.Exception ex) {
out.println(
"<html>"
+ "<head><title>"
+ "moviedb: error"
+ "</title></head>\n<body>"
+ "<p>SQL error in doGet: "
+ ex.getMessage()
+ "</p></body></html>");
return;
}
out.close();
}
public static boolean createTargetTable(
Connection conn,
String targetSchema,
String targetTable,
boolean targetAppendOnly,
boolean targetCompressed,
boolean targetRowOrientation,
String sourceType,
String sourceServer,
String sourceInstance,
int sourcePort,
String sourceDatabase,
String sourceSchema,
String sourceTable,
String sourceUser,
String sourcePass)
throws Exception {
String method = "createTargetTable";
int location = 1000;
try {
location = 2000;
boolean found = false;
String strSQL =
"SELECT COUNT(*) \n"
+ "FROM INFORMATION_SCHEMA.TABLES \n"
+ "WHERE TABLE_SCHEMA = '"
+ targetSchema
+ "' \n"
+ " AND TABLE_NAME = '"
+ targetTable
+ "'";
location = 2100;
Statement stmt = conn.createStatement();
location = 2200;
ResultSet rs = stmt.executeQuery(strSQL);
location = 2307;
while (rs.next()) {
if (rs.getInt(1) > 0) found = true;
}
location = 2400;
if (!(found)) {
String tableDDL =
CommonDB.getGPTableDDL(
sourceType,
sourceServer,
sourceInstance,
sourcePort,
sourceDatabase,
sourceSchema,
sourceTable,
sourceUser,
sourcePass,
targetSchema,
targetTable,
targetAppendOnly,
targetCompressed,
targetRowOrientation);
if (debug) Logger.printMsg("Table DDL: " + tableDDL);
location = 2800;
stmt.executeUpdate(tableDDL);
}
location = 3000;
return found;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static int executeSQL(Connection conn, String strSQL) throws SQLException {
String method = "executeSQL";
int location = 1000;
if (strSQL == null) strSQL = "";
try {
location = 2000;
int numRows = 0;
location = 2100;
String SQL = "";
location = 2150;
strSQL = strSQL.trim();
location = 2200;
int endPosition = strSQL.indexOf(";");
location = 2300;
Statement stmt = conn.createStatement();
location = 2400;
if (endPosition > -1) {
location = 2500;
while (endPosition > -1) {
location = 2600;
SQL = strSQL.substring(0, endPosition);
if (debug) Logger.printMsg("Executing sql: " + SQL);
// if select, execute query else execute update
if ((strSQL.toUpperCase()).startsWith("SELECT")) {
location = 2700;
stmt.execute(SQL);
} else {
location = 2900;
numRows = numRows + stmt.executeUpdate(SQL);
}
location = 3100;
strSQL = strSQL.substring(endPosition + 1).trim();
location = 3200;
endPosition = strSQL.indexOf(";");
}
} else if (strSQL.length() > 0) {
location = 4000;
if (debug) Logger.printMsg("Executing sql: " + SQL);
// if select, execute query else execute update
if ((strSQL.toUpperCase()).startsWith("SELECT")) {
location = 4200;
stmt.execute(strSQL);
// discard results
} else {
location = 4300;
numRows = stmt.executeUpdate(strSQL);
}
}
location = 5000;
return numRows;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static void main(String[] args) throws Exception {
String method = "main";
int location = 1000;
int argsCount = args.length;
String sourceType = "";
String sourceServer = "";
String sourceInstance = "";
int sourcePort = 0;
String sourceDatabase = "";
String sourceSchema = "";
String sourceTable = "";
String refreshType = "";
String appendColumnName = "";
int appendColumnMax = 0;
String gpDatabase = "";
int gpPort = 0;
int queueId = 0;
int connectionId = 0;
String selectSQL = "";
// this is an external table that a user defines
if (argsCount == 4) {
gpDatabase = args[0];
gpPort = Integer.parseInt(args[1]);
connectionId = Integer.parseInt(args[2]);
selectSQL = args[3];
}
// this is an extrenal table Outsourcer defines
else if (argsCount == 13) {
sourceType = args[0];
sourceServer = args[1];
sourceInstance = args[2];
sourcePort = Integer.parseInt(args[3]);
sourceDatabase = args[4];
sourceSchema = args[5];
sourceTable = args[6];
refreshType = args[7];
appendColumnName = args[8];
appendColumnMax = Integer.parseInt(args[9]);
gpDatabase = args[10];
gpPort = Integer.parseInt(args[11]);
queueId = Integer.parseInt(args[12]);
}
String gpServer = "localhost";
String gpUserName = System.getProperty("user.name");
String sourceUser = "";
String sourcePass = "";
Connection gpConn = null;
try {
location = 3000;
gpConn = CommonDB.connectGP(gpServer, gpPort, gpDatabase, gpUserName);
if (argsCount == 13) {
location = 3100;
executeOS(
sourceType,
sourceServer,
sourceInstance,
sourcePort,
sourceDatabase,
sourceSchema,
sourceTable,
refreshType,
appendColumnName,
appendColumnMax,
gpConn,
queueId);
} else if (argsCount == 4) {
location = 3200;
executeExt(gpConn, connectionId, selectSQL);
}
location = 4000;
gpConn.close();
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
} finally {
if (gpConn != null) gpConn.close();
}
}
public static void updateStatus(
Connection conn,
int queueId,
String status,
Timestamp queueDate,
Timestamp startDate,
String errorMessage,
int numRows,
int id,
String refreshType,
String targetSchema,
String targetTable,
boolean targetAppendOnly,
boolean targetCompressed,
boolean targetRowOrientation,
String sourceType,
String sourceServer,
String sourceInstance,
int sourcePort,
String sourceDatabase,
String sourceSchema,
String sourceTable,
String sourceUser,
String sourcePass,
String columnName,
String sqlText,
boolean snapshot)
throws SQLException {
String method = "updateStatus";
int location = 1000;
String strQueueId = setSQLString(queueId);
status = setSQLString(status);
String strQueueDate = setSQLString(queueDate);
String strStartDate = setSQLString(startDate);
errorMessage = setSQLString(errorMessage);
String strNumRows = setSQLString(numRows);
String strId = setSQLString(id);
refreshType = setSQLString(refreshType);
targetSchema = setSQLString(targetSchema);
targetTable = setSQLString(targetTable);
String strTargetAppendOnly = setSQLString(targetAppendOnly);
String strTargetCompressed = setSQLString(targetCompressed);
String strTargetRowOrientation = setSQLString(targetRowOrientation);
sourceType = setSQLString(sourceType);
sourceServer = setSQLString(sourceServer);
sourceInstance = setSQLString(sourceInstance);
String strSourcePort = setSQLString(sourcePort);
sourceDatabase = setSQLString(sourceDatabase);
sourceSchema = setSQLString(sourceSchema);
sourceTable = setSQLString(sourceTable);
sourceUser = setSQLString(sourceUser);
sourcePass = setSQLString(sourcePass);
columnName = setSQLString(columnName);
sqlText = setSQLString(sqlText);
String strSnapshot = setSQLString(snapshot);
try {
location = 2000;
Statement stmt = conn.createStatement();
location = 2400;
String strSQL =
"SELECT os.fn_update_status("
+ strQueueId
+ ", "
+ status
+ ", "
+ strQueueDate
+ ", "
+ strStartDate
+ ", ";
strSQL +=
errorMessage
+ ", "
+ strNumRows
+ ", "
+ strId
+ ", "
+ refreshType
+ ", "
+ targetSchema
+ ", "
+ targetTable
+ ", ";
strSQL +=
strTargetAppendOnly
+ ", "
+ strTargetCompressed
+ ", "
+ strTargetRowOrientation
+ ", "
+ sourceType
+ ", ";
strSQL +=
sourceServer
+ ", "
+ sourceInstance
+ ", "
+ strSourcePort
+ ", "
+ sourceDatabase
+ ", "
+ sourceSchema
+ ", ";
strSQL +=
sourceTable
+ ", "
+ sourceUser
+ ", "
+ sourcePass
+ ", "
+ columnName
+ ", "
+ sqlText
+ ", "
+ strSnapshot
+ ")";
if (debug) Logger.printMsg("Updating Status: " + strSQL);
location = 2500;
stmt.executeQuery(strSQL);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
