sql select to file using java
Date: Wed, 17 Feb 2010 20:33:49 -0800 (PST)
Message-ID: <4b76c2bf-ef42-4152-b850-70d67ff49c82_at_f8g2000yqn.googlegroups.com>
Hi All,
I have a UNIX script that runs Java code to select data from Oracle
tables and write it to a file. Following is the existing code (which
is old, was written by someone else, and runs on Java 1.4). The
problem I am having is that some accounts are not written to the
result file, although if I run the queries individually (on SQL
Developer) the accounts are selected. The script runs without any
errors. NOTE: the accounts that are not written to the file are at the
end of the sql cursor. In fact any account after 200,000 line is not
written to the file.
Question: I am thinking that maybe there is a limitation in the size
of the file written to disk? What could be the cause of this anomaly.
Any help is very much appreciated!
public class ExtractAccountInfor {
Connection connection = null;
public ExtractAccountInfor(String dbstring, String user, String password, String fileName) {
try {
DriverManager.registerDriver(new
oracle.jdbc.driver.OracleDriver());
int i = 0;
while (connection == null) {
System.out.println("Try to get database connection.");
connection =
DriverManager.getConnection(dbstring,user,password);
Thread.sleep(2000);
i++;
if (i == 5) {
System.out.println("Failed to get database
connection, 5 times attend!");
System.exit(0);
}
}
java.util.Date now = new java.util.Date();
DateFormat formatter = null;
formatter = new SimpleDateFormat("MM/dd/yyyy:HH:mm:ss");
String createDate =formatter.format(now);
Statement stmt = null;
ResultSet rset = null;
Statement stmt2 = null;
ResultSet rset2 = null;
StringBuffer outPutResult = new StringBuffer();
StringBuffer currentLine = new StringBuffer();
FileOutputStream fos2 = null;
fos2 = new FileOutputStream(fileName);
int count =0;
try{
stmt = connection.createStatement();
String sql = "select account_number, account_suffix, enabled
from account order by account_number,account_suffix ";
System.out.println("Start getting account information from
database.");
rset = stmt.executeQuery(sql);
String currentAccount = "";
while (rset.next()) {
String account = rset.getString(1);
System.out.println( "(1) Account: " + account);
String suffix = rset.getString(2);
stmt2 = connection.createStatement();
String sql2 = "select n.node_id, n.node_desc,
to_char(n.FTC_CERT_DOCUMENT_DATE, 'mm/dd/yyyy:hh24:mi:ss'),
n.FTC_CERT_DOCUMENT_AVAILABLE, p.product_code " +
"from xxxxxxxxxxxxxx p, xxxx n " +
"where p.account_number = " + account + " and " +
"p.account_suffix = '" + suffix + "' and " +
"n.node_id = p.node_id and " +
"( p.product_code = 1 or " +
" p.product_code = 4 or " +
" p.product_code = 5 or " +
" p.product_code = 3 )";
rset2 = stmt2.executeQuery(sql2);
String nodeID = "";
String nodeDec = "";
String mvr = "";
String clua = "";
String clup = "";
String ncf = "";
int product = 0;
while (rset2.next()) {
System.out.println( "(2) Account: " + account +
suffix);
nodeID = rset2.getString(1);
nodeDec = rset2.getString(2);
}
System.out.println( "(3) Account: " + account);
String accountEnable = rset.getString(3);
currentLine.delete(0, currentLine.toString().length());
currentLine.append("\"");
currentLine.append(account);
currentLine.append(suffix);
currentLine.append("\",\"");
currentLine.append(accountEnable);
currentLine.append("\",\"");
currentLine.append(nodeID);
currentLine.append("\",\"");
currentLine.append(nodeDec);
outPutResult.append(currentLine.toString());
System.out.println( "CurretLine: " + currentLine);
count++;
rset2.close();
stmt2.close();
if (count%1000==0){
fos2.write(outPutResult.toString().getBytes());
outPutResult = new StringBuffer("");
}
}
} catch (Exception x ){
x.printStackTrace();
System.out.println("Exception1: currentLine" +
currentLine.toString());
} finally {
try {
if (rset != null) {
rset.close();
}
if (stmt != null) {
stmt.close();
}
}
catch (Exception e) {
e.printStackTrace();
System.out.println("Exception2: " +
e.getMessage());
}
}
fos2.close();
System.out.println("Finish write file, total record: " +
count);
} catch (Exception x) {
System.out.println("Exception3: " + x.getMessage());
x.printStackTrace();
try {
if (connection != null) {
connection.close();
}
}
catch (SQLException e) {
System.out.println("Exception3: " + e.getMessage());
e.printStackTrace();
}
}
finally {
try {
if (connection != null) {
connection.close();
}
}
catch (Exception x) {
System.out.println("Exception4: " + x.getMessage());
x.printStackTrace();
}
}
}
public static void main(String[] args) {
if (args.length < 4) {
System.out.print("Worng Usage!!");
System.out.print("Usage: ArchiveTIFImage [db string] [db
userID] [db password] [file name]");
return;
}
try {
ExtractAccountInfor extract = new
ExtractAccountInfor(args[0], args[1], args[2], args[3]);
}
catch (Exception x) {
System.out.println("Exception5: " + x.getMessage());
x.printStackTrace();
}
System.exit(0);
}
} Received on Wed Feb 17 2010 - 22:33:49 CST