sql select to file using java [Oracle Help]

Prev: Simple update statement (or is it?)
Next: Charts - How to customize data labels?

From: sil on 17 Feb 2010 23:33

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);
}
}

From: sil on 19 Feb 2010 13:59

On Feb 17, 11:33 pm, sil <schit...(a)gmail.com> wrote:
> 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);
> }
>
> }

I found the problem in the java code itself. The last outPutResult
would never be persisted to the file when the count variable is not a
multiple of 1,000.

From: Tim X on 21 Feb 2010 16:44

sil <schitroc(a)gmail.com> writes:

> On Feb 17, 11:33 pm, sil <schit...(a)gmail.com> wrote:
>> 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);
>> }
>>
>> }
>
> I found the problem in the java code itself. The last outPutResult
> would never be persisted to the file when the count variable is not a
> multiple of 1,000.

Glad you found the bug.

Thanks also for reminding me why I'm so very pleased I no longer have to
write Java!

Tim

--
tcross (at) rapttech dot com dot au

From: Gerard H. Pille on 22 Feb 2010 14:19

Tim X wrote:
>
> Glad you found the bug.
>
> Thanks also for reminding me why I'm so very pleased I no longer have to
> write Java!
>
> Tim
>

Tim, that was an example of how NOT to write Java, how NOT to access an Oracle database, and so
forth.

|
Pages: 1
Prev: Simple update statement (or is it?)
Next: Charts - How to customize data labels?