FTPClient within Oracle RDBMS problem

[Thomas Stiegler <ts...@kn-info.de>]

Hi,
I use the Jakarta commons net java classes v1.1 to transfer data via
ftp from within a PL/SQL procedure in the RDBMS without using a local
temporary file. The code fragments below show the principle.

This technique works pretty well on any Linux distributions and any
RDMBS version (8i, 9i both in several releases). But if I try to use
it on any Windows OS (checked with WinNT 4 SP6a, Win2000 SP3, WinXP
Pro SP1 and with many RDMBS versions - 8.1.6, 8.1.7, 9.2.0.1, 9.2.0.4)
the systems hangs often during the second call to the get method.
After that I have to shutdown the db and sometimes to reboot the
server to be able to transfer another file. This behavior is
reproducable in general and occours on any Windows OS and any RDBMS
mentioned above. Further investigations show that the first call of
any FTP function after the successful login always causes the system
to hang. The login process itself is always succesful. Using active or
passive mode makes no sense. It seems to me there may occour a
communication situation the common net ftp class can not handle inside
the PL/SQL environment respectively within the internal JVM on a
Windows OS.

Any hints to resolve this situation would be highly appreciated.

Regards, Tom.

Code fragments:

class FTP {
public boolean login(String sHost, String sUser, String sPassword)
throws IOException {
fc = new FTPClient();
fc.connect(sHost);
if (logReply() >= 500) return false;
if(!FTPReply.isPositiveCompletion(fc.getReplyCode())) {
fc.disconnect();
return false;
}
boolean bOk = fc.login(sUser, sPassword);
if (logReply() >= 500) return false;
return bOk;
}

public int getInTable(String sTable, String sDir, String sFile,
Connection c)
throws Exception {
byte b[] = new byte[4000];
int iID = 0;
int iSeq = 0;

PreparedStatement pstmt = c.prepareStatement(
"SELECT FTP_ID.NEXTVAL FROM DUAL");
ResultSet rs = pstmt.executeQuery();
if (rs.next()) iID = rs.getInt(1);
rs.close();
pstmt.close();

pstmt = c.prepareStatement(
"INSERT INTO " + sTable + " (ID, SEQ, DATA) VALUES(?, ?, ?)");

if (!sDir.equals("")) {
fc.changeWorkingDirectory(sDir);
if (logReply() >= 500) return -1;
}

InputStream is = fc.retrieveFileStream(sFile);
if (logReply() >= 500) return -1;
if (is == null) return -1;

long lStart = System.currentTimeMillis();
int iBytesRead;
while((iBytesRead = is.read(b, 0, 4000)) > -1) {
pstmt.setInt(1, iID);
pstmt.setInt(2, iSeq++);
pstmt.setString(3, new String(b, 0, iBytesRead));
pstmt.executeUpdate();
iBytes += iBytesRead;
}
pstmt.close();
is.close();
fc.completePendingCommand();
if (logReply() >= 500) return -1;
return iID;
}
}

Regards,

Thomas Stiegler

***************************************
k+n Informationssysteme GmbH
Ringstrasse 45
D-09247 Roehrsdorf

Phone: +49-(0)3722-5235-0
Fax: +49-(0)3722-5235-20
mailto:tstiegler@kn-info.de
http://www.kn-info.de
http://www.accountplus.de
http://www.billingsolutions.info
***************************************



---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org

Re: FTPClient within Oracle RDBMS problem

["Jeffrey D. Brekke" <jb...@wi.rr.com>]

>>>>> On Wed, 26 May 2004 11:22:44 +0200, Thomas Stiegler <ts...@kn-info.de> said:

[SNIPPED]

> InputStream is = fc.retrieveFileStream(sFile); if (logReply() >=
> 500) return -1; if (is == null) return -1;

> long lStart = System.currentTimeMillis(); int iBytesRead;
> while((iBytesRead = is.read(b, 0, 4000)) > -1) { pstmt.setInt(1,
> iID); pstmt.setInt(2, iSeq++); pstmt.setString(3, new String(b, 0,
> iBytesRead)); pstmt.executeUpdate(); iBytes += iBytesRead;
> }
> pstmt.close(); is.close(); fc.completePendingCommand(); if
> (logReply() >= 500) return -1; return iID;
> }
> }

I thought the completePendingCommand() should be done before the
closes, definitly before the is.close() call.

-- 
=====================================================================
Jeffrey D. Brekke                                   jbrekke@wi.rr.com
Wisconsin,  USA                                     brekke@apache.org
                                                    ekkerbj@yahoo.com


---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org