We just upgraded our Oracle DB from 9i to 10gR2. We have quite a bit of java code in the database. According to the DBAs we were using 1.3.1 java in 9i and are also using 1.3.1 with 10g. We can use 1.4 if necessary. We are using the sun.net.ftp library in a java object in the db. In this library there is a class called ftpclient. This ftp object in the DB began to fail after the upgrade. Could be coincidence.. In our code we are not issuing any methods of this class specifying to use active or passive mode. Our network people have shown us that the ftp is going into pasv mode right away at startup. According to our network people this used to start up in active mode and according to them ftp always starts in active mode, if not told to do otherwise. Are there default java settings in the database somewhere or in the sun.net.ftp library that control this setting? Im thinking somebody before my time in 9i may have set this to active and now by default becausse of new installation of the libraries its set to passive. Maybe. So, Its not the java object code setting it to passive and the ftp session is going immediately into 'PASV' mode. Whats interesting our fire wall does allow passive ftp and the ftp client does transfer some files before failing. Here is excerpt from the network rep. Question is..
How do I get this thing to go into active mode when it is clearly starting up in pasv mode and doing the odd ipv6 behavior below?
Is their something else I can use instead of this?
I did not write the code.
-- SNIP--
For what it's worth, we saw the same results from last night's attempt - even with our workaround in place. Immediately after the "No such file" message, the ftp client attempts to use an enhanced active FTP command while in passive mode. Our firewall still blocks this, as it interprets the command as an attack using the FTP protocol.
The command being issued is an "EPRT" command. This is an enhanced version of the "PORT" command that is typically used in the default (i.e., active) mode of FTP, where the client tells the server what TCP port he will be listening on, so that the server can send the data to this port. The enhanced part just means that it allows the client to specify an IPv6 address, but that format is not being used here.
Nevertheless, something is telling the ftp client to use passive FTP when it's probably not necessary.
-- SNIP --
import java.io.*;*
*import java.sql.*;
import oracle.sql.*;*
*import oracle.jdbc.driver.*;
import sun.net.ftp.*;
import java.net.Socket;
/****************************************************************/
/* */*
*/* This version: */*
*/* 1) FTP from remote server */*
*/* 2) Writes files to drive on DB Server */*
*/* 3) Deletes Files from remote server */*
*/* */*
*/* 08/25/2003 add ais_case.err_cde is null */*
*/* 10/07/2004 Remove update of AIS_CASE when error condition */*
*/* is encountered. Missing File check moved to */*
*/* SMS_DMV_TRANSFER Package */*
*/* */
/****************************************************************/
public class sims_ftp
{
static FtpClient ftpClientRead = null;
public static void get(String remoteHost,
NUMBER remotePort,
String remoteUsername,
String remotePassword,
String remoteDirectory,
String localDirectory,
String[] outStatus,
String[] outError) throws Exception
{
int id;
String dirDay = new String();
String filename = new String();
String casePKey = new String();
Date caseTransferDate;
String EOL = "\r\n";
InputStream is;
System.out.println("remoteDirectory: " + remoteDirectory);
try // ftp connection
{
outStatus[0] = "00";
outError[0] = "";
// set connection to transfer files
System.out.println("Read" +remoteHost );
ftpClientRead = new FtpClient(remoteHost, remotePort.intValue());
System.out.println("login");
ftpClientRead.login(remoteUsername, remotePassword);
// System.out.println("binary");
ftpClientRead.binary();
if (!remoteDirectory.equals("DEFAULT"))
{
ftpClientRead.cd(remoteDirectory);
}
System.out.println("Connected");
// set connection to delete files
// System.out.println("socket");
// Socket controlSock = new Socket(remoteHost, remotePort.intValue());
// System.out.println("outputstream");
// OutputStream osDelete = controlSock.getOutputStream();
// Writer writer = new OutputStreamWriter(osDelete);
//
// writer.write("USER " + remoteUsername + EOL);
// writer.flush();
// writer.write("PASS " + remotePassword + EOL);
// writer.flush();
// if (!remoteDirectory.equals("DEFAULT"))
// {
// writer.write("CWD " + remoteDirectory + EOL);
// writer.flush();
// }
// Connect to Oracle and get filenames
Connection conn = new OracleDriver().defaultConnection();
String sql = "select c.dmv_case_pk," +
" trunc(d.dmv_trfr_dte), " +
" to_char(d.dmv_trfr_dte,'dy')," +
" i.img_filename " +
" from ais_case c," +
" ais_document d," +
" ais_image i" +
" where c.err_cde is null" +
" and c.dmv_case_pk = d.dmv_case_pk" +
" and d.doc_id = i.doc_id" +
" and trunc(d.dmv_trfr_dte) = trunc(i.dmv_trfr_dte)" +
" and i.accd_rpt_src_ind = 'B'" +
" and i.img_filename is not null";
try // oracle execute sql
{
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(sql);
int length;
while (rset.next())
{
casePKey = rset.getString(1);
caseTransferDate = rset.getDate(2);
dirDay = rset.getString(3);
filename = rset.getString(4);
try // open filenames
{
is = ftpClientRead.get(filename);
File fileOut = new File(localDirectory + dirDay + "/" + filename);
FileOutputStream os = new FileOutputStream(fileOut);
// System.out.println("FILE: " + fileOut);
try // file data transfer
{
byte[] buffer = new byte[16264];
while((length = is.read(buffer)) != -1)
{
os.write(buffer, 0, length);
}
} // file data transfer
catch (Exception ex)
{
// *********** commented out ***************
// 10/07/2004 updateCase(conn, casePKey, caseTransferDate.toString(), "913");
// ******************************************
outStatus[0] = "99";
outError[0] = "ERROR: " + ex;
}
finally // file data transfer
{
// System.out.println("FINALLY");
os.close();
}
// Delete file
// writer.write("DELE " + filename + EOL);
// writer.flush();
}
catch (FileNotFoundException ex)
{
System.out.println("MISSING FILE: " + filename);
// *********** commented out ***************
// 10/07/2004 updateCase(conn, casePKey, caseTransferDate.toString(), "913");
// ******************************************
}
} // end while rset
rset.close();
stmt.close();
}
catch (Exception ex) // oracle execute sql
{
outStatus[0] = "99";
outError[0] = "ERROR: " + ex;
}
// close the delete connection
// writer.write("QUIT" + EOL);
// writer.flush();
conn.close(); // close the oracle connection
}
catch (Exception ex) // ftp connection
{
outStatus[0] = "99";
outError[0] = "ERROR: " + ex;
}
finally // ftp connection
{
if (ftpClientRead != null)
{
ftpClientRead.closeServer();
}
}
} // end get()
private static void updateCase(Connection inConnection,
String inCasePKey,
String inCaseTransferDate,
String inErrorCode) throws Exception
{
String usql = "update ais_case " +
" set err_cde = '" + inErrorCode + "'" +
" where dmv_case_pk = '" + inCasePKey + "'" +
" and trunc(dmv_trfr_dte) = trunc(to_date( '" + inCaseTransferDate +
"','YYYY-MM-DD'))";
Statement ustmt = inConnection.createStatement();
ustmt.executeUpdate(usql);
ustmt.close();
} // end updateCase
} // end class javatest
;
Edited by: slugo100 on Jun 19, 2008 6:10 AM
Edited by: slugo100 on Jun 19, 2008 6:17 AM