JDBC Driver closing inputstream before i have a chance to use it?

[Ryan Kostrzewa]

Hi everyone!

In my code, I am using the Apache POI libraries to generate some Excel and Word documents from templates, which are stored in a database as BLOBs. I load the template as an InputStream, and then use that stream to create the objects the Apache POI library uses. It works in full when I run my code locally. The issue I'm running into is when I deploy my code to a Dev or QA server, I'm getting the exception below. Line 7 in my sample code below the exception is Line 659 in "FileOpsImpl.java", as referenced in the exception. It looks to me like somehow that input stream is being closed before I get to that line in the code. Does that make sense? Is that possible? Is there any way to resolve this? I'm obviously not manually closing it until wayyy later in the code. Is there anything I can do about this?

Thanks a ton!

java.io.IOException: [BEA][Oracle JDBC Driver]Object has been closed.
at weblogic.jdbc.base.BaseInputStreamWrapper.validateClosedState(Unknown Source)
at weblogic.jdbc.base.BaseInputStreamWrapper.read(Unknown Source)
at java.io.FilterInputStream.read(FilterInputStream.java:116)
at java.io.PushbackInputStream.read(PushbackInputStream.java:169)
at java.util.zip.ZipInputStream.readFully(ZipInputStream.java:397)
at java.util.zip.ZipInputStream.readLOC(ZipInputStream.java:230)
at java.util.zip.ZipInputStream.getNextEntry(ZipInputStream.java:74)
at org.apache.poi.openxml4j.util.ZipInputStreamZipEntrySource.(ZipInputStreamZipEntrySource.java:51)
at org.apache.poi.openxml4j.opc.ZipPackage.(ZipPackage.java:83)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:228)
at org.apache.poi.util.PackageHelper.open(PackageHelper.java:39)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.(XSSFWorkbook.java:187)
at com.capitalone.citrix.ssp.services.FileOpsImpl.generateDownload(FileOpsImpl.java:659)
at com.capitalone.citrix.ssp.controller.DownloadController.display(DownloadController.java:52)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:781)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:721)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:943)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:877)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:857)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:292)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:175)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3498)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
at weblogic.security.service.SecurityManager.runAs(Unknown Source)
at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2180)
at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2086)
at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1406)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:201)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:173)

My code is as follows:

			InputStream is = katetDao.getTemplate("ird");
			AutoPopulatingList<Requirements> irdList = katetService.openRequirementsList(recordModel.getRecordId()); 
			
			List<Requirements> windowsList = new ArrayList<Requirements>();
			List<Requirements> linuxList = new ArrayList<Requirements>();

			XSSFWorkbook my_xlsx_workbook = new XSSFWorkbook(is); // <-- Line 659, the offender
			XSSFSheet my_worksheet = my_xlsx_workbook.getSheetAt(0);
			//int tempRow = 15; 
			Cell cell = null; 
		
			int linuxRow = 4; 
			int windowsRow = 4; 
			for(int i = 0; i < irdList.size(); i++){
				Requirements irdObject = irdList.get(i);
				if(irdObject.getType().contains("DB")){
					continue; 
				}
				if(irdObject.getOs().equals("Windows")){
					my_worksheet = my_xlsx_workbook.getSheetAt(0);

					cell = my_worksheet.getRow(windowsRow).getCell(1);
					cell.setCellValue(recordModel.getProjectName());
					cell = my_worksheet.getRow(windowsRow).getCell(2);
					cell.setCellValue(recordModel.getProjectNumber());
					cell = my_worksheet.getRow(windowsRow).getCell(3);
					cell.setCellValue(irdObject.getCiName());
					cell = my_worksheet.getRow(windowsRow).getCell(4);
					cell.setCellValue(irdObject.getVirtualCluster());
					cell = my_worksheet.getRow(windowsRow).getCell(5);
					cell.setCellValue(irdObject.getCommonName());
					cell = my_worksheet.getRow(windowsRow).getCell(6);
					cell.setCellValue(irdObject.getDescription());
					cell = my_worksheet.getRow(windowsRow).getCell(7);
					cell.setCellValue(irdObject.getMachineType());
					cell = my_worksheet.getRow(windowsRow).getCell(9);
					cell.setCellValue(irdObject.getGoldImage());
					cell = my_worksheet.getRow(windowsRow).getCell(10);
					cell.setCellValue(irdObject.getRam());
					cell = my_worksheet.getRow(windowsRow).getCell(11);
					cell.setCellValue(irdObject.getNumCpus());
					cell = my_worksheet.getRow(windowsRow).getCell(12);
					cell.setCellValue(irdObject.getcDriveSize());
					cell = my_worksheet.getRow(windowsRow).getCell(13);
					cell.setCellValue(irdObject.getdDriveSize());
					cell = my_worksheet.getRow(windowsRow).getCell(17);
					cell.setCellValue(irdObject.getControlDomain());
					cell = my_worksheet.getRow(windowsRow).getCell(18);
					cell.setCellValue(irdObject.getAdDomains());
					cell = my_worksheet.getRow(windowsRow).getCell(27);
					cell.setCellValue(irdObject.getPatchingWeek());
					cell = my_worksheet.getRow(windowsRow).getCell(28);
					cell.setCellValue(irdObject.getPatchingDay());
					cell = my_worksheet.getRow(windowsRow).getCell(29);
					cell.setCellValue(irdObject.getPatchingWindowStart());
					cell = my_worksheet.getRow(windowsRow).getCell(30);
					cell.setCellValue(irdObject.getPatchingWindowEnd());
					cell = my_worksheet.getRow(windowsRow).getCell(31);
					cell.setCellValue(irdObject.getAppMaintenanceWindow());
					cell = my_worksheet.getRow(windowsRow).getCell(34);
					cell.setCellValue(irdObject.getVlanNumber());
					cell = my_worksheet.getRow(windowsRow).getCell(36);
					cell.setCellValue(irdObject.getIpAddress());
					cell = my_worksheet.getRow(windowsRow).getCell(37);
					cell.setCellValue(irdObject.getGateway());
					cell = my_worksheet.getRow(windowsRow).getCell(38);
					cell.setCellValue(irdObject.getSubnetMask());
					cell = my_worksheet.getRow(windowsRow).getCell(40);
					cell.setCellValue(irdObject.getIpToIlo());
					cell = my_worksheet.getRow(windowsRow).getCell(41);
					cell.setCellValue(irdObject.getMacAddress());
					cell = my_worksheet.getRow(windowsRow).getCell(42);
					cell.setCellValue(irdObject.getProvisionMacAddress());
					cell = my_worksheet.getRow(windowsRow).getCell(43);
					cell.setCellValue(irdObject.getPatchingDay());
					cell = my_worksheet.getRow(windowsRow).getCell(50);
					cell.setCellValue("FALSE");
					cell = my_worksheet.getRow(windowsRow).getCell(51);
					cell.setCellValue("TRUE");
					cell = my_worksheet.getRow(windowsRow).getCell(57);
					cell.setCellValue(irdObject.getAppEnvCi());
					cell = my_worksheet.getRow(windowsRow).getCell(58);
					cell.setCellValue(irdObject.getCiOwner());
					cell = my_worksheet.getRow(windowsRow).getCell(59);
					cell.setCellValue(irdObject.getAdditionalEmail());
					
					windowsRow++;
				} else if (irdObject.getOs().equals("Linux")){
					my_worksheet = my_xlsx_workbook.getSheetAt(1);

					cell = my_worksheet.getRow(windowsRow).getCell(1);
					cell.setCellValue(recordModel.getProjectName());
					cell = my_worksheet.getRow(windowsRow).getCell(2);
					cell.setCellValue(recordModel.getProjectNumber());
					cell = my_worksheet.getRow(windowsRow).getCell(3);
					cell.setCellValue(irdObject.getCiName());
					cell = my_worksheet.getRow(windowsRow).getCell(4);
					cell.setCellValue(irdObject.getVirtualCluster());
					cell = my_worksheet.getRow(windowsRow).getCell(5);
					cell.setCellValue(irdObject.getCommonName());
					cell = my_worksheet.getRow(windowsRow).getCell(6);
					cell.setCellValue(irdObject.getDescription());
					cell = my_worksheet.getRow(windowsRow).getCell(7);
					cell.setCellValue(irdObject.getMachineType());
					cell = my_worksheet.getRow(windowsRow).getCell(9);
					cell.setCellValue(irdObject.getGoldImage());
					cell = my_worksheet.getRow(windowsRow).getCell(10);
					cell.setCellValue(irdObject.getRam());
					cell = my_worksheet.getRow(windowsRow).getCell(11);
					cell.setCellValue(irdObject.getNumCpus());
					cell = my_worksheet.getRow(windowsRow).getCell(12);
					cell.setCellValue(irdObject.getAdditionalDiskSize());
					cell = my_worksheet.getRow(windowsRow).getCell(16);
					cell.setCellValue(irdObject.getControlDomain());
					cell = my_worksheet.getRow(windowsRow).getCell(25);
					cell.setCellValue(irdObject.getPatchingWeek());
					cell = my_worksheet.getRow(windowsRow).getCell(26);
					cell.setCellValue(irdObject.getPatchingDay());
					cell = my_worksheet.getRow(windowsRow).getCell(27);
					cell.setCellValue(irdObject.getPatchingWindowStart());
					cell = my_worksheet.getRow(windowsRow).getCell(28);
					cell.setCellValue(irdObject.getPatchingWindowEnd());
					cell = my_worksheet.getRow(windowsRow).getCell(29);
					cell.setCellValue(irdObject.getAppMaintenanceWindow());
					cell = my_worksheet.getRow(windowsRow).getCell(32);
					cell.setCellValue(irdObject.getVlanNumber());
					cell = my_worksheet.getRow(windowsRow).getCell(34);
					cell.setCellValue(irdObject.getIpAddress());
					cell = my_worksheet.getRow(windowsRow).getCell(35);
					cell.setCellValue(irdObject.getGateway());
					cell = my_worksheet.getRow(windowsRow).getCell(36);
					cell.setCellValue(irdObject.getSubnetMask());
					cell = my_worksheet.getRow(windowsRow).getCell(37);
					cell.setCellValue(irdObject.getIpToIlo());
					cell = my_worksheet.getRow(windowsRow).getCell(38);
					cell.setCellValue(irdObject.getMacAddress());
					cell = my_worksheet.getRow(windowsRow).getCell(39);
					cell.setCellValue(irdObject.getProvisionMacAddress());
					cell = my_worksheet.getRow(windowsRow).getCell(47);
					cell.setCellValue("FALSE");
					cell = my_worksheet.getRow(windowsRow).getCell(48);
					cell.setCellValue("TRUE");
					cell = my_worksheet.getRow(windowsRow).getCell(54);
					cell.setCellValue(irdObject.getAppEnvCi());
					cell = my_worksheet.getRow(windowsRow).getCell(55);
					cell.setCellValue(irdObject.getCiOwner());
					cell = my_worksheet.getRow(windowsRow).getCell(56);
					cell.setCellValue(irdObject.getAdditionalEmail());					
					linuxRow++;
				}
			}


			is.close();
			File irdForm = new File("ird_form.xlsx");
			FileOutputStream output_file = new FileOutputStream(irdForm);

			my_xlsx_workbook.write(output_file);
			output_file.close(); 
			return irdForm; 

[Rob Spoor]

Can you show us the code for katetDao.getTemplate?

[Ryan Kostrzewa]

Indeed -

public InputStream getTemplate(String templateName) throws Exception {
		Connection conn = null;
		PreparedStatement ps = null;
		InputStream is = null; 
		try {
			System.out.println("GET");
			conn = getDRAConnection();
			
			String strQuery = "select FILE_DATA from KATET_TEMPLATES where TEMPLATE_NAME = ?";

			ps = conn.prepareStatement(strQuery);
			ps.setString(1, templateName);
			ResultSet rs = ps.executeQuery();

			while(rs.next()){
				is = (rs.getBinaryStream("FILE_DATA"));
				System.out.println("RS Success");
			}

		} catch (Exception e) {
			throw e;
		} finally {
			try {
				if(ps != null) {
					ps.close();
				}
				if(conn != null) {
					conn.close();
				}
			} catch (SQLException e){
				throw e; 
			}
		}
		return is;	
	}

[Ryan Kostrzewa]

I'm actually suspicious that this might be a Weblogic issue, something about how it's handling the JNDI connection while retrieving something out of the database larger than, say, a set of Strings, but that's just a guess at this point.

[Claude Moore]

Ryan Kostrzewa wrote:I'm actually suspicious that this might be a Weblogic issue, something about how it's handling the JNDI connection while retrieving something out of the database larger than, say, a set of Strings, but that's just a guess at this point.

You may be right...how long does the method takes to read all data and write them in xls file? Is it possible that it takes so long that weblogic timeouts the transaction?

[Rob Spoor]

Line 27 is the culprit, and if you remove that then you still have line 30. Closing a Connection closes all of its JDBC resources. That includes statements. Closing a Statement closes all of its JDBC resources. That includes ResultSets. And closing a ResultSet closes all of its JDBC resources. And that in the end causes the issue - one of those resources is the InputStream.

[Ryan Kostrzewa]

Claude - about 3 seconds when it's run locally - I can't imagine it would be too much longer in other environments, but possibly a little bit more.

Rob - ahhh, that makes sense! Let me add that in. In that case, when SHOULD I close the connection? Seems like maybe returning an inputstream isn't a good idea then, because you'd have to manage that connection in whichever method is calling "getTemplate()" in this case, right?

[Rob Spoor]

Correct. You could possibly wrap the input stream into a custom implementation that keeps a reference to the result set, prepared statement and connection, and closes all three when the input stream is closed, but that still does not seem like a very good solution. Letting the DAO method return the worksheet also seems wrong (logic goes into a DAO... not good).

If you use a Java 8 enabled container (don't know if WebLogic is one) you could change your getTemplate method into a processTemplate method that takes a Consumer<InputStream> or a similar functional interface, then you could keep the logic to the calling method but still keep the closing of resources local to the DAO method (which just takes the logic as a parameter).

[Ryan Kostrzewa]

Just for kicks (ignoring convention, for a moment) I tried just hardcoding the getTemplate() function into the code instead of calling it, and handling the streams, connections, and statements all from there, but still no dice. It's bizarre because this does work when I'm running the code locally, so I'm getting the feeling more and more that it's a Weblogic issue. I'm also not 100% sure it's a timeout, because the error seems to come up immediately upon clicking the "download" button. It's not like it's sitting there thinking for a few seconds and then it crashes.

Man, this is tough.

[Ryan Kostrzewa]

Resolved! By complete and total dumb luck...

In the getTemplate function, I changed the segment of code from

while(rs.next()){
				is = (rs.getBinaryStream("FILE_DATA"));
				System.out.println("RS Success");
			}

to

while(rs.next()){
				blob = (rs.getBlob("FILE_DATA"));
				System.out.println("RS Success");
			}
			is = blob.getBinaryStream(); 

No idea why it worked, but it does. Hooray for not getting fired!