Saturday 8 September 2012

Tally ERP 9 to Java via ODBC bridge


The following is the working java code to extract consolidated data from Tally Database. 
Note: Type the following code by changing the 'TallyODBC' name to the 'TallyODBC' name in your PC and the compile but before running the code make sure that your 'Tally.exe' is running as an administrator especially if its Windows 7.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

/**
 *
 * @author SUNEET
 */
public class TallyODBC {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        // TODO codimporte application logic here
        try {
              Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

              Connection con = DriverManager.getConnection("jdbc:odbc:TallyODBC_9696", "", "");
              Statement stmt = (Statement) con.createStatement();
              ResultSet rs = stmt.executeQuery("SELECT StockItem.`$Parent`, StockItem.`$Name`, StockItem.`$BaseUnits`, StockItem.`$OpeningBalance`, StockItem.`$_InwardQuantity`, StockItem.`$_OutwardQuantity`, StockItem.`$_ClosingBalance` FROM SuneetLtd.TallyUser.StockItem StockItem ORDER BY StockItem.`$Parent`");
           
              int numberOfColumns = rs.getRow();
              int rowCount = 1;
              while (rs.next()) {
                  //for(int i = 0; i < 7; i++){
                       int op = Integer.parseInt(rs.getString("StockItem.`$OpeningBalance`").substring(0, (rs.getString("StockItem.`$OpeningBalance`").length() - 3)).trim());
                       int in = Integer.parseInt(rs.getString("StockItem.`$_InwardQuantity`").substring(0, (rs.getString("StockItem.`$_InwardQuantity`").length() - 3)).trim());
                       int out = Integer.parseInt(rs.getString("StockItem.`$_OutwardQuantity`").substring(0, (rs.getString("StockItem.`$_OutwardQuantity`").length() - 3)).trim());
                       int cl = Integer.parseInt(rs.getString("StockItem.`$_ClosingBalance`").substring(0, (rs.getString("StockItem.`$_ClosingBalance`").length() - 3)).trim());
                       System.out.println(rs.getString("StockItem.`$Parent`") + " \t " + rs.getString("StockItem.`$Name`") + " \t " + rs.getString("StockItem.`$BaseUnits`") + " \t " + op + " \t " + in + " \t " + out + " \t " + cl);
                  //}            
                rowCount++;
              }
              //System.out.println(rowCount);
              stmt.close();
              con.close();
          } catch (Exception e) {
          System.out.println(e);
        }
    }
}

Output:

Raw Materials R 1 nos 5000 5000 0 10000
Raw Materials R 2 nos 7000 8000 0 15000

More Coming Soon