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.
- By exposing methods of the Object(s) of the Collection.
- 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 TallyPrime ODBC Server
Below mentioned code snippet can be used to 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
