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.
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.
Tally.ERP 9 as a Client – Retrieving Data from External Database.
Tally 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>
Example 1 : Import the Ledger Master from MS Access.
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]
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.
[#Menu: Gateway of Tally]
Add : Item : Ledger Import : Call : Ledger Import
[Function: Ledger Import]
01 : WALK COLLECTION : Led Coll
02 : NEW OBJECT : Ledger : $LedName
03 : SET VALUE : Name : $LedName
04 : SET VALUE : Parent : $LedParent
05 : SET VALUE : Opening Balance : $LedOpBal
06 : SAVE TARGET
07 : END WALK
08 : MSGBOX : "Status" : "Process completed successfully!!"
09 : RETURN
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$]"
[Collection : Led Coll]
Source Collection : ExcelData
Compute : LedName: $_1
Compute : LedParent: $_2
Compute : LedOpBal : $$AsAmount:$_3
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.
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.
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.
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.
Tally.ERP 9 as a Client – Retrieving Data from External Database.
Tally 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>
Example 1 : Import the Ledger Master from MS Access.
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]
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.
[#Menu: Gateway of Tally]
Add : Item : Ledger Import : Call : Ledger Import
[Function: Ledger Import]
01 : WALK COLLECTION : Led Coll
02 : NEW OBJECT : Ledger : $LedName
03 : SET VALUE : Name : $LedName
04 : SET VALUE : Parent : $LedParent
05 : SET VALUE : Opening Balance : $LedOpBal
06 : SAVE TARGET
07 : END WALK
08 : MSGBOX : "Status" : "Process completed successfully!!"
09 : RETURN
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$]"
[Collection : Led Coll]
Source Collection : ExcelData
Compute : LedName: $_1
Compute : LedParent: $_2
Compute : LedOpBal : $$AsAmount:$_3
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
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.
- 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.
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
Exposing Collections to ODBC.
A Collection is exposed to ODBC by using the attribute, "IsODBCTable".
Example :
[Collection : Vouchers]
Type : Voucher
Is ODBCTable : Yes
Example 1: Firing SQL statements from MS Excel to Tally.ERP 9 ODBC Server.
Step 1 : - Open a New Work Book in MS Excel.
By prefixing '_' to external method(s) of an internal object or method(s) of an external Object can be exposed to ODBC.
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
Exposing Collections to ODBC.
A Collection is exposed to ODBC by using the attribute, "IsODBCTable".
Example :
[Collection : Vouchers]
Type : Voucher
Is ODBCTable : Yes
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.
'Query Wizard - Choose Columns' window.
Step 5 : - Filter the Data to specify which rows to include in query from
'Query Wizard - Filter Data' window.
'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.
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.
Syntax :
[Collection : <Collection Name>]
SQL Parms : <Parameter>
SQL Values : <Column Name> : <Expression>
Example 1 : Calling the SQL Procedure '_StkBatches' in MS Excel.
[Collection : _StkBatches]
Type : Batches
Childof : ##StkItemName
SQLParms : StkItemName
SQLValues : Name : $Name
SQLValues : Amount :$ClosingBalance
[Variable : StkItemName]
Type : String
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 .
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 :
[Collection : <Collection Name>]
SQL Parms : <Parameter>
SQL Values : <Column Name> : <Expression>
Example 1 : Calling the SQL Procedure '_StkBatches' in MS Excel.
[Collection : _StkBatches]
Type : Batches
Childof : ##StkItemName
SQLParms : StkItemName
SQLValues : Name : $Name
SQLValues : Amount :$ClosingBalance
[Variable : StkItemName]
Type : String
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 .
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
- Select $Name, $ClosingBalance from Ledger where
- Select TOP 2 from Ledger
Can you suggest any link which gives all the column and tables with description of tables.
ReplyDeleteHi Raghunathan, have you been able to find out the answer to your query? as I am also looking for the list of all tables/procedures in Tally ERP 9.
Deleteplease let me know if you find the answer.
sales, purchase table name in tally.........
ReplyDeleteVery nice article to learn tally integration,
ReplyDeleteI have on query, I want to use SQL commands and to fetch Natur Of Account (Assets, Liability, Income, Expnese)
Select $Name, $Parent, $NatureOfAccount From Groups
But unable to fetch the record due NatureOfAccount column
Please suggest possible solution to get nature of account
I am running the code from the php, connection to the tally is ok. but when i execute the query it show the error
ReplyDelete$conn=odbc_connect('TallyODBC_9088','unique','solapur!789');
if (!$conn) {
exit("Connection Failed: " . $conn);
}
else
{
echo "Connected";
}
$sql="select \$Name From Ledger";
$rs=odbc_exec($conn,$sql);
if (!$rs) {
exit("Error in SQL");
}
error
Connected
Warning: odbc_exec() [function.odbc-exec]: SQL error: Failed to fetch error message, SQL state HY000 in SQLExecDirect in D:\newsoft\XAMPP\xampp\htdocs\30-09\Unique\Unique\Files\tally.php on line 17
Error in SQL
Anybody can help me?..Actually i am trying to connectivity with tally using odbc command with username and password but it's not working fine mean it's not match the username and password only and only match port number. So any body has any idea about tally connectivity with username and password? if have so please help me, it's very urgent.
ReplyDelete[odbc driver manager]data source name not found and no default driver specified
ReplyDeleteRun Tally.exe as administrator, make sure you are entering correct Driver name. You can get driver name by typing odbc on your start menu, and open odbc data sources application, there you'll be able to find
DeletePlease help me to update/correction could be made in tally master addresses through excel ODBC or another software.
ReplyDeletePlease help.
Thanks,
how to get all voucher details in csv format - gosher dinesh - 9913473289
ReplyDeleteI am using below query to fetch Voucher Basic Information:
ReplyDeleteselect $Date, $Guid, $VoucherTypeName, $VoucherNumber, $PartyLedgerName, $VoucherKey from CompanyVouchers
But how can I fetch Ledger Entries(From and To entries) of a Voucher?
when i import from excel to tally using same code it does not work but message shows process completed successfully, and when i go and check the tally then i find that no ledger is created. please guide
ReplyDeleteGood tool to integrate tally with .net (c#,vb.net) available at http://om-softwares.et
ReplyDeleteSir, Can you please help me with the following:
ReplyDeleteIn Sales Voucher / Invoice / Sales Order form, I need to prompt user for Weighment slip number and set the qty by retrieving from MS Acces via ODBC query.