h3. Creating an Extractor for downloading MaxDB Table Data to XML Most of the database query tools allow the user to export the data to local files. This is a very important feature that every one expects. But the main thing that is to be noticed is the format in which it is getting downloaded. Here we have a main question to answer. Is it possible to make other systems understand the contents of the file with minimum manipulation? I hope most of the cases this does not hold well. This obstacle has been overcome using the XML format which can be understood by any system. I found a tool named SQLYog (http://www.webyog.com) which had an option to download table data to XML format. 
I thought of trying to download the MaxDB Table data to XML format. The Excel and an ordinary file format were the two possibilities I had with SQL Studio. 
Then I developed a simple java application to download the MaxDB data to XML format. This weblog discusses the same considering a simple table with a few records for test purpose. Table Definition I took a simple table with 3 fields for the discussed case. 
Table Contents The test table contained three records that are to be downloaded to XML. 
The Java Extractor Program In this program the ResultSetMetaData plays a key role while building the XML tags for the data that was downloaded. /** * Project :DataDownloadAsXML * Date :Jul 26, 2005 * Programmed By: Kathirvel Balakrishnan * Company : Wipro Technologies * Designation : Project Engineer */ package com.xmldata; import java.io.*; import java.sql.*; public class ExtractMaxDBDataToXML { String serverName = null; String dataBase = null; String tableName = null; String userName = null; String password = null; String fileName = null; Connection myConnection; Statement myStatement; ResultSet myRs = null; ResultSetMetaData myRsM = null; /** * The constructors are created for the program. */ public ExtractMaxDBDataToXML(String serverName, String dataBase, String tableName, String userName, String password, String fileName){ this.serverName = serverName; this.dataBase = dataBase; this.tableName = tableName; this.userName = userName; this.password = password; this.fileName = fileName; } public void loadDriver(){ try{ Class.forName("com.sap.dbtech.jdbc.DriverSapDB"); System.out.println("The Driver has been loaded successfully!"); }catch(Exception E1){ System.out.println("Unable to load the error!"); System.out.println("Exceptions:"+E1.toString()); System.exit(1); } } public void createXML() throws IOException { System.out.println("Establishing connection to Server"); try{ FileWriter xmlWriter = new FileWriter(fileName); //Establishing the connection with the database myConnection = DriverManager.getConnection("jdbc:sapdb://" + serverName + "/" + dataBase, userName, password); myStatement = myConnection.createStatement(); System.out.println("Connection to Server was Established"); // Executing the SQL Query myRs = myStatement.executeQuery("select * from "+tableName); myRsM = myRs.getMetaData(); // Processing the resultset and downloading data to XML System.out.println("Data download to XML started\n"); xmlWriter.write(""); for(int i=1; i<=myRsM.getColumnCount();i++){ System.out.print(myRs.getString(i)+"\t"); xmlWriter.write("<"+myRsM.getColumnName(i)+">"+myRs.getString(i)+"</"+myRsM.getColumnName(i)+">"); } System.out.println("\n"); xmlWriter.write(""); } xmlWriter.write(""); System.out.println("Data successfully downloaded to XML"); // Closing the connection parameters xmlWriter.close(); myRs.close(); myStatement.close(); myConnection.close(); }catch(IOException E){ System.out.println("File Handling Errors :"+E.toString()); }catch(Exception E2) { // Prints if any exception is raised System.out.println(" Exceptions : "+E2.toString()); } } public static void main(String[] args) throws IOException { ExtractMaxDBDataToXML myXML = new ExtractMaxDBDataToXML("localhost","TEST","ACCESSCARD","DBADMIN","maxdb","d:/MaxDB.xml"); myXML.loadDriver(); myXML.createXML(); } } The Result of the Program The data was successfully extracted to XML format and the results were displayed in the console. 
The extracted XML file The XML file created had all the details and is given below. 
I thought of trying to download the MaxDB Table data to XML format. The Excel and an ordinary file format were the two possibilities I had with SQL Studio. Then I developed a simple java application to download the MaxDB data to XML format. This weblog discusses the same considering a simple table with a few records for test purpose. Table Definition I took a simple table with 3 fields for the discussed case. Table Contents The test table contained three records that are to be downloaded to XML. The Java Extractor Program In this program the ResultSetMetaData plays a key role while building the XML tags for the data that was downloaded. /** * Project :DataDownloadAsXML * Date :Jul 26, 2005 * Programmed By: Kathirvel Balakrishnan * Company : Wipro Technologies * Designation : Project Engineer */ package com.xmldata; import java.io.*; import java.sql.*; public class ExtractMaxDBDataToXML { String serverName = null; String dataBase = null; String tableName = null; String userName = null; String password = null; String fileName = null; Connection myConnection; Statement myStatement; ResultSet myRs = null; ResultSetMetaData myRsM = null; /** * The constructors are created for the program. */ public ExtractMaxDBDataToXML(String serverName, String dataBase, String tableName, String userName, String password, String fileName){ this.serverName = serverName; this.dataBase = dataBase; this.tableName = tableName; this.userName = userName; this.password = password; this.fileName = fileName; } public void loadDriver(){ try{ Class.forName("com.sap.dbtech.jdbc.DriverSapDB"); System.out.println("The Driver has been loaded successfully!"); }catch(Exception E1){ System.out.println("Unable to load the error!"); System.out.println("Exceptions:"+E1.toString()); System.exit(1); } } public void createXML() throws IOException { System.out.println("Establishing connection to Server"); try{ FileWriter xmlWriter = new FileWriter(fileName); //Establishing the connection with the database myConnection = DriverManager.getConnection("jdbc:sapdb://" + serverName + "/" + dataBase, userName, password); myStatement = myConnection.createStatement(); System.out.println("Connection to Server was Established"); // Executing the SQL Query myRs = myStatement.executeQuery("select * from "+tableName); myRsM = myRs.getMetaData(); // Processing the resultset and downloading data to XML System.out.println("Data download to XML started\n"); xmlWriter.write(""); for(int i=1; i<=myRsM.getColumnCount();i++){ System.out.print(myRs.getString(i)+"\t"); xmlWriter.write("<"+myRsM.getColumnName(i)+">"+myRs.getString(i)+"</"+myRsM.getColumnName(i)+">"); } System.out.println("\n"); xmlWriter.write(""); } xmlWriter.write(""); System.out.println("Data successfully downloaded to XML"); // Closing the connection parameters xmlWriter.close(); myRs.close(); myStatement.close(); myConnection.close(); }catch(IOException E){ System.out.println("File Handling Errors :"+E.toString()); }catch(Exception E2) { // Prints if any exception is raised System.out.println(" Exceptions : "+E2.toString()); } } public static void main(String[] args) throws IOException { ExtractMaxDBDataToXML myXML = new ExtractMaxDBDataToXML("localhost","TEST","ACCESSCARD","DBADMIN","maxdb","d:/MaxDB.xml"); myXML.loadDriver(); myXML.createXML(); } } The Result of the Program The data was successfully extracted to XML format and the results were displayed in the console. The extracted XML file The XML file created had all the details and is given below.
5 Comments
