Case Study for tally connectivity using ODBC and Inventory management application

This is case study for the problem of connective tally with the inventory management system for the small business application. tally connectivity using ODBC

tally connectivity

There are two ways for tally connectivity to external Inventory Management Application
1. Integration using Tally ODBC Interface
2. Integration using Tally XML Interface
Tally communicates with the external world mainly using two Interfaces.

Scenario

As per the Scenario, the Inventory management application has three Basic modules:
1. Purchase
2. Sales
3. Inventory Management
Purchase:
- Take care of purchase department.
- Contains Information about vendors (Suppliers)
- Create Invoice module.
Sales:
- Take care of sales department.
- Contents information about customer.
- Create sales invoice module.
Inventory Management:
- Take care of inventory department. Contents information about inventory items.
- Whenever sales or purchase receipt are generated the inventory management will automatically takes place.
- For Example: If we create the purchase invoice. The respected purchased inventory item will automatically increase by no of quantities mentioned in purchase receipt.
- If we create sales invoice .The respected sales inventory item will automatically reduced by number of quantity mentioned in sales invoice. Since, this Application is not capable of doing accounting, the need of Tally arises.

1. Integration using Tally ODBC Interface.

ODBC (Open Database Connectivity) makes it possible to access data from Inventory Management Application, regardless of which Database Management System (DBMS) is handling the data. ODBC manages this by inserting a middle layer, called a database driver between an application and the DBMS. The purpose of this layer is to translate the application's database queries into commands that the DBMS can understand.

For this to function, both the application and the DBMS must be ODBC compliant i.e., the application must be capable of issuing ODBC commands and the DBMS must be capable of responding to them. Tally provides the ODBC Interface which makes it possible for applications to talk to Tally Database. By using this interface, external applications will be able to retrieve data from Tally. Tally acts as a Server delivering Data to external applications. Using the ODBC Interface, Tally.ERP 9 can make ODBC calls to an External Database and retrieve data from them. In such a case Tally acts as a Client to pull Data from disparate Data Sources.
As per following figure.

Note :- C#.net application must be capable of issuing ODBC commands and the DBMS must be capable of responding to them.
1.1 ODBC Interface- Open Database Connectivity (ODBC) is an interface for accessing data in a heterogeneous environment of relational and non- relational database management systems.
- ODBC is an Application Program Interface (API) specification that allows applications to access multiple database systems using Structured Query Language (SQL). ODBC provides maximum interoperability-a single application can access many different database systems. This allows an ODBC developer to develop an application, without targeting a specific type of data source.
- A typical ODBC implementation will have following components
1. ODBC Client
2. ODBC Driver
3. ODBC Server
ODBC
Middle Layer
I. M. Application
DB
ODBC Client - An ODBC client implements ODBC API. The ODBC API in turn will
communicate with the ODBC Driver provided by the Database.
ODBC Driver The ODBC driver is a library that implements the functions supported by
the ODBC API. It processes ODBC function calls, submits SQL requests to Database,
and returns results back to the application.
ODBC Server A database which supports ODBC can understand the SQL. Normally
ODBC Driver submits SQL request from the ODBC client and these SQL request will be
executed and result will be given back to the ODBC client
Tally.ERP 9 is a application which can act as ODBC Server as well as ODBC Client.
Note:- If Inventory Management Application supports ODBC interface, then it needs to
implement the ODBC API. The ODBC API in turn will communicate with the ODBC
Driver provided by the Database. The ODBC driver is a library that implements the
functions supported by the ODBC API. It processes ODBC function calls, submits SQL
requests to Database, and returns results back to the application. Tally.ERP 9 can act as
Application i.e. as ODBC client as well as Database i.e. ODBC server.

1.2 Tally.ERP 9 as a Client – Retrieving Data from External Database
Objects
SQL+Driver
Data
Collection
Tally
DB
Exter
nal
ODBC
External DB
ODBC
server
- Tally.ERP 9 is an ODBC enabled application. It can talk to ODBC Drivers of any external Database.  In TDL, Collection is a definition which holds the data. Collection Definition has a capability to gather the data from the external data source through ODBC. A Collection definition can communicate to the ODBC drivers of the external database either through Data Source Name (DSN) of the external database or through DSN less i.e. directly by mentioning ODBC Driver, Drive ID, path of the source, etc. In Collection Definition, SQL queries are used to gather the required information the external database.

3.3 Tally.ERP 9 as a Server (Retrieving Data from Tally DB using an Inventory
Management Application )
1. Tables
2. Calling a Procedure
A Client application can access Tally.ERP 9 data in two forms

3.4 Retrieving Data By Calling an SQL Procedure. An Inventory Management Application can call a SQL Procedure of a Tally.ERP 9.
But within Tally.ERP 9 this is a Collection with its name prefixed with an underscore. The Collection attribute, SQLParms is used to pass parameters to procedures. This Collection takes the parameter from the Client application by using the Collection attribute 'SQL Params'. The Collection attributes 'SQL Values' is used to return the values from procedure back to the client application.

2 . Integration using XML Interface

- As the Inventory Management Application is C#.net based. It is flexible with scripting languages and oops concepts.
- And Tally.ERP 9 has supported integration with web scripting languages or any environment capable of supporting XML and HTTP. Integration with these products is possible as XML import and export capability is built into Tally.ERP 9. In fact, Tally.ERP 9 delivers most of the functionalities of Web Services provided by Micro-soft's.NET framework. All Tally.ERP 9 data is accessible to any number of potentially disparate systems through the use of Internet standards such as XML and HTTP. In other words, Tally.ERP 9 can communicate with any environment capable of sending and receiving XML over HTTP. 2.1 Tally.ERP 9 as a Server – Using External application as Front End Data can be accessed from Tally.ERP 9 once the connection is established between Tally.ERP 9 and other external applications. Here we are usingData can be posted from Visual Basic to Tally.ERP 9 through XML Interface. The existing Tally.ERP 9 data can be altered and deleted from Visual Basic. Let us discuss some scenarios for using external application as front end 2.2 Tally.ERP 9 as a Client – Tally as a Front end for Web Services With the new enhancements now Tally can send a request to the HTTP Server and display the
response. Tally sends the request as an XML and receives the response in XML format. Tally can collect data directly from an XML file or from a web service running on the server. Even data can be sent along with the request to the web service which can process the data and
return the result as an XML. The web service in turn might be operating on an external database. Following Figure shows the functionality when Tally acting as a client interacts with a web service operating on the external data.

- Collection capability has been enhanced to gather live data from HTTP/web-service delivering XML. The entire XML is now automatically converted to TDL objects and is available natively in TDL reports.

- Reports can be shown in Tally with live data from an HTTP server. Coupled with the new [OBJECT:] extensions and Using POST action data can be submitted data back to the server almost operating Tally as a client to HTTP-XML web-services. - This capability allows us(Tester, user) to retrieve and store data as objects in Collection. The attributes in collection for gathering XML based data from a remote server over HTTP are Remote URL, Remote Request , XML Object Path, and XML Object. Whenever the collection is referred the data is fetched from the remote server and is populated in the collection.