Re: sql select to file using java
Date: Mon, 22 Feb 2010 08:44:33 +1100
Message-ID: <87mxz2462m.fsf_at_lion.rapttech.com.au>
sil <schitroc_at_gmail.com> writes:
> On Feb 17, 11:33 pm, sil <schit..._at_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 auReceived on Sun Feb 21 2010 - 15:44:33 CST