Writing from a Text file to BLOB

[navi kumar]

Hi,

I have a text file on UNIX file system. This text files contains some 5000 lines. Where each line represents a record in an Oracle Table.( basically i spooled a oracle table's data into this text file)

I have a java class that writes this text file into a BLOB column on oracle database.

As you know there are two modes: ASCII and BINARY.

Basically, ASCII mode should be used when end-of-line character translation is required. and Binary mode (a.k.a. image mode) should be used whenever no end-of-line translation is required.

I need end-of-line character. i.e, each record should be written in new line in the BLOB.

But with the code i have, i am doing it the other way.
for ex: the data in blob should be like
1|VA|ARTNVAFC|FALLS CHURCH|2|H2009|8A00746|8A00738|FALLS CHURCH||TESINC|RELEASED TO CONSTRUCTION|03/30/2004||139|28|0|0|0|0|0|0|167||827|DISTRIBUTION|112635|O|||||||
2|VA|ARTNVAFC|FALLS CHURCH|2|H2010|8A00746|8A00736|FALLS CHURCH||TESINC|RELEASED TO CONSTRUCTION|02/20/2004||178|0|0|0|0|0|0|0|178||1711|DISTRIBUTION|135843|O|||||||

JAVA PROGRAM: I am not including all the code.. these the snippet that uploads the BLOB file in oracle table with the file from UNIX file system. .

byte[] fileData=getFileData(fileName , filePath);
updateFileData(conn,fileName,fileData);
public byte[] getFileData(String fileName,String filePath)
{
// Returns the contents of the file in a byte array.
File file=null;
try
{
file=new File(filePath + fileName);
if (!file.exists())
{
logFileException(filePath + fileName + " does not exists in the directory /u22/extract/");
return null;
}
InputStream is = new FileInputStream(file);
// Get the size of the file
long length = file.length();
/* You cannot create an array using a long type. It needs to be an int type.
Before converting to an int type, check to ensure that file is not larger than Integer.MAX_VALUE.*/

// Create the byte array to hold the data
byte[] bytes = new byte[(int)length];
// Read in the bytes
int offset = 0;
int numRead = 0;
while (offset < bytes.length && (numRead=is.read(bytes, offset, bytes.length-offset)) >= 0)
{
offset += numRead;
}
// Ensure all the bytes have been read in
if (offset < bytes.length)
{
logFileException(filePath + fileName + " Could not completely read file ");
return null;
}
// Close the input stream and return bytes
is.close();
return bytes;

}
catch(Exception e)
{}
return null;
}

public boolean updateFileData(Connection conn , String fileName,byte[] fileData)
{
PreparedStatement pstmt = null;
ResultSet rs1=null;
java.sql.Blob tempBlob=null;
FileInputStream is;
java.io.OutputStream os;
try
{
String fileNameStr=fileName.toUpperCase() ;
fileNameStr=fileNameStr.replaceAll("'","''");
pstmt=conn.prepareStatement("select ATTACH_FILE_CONTENT FROM TABLE_ATTACHMENTS "
+" Where upper(ATTACH_NAME)='" + fileNameStr + "' for update ");
rs1 = pstmt.executeQuery();
while (rs1.next())
{
tempBlob=rs1.getBlob("ATTACH_FILE_CONTENT");
}
pstmt.close();
rs1.close();
os=((oracle.sql.BLOB)tempBlob).getBinaryOutputStream();
os.write(fileData);
os.flush();
os.close();

pstmt = conn.prepareStatement("UPDATE " + TABLE_ATTACHMENTS + " set UPDATED_BY='USER',ATTACH_FILE_CONTENT=?, UPDATED_DT=SYSDATE Where upper(ATTACH_NAME)=? ");
pstmt.setBlob(1,tempBlob);
pstmt.setString(2,fileName.toUpperCase());
pstmt.executeUpdate();
pstmt.close();
return true;

}
catch(Exception e)
{
e.printStackTrace();
log ("updateFileData() - file Name=" + fileName + " exception " + e.toString() );
try
{
pstmt.close();
rs1.close();
}
catch(Exception ex)
{ex.printStackTrace();}
}

finally{
try
{
if (rs1 != null) {rs1.close();}
if (pstmt != null) {pstmt.close();}
}
catch(Exception e)
{e.printStackTrace();}
}
return false;
}
the data in the blob with above code is like :

1|VA|ARTNVAFC|FALLS CHURCH|2|H2009|8A00746|8A00738|FALLS CHURCH||TESINC|RELEASED TO CONSTRUCTION|03/30/2004||139|28|0|0|0|0|0|0|167||827|DISTRIBUTION|112635|O|||||||with a line delimiter here 2|VA|ARTNVAFC|FALLS CHURCH|2|H2010|8A00746|8A00736|FALLS CHURCH||TESINC|RELEASED TO CONSTRUCTION|02/20/2004||178|0|0|0|0|0|0|0|178||1711|DISTRIBUTION|135843|O|||||||

Thanks a lot

Naveen

[Ernest Friedman-Hill]

Do you have a question? I don't really see one here.

What I do see is the greatest crime a Java programmer can commit: empty catch blocks. These nasty beasts swallow errors without warning, and make meaningful detection and repair of code defects all but impossible.

[navi kumar]

Hi Hill,

Thanks for the nimble reply.

Well With the code I have above, I am able to get data from a text file in unix file system into a BLOB column in Oracle table.

But..the data is expected to be present in the BLOB with each line in text file as a new line. But alas! with the code I have, i am writing it as stream of bytes with line delimiter(a rectangular box) at end of each line.

The question now is, is it possible to write the data into BLOB with each line seperated by a end-of-line instead of line delimiter(rectangular box)??

Regarding the catch block: The above code was cut shorted... morever there are no run time errors with the code!!

Thanks,
Naveen

[Ernest Friedman-Hill]

Please don't post the same question to more than one forum; see our explanation here. I've deleted the other copy of this thread.

Now as to your question: given your code here, your BLOB will contain exactly the bytes that are in the file, including any line separators. You haven't explained precisely what you want to have happen; I don't know what distinction you're making between "a line delimiter" and an "end-of-line" -- although I can guess.

On UNIX systems, text uses the '\n' character (or ASCII 10, or Control-J) to mark the end of each line. On Windows systems, however, two characters are used: the sequence '\r\n', (or ASCII 13 10, or Control-M Control-J). If you open a Windows text file in some UNIX programs, you'll see the Control-M at the end of each line. If you open a UNIX file in some Windows programs, you'll see the lines run together, possibly with some little glyph (like a hollow box) to indicate the '\n' characters. Notepad is one of the Windows programs that can't handle UNIX files, but many other Windows programs can.

So it sounds to me as if you're saving in one format, and reading in the other. What you need to do is choose one common format and use it throughout the application. For example, if you want to write the BLOB in Windows format, then read the file line-by-line using a BufferedReader and then write the \r\n line endings into the BLOB explicitly.

[navi kumar]

Hi Hill,

sorry for double post..a bit of desperation!!

Anyhow could you give me a sample java code for reading the file with BufferedReader and writing in windows format.

Me not so good with java...thanks for understanding the prob clearly.

[navi kumar]

I tried this, but is of no use.. returning a NULLPOINTEREXCEPTION:
InputStreamReader is = new FileReader(file);
BufferedReader bf = new BufferedReader(is);
while((bf.readLine())!= null)
{
if(bf.readLine().endsWith("\n"))
{
bf.readLine().replaceAll("\n", "\r\n");
}
}
I think "\n" is not found and hence NULLPOINTEREXCEPTION as suffix in endsWith(String suffix) is null!!

Any suggestions???

Thanks,
Naveen

[Jesper de Jong]

Each time you call bf.readLine(), it reads the next line. So what do you think happens in this piece of code:

while ((bf.readLine())!= null)   // Reads a line
{
    if (bf.readLine().endsWith("\n"))   // Reads the next line
    {
        bf.readLine().replaceAll("\n", "\r\n");   // Reads the next line
    }
}

Replace this with something like this:

String line;
while ((line = bf.readLine())!= null)   // Reads a line
{
    if (line.endsWith("\n"))   // Look at the line we just read
    {
        line.replaceAll("\n", "\r\n");   // Look at the line we just read
    }
 
    // Add some code here to do something with the line
}

[navi kumar]

Thanks for the reply.

Yes, i recoded it ...it is working fine now.