https://help.tallysolutions.com/docs/td9rel54/integration_capabilities/using_odbc_interface.htm

Integration using 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

ODBC Client

ODBC Driver

ODBC Server

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. If any 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.

Tally.ERP 9 as a Client – Retrieving Data from External Database

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.

Once required data is brought in to the Tally.ERP 9 application, the each row is treated as one Object and each column of that row as method of that Object. Thus the external data can be utilized inside the application.

Syntax

[Collection:<Collection Name>]

ODBC : <Driver Info>

SQL : <SQL Statement>

Where <Driver Info> can be a DSN or ODBC driver, Driver ID & path of the data source can be mentioned and <SQL Statement> is SELECT query.

Example 1: Import the Ledger Master from MS Access

Sample Data

TDL Collection to gather data from MS Access

[Collection: Led Coll From Access]

ODBC: "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Masters.mdb;Uid=;Pwd=;"

SQL : Select * From LedgerMaster

[Collection: Led Coll]

Source Collection : Led coll From Access

Compute : LedName : $_1

Compute : LedParent : $_2

Compute : LedOpBal : $$AsAmount:$_3

Alternatively TDL Collection gathers data from MS Access in following way

[Collection: Led Coll From Access]

ODBC : "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Masters.mdb;Uid=;Pwd=;"

SQL : Select * From LedgerMaster

SQL Object : AccessObj

[Object: AccessObj]

LedName  : $_1

LedParent : $_2

LedOpBal : $$AsAmount:$_3

Utilizing the ODBC Collection in a user defined Function to store the Ledger Objects in Tally DB

[Function: Ledger Import]

01 : WALK COLLECTION: Led Coll

02 : IF : @@LedgerExists > 0

03 : NEW OBJECT: Ledger : $LedName

04 : CALL : Set Values

05 : SAVE TARGET

06 : ELSE

07 : NEW OBJECT: Ledger

08 : CALL : Set Values

09 : SAVE TARGET

10 : END IF

11 : END WALK

12 : MSGBOX : "Status" : "Process completed successfully!!"

13 : RETURN

[Function : Set Values]

01 : SET VALUE : Name  : $LedName

02 : SET VALUE : Parent: $LedParent

03 : SET VALUE : Opening Balance : $LedOpBal

[System : Formula]

Ledger Exists : $$FilterCount:Ledger:IsMyLedger

IsMyLedger : $Name = $$ReqObject:$LedName

Example 2 : Import the Ledger Master from MS Excel

TDL Collection to gather data from MS Excel

[Collection: ExcelData]

ODBC : "Driver={Microsoft Excel Driver (*.xls)};Dbq=C:\Masters.xls;DriverId=790"

SQL : "Select * From [Sheet1$]"

Tally.ERP 9 as a Server – Retrieving Data from Tally DB using an External Application

A Client application can access Tally.ERP 9 data in two forms

Tables

Calling a Procedure

Retrieving Data Using Tables

Tally.ERP 9 stores the data in terms of Objects. But for the external application each Object is mapped to a row and a Collection to a Table.

When Tally.ERP 9 is running as a Server to an ODBC client, not all the data i.e. Collection(s) are available to Client application. A Collection can be made available to ODBC by following two steps procedure.

1. By exposing methods of the Object(s) of the Collection.

2. By using Collection attribute 'Is ODBC Table'

Exposing Methods to ODBC

By prefixing '_ to external method(s) of an internal object or method(s) of an external Object can be exposed to ODBC.

Note:

By default all the methods of the internal objects are exposed to ODBC

Only First level methods of an Object can be exposed directly

Example

[#Object : Ledger]

_Difference : $ClosingBalance -$OpeningBalance

The code snippet alters an internal object, Ledger, to add an external method, _Difference and exposes it to ODBC.

Exposing Collections to ODBC

A Collection is exposed to ODBC by using the attribute, IsODBCTable .

Example

[Collection: Vouchers]

Type : Voucher

Is ODBCTable: Yes

The Collection Vouchers is exposed to ODBC by using the attribute IsODBCTable.

Example 1: Firing SQL statements from MS Excel to Tally.ERP 9 ODBC Server

Step 1 : - Open a New Work Book in MS Excel

Step 2 :- Go to Data > Import External Data > New Database Query

Step 3:- Select Tally ODBC Driver from 'Choose Data Source' window.

Step 4:- Select required Columns from Ledger Table from the 'Query Wizard - Choose Columns' window

Step 5:- Filter the Data to specify which rows to include in query from 'Query Wizard - Filter Data' window

Step 6:- Sort the data from 'Query Wizard - Sort Order' window

Step 7:- From 'Query Wizard - Finish' window 'Return Data to Microsoft Office Excel'

Step 8:- View the result in Excel sheet.

Example 2: Firing SQL statements from a VB application to Tally.ERP 9 ODBC Server

Below mentioned code snippet can be used establish ODBC connection with Tally.ERP 9 and to then SQL queries can be fired.

Dim TallyCn As ADODB.Connection

Set TallyCn = New ADODB.Connection

TallyCn.Open "TallyODBC_9000"

Set rst = New ADODB.Recordset

rst.Open "Select $Name From Ledger", TallyCn, adOpenDynamic, adLockOptimis-tic

Retrieving Data By Calling an SQL Procedure

A Client 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.

Collection Attribute – SQLParms

This attribute is used to pass parameter(s) to a SQL Procedure. The parameter is a System variable.

SQL Parms : <Parameter>

Where <Parameter> is a name of the Variable

Collection Attribute – SQLValues

This attribute returns value to client application. SQLValues require two parameters, the Column Name and the values for the column.

SQL Values : <Column Name> : <Expression>

Where <Column Name> is a name of the column i.e. column header and <Expression> is the expression which evaluates to a value and returned back to the client application.

Usage of SQL Procedure

The SQL Procedure '_StkBatches' displays the Batch name and Closing Balance for a given Stock Item.

[Collection : _StkBatches]

Type : Batches

Childof : ##StkItemName

SQLParms : StkItemName

SQLValues : Name : $Name

SQLValues : Amount :$ClosingBalance

[Variable:StkItemName]

Type : String

Example 1: Calling the SQL Procedure '_StkBatches' in MS Excel

Step 1 : - Open a New Work Book in MS Excel

Step 2 :- Go to Data -> Import External Data -> New Database Query

Step 3:- Select Tally ODBC Driver from 'Choose Data Source' window to open 'Microsoft Query' screen

Step 4 :- Go to File -> Execute SQL

Step 5:- In 'Execute SQL' window click on 'Procedures'

Step 6 : In 'Select Procedures' window, select procedure '_StkBatches'

Step 7: Pass appropriate stock item name as parameter to the procedure and 'Execute'

Step 8:- View the result in 'Query1' window

Step 9:- From 'Microsoft Query' screen, Go to File -> Return Data to Microsoft Office Excel

Step 10 :- View the Result in Excel sheet .

Example 2: Calling the SQL Procedure from a VB Application

Dim DBcon As New ADODB.Connection

Dim objCmd As New ADODB.Command

Dim objRs As New ADODB.Recordset

DBcon.CursorLocation = adUseClient

'Establish the connection using Tally ODBC Driver DBcon.Open "TallyODBC_9000"

objCmd.ActiveConnection = DBcon objCmd.CommandType = adCmdStoredProc objCmd.CommandText = "_PartyBills"

'Pass the the Stock Item Name as Parameter

objCmd.CreateParameter (ODBCMAIN.CmbLedger.Text)

'Call the SQL procedure

Set objRs = objCmd.Execute

Using calculator pane for testing SQL commands

Tally.ERP 9 has an in-built SQL processor that processes SQL Select statements on collections. By default, only the collections at first level are available for selection.

Syntax

Select [<Method Name/s> <*>] from <Collection / Table> where <Condition> order by <Method Name/s>

Example

Select $Name from Ledger

Select $Name, $ClosingBalance from Ledger Select * from Ledger

Select $Name from ODBCTables

Select $Name, $ClosingBalance from Ledger where $$IsDr:$ClosingBalance order by $ClosingBalance DESC

Select $Name, $ClosingBalance from Ledger where $$IsDr:$ClosingBalance order by $ClosingBalance

Select TOP 2 from Ledger