package org.unitime.timetable.util;

import java.io.IOException;
import java.io.StringReader;
import java.io.StringWriter;
import java.sql.CallableStatement;
import java.sql.Connection;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.SAXReader;
import org.dom4j.io.XMLWriter;
import org.hibernate.engine.SessionFactoryImplementor;
import org.unitime.timetable.ApplicationProperties;
import org.unitime.timetable.interfaces.RoomAvailabilityInterface;
import org.unitime.timetable.model.dao._RootDAO;

public class BlobRoomAvailabilityService extends RoomAvailabilityService {
    private static Log sLog = LogFactory.getLog(RoomAvailabilityInterface.class);
    private String iRequestSql =
        ApplicationProperties.getProperty("tmtbl.room.availability.request","{ call room_avail_interface.request(?) }");
    private String iResponseSql =
        ApplicationProperties.getProperty("tmtbl.room.availability.response","{? = call room_avail_interface.response()}");

    protected void sendRequest(Document request) throws IOException {
        try {
            StringWriter writer = new StringWriter();
            (new XMLWriter(writer,OutputFormat.createPrettyPrint())).write(request);
            writer.flush(); writer.close();
            SessionFactoryImplementor hibSessionFactory = (SessionFactoryImplementor)new _RootDAO().getSession().getSessionFactory();
            Connection connection = hibSessionFactory.getConnectionProvider().getConnection();
            CallableStatement call = connection.prepareCall(iRequestSql);
            call.setString(1, writer.getBuffer().toString());
            call.execute();
            call.close();
            hibSessionFactory.getConnectionProvider().closeConnection(connection);
        } catch (Exception e) {
            sLog.error("Unable to send request: "+e.getMessage(),e);
        }
    }
    
    protected Document recieveResponse() throws IOException, DocumentException {
        try {
            SessionFactoryImplementor hibSessionFactory = (SessionFactoryImplementor)new _RootDAO().getSession().getSessionFactory();
            Connection connection = hibSessionFactory.getConnectionProvider().getConnection();
            CallableStatement call = connection.prepareCall(iResponseSql);
            call.registerOutParameter(1, java.sql.Types.CLOB);
            call.execute();
            String response = call.getString(1);
            call.close();
            hibSessionFactory.getConnectionProvider().closeConnection(connection);
            if (response==null || response.length()==0) return null;
            StringReader reader = new StringReader(response);
            Document document = (new SAXReader()).read(reader);
            reader.close();
            return document;
        } catch (Exception e) {
            sLog.error("Unable to recieve response: "+e.getMessage(),e);
            return null;
        }
    }
}

@@ -598,16 +598,70 @@
 erface;%0A
+import org.unitime.timetable.model.base._BaseRootDAO;%0A
 import o
@@ -701,16 +701,16 @@
 ootDAO;%0A
-
 %0Apublic 
@@ -1660,32 +1660,54 @@
 etConnection();%0A
+            try %7B%0A    
             Call
@@ -1772,24 +1772,28 @@
             
+    
 call.setStri
@@ -1834,32 +1834,36 @@
 ));%0A            
+   
+ 
 call.execute();%0A
@@ -1854,32 +1854,36 @@
 call.execute();%0A
+    
             call
@@ -1884,32 +1884,60 @@
   call.close();%0A
+            %7D finally %7B%0A    
             hibS
@@ -1995,32 +1995,46 @@
 on(connection);%0A
+            %7D%0A
         %7D catch 
@@ -2110,32 +2110,103 @@
 etMessage(),e);%0A
+        %7D finally %7B%0A            _RootDAO.closeCurrentThreadSessions();%0A
         %7D%0A    %7D%0A
@@ -2526,32 +2526,90 @@
 etConnection();%0A
+            String response = null;%0A            try %7B%0A    
             Call
@@ -2675,24 +2675,28 @@
             
+   
+ 
 call.registe
@@ -2738,32 +2738,36 @@
 B);%0A            
+    
 call.execute();%0A
@@ -2770,38 +2770,35 @@
 ();%0A            
+   
-String
  response = call
@@ -2816,32 +2816,36 @@
 1);%0A            
+ 
+   
 call.close();%0A  
@@ -2834,32 +2834,60 @@
   call.close();%0A
+            %7D finally %7B%0A    
             hibS
@@ -2945,32 +2945,46 @@
 on(connection);%0A
+            %7D%0A
             if (
@@ -3316,32 +3316,32 @@
 etMessage(),e);%0A
-
             retu
@@ -3341,32 +3341,103 @@
    return null;%0A
+        %7D finally %7B%0A            _RootDAO.closeCurrentThreadSessions();%0A
         %7D%0A    %7D%0A

make sure that no connection is kept open (especially when the executed query fails)