import java.io.*;
import java.sql.*;
import java.util.*;
import java.sql.Date;
import java.math.BigDecimal;
import com.adeptia.indigo.io.*;
import com.adeptia.indigo.utils.*;
import com.adeptia.indigo.storage.*;
import javax.security.auth.Subject;
import com.adeptia.indigo.security.AuthUtil;
import com.adeptia.indigo.services.transport.connector.DatabaseConnectionInfo;



// Magic starts from here
		// get the context variables from record
		OutputStream os = service.getOutputStream();
		Connection con = null;
		CallableStatement cstmt = null;
		RecordOutputStream _recordOutputStream = null;
		try {

			// sample prodid value used in this example
			String prodid = "5026";

			// if you want to get the prodid from context then use the following
			// syntax
			// you can set the value of the prodid in mapping using set context
			// function or in flow using put context var action and use it here
			// String prodid = (String) context.get("prodid");

			// copy paste the db info entity id and place it in below variable
			String dbConnectionInfoId = "192168001206135185020834500055";
			// retrieve database connection
			Subject _subject = AuthUtil.getAdminSubject();
			DatabaseConnectionInfo dbInfo = null;
			Entity beanObject1 = DatabaseConnectionInfo.class.newInstance();
			EntityManager entityManager1 = EntityManagerFactory
					.getEntityManager(beanObject1.getClass(), _subject);
			Iterator it = entityManager1.retrieve();
			while (it.hasNext()) {
				DatabaseConnectionInfo temp = (DatabaseConnectionInfo) it
						.next();
				String entityId = temp.getId();
				if (entityId.equals(dbConnectionInfoId)) {
					dbInfo = temp;
					break;
				}
			}
			con = JdbcUtils.getConnection(dbInfo, _subject);
			// execute stored procedure in database that expects one parameter
			cstmt = con.prepareCall("{ Call dbo.usp_EBR_GDM () }");

			// set the parameter with the variable declared above
			// here 1 means the first parameter in the procedure call
			// cstmt.setString (1, prodid);

			// if you have value such as decimal then use this example
			// cstmt.setBigDecimal (1, bd);
			// if you want to pass a date in the parameter then use this example
			// cstmt.setDate(2, new Date(System.currentTimeMillis()));

			// this executes the stsor proc
			// cstmt.execute();

			// read result set in XML and set it in output stream
			// go to process flow logs repository and see the data output of the
			// stor procedure resultset
			_recordOutputStream = RecordStreamFactory.createOutputStream(os,
					"XML", "UTF-8");
			_recordOutputStream.writeProlog();
			ResultSet rs = null;
			try {
				rs = cstmt.executeQuery();
				
				while (rs.next()) {
					ResultSetMetaData metaData = rs.getMetaData();
					int columnnCnt = metaData.getColumnCount();
					Record record = IoObjectPool.borrowRecord();
					String dbvendername = con.getMetaData()
							.getDatabaseProductName();
					for (int i = 1; i <= columnnCnt; i++) {
						Field field = new Field();
						FieldMeta fieldMeta = field.getMeta();
						fieldMeta.setName((metaData.getColumnName(i)).replaceAll(" ", "_").replaceAll("\\(","").replaceAll("\\)",""));	
              String[] map = FieldMeta.mapTypeAndFormat(metaData.getColumnTypeName(i), dbvendername);
						fieldMeta.setType(map[0]); 
						fieldMeta.setFormat(map[1]);
						int columnType = metaData.getColumnType(i);
						fieldMeta.setDbType(columnType);
						Object value = rs.getObject(i);
						if (columnType == Types.DATE) {
							if (value instanceof java.sql.Date) {
								java.sql.Date date = (java.sql.Date) value;
								value = new Long(date.getTime());
							}
						} else if (columnType == Types.TIMESTAMP) {
							if (value instanceof Timestamp) {
								Timestamp timestamp = (Timestamp) value;
								value = new Long(timestamp.getTime());
							}
						} else if (columnType == Types.TIME) {
							if (value instanceof Time) {
								Time time = (Time) value;
								value = new Long(time.getTime());
							}
						} else if (columnType == Types.BLOB) {
							if (value instanceof Blob) {
								Blob blob = (Blob) value;
								value = FieldMeta.getBlobValue(blob
										.getBinaryStream());
							}
						} else if (columnType == Types.CLOB) {
							if (value instanceof Clob) {
								Clob clob = (Clob) value;
								value = FieldMeta.getClobValue(clob
										.getCharacterStream(), "UTF-8");
							}
						}
						if (value == null) {
							field.setValue(null);
						} else {
							field.setValue(value.toString());
						}
						record.addField(field);
					}
					_recordOutputStream.writeRecord(record);
					_recordOutputStream.reset();
					IoObjectPool.returnRecord(record);
				}
			} finally {
				if (rs != null) {
					rs.close();
				}
			}
		} catch (Exception e) {
			service.getLogger().error(
					"Error while executing stored procedure :: "
							+ e.getMessage(), e);
			throw e;
		} finally {
			if (_recordOutputStream != null) {
				_recordOutputStream.close();
			}
			if (cstmt != null) {
				cstmt.close();
			}
			if (con != null) {
				con.close();
			}
		}