HomeDeveloper ReferenceIntegration CapabilitiesIntegration using ODBC Interface

 

Explore Categories

 

 PDF

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. TallyPrime 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. TallyPrime can act as Application i.e. as ODBC client as well as Database i.e. ODBC server.

TallyPrime as a Client – Retrieving Data from External Database

Watch the below video to understand the procedure to import data from external sources into Tally.

TallyPrime 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 TallyPrime 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$]”

TallyPrime as a Server – Retrieving Data from Tally DB Using an External Application

A Client application can access TallyPrime data in two forms

  • Tables
  • Calling a Procedure

Watch the below video to know the procedure to pull Tally data into a third party application.

Retrieving Data Using Tables

TallyPrime 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 TallyPrime 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 TallyPrime 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 TallyPrime 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 TallyPrime. But within TallyPrime 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

TallyPrime 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

Post a Comment

Is this information useful?
YesNo
Helpful?