Uploading Data from Firebird
Zoho Reports Upload Tool is a downloadable standalone utility, which you can install in your local environment and connect to upload data from your local databases behind firewall into Zoho Reports. If you have stored your data Firebird database, then you can use Upload Tool to pull data from your databases and upload the same into Zoho Reports.
In this section, we will discuss about how to connect the Upload Tool to pull data from local/hosted Firebird database into Zoho Reports. You can also read about how to configure the Upload tool to periodically upload/synchronize the data from your databases into Zoho Reports.
Downloading and Installing Upload Tool
Zoho Reports Upload Tool can be downloaded from the link below.
You can easily install the tool by extracting the file in the required location, from where you can connect to your local database and Zoho Reports reporting database through internet. To know more details on installing Upload Tool, refer to the following link.
Configuring the Upload Tool
The conf directory contains all the configuration files for the tool. Before you execute the tool you need to specify the appropriate configuration settings. The /conf directory consists of three files.
- common_params.conf - Provides connection parameters to specify the details of the Zoho Reports account into which the data need to be uploaded.
- database_connection_params.conf - Provides database connection parameters to specify the details of the local/hosted Firebird database from which data need to be uploaded.
- database_sql_queries.xml - Allows you to specify the SQL SELECT queries that are to be used to fetch the data from your local/hosted Firebird database and upload the same into Zoho Reports reporting database.
Connection Parameters
The common_params.conf file provides connection parameters to specify the details of the Zoho Reports account into which the data need to be uploaded. Apart from the Zoho Reports Connection Parameters, this also contains Import Parameters and proxy server details.
Common Settings
In the common_params.conf file, specify the following parameters. This allows the Upload Tool to connect the REPORT_SERVER_URL after being authenticated by the IAM_SERVER_URL
| Parameter | Description | Mandatory/Optional |
IAM_SERVER_URL | Specify the URL of the Zoho Authentication server. i.e., https://accounts.zoho.com. In case your Zoho Reports account is hosted in the EU server, then specify as https://accounts.zoho.eu | Mandatory |
| REPORT_SERVER_URL | Specify the Zoho Reports service URL In case your Zoho Reports account is hosted in the EU server, then specify as https://reportsapi.zoho.eu | Mandatory |
Zoho Reports Connection Parameters
The following table contains connection parameters for Zoho Reports.
| Parameter | Description | Mandatory/Optional |
USER_EMAIL_ADDRESS | Specify your Zoho Reports account E-mail ID | Mandatory |
| AUTHTOKEN | Specify your Zoho Reports AuthToken. This is a unique token provided for your account for secure access.
In case you do not have an AuthToken, you can generate one by accessing the below link and login to Zoho Reports. https://accounts.zoho.com/apiauthtoken/create?SCOPE=ZohoReports/reportsapi AuthToken for your account will be generated, as given below (the value for the parameter AUTHTOKEN in the response given below, is the acutal AuthToken for your account.) #Wed Feb 29 03:07:33 PST 2012 AUTHTOKEN= bad18eba1ff45jk7858b8ae88a77fa30 RESULT=TRUE | Mandatory |
| DBOWNER_EMAIL_ADDRESS | Specify the Zoho Reports account Admin E-mail ID here. | Mandatory |
To connect the tool to the internet through a proxy server, you need to provide the following. If you are using a Direct Internet connection, then this setting can be ignored.
| Parameter | Description | Mandatory/Optional |
USEPROXY | Set this to true, if you are connected through a Proxy server. Set this to false, if you are connected to the Internet directly. By default this value is set to false. | Mandatory |
| PROXYHOST | Specify the machine name or IP address where the proxy server is running in your network. | Mandatory |
| PROXYPORT | Specify the port in which the proxy server is running. | Mandatory |
| PROXYUSERNAME | Specify your proxy server user name to access the proxy server. | Mandatory |
| PROXYPASSWORD | Specify your proxy password to access the proxy server. | Mandatory |
Import Parameters
These import settings help Zoho Reports properly understand the data being imported. You need not have to configure these parameters unless you want to fine tune the import process. The default settings should suffice for most cases.
These parameters are available in the common_params.conf file. To know more about the available Import Parameters and how to set them, refer here.
Database Connection Parameters
You can configure the settings to connect to your local/hosted Firebird database in the database_connection_params.conf file.
Specify the following connection parameters.
| DBTYPE | Specify the database type as firebird. |
| HOSTNAME | Specify your local/hosted machine name in which Firebird is running. |
| Port | Specify the database port number. The default port number for Firebird is 3050. |
| USERNAME | Specify the Firebird user name. |
| PASSWORD | Specify the Firebird password. |
| FIREBIRDFILENAME | Specify the complete path of Firebird database file name which contains the data. e.g., FIREBIRDFILENAME=C://FIREBIRD irebirdfilesmydb.fdb |
Database SQL Queries
The Database sql queries file, namely database_sql_queries.xml, allows you to specify the SQL SELECT queries that are to be used to fetch the data from your local/hosted Firebird database and upload the same into Zoho Reports reporting database. You can specify any number of queries to fetch to upload data as required.
The following is the query format.
In the above query format the sample values for the parameters are given in bold. You can replace these bold content with your values. The following table explains parameters for the sql queries.
| sql_query | Specify the SQL SELECT query to be executed in the local/hosted MySQL database for fetching the necessary data. Example 1: select * from local_db_table This query fetches all the record from the local_db_table Example 2: select * from employee where age > 25 This query fetches all the record from the employee table whose age is greater than 25 Please note that the '<' symbol in the criteria should be replaced with < and '>' symbol in the criteria should be replaced with > |
| dbname | The Zoho Reports database name into which the data is to be uploaded after executing the SQL Query. Note: Ensure that the reporting database is already available in Zoho Reports account. If it does not exist create the database before executing this upload to avoid failure. Refer Creating a New Reporting Database to know how to create a reporting database. |
| tablename | The Zoho Reports table name into which the data is to be uploaded after executing the SQL Query. Ensure that the specified table with similar column structure is already created in the Zoho Reports Reporting Database. Refer Creating a Table to know how to create a table. You also allow Zoho Reports to create the table by setting the ZOHO_CREATE_TABLE parameter to true in the common_params.conf file. |
| importtype | Sets how the data need to be imported. Available options are :
|
| matchingcols | This is applicable only when the importtype is set to UPDATEADD. Specify column ( or a combination of columns) whose value will uniquely identify each record in that table. If the record already exists in the table then it will be replaced with the new values in the data being uploaded. Otherwise the data will be added at the end of the table. |
| selectcols | The column names separated by comma. Only these columns are uploaded from the resultant query data into the online reporting database. Leave this as "" (empty) if you want all the columns to be uploaded. |
| skiptop | The number of rows to be skipped from the top in the resultant query data before being uploaded. Leave this as "" (empty) if you want all the rows from the resultant query data to be uploaded. |
| batchsize | Upload Tool splits the data into batches for uploading. The batchsize param splits the huge data into batches for uploading. The default value for the batchsize param is 10000. This is a replacement for "LINES_TO_SEND" parameter which can be found in common_params.conf file. The param "LINES_TO_SEND" is deprecated in the latest version of the upload tool. |
| queuesize | Upload Tool splits the data into batches for uploading and writes the data in the temporary local file. The queuesize helps to regulate the file generations. Default value is nill (which means, there is no limit in file generation). |
Executing the Upload Tool
Once you have configured the parameters, you can execute the Upload Tool by invoking the below file available in the bin folder.
- In Windows OS - UploadFromDB.bat
- Mac/Linux OS - UploadFromDB.sh
You can also execute the Upload Tool using the commamd line. The following is an example command to invoke the Upload Tool.
You can also specify the authentication details such as Zoho Reports Login e-mail ID, AuthToken and Database owner e-mail ID in the command line. However, specifying these details in the common_params.conf file reduces the tedious process of repeating these details every time in your command line arguments.
In case you specify these values in the command line apart from specifying in the common_params.conf file, then the values provided in the command line will take precedence.
The following is an example command to invoke the Upload Tool along with user e-mail ID and AuthToken.
The following is an example command to invoke the Upload Tool along with user e-mail ID, AuthToken and Database owner e-mail ID for shared users.
Note Mac users:
- After you unzip the Upload Tool files, open a terminal/command-line and change the directory to /bin.
- Type the following command at the command prompt.
whereis java - Copy the java location returned by your system.
- Open the setEnv.sh file and remove the following snippet.
export JAVA_HOME=$TOOL_HOME/jre
- Add the following snippet.
export JAVABIN=<paste_your_mac_os_java_location>
Example
export JAVABIN=/usr/bin/java - Save the setEnv.sh file.
Setting up Periodic Upload / Synchronization
Using Upload Tool you can periodically schedule data uploads from your local/hosted Firebird database to Zoho Reports. This enables you to have the latest data from your application synced peridoically into Zoho Reports, and hence reports created over this data stays current.
You can setup a periodic scheduler using the Operating System Scheduler feature as explained below.
Setting up Schedule in Windows Operating System
- Click Start > Settings > Control Panel >Scheduled Tasks.
- Click Add Scheduled Task. The Schedule Task wizard will open.
- Click Action > Create Task to open the Create Task dialog.
- Open Action tab and then click New.
- Click Browse button and select the upload tool command line batch file UploadFromDB.bat in the Program/Script field. Ensure that you have provided the necessary settings in the Upload tool configuration files.
- In the Add Argument field enter the following command line arguments.<zoho_login_email_address> -A <authtoken>
Example
eduardo@zillum.com -A bad18eba1ff45jk7858b8ae88a77fa30
- Specify the time to trigger the task in Trigger tab.
- Click OK to save the task.
Setting up Schedule in Linux or Mac Operating System
In Linux and Mac, you can use the crontab command for scheduling the migration process using the command line script UploadFromDB.sh (Checkout Simple Help on Linux Crontab command)
The following steps explains how to setup the cron utility for scheduling data upload at a specific interval.
- Open the terminal and type the below command.crontab -e
- Append the prarmeters as a command in the following format :MIN HOURS * * * /UploadToolHome/bin/UploadFromDB.sh username -A authtokenNote: * operator is used to specify all possible values for a field.Example30 8 * * * /UploadToolHome/bin/UploadFromDB.sh eduardo@zillum.com -A bad18eba1ff45jk7858b8ae88a77fa30The above script will execute the Upload Tool at 8:30 AM everyday and upload the data from your Firebird database.
- Save the file.
Syntax of crontab
The following are the crontab syntax used to schedule Upload Tool.
MIN HOUR DOM MON DOW CMD ARG1 ARG2 ..
- MIN: Minutes (0 to 59)
- HOUR: Hours (0 to 23)
- DOM: Day of Month (1 to 31)
- MON: Month Field (1 to 12)
- DOW: Day of Week (0 to 6)
- CMD: Command to execute. For scheduling data upload, specify the absolute path of UploadFromDB.sh file
- ARG: Arguments/Parameter will pass to script. It is an optional.
Click here to know more about the cron jobs.
Points to consider, while scheduling periodic upload using Command line mode:
- It is the users responsibility to ensure that the latest data is uploaded into Zoho Reports by setting the schedule interval accordingly . Also ensure that you have provided the appropriate SQL Select query to pull the data.
- In case you have deleted few records in the tables in your local database being uploaded, the only option to remove these records from Zoho Reports database is to set the Import Type as TRUNCATEADD. This will delete all the records in the corresponding table in Zoho Reports and then add the data newly fetched records from the local database into Zoho Reports.
- In case you have modified few records in the tables in your local database being uploaded, then to get this modified in Zoho Reports database set the Import Type as UPDATEADD and specify the matching columns. The Upload Tool will compare the records in the corresponding table in Zoho Reports with the data being uploaded from your local database table based on the matching columns. If the record already exists in the Zoho Reports table then it will be replaced with the new values available from the local database. If not, it will be added as new records in Zoho Reports.
Viewing the Data Online
To view the data that you have uploaded:
- Login to http://reports.zoho.com
- Click on the corresponding Database name under My Databases
- Click on the corresponding table at the Left to open the table and view the uploaded data.
FAQ and Troubleshooting Tips
Frequently Asked Questions
1. Can I use my Yahoo / Gmail / Google Apps / Facebook user credentials to upload data using Upload Tool?
Currently, Zoho Reports Upload Tool does not support uploading data using Yahoo / Gmail / Google Apps / Facebook accounts credentials. The Upload Tool expects Zoho Username and Password to authenticate the user. So other login users need to generate a Zoho Username and Password to upload data into Zoho Reports database.
Follow the steps below to generate a Zoho Username and Password:
- Login into Zoho Reports using your Yahoo / Gmail / Google Apps / Facebook account credentials.
- Click the drop-down menu displayed with your user name at the top right corner.
- Click Account Settings menu item, this will open Zoho Accounts Profile page (You can also directly access/open this page using direct link https://accounts.zoho.com).
- In the Home tab, click Change Password link.
- Reset the password.
Now in Zoho Reports home page, you can find the Zoho Username at the top right corner. Use the new Zoho Username and Zoho Password in the Zoho Reports Upload Tool to upload the data into your account.
Note:
- Generating Zoho password will not affect signing into your account using your Yahoo / Gmail / Google Apps / Facebook account.
2. How to increase / decrease the batch size for upload?
To upload the data of larger size, Zoho Reports Upload Tool splits the data and uploads as batches. The user can configure the number of rows to be sent for each batch by modifying the LINES_TO_SEND parameter in the common_params.conf file. By default this will be set to 5000.
Follow the steps given below to specify the batch size:
- Open /conf/common_params.conf file.
- Set the LINES_TO_SEND parameter to the number of lines you want to upload in each batch as given below.
Example
- Save the configuration file and start uploading the data.
Note:
- It is mandatory that the each batch size should not exceed 20 MB or 100,000 records.
3. Can I allow the shared user to upload the data into my table?
Yes, you can allow your shared users to upload data into your tables using Upload Tool. Follow the steps given below to do this.
Steps to be followed by you who is the Admin or Database Owner:
- Login into database owner's account.
- Share the table, in which you want to allow your shared user to upload data, with Import Permission.
- For more details, refer to this FAQ section.
Steps to be followed by the shared user:
- Open /conf/common_params.conf file.
- Add the DBOWNERNAME parameter and set this to Database Owner's Zoho Reports Email id or User name
Example:
- Save the configuration file.
- Start uploading the data using the shared user's Zoho Reports credentials.
Yes, you can use Windows Authentication to connect to your SQL Server database and fetch the necessary records to be uploaded into your Zoho Reports account.
To do this, you need to provide the database name followed by the Windows domain name separated by semi-colon ' ; ' for the parameter DBNAME in thedatabase_connections_params.conf file.
Syntax:
Where
<your_database_name> - This is the database name in SQL Server to connect
<your_domain_name> - The Windows domain name that you use.
Example:
4. Can Upload tool be used to upload data from remotely hosted databases into Zoho Reports?
Yes, you can upload data from remotely hosted databases (running in a remote machine) into Zoho Reports using Upload Tool, provided a network connection via Java Database Connectivity (JDBC) Driver could be established between the remote database server and the machine in which you have installed the Upload Tool.
The connection setting for the hosted database should be done in the same way as you do for a local database. For the "HOSTNAME" and "PORT" parameters, you need to specify the remote hosted database server IP address or host name and the corresponding port number. To know how to specify connection setting, refer to Specify Database Connection Settings section.
5. Is it possible to fetch data from multiple database using Upload Tool?
Yes, it is possible to setup the Zoho Reports upload tool to fetch data from multiple database. By default, the Upload tool is designed to upload data from one database in an upload process. However, we can modify the configuration files and executable bat or sh files to upload data from more than one database.
To accomplish the above set up, you need to create different set of configuration files required for each database.
For example, let's say you have two different databases to be connected for data upload. We should have one set of configuration files for Database One and another set of configuration files for Database Two.
If you are working on Windows environment you can download the modified sample configuration and batch (UploadFromDB.bat) files from this download link. Similar changes are required for the UploadFromDB.sh shell script if you need to set up these configuration on a Linux/Mac environment.
Follow the below steps once you have downloaded the sample and extracted the same.
- Conf files to be copied to Upload tool's config folder
(i.e., "<UPLOAD_TOOL_HOME>/ZohoReports/Uploadtool/conf")- database_connections_params1.conf
- database_sql_queries1.xml
- database_connections_params2.conf
- database_sql_queries2.xml
- Executable "UploadFromDB.bat" file to be copied to Upload tool's bin folder
( i.e., "<UPLOAD_TOOL_HOME>ZohoReports/UploadTool/bin"). The .bat file has been modified to upload the two different databases with the given two different configurations.
The following are the steps to configure two databases in the Upload tool:
Configuration for Database One:
- Provide the database connection properties in
"database_connections_params1.conf ", - Define the necessary queries in " database_sql_queries1.xml ".
Configuration2 for the Database Two:
- Provide the database connection params in
" database_connections_params2.conf ", - Define the necessary queries in " database_sql_queries2.xml ".
Once the above setup is done, execute the modified batch files copied to the bin directory. This will fetch data from both the databases and upload that into Zoho Reports.
6. Is it mandatory to follow similar column names in Zoho Reports table as available in my local database?
No. You can follow a different column names in Zoho Reports table from what is there in your local database and still upload data using Upload Tool. Follow the steps to upload the data from your local database into the corresponding column in Zoho Reports table.
- Open <Tool_Home>/conf/database_sql_queries.xml.
- Specify the query as given below to upload data from local database into the corresponding column in Zoho Reports table.
</Query> </Queries>
The above query will pull the data from column1-local-db, column2-local-db andcolumn2-local-db from your local database databasetable1 and import them into the column incolumn1-zohoreports-table, column1-zohoreports-table and column1-zohoreports-table in the Zoho Reports table.
- Save the file and start the upload process.
7. How to upload data with different date format from the in-house/local databases into Zoho Reports?
Zoho Reports Upload Tool expect the date format of the data being uploaded as dd/MM/yyyy HH:mm:ss. In case you are using a custom date format in your local database, then you need to convert your date value to the required format in your SQL SELECT Query using the DATE_FORMAT SQL function.
To convert the data format you need to set a date column to the DATE_FORMAT SQL function and specify the required default date format as given below.
In case your date column is set as string field, then you need to convert the data type using DATE function as given below.
Troubleshooting Tips
Problem 1: I get "The host did not accept the connection within timeout of 15000 ms" while trying to upload data using Upload Tool. How to solve it?
Solution: This could be because of improper proxy server settings. If you are connecting the Internet through a proxy server, ensure that you have configured the correct proxy server details in the common_params.conf file. For more details, refer to the Setup: Common Settings and Proxy Configuration section.
Problem 2: I get an error message as ‘out of memory’ while trying to upload the file. How can I solve this error?
Solution: This could be because, the default memory allocated to the Java Virtual Machine (which executes the Upload tool) in your machine was not sufficient to import a large file using our Upload Too. Hence it throws the "Out of Memory". Follow the given steps to resolve this issue:
- Open the file setEnv.bat / setEnv.sh available in the directory <Upload_Tool_Home>/bin
- Add the property -Xmx1024 at the end of the variable JAVA_OPTS as below:
Windows: set JAVA_OPTS=%JAVA_OPTS% -XX:NewSize=48M -Xmx1024M
Linux/Mac: export JAVA_OPTS=$JAVA_OPTS -XX:NewSize=48M -Xmx1024M - Save the file and start the upload process.
Problem 3: I get "Error!!! Sorry, you cannot upload files that exceed 50MB in size" while uploading data using Upload Tool. How to overcome this?
Solution: To upload the data of larger size, Zoho Reports Upload Tool splits the data and uploads them as batches. It is important that the batch size should not exceed 20 MB or 100,000 records per batch. You can increase or decrease the lines/records to send for each batch in common_params.conf file.
Follow the steps given below to set the lines to be uploaded in a batch:
- Open <Tool_Home>/conf/common_params.conf file
- Change the parameter LINES_TO_SEND=<no_of_lines>
- Save the configuration file and start uploading the data
For more details, refer to #Q3.
Problem 4: I get an error message as "Another import is in progress in this table started by the user 'User name' at 'time of import'." How to overcome this?
Solution: You will get the above message when more than one import process is running on the same table at the same time. Ensure that no other user is importing into the same table before initiating the progress.
Problem 5: I get a message as "ZOHO_MATCHING_COLUMNS is not present in the request parameters list". How to solve it?
Solution: You will get the above error message when you have set the import type to UPDATEADD and have not specified any value for the ZOHO_MATCHING_COLUMNS parameter. This parameter is mandatory when the ZOHO_IMPORT_TYPE is set to UPDATEADD. Set this parameter to column names based on which the existing records in the table need to be matched. If the record already exists in the table then it will be replaced with the new values in the uploaded CSV file. Remaining rows will be added at the end of the table.
Note:
- It is recommended to set columns with unique values as matching column.
Problem 6: I get "Error!!! Column "Column_Name" is present in match columns but not in selected columns" while uploading data. How to overcome this?
Solution: You will get the above error when the column specified for the ZOHO_MATCHING_COLUMNS parameter is not available in the table. When you have set Import Type to UPDATEADD, it is mandatory to specify matching column. These columns will be used to check whether a record already exist in the table. Ensure that you have specified a valid column name for matching column before uploading data to avoid failure.
Problem 7: I get an error message as "Maximum Concurrent User Tickets Limit Exceeded". How to solve this?
Solution: When you access the service a session will be created, which will be deactivated once you logout or after 7 days. If you did not logout or access the service from multiple locations then multiple active sessions will be created for your account. Zoho service restricts a user to have maximum of 20 active sessions. In case you have exceeded this limit, you will not be allowed the access the service. To overcome this you need to close the active sessions.
Follow the steps below to close the current active sessions.
- Login to http://accounts.zoho.com with your Zoho account credentials
- Choose the Active Session under the Home tab.
- Remove all active sessions by clicking the close all other sessions.
Problem 8: I get an error message as "You need to (re)login to perform this operation." How do I overcome this?
Solution: You will get the above message when Zoho Reports encounters an authentication problem. This could happen in the following scenarios:
- Your AuthToken has expired.
- Your account is in our EU data center (https://www.zoho.eu/reports/), but you try logging in to our US data center (https://www.zoho.com/reports/).
Ensure that your AuthToken is active. In case your Zoho Reports account is hosted in our EU data center, then specify the Authentication parameters as below.
- IAM_SERVER_URL= https://accounts.zoho.eu
- REPORT_SERVER_URL= https://reportsapi.zoho.eu