Salesforce ODBC Driver

Salesforce ODBC Driver can be used to read data from Salesforce.com (Cloud CRM). Using this driver you can easily integrate Salesforce data inside SQL Server (T-SQL) or your BI / ETL / Reporting Tools / Programming Languages. Write familiar SQL queries to read data without any coding effort.

Feature Summary

Read Salesforce CRM using familiar SQL Query language
Support SELECT / INSERT / UPDATE / DELETE SQL syntax (CRUD operations)
Support BULK API for large dataset (Table / SOQL Query)
Support Parallel Processing by default (Adjust MaxParallelThreads = 6)
Support loading a large amount of data from external systems using SOURCE clause
Integrate insight any ODBC Compliant Reporting / ETL tools (e.g. Power BI, Tableau, Qlik, SSRS, Informatica, Excel, SSIS)
Support for programming languages such as JAVA, C#, Python, PowerShell and more…
Tight integration with Microsoft SQL Server (With support for Gateway Option – No need to install Driver on Server)
Support for full SOQL Query Language
Supports OAuth Tokens security rather than Password (useful for 2FA / MFA handling)

Salesforce ODBC Driver UI

Configure Salesforce CRM Connection

Read from Salesforce CRM Table – SOQL Query Preview

Integration Scenarios (Reporting / ETL / BI / Programming)

ZappySys ODBC Drivers built using ODBC standard which is widely adopted by industry for a long time. Which mean the majority of BI Tools / Database Engines / ETL Tools already there will support native / 3rd party ODBC Drivers. Below is the small list of most popular tools / programming languages our Drivers support. If your tool / programming language doesn’t appear in the below list, which means we have not documented use case but as long as your tool supports ODBC Standard, our drivers should work fine.

ZappySys ODBC Drivers for REST API, JSON, XML - Integrate with Excel, Power BI, Tableau, QlikView, QlikSense, Informatica PowerCenter, Excel, SQL Server, SSIS, SSAS, SSRS, Visual Studio / WinForm / WCF, Python, C#, VB.net, PHP. PowerShell

ZappySys ODBC Drivers – Integrate with Excel, Power BI, Tableau, QlikView, QlikSense, Informatica PowerCenter, SQL Server, SSIS, SSAS, SSRS, Visual Studio / WinForm / WCF, Python, C#, VB.net, PHP. PowerShell

BI / Reporting Tools Integration	ETL Tools Integration	Programming Languages Integration
Microsoft Power BI Tableau SSRS (SQL Reporting Services) QlikView /Qlik Sense MicroStrategy Google Sheet Microsoft Excel Microsoft Access Oracle OBIEE Many more (not in this list)…..	Informatica PowerCenter (Windows) Informatica Cloud SSIS (SQL Integration Services) SQL Server Talend Data Studio Pentaho Kettle Oracle OBIEE Many more (not in this list)…..	Visual Studio C# C++ JAVA Python PHP PowerShell T-SQL (Using Linked Server)

ODBC Integration Screenshots in various tools

Tableau Integration - ODBC Driver connection for REST API / XML / JSON / SOAP / OData
Power BI Integration - ODBC Driver connection for REST API / XML / JSON / SOAP / OData
SSRS (SQL Server Reporting Services) Integration - ODBC Driver connection for REST API / XML / JSON / SOAP / OData
Visual Studio Integration (WinForm, Grid Data binding using ADO.net) - ODBC Driver connection for REST API / XML / JSON / SOAP / OData
Microsoft Excel Integration - ODBC Driver connection for REST API / XML / JSON / SOAP / OData

ZappySys Data Gateway (ODBC Bridge for SQL Server / JAVA / Linux / Mac)

ZappySys has developed a unique bridge called ZappySys Data Gateway Service (ZSDG) which can help to access our Drivers in SQL Server or JAVA based Apps or Non-Windows OS (e.g. Mac, Linux). ZappySys Data Gateway service can run in the cloud (VM Exposed to internet) or you can install locally on-premises.

Client application can connect to Data Gateway Service using any Microsoft SQL Server compatible driver (i.e. SQL Server ODBC, OLEDB, ADO.net or JDBC Driver or Linked Server in SQL Server). Data Gateway can be installed on the central server where you can have many users who can connect to Data Gateway to use ZappySys Drivers without installing anything on their machine. Data Gateway Service understands TDS Protocol and Client App can be running on any machine or operating system (MacOS, Linux, Windows).

ZappySys Data Gateway – Connect to JSON, XML, OData, REST API, SOAP data sources using TDS protocol compatible drivers (or any SQL Server ODBC, JDBC, OLEDB, ADO.net driver )

Microsoft SQL Server Integration using Data Gateway Service (T-SQL)

Here is one possible use case of using Data Gateway Service. Any DBA or Non-Programmer can start writing T-SQL queries to use ZappySys Drivers (e.g. REST API, JSON, XML, CSV data source) right inside your usual T-SQL code (You can access data from Salesforce, REST API, JSON, XML, CSV inside Views, Functions or SQL Stored Procedures).

This approach can eliminate any possible ETL work needed to extract data outside of SQL Server, you can start using your existing SQL Skill to achieve previously hard to achieve scenarios without coding.

For many other possible use case of Data Gateway click here.

SQL Server Integration Example – Query REST API / JSON Files / XML Files inside SQL Server using ZappySys Data Gateway Service (Use of Linked Server / OPENQUERY Feature in T-SQL Code / SSMS)

Video Tutorial – Calling ZappySys Drivers inside SQL Server (JSON / REST Driver use case)

Here is a short video to demonstrate a use case of Data Gateway. With this approach you can import data from REST API or any other data source for which ZappySys offers Drivers (e.g. Amazon S3, Azure, SFTP, Salesforce, XML , CSV)

SQL Query Examples – Salesforce ODBC Driver

SOQL Query – Basic Query

This example shows how to write simple SOQL query (Salesforce Object Query Language). It uses WHERE clause, ORDER BY and LIMIT clause. Using limit clause it will return only 10 records at maximum.

SOQL is similar to database SQL query language but much simpler and many features you use in database query may not be supported in SOQL (Such as JOIN clause not supported)

For more information about SOQL query syntax check below links :
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select.htm
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql.htm

SELECT Id, LastName, FirstName 
FROM Contact
Where LastName!= 'Smith'
ORDER BY LastName, FirstName
LIMIT 10

SOQL Query – Parent fields (Child-to-Parent / Many-to-One)

This example shows how to get field value from related parent object using dot operator.

For more information about relationships query check this link : https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_using.htm

SELECT Id, FirstName, LastName, Account.Name FROM Contact

SOQL Query – Child records (Parent-to-Child / One-to-Many)

This example shows how to get child records for selected parent records (e.g. All contacts for specified accounts).

For more information about relationships query check this link : https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_using.htm

SELECT Account.Id, Account.Name
    ,(SELECT Contact.FirstName, Contact.LastName, Contact.Email FROM  Account.Contacts)
FROM Account

SOQL Query – Child Query with Order By and LIMIT

This example shows how to use child query and limit number or child records for each parent row.

SELECT Name, (SELECT FirstName, LastName FROM Contacts ORDER BY CreatedDate LIMIT 5) 
FROM Account
Where Name='ZappySys'

SOQL Query – Object and field alias

This example shows how to alias Table name or field name in SOQL queries. SOQL has very limited support for alias. You can alias Table name in any query but field alias is only allowed in Aggregation query only (Query with at least one aggregation function).

For more information about Alias in SOQL query check this link : https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_alias.htm?search_text=Alias
For more information about Alias in Aggregation query check this link: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_groupby_alias.htm?search_text=Alias

SELECT c.Id, c.LastName, a.Name
FROM Contact c,c.Account a
LIMIT 10

SOQL Query – GROUP BY / Aggregate function

This example shows how to use GROUP BY clause along with optional HAVING clause. When you use Aggregate function in SQL Query you can also use alias for field (This is the only time alias can be used). Child query cannot be used when Aggregate functions are used. Refer help file to learn more about other consideration.

For more information about GROUP BY check this link : https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_groupby.htm

SELECT LeadSource, COUNT( Name ) Cnt
FROM Lead
GROUP BY LeadSource
HAVING COUNT(Name) > 100

SOQL Query – GROUP BY / Aggregate function

This example shows how to use COUNT function. You can use COUNT without or with WHERE / GROUP BY clause too.

For more information about COUNT check this link : https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_count.htm

SELECT COUNT(Id) Total FROM Account Where Name LIKE 'A%'

SOQL Query with DateTime / Use of variable

This example shows how to use date/time in WHERE clause. Date and time must be yyyy-MM-ddTHH:mm:ssZ format (make sure to include time part and add Z for UTC indicator after date and time just like example). You can use variable placeholders anywhere in the query to make it dynamic. You can use inbuilt Date Literals (e.g. TODAY, YESTERDAY, LAST_N_DAYS:365). By default when variable with DateTime type is used then its automatically converted to UTC datetime format before sending query to server. If you want to query by UTC Date only (without time portion from variable) then use TO_UTC_DATE rather than TO_UTC_DATETIME format specifier (e.g. SELECT Id, FirstName FROM Contact WHERE CreatedDate < {{System::ContainerStartTime,TO_UTC_DATE}} ). To convert UTC date variable to Local Date use TO_LOCAL_DATE or TO_LOCAL_DATETIME

For more information about query datetime field check this link : https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm?search_text=date
For more information about variable placeholder usage check this link : https://zappysys.com/onlinehelp/ssis-powerpack/scr/ssis-format-specifiers.htm

SELECT Id, LastName, FirstName, Email, Account.Name
FROM Contact
Where LastModifiedDate < {{System::ContainerStartTime,TO_UTC_DATETIME}} AND LastModifiedDate > 1999-12-31T00:00:00Z AND CreatedDate < YESTERDAY
LIMIT 10

SOQL Query – IN clause with Subquery

This example shows how to use sub query result for IN clause.
For more information check this link:
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_comparisonoperators.htm

SELECT Id, Name
FROM Account
WHERE Id IN
  (
    SELECT AccountId
    FROM Contact
    WHERE LastName LIKE 'a%'
  )
  AND Id IN
  (
    SELECT AccountId
    FROM Opportunity
    WHERE isClosed = false
  )

SOQL Query – Use of special characters (i.e Escape sequence)

This example shows how to use reserved special characters inside string literals using escape sequence. Anytime you have reserved character in string then use slash (\) in front of character e.g. Sam's Farm would be Sam\'s Farm.
For more information check below links:
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_quotedstringescapes.htm
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_reservedcharacters.htm

SELECT Id FROM Account WHERE Name = 'Sam\'s Farm

DML – UPDATE Example

This example shows how to use UPDATE one or more records in salesforce table. You can supply any valid SOQL compatible WHERE clause.

UPDATE account SET name='New Company Name', BillingState='GA' WHERE id IN('0014N00001hTNEEQA4')

WITH (
       Output=1   /*Other values can be Output='*' , Output=1 , Output=0 , Output='Col1,Col2...ColN'.  When Output option is supplied then error is not thrown but you can capture status and message in __RowStatus and __ErrorMessage output columns*/
)

DML – INSERT Example

This example shows how to create a new record in salesforce table.

INSERT INTO Account(Name,BillingCity,PK__c)
VALUES ('mycompany name','New York','K100')

WITH(
       Output=1 /*Other values can be Output='*' , Output=1 , Output=0 , Output='Col1,Col2...ColN'.  When Output option is supplied then error is not thrown but you can capture status and message in __RowStatus and __ErrorMessage output columns*/
)

DML – DELETE Example

This example shows how to delete one or more records in salesforce table. You can supply any valid SOQL compatible WHERE clause.

DELETE FROM account WHERE id IN ('0014N00001hTNEEQA4')

WITH(
       Output=1 /*Other values can be Output='*' , Output=1 , Output=0 , Output='Col1,Col2...ColN'.  When Output option is supplied then error is not thrown but you can capture status and message in __RowStatus and __ErrorMessage output columns*/
)

DML – UPSERT Example (Update or Insert)

This example shows how to Update or Insert record if not found (i.e. UPSERT). For Upsert You must supply Key field name in WITH clause. Driver lookup record based on Key column, if not found in salesforce then it creates a new row else update exising record. For Key field you can use id column (system field) or any valid external id field created using this method https://zappysys.com/links/?id=10098 .

UPSERT INTO Account(Name, BillingCity,SupplierId__c)
VALUES ('mycompany name','New York','K100')
WITH (KEY='SupplierId__c',Output=1)

External Input from MSSQL – INSERT multiple rows from MSSQL to Salesforce

This example shows how to call INSERT operation which takes multiple rows as input from external system (i.e. MSSQL, ODBC, OLEDB). In this example we specified Driver Type as MSSQL. For other system (i.e. CSV, API, ORACLE) change driver type to ODBC adjust connection string / query. Make sure query outputs column names which are found in Salesforce Table. EXTERNAL option maps target columns based on input query. In order to run this example service account used to run Data Gateway Service must have correct permission if you are using windows authentication.

INSERT INTO Account
SOURCE('MSSQL' --external driver type (MSSQL, ODBC, OLEDB)
      ,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'  --driver specific connection string (i.e. MSSQL, ODBC, OLEDB)
      ,'select ''Account001'' as Name,''City001'' as BillingCity,1 as NumberofLocations__c  UNION
          select ''Account002'' as Name,''City002'' as BillingCity,2 as NumberofLocations__c  UNION
          select ''Account003'' as Name,''City003'' as BillingCity,3 as NumberofLocations__c '
          --query for external system (i.e. MSSQL). Output column names in query must match columns found in target table (i.e. Account).
          --To see all possible columns in target, select table from dropdown on the preview tab
      )


WITH(
       Output=1/*Other values can be Output='*' , Output=1 , Output=0 , Output='Col1,Col2...ColN'.  When Output option is supplied then error is not thrown but you can capture status and message in __RowStatus and __ErrorMessage output columns*/        
    --,EnableBulkMode=1 --use this to invoke Bulk API (usually slower for less number of rows, works better for large dataset 5K+)
    --OR--  
      ,EnableParallelThreads=1 --//Send records in multiple threads for faster data processing (200 rows sent in each batch). If you use EnableBulkMode=1 then this option is ignored and 10000 rows processed in a each batch using Job Style API rather than Real Time API (faster for less than few hundred rows).
      ,MaxParallelThreads=6  --//Maximum threads to use for parallel processing
)

External Input from ODBC – INSERT multiple rows from ODBC Source (i.e. CSV) to Salesforce

INSERT INTO Account
SOURCE(-'ODBC'---external driver type (MSSQL, ODBC, OLEDB)
      ,'Driver={ZappySys CSV Driver};DataPath=c:\somefile.csv' --driver specific connection string (i.e. MSSQL, ODBC, OLEDB)
      ,'SELECT Acct_Name as Name,Billing_City as BillingCity,Locations as  NumberofLocations__c
FROM $
WITH( 
--You can use SRC=xxxxxx or static data using DATA=xxxxxx attribute below (either or). For example purpose we use static data but in real world you will read from file so comment DATA=xxx
--SRC=''c:\file_1.csv''
--SRC=''c:\some*.csv''
--SRC=''https://abc.com/api/somedata-in-csv''
DATA = ''Acct_Name,Billing_City,Locations
Account001,City001,1
Account002,City002,2
Account003,City003,3
''  
 )'
)
--query for external system (i.e. MSSQL). Output column names in query must match columns found in target table (i.e. Account) this is the reason we used alias in names.
--To see all possible columns in target, select table from dropdown on the preview tab


WITH(
       Output=1/*Other values can be Output='*' , Output=1 , Output=0 , Output='Col1,Col2...ColN'.  When Output option is supplied then error is not thrown but you can capture status and message in __RowStatus and __ErrorMessage output columns*/        
    --,EnableBulkMode=1 --use this to invoke Bulk API (usually slower for less number of rows, works better for large dataset 5K+)
    --OR--
      ,EnableParallelThreads=1 --//Send records in multiple threads for faster data processing (200 rows sent in each batch). If you use EnableBulkMode=1 then this option is ignored and 10000 rows processed in a each batch using Job Style API rather than Real Time API (faster for less than few hundred rows).
      ,MaxParallelThreads=6--//Maximum threads to use for parallel processing
)

Bulk Api – INSERT large amount of data from External Source (i.e. MSSQL) to Salesforce

This example shows use EnableBulkMode option to insert large amount of data to salesforce (Invoke Job Style Bulk API). By default 200 rows are written in a single batch but when you use Bulk API mode it sends 10000 rows in a single batch. BulkAPI mode might not give you performance gain if you are writing only few hundred rows. In this example we specified Driver Type as MSSQL. For other system (i.e. CSV, API, ORACLE) change driver type to ODBC adjust connection string / query. Make sure query outputs column names which are found in Salesforce Table. EXTERNAL option maps target columns based on input query. In order to run this example service account used to run Data Gateway Service must have correct permission if you are using windows authentication.

INSERT INTO Account
SOURCE('MSSQL'--external driver type (MSSQL, ODBC, OLEDB)
      ,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'--driver specific connection string (i.e. MSSQL, ODBC, OLEDB)	  
      ,'select top 1000000 C_NAME as Name,C_CITY as BillingCity, C_LOC as NumberofLocations__c  From very_large_staging_table'
      )
WITH(
      Output=1/*Other values can be Output='*' , Output=1 , Output=0 , Output='Col1,Col2...ColN'.  When Output option is supplied then error is not thrown but you can capture status and message in __RowStatus and __ErrorMessage output columns*/  
     ,EnableBulkMode=1 --use Job Style Bulk API (uses 10000 rows per batch rather than 200)  
   --,MaxRowsPerJob=500000 --//useful to control memory footprint in driver
    --,ConcurrencyMode='Default' /* or 'Parallel' or 'Serial' if you get locking errors then change to Serial*/
)

Bulk Api – DELETE large amount of data (Read Ids from External Source)

This example shows use EnableBulkMode option to delete large amount of records from salesforce (Invoke Job Style Bulk API). For delete you must pass Id column from source (if source has different name make sure to alias to Id in SQL). By default 200 rows are written in a single batch but when you use Bulk API mode it sends 10000 rows in a single batch. BulkAPI mode might not give you performance gain if you are writing only few hundred rows. In this example we specified Driver Type as MSSQL. For other system (i.e. CSV, API, ORACLE) change driver type to ODBC adjust connection string / query. Make sure query outputs column names which are found in Salesforce Table. EXTERNAL option maps target columns based on input query. In order to run this example service account used to run Data Gateway Service must have correct permission if you are using windows authentication.

DELETE FROM Account
SOURCE('MSSQL' --external driver type (MSSQL, ODBC, OLEDB)
      ,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'  --driver specific connection string (i.e. MSSQL, ODBC, OLEDB)
      ,'select top 1000000 Account_ID as Id From very_large_staging_table'
      )
WITH(
       Output=1/*Other values can be Output='*' , Output=1 , Output=0 , Output='Col1,Col2...ColN'.  When Output option is supplied then error is not thrown but you can capture status and message in __RowStatus and __ErrorMessage output columns*/ 
      ,EnableBulkMode=1 --use Job Style Bulk API (uses 10000 rows per batch rather than 200) 
    --,MaxRowsPerJob=500000 --//useful to control memory footprint in driver
    --,ConcurrencyMode='Default' /* or 'Parallel' or 'Serial' if you get locking errors then change to Serial*/
)

Bulk Api – UPDATE large amount of data (Read from External Source)

This example shows use EnableBulkMode option to update large amount of data to salesforce (Invoke Job Style Bulk API). For update you must pass Id column from source (if source has different name make sure to alias to Id in SQL). By default 200 rows are written in a single batch but when you use Bulk API mode it sends 10000 rows in a single batch. BulkAPI mode might not give you performance gain if you are writing only few hundred rows. In this example we specified Driver Type as MSSQL. For other system (i.e. CSV, API, ORACLE) change driver type to ODBC adjust connection string / query. Make sure query outputs column names which are found in Salesforce Table. EXTERNAL option maps target columns based on input query. In order to run this example service account used to run Data Gateway Service must have correct permission if you are using windows authentication.

UPDATE Account
SOURCE('MSSQL' --external driver type (MSSQL, ODBC, OLEDB)
      ,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'  --driver specific connection string (i.e. MSSQL, ODBC, OLEDB)
      ,'select top 1000000 Account_ID as Id, Account_Name as Name,City as BillingCity From very_large_staging_table'
      )
WITH(
       Output=1 /*Other values can be Output='*' , Output=1 , Output=0 , Output='Col1,Col2...ColN'.  When Output option is supplied then error is not thrown but you can capture status and message in __RowStatus and __ErrorMessage output columns*/
      ,EnableBulkMode=1 --use Job Style Bulk API (uses 10000 rows per batch rather than 200) 
    --,MaxRowsPerJob=500000 --//useful to control memory footprint in driver
    --,ConcurrencyMode='Default' /* or 'Parallel' or 'Serial' if you get locking errors then change to Serial*/
)

Programming Language Examples

Most programming languages come with out of the box support for ODBC. Which means you can use ZappySys ODBC drivers inside your favorite language. Here are few languages which already support ODBC. We have used JSON Driver / SQL query as an example but concept is same for other drivers too. Refer to help file to learn more about Driver specific Connection String and SQL Query.

C#JAVAPythonPHPPowerShell

using (OdbcConnection conn = 
            new OdbcConnection("DRIVER ={ZappySys Salesforce Driver};"))
{
    conn.Open();
    cmd = new OdbcCommand(
@"SELECT 
Country as Invoice_Country, SUM(UnitPrice * Quantity) Total 
FROM value
GROUP BY Country
ORDERBY Total DESC", conn);
 
    var rdr = cmd.ExecuteReader();
    while (rdr.Read())
    {
        Console.WriteLine("---- Fetching Row -------");
        for (i = 0; i < rdr.FieldCount; i++)
        {
            Console.Write("Field {0}={1} ", i, rdr[i]);
        }
        Console.WriteLine("");
    }
}

//Assuming the Microsoft SQL Server JDBC Driver is in below folder
//C:\Program Files\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\auth\x64
private static final String jdbcDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
 
//The JDBC connection URL to connect to ZappySys Data Gateway Service using SQL Server driver
private static final String jdbcURL = "jdbc:sqlserver://localhost:5000;databasename=master;user=tdsuser;password=tds123;";
 
//Connect to the database
Connection databaseConnection = DriverManager.getConnection(jdbcURL);
System.out.println("Connected to ZappySys Data Gateway Service using Microsoft SQL Server JDBC driver");
 
//declare the statement object
Statement sqlStatement = databaseConnection.createStatement();
 
ResultSet rs = sqlStatement.executeQuery("SELECT Country , SUM(UnitPrice * Quantity) Total " 
	+ "FROM value " 
	+ "GROUP BY Country " 
	+ "WITH (SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json')");
 
while (rs.next()) {
  System.out.println("-----Fetching new row----\n");	
  System.out.println(rs.getString("Country") + "\n");
  //System.out.println(rs.getString("Total") + "\n");
}

#Example of using ODBC driver inside Python using pyodbc library (Read more info about pyodbc from below)
#https://github.com/mkleehammer/pyodbc/wiki

import pyodbc 
 
#connect to api service using ZappySys ODBC driver for JSON

#Use DSN 
#conn = pyodbc.connect(r'DSN=MyZappyDsnName;')

# OR Use direct connection string 
conn = pyodbc.connect(
    r'DRIVER={ZappySys Salesforce Driver};'
    )
cursor = cnxn.cursor()	
 
#execute query to fetch data from API service
cursor.execute("SELECT * FROM value ORDER BY Country WITH (SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json')") 
row = cursor.fetchone() 
while row: 
    print row[0] 
    row = cursor.fetchone()

echo "Example of using ZappySys ODBC Driver in PHP\n";
 
$conn = odbc_connect("DRIVER={ZappySys Salesforce Driver};", "", "");
$sql = "SELECT * FROM value ORDER BY Country WITH (SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json')";
$rs = odbc_exec($conn,$sql);
 
echo "Fetching first row....\n";
odbc_fetch_row($rs);
echo "Country=" . odbc_result($rs,"Country") . "\n";
 
echo "Closing connection ....\n";
odbc_close($conn);

$conn = New-Object System.Data.Odbc.OdbcConnection
$conn.ConnectionString = "DRIVER={ZappySys Salesforce Driver}"
 
#--OR-- Use DSN name
#$conn.connectionstring = "DSN=MyDSNName"
 
$conn.Open()
 
# -------------------------------------------------------------------------------
# In powershell $ is special char so we used `$ in below string to escape it. 
# Also We used multi string start with "@<new line> and ends with <new line>"@
# -------------------------------------------------------------------------------
$sql = 
@"
SELECT * FROM value 
WITH (SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Customers?`$format=json')
"@
 
$cmd = $conn.CreateCommand()
$cmd.CommandText = $sql
 
$dataset = New-Object System.Data.DataSet
#Load data in DataSet
(New-Object System.Data.Odbc.OdbcDataAdapter($cmd)).Fill($dataSet)
 
#Export datatable to file in CSV format
$dataset.Tables[0] | ConvertTo-csv -NoTypeInformation -Delimiter "`t" | Out-File "c:\temp\dump.csv" -fo
 
Write-Host "Total rows $($dataSet.Tables[0].Rows.Count)"
$conn.Close()

Salesforce ODBC Driver

Feature Summary

Featured Articles

Salesforce ODBC Driver UI

Integration Scenarios (Reporting / ETL / BI / Programming)

ODBC Integration Screenshots in various tools

ZappySys Data Gateway (ODBC Bridge for SQL Server / JAVA / Linux / Mac)

Microsoft SQL Server Integration using Data Gateway Service (T-SQL)

Video Tutorial – Calling ZappySys Drivers inside SQL Server (JSON / REST Driver use case)

SQL Query Examples – Salesforce ODBC Driver

SOQL Query – Basic Query

SOQL Query – Parent fields (Child-to-Parent / Many-to-One)

SOQL Query – Child records (Parent-to-Child / One-to-Many)

SOQL Query – Child Query with Order By and LIMIT

SOQL Query – Object and field alias

SOQL Query – GROUP BY / Aggregate function

SOQL Query – GROUP BY / Aggregate function

SOQL Query with DateTime / Use of variable

SOQL Query – IN clause with Subquery

SOQL Query – Use of special characters (i.e Escape sequence)

DML – UPDATE Example

DML – INSERT Example

DML – DELETE Example

DML – UPSERT Example (Update or Insert)

External Input from MSSQL – INSERT multiple rows from MSSQL to Salesforce

External Input from ODBC – INSERT multiple rows from ODBC Source (i.e. CSV) to Salesforce

Bulk Api – INSERT large amount of data from External Source (i.e. MSSQL) to Salesforce

Bulk Api – DELETE large amount of data (Read Ids from External Source)

Bulk Api – UPDATE large amount of data (Read from External Source)

Programming Language Examples