ODBC API Driver (Pre-Configured Connectors)

This example shows how to download binary data (blob) as a Byte array. Use SaveContentAsBinary=1 to treat response as binary, and Meta=’data:Blob’ to return the data column as Byte[] (DT_IMAGE).

SELECT * FROM generic_request
WITH (
    URL='/small.png'
  --OR use full URL
  --URL='https://zappysys.com/downloads/files/test/small.png'
  , RequestMethod='GET'
  , SaveContentAsBinary=1
  , Meta='data:Blob'
)
-- Alternative options for Meta:
-- , Meta='data:DT_IMAGE' -- Direct SSIS type (DT_IMAGE for binary data)
-- , Meta='data:Byte(10000)' -- Binary data with length (becomes DT_BYTES if <= 8000, DT_IMAGE if > 8000)
-- Note: If you remove Meta='data:Blob', the data column will return as base64 string instead of Byte[] array

Open this example in the help

This example shows how to download a file directly to disk. Use SaveContentAsBinary=1 to treat response as binary, and ResponseDataFile to specify the target file path.

SELECT * FROM generic_request
WITH (
    URL='/small.png'
  --OR use full URL
  --URL='https://zappysys.com/downloads/files/test/small.png'
  , RequestMethod='GET'
  , SaveContentAsBinary=1
  , ResponseDataFile='c:\temp\small.png'
  , RawOutputDataRowTemplate='{Status:"Downloaded"}'
  , EnableRawOutputModeSingleRow=1
  , FileOverwriteMode='AlwaysOverwrite'
)
-- FileOverwriteMode options:
-- 'AlwaysOverwrite' or 0 - Always overwrite existing file
-- 'FailIfExists' or 1 - Fail if file already exists
-- 'SkipIfExists' or 2 - Skip download if file already exists

Open this example in the help

SELECT id,name,checknumber FROM MyTable
WITH
(
--//Custom Metadata (Direct Mode)<<<
--//Add all possible columns here and it will be part of metadata (this approach ignores any metadata comes from table itself). This approach usually helpful when Table has dynamic data (scan 300 rows to detect types) which may cause issue if zero records found.
META = 'id:int;checknumber:string(10);name:string(10);some_extra_column:datetime'

--//Custom Metadata with Override  (Direct Mode)<<<
--You can use @OverrideMode:1 in the column list to override meta for specific column(s) only and use all other columns from reported metadata for table - In below example we are changing default datatype/length for id and checknumber, all other columns are dynamic so dont have to supply in META clause
--META = '@OverrideMode:1;id:int;checknumber:string(10)'

--//Metadata from File (supply metadata stored in file)
--META = 'c:\mytable_meta.txt'
)

Open this example in the help

Basic query examples.

--Whenever Possible try to write Simple Query. When you use WHERE / GROUP BY / ORDER BY Expressions. Driver may invoke Client side processing if server side doesnt support that construct.
--For comment use line comment with two dash (e.g. --my comment) or use block comment for multi line comment (e.g. /*my comment*/ )
--Any query must contain at least two required parts
--data source (or table) names and a list of column names
--column names come first (preceded by SELECT keyword)
--table names follow (preceded by FROM keyword)

--Here is the query that returns all rows with all columns (* stands for all columns) from 'Accounts' table
SELECT * FROM Accounts

--Use quoted identifiers when you have space or other character in names (e.g. Column or Table name contains space)
SELECT ID, "Account Name" FROM "Closed Accounts"
SELECT ID, [Account Name] FROM [Closed Accounts]

--returns all rows from 'Accounts' table, each row will have Id and Amount columns
SELECT Id, Amount FROM Accounts

--returns only those rows of 'Accounts' table that meet next condition: Amount is greater or equal to 1000.0
SELECT Id, Amount FROM Accounts WHERE Amount >= 1000.0

--returns only those rows of 'Accounts' table that meet next condition: Amount is greater than 1000 or DepartamentID is equal to 123
SELECT Id, Amount FROM Accounts WHERE (Amount > 1000.0 OR DepartamentID = 123)

--returns only those rows of 'Accounts' table that meet next condition: DepartamentID is equal to 1, 101 or 202
SELECT Id, Amount FROM Accounts WHERE DepartamentID IN (1, 101, 202)

--returns only those rows of 'Accounts' table that meet next condition: DepartamentID is equal to 321 and Amount is less than 100
SELECT Id, Amount FROM Accounts WHERE DepartamentID = 321 AND Amount < 100

--returns only those rows of 'Persons' table that meet next condition: Name column value starts with 'John'
SELECT * FROM Person WHERE Name LIKE 'John%'

--case statement
SELECT
 FirstName,
 LastName,
 CASE Country
  WHEN 'US' THEN 'NA'
  WHEN 'Canada' THEN 'NA'
  WHEN 'India' THEN 'Asia'
  WHEN 'China' THEN 'Asia'
  ELSE 'Unknown'
 END ColRegion
 FROM Person

Open this example in the help

This example shows how to call any API URL for the connector. For binary data, use Meta=’data:Byte’, Meta=’data:Blob’, or Meta=’data:DT_IMAGE’. You can also use direct SSIS types (e.g., DT_IMAGE, DT_WSTR, DT_I4) with optional length (e.g., DT_WSTR(1000))

SELECT * FROM generic_request
WITH (
    URL='/customers?para=123'
    --OR use full URL
    --URL='https://mycompany.com/api/customers?para=123'
  , RequestMethod='POST'
  , Filter='$.value[*]' --optional if you like to read from array inside document
  , Headers='Content-Type: application/json || x-header2: abcd'
  , Body='{para1: 1, para2: "ABC"}'

  --Meta data is optional but very useful for POST calls to avoid double requests
  --column names are attribute under specified filter. You can use underscore for nested attribute e.g.  info_address_zip: int (when JSON looks like this { info: { address: { zip: 1234, city: "aaa" } } } )
  --,Meta='col1:int;col2:string;col3:bool;col4:double;col5:datetime' --userdefined types
  --,Meta='col1;col2;col3;col4;col5' --default types
  --,Meta='data:Byte' --Single byte (DT_I1) - backward compatible, use Blob or DT_IMAGE for byte arrays
  --,Meta='data:Byte(1000)' --Binary data with length (becomes DT_BYTES if <= 8000, DT_IMAGE if > 8000)
  --,Meta='data:Blob' --Binary data as blob (always DT_IMAGE, length ignored)
  --,Meta='data:DT_IMAGE' --Direct SSIS type (DT_IMAGE for binary data)
  --,Meta='col1:DT_WSTR(1000)' --Direct SSIS type with length (DT_WSTR with length 1000)
)

Open this example in the help

This example shows how to call any API URL for the connector

SELECT * FROM generic_request
WITH (
    URL='/files/upload?para=123'
    --OR use full URL
    --URL='https://mycompany.com/api/files/upload?para=123'
  , RequestMethod='POST'
  , Filter='$.value[*]' --optional if you like to read from array inside document
  , Headers='Content-Type: application/octet-stream || x-header2: abcd'
  , Body='@c:\data\myfile.zip'
/*
 Use below if File Upload needs additional metadata along with binary data - One Key/Value per line. File Path must start with @ symbol
  , Body='myfile=@c:\data\myfile.zip
myfilename=myfile.zip
field1=aaaa
field2=bbbb'
*/
  , IsMultiPart=1

  --Meta data is optional but very useful for POST calls to avoid double requests
  --column names are attribute under specified filter. You can use underscore for nested attribute e.g.  info_address_zip: int (when JSON looks like this { info: { address: { zip: 1234, city: "aaa" } } } )
  --,Meta='col1:int;col2:string;col3:bool;col4:double;col5:datetime' --userdefined types
  --,Meta='col1;col2;col3;col4;col5' --default types
)

Open this example in the help

This examples shows how to supply connection information via CONNECTION clause in the sql query. CONNECTION clause takes precedence over the default values configured for data source using UI

SELECT * FROM Customers
--WITH(Param1 = 'something', Param2 = 'something')
CONNECTION(

    , Provider = 'OData'
    , ServiceUrl = 'https://services.odata.org/V3/Northwind/Northwind.svc'
--, Provider = 'Custom'
--, ConfigFile = 'C:\connectors\odata-connector.xml'
--, AuthName = 'BasicAuth'
--, UserName = 'user1'
--, Password = 'pass1'
--, Parameters = '[{Name:"ConnPram1",Value:"some1"}, {Name:"ConnPram2",Value:"some2"}]'
)

Open this example in the help

This examples shows how to supply direct ConfigText (connector xml) in the optional CONNECTION clause of SQL query. CONNECTION clause takes precedence over the default values configured for data source using UI

SELECT * FROM Customers
--WITH(Param1='something', Param2='something')
CONNECTION(
--	,Provider='OData'
	,ServiceUrl='https://services.odata.org/V3/Northwind/Northwind.svc'
	,Provider='Custom'
	,UseEmbeddedConfig=1
    ,ConfigText='<ApiConfig>
  <ServiceUrls>
    <ServiceUrl Url="https://services.odata.org/V3/Northwind/Northwind.svc" />
  </ServiceUrls>
  <EndPoints>
    <EndPoint Name="Customers" Url="/Customers?$format=json" Method="GET" Filter="$.value[*]" Body="" />
  </EndPoints>
</ApiConfig>'
--	,ConfigFile='C:\connectors\odata-connector.xml'  --to use file mode remove UseEmbeddedConfig, ConfigText and set Provider='Custom'
--	,AuthName='BasicAuth'
--  ,UserName='user1'
--  ,Password='pass1'
--	,Parameters='[{Name:"ConnPram1",Value:"some1"}, {Name:"ConnPram2",Value:"some2"}]'
)

Open this example in the help

This example shows how to use WITH clause along with Connection clause. Use WITH clause to supply request parameters (Click on [Query Builder] to build request correctly). You can use CONNECTION clause to override any connection level parameters.

SELECT * FROM Customers
LIMIT 3
WITH($filter='Country eq ''UK''')
CONNECTION(Provider='OData', ServiceUrl='https://services.odata.org/V3/Northwind/Northwind.svc')

Open this example in the help

This example shows how to call INSERT row action for a specified table. Optionally you can supply Parameters via WITH clause.

INSERT INTO MyTable(Col1,Col2) Values('something', 1234)

--When you use below clause then make sure to remove static column names / values from INSERT/UPDATE/UPSERT (i.e. SET C1=.., VALUES(..)) clause because it will be supplied by SOURCE SQL
--SOURCE( 'MSSQL'--OR 'ODBC'
--        --//SQL Mixed Mode:
--        , 'Data Source=localhost;Initial Catalog=tempdb;User ID=user1;Password=mypass123;Integrated Security=false'
--        --//-OR- SQL Windows Auth: (For this to work - ZappySys Data Gateway Service Account must be added as SQL Server Login) -- See https://zappysys.com/links/?id=10151
--        , 'Data Source=localhost;Initial Catalog=MyDB;Integrated Security=true'
--        , 'select SrcCol1 as TgtCol1,SrcCol2 as TgtCol2,''USA'' [$$MyParam2] FROM dbo.SomeTable')

--WITH(Param1='something', Param2='something')

--CONNECTION( UserName='user1'
--        , Password='mypassword123'
--        , Provider='Custom'
--        , ConfigFile='c:\etl\myapi-connector.xml'
--        , ServiceUrl='https://myapi.com/v1'
--        , Parameters='[{Name:'MyConnParam1',Value:'something'},{Name:'MyConnParam2',Value:'something'}]'
--        , AuthName='Http')

Open this example in the help

This example shows how to call UPDATE on a table. Optionally you can supply Parameters via WITH clause.

UPDATE MyTable SET Col1='something', Col2=1234

--When you use below clause then make sure to remove static column names / values from INSERT/UPDATE/UPSERT (i.e. SET C1=.., VALUES(..)) clause because it will be supplied by SOURCE SQL
--SOURCE( 'MSSQL'--OR 'ODBC'
--        --//SQL Mixed Mode:
--        , 'Data Source=localhost;Initial Catalog=tempdb;User ID=user1;Password=mypass123;Integrated Security=false'
--        --//-OR- SQL Windows Auth: (For this to work - ZappySys Data Gateway Service Account must be added as SQL Server Login) -- See https://zappysys.com/links/?id=10151
--        , 'Data Source=localhost;Initial Catalog=MyDB;Integrated Security=true'
--        , 'select SrcCol1 as TgtCol1,SrcCol2 as TgtCol2,''USA'' [$$MyParam2] FROM dbo.SomeTable')

--WITH(Param1='something', Param2='something')

--CONNECTION( UserName='user1'
--        , Password='mypassword123'
--        , Provider='Custom'
--        , ConfigFile='c:\etl\myapi-connector.xml'
--        , ServiceUrl='https://myapi.com/v1'
--        , Parameters='[{Name:'MyConnParam1',Value:'something'},{Name:'MyConnParam2',Value:'something'}]'
--        , AuthName='Http')

Open this example in the help

This example shows how to call DELETE row action for a specified table. Optionally you can supply Parameters via WITH clause.

DELETE FROM MyTable

--When you use below clause then make sure to remove static column names / values from INSERT/UPDATE/UPSERT (i.e. SET C1=.., VALUES(..)) clause because it will be supplied by SOURCE SQL
--SOURCE( 'MSSQL'--OR 'ODBC'
--        --//SQL Mixed Mode:
--        , 'Data Source=localhost;Initial Catalog=tempdb;User ID=user1;Password=mypass123;Integrated Security=false'
--        --//-OR- SQL Windows Auth: (For this to work - ZappySys Data Gateway Service Account must be added as SQL Server Login) -- See https://zappysys.com/links/?id=10151
--        , 'Data Source=localhost;Initial Catalog=MyDB;Integrated Security=true'
--        , 'select SrcCol1 as TgtCol1,SrcCol2 as TgtCol2,''USA'' [$$MyParam2] FROM dbo.SomeTable')

--WITH(Param1='something', Param2='something')

--CONNECTION( UserName='user1'
--        , Password='mypassword123'
--        , Provider='Custom'
--        , ConfigFile='c:\etl\myapi-connector.xml'
--        , ServiceUrl='https://myapi.com/v1'
--        , Parameters='[{Name:'MyConnParam1',Value:'something'},{Name:'MyConnParam2',Value:'something'}]'
--        , AuthName='Http')

Open this example in the help

This example shows how to call UPSERT action for a specified table. Optionally you can supply Parameters via WITH clause.

UPSERT INTO MyTable(Col1,Col2) Values('something', 1234)

--When you use below clause then make sure to remove static column names / values from INSERT/UPDATE/UPSERT (i.e. SET C1=.., VALUES(..)) clause because it will be supplied by SOURCE SQL
--SOURCE( 'MSSQL'--OR 'ODBC'
--        --//SQL Mixed Mode:
--        , 'Data Source=localhost;Initial Catalog=tempdb;User ID=user1;Password=mypass123;Integrated Security=false'
--        --//-OR- SQL Windows Auth: (For this to work - ZappySys Data Gateway Service Account must be added as SQL Server Login) -- See https://zappysys.com/links/?id=10151
--        , 'Data Source=localhost;Initial Catalog=MyDB;Integrated Security=true'
--        , 'select SrcCol1 as TgtCol1,SrcCol2 as TgtCol2,''USA'' [$$MyParam2] FROM dbo.SomeTable')

--WITH(Param1='something', Param2='something')

--CONNECTION( UserName='user1'
--        , Password='mypassword123'
--        , Provider='Custom'
--        , ConfigFile='c:\etl\myapi-connector.xml'
--        , ServiceUrl='https://myapi.com/v1'
--        , Parameters='[{Name:'MyConnParam1',Value:'something'},{Name:'MyConnParam2',Value:'something'}]'
--        , AuthName='Http')

Open this example in the help

This example shows how to use SOURCE clause to perform Bulk INSERT operation. In this example we are reading many rows from external system (e.g. MSSQL) and supplying to INSERT command.

INSERT INTO MyTable
--Column list / Values omitted when you use SOURCE clause
SOURCE('MSSQL', 'Data Source=localhost;Initial Catalog=SalesDB;Integrated Security=false;User Id=user1;Password=abcd','select Id as [TargetId],''USA'' as [$$MyParam2] FROM dbo.SomeTable')

--For bulk input, map columns in External Query (Must use alias column name to match INSERT command Input Column names - see help file)
--If parameter value not same for all input rows then you can prefix some column with $$ to map as parameter (i.e. $$MyParam1)
--SOURCE('ODBC', 'Driver={ZappySys CSV Driver};DataPath=C:\AccountsToInsert.csv','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM $')
--SOURCE('ODBC', 'DSN=MyDSN','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')
--SOURCE('OLEDB', 'Provider=SQLNCLI11;Server=localhost,1433;Database=tempdb;Trusted_Connection=yes;','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')

WITH(MyParam1='something', MyParam2='something')

Open this example in the help

This example shows how to use SOURCE clause to perform Bulk DELETE operation. In this example we are reading many rows from external system (e.g. MSSQL) and supplying to DELETE command.

DELETE FROM MyTable
SOURCE('MSSQL', 'Data Source=localhost;Initial Catalog=SalesDB;Integrated Security=false;User Id=user1;Password=abcd','select Id as [TargetId],''USA'' as [$$MyParam2] FROM dbo.SomeTable')

--For bulk input, map columns in External Query (Must use alias column name to match DELETE command Input Column names - see help file)
--If parameter value not same for all input rows then you can prefix some column with $$ to map as parameter (i.e. $$MyParam1)
--SOURCE('ODBC', 'Driver={ZappySys CSV Driver};DataPath=C:\AccountsToDelete.csv','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM $')
--SOURCE('ODBC', 'DSN=MyDSN','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')
--SOURCE('OLEDB', 'Provider=SQLNCLI11;Server=localhost,1433;Database=tempdb;Trusted_Connection=yes;','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')

WITH(MyParam1='something', MyParam2='something')

Open this example in the help

Advanced Query can invokes ClientSide query Engine which means entire table is fetched in memory for processing causing OutOfMemory Exception (OOM). To avoid this error you can specify Disk based Temporary storage for query engine.

SELECT * MyReallyLargeTable
WITH(
  TempStorageMode='Disk' --or 'Memory'
--OR
--TempStorageMode=1 --//Memory=0, Disk=1
)

Open this example in the help

This example shows how to use date time field in WHERE or / ORDER BY. Date Time field needs special handling. You have to use DATETIME function else it will do string compare rather true date time compare.

SELECT * FROM Orders
WHERE OrderDate<=DATETIME('1997-12-31 00:00:00') -- OR use DATE('1997-12-31') DateTime column can be queried this way. You must wrap DATETIME function around it. Also must use 'yyyy-MM-dd' or 'yyyy-MM-dd HH:mm:ss'  or 'yyyy-MM-dd HH:mm:ss.fff' format (where fff is milliseconds)
ORDER BY DATETIME(OrderDate)
--WITH (MyParam1='something1', MyParam2='something2')
CONNECTION(Provider='OData', ServiceUrl='https://services.odata.org/V3/Northwind/Northwind.svc');

Open this example in the help

This example shows how to use WHERE clause and search by datetime, use GROUP BY / HAVING, ORDER BY and LIMIT clauses. You can use OData Connector with Northwind Service URL to run this example.

SELECT
  Country AS Invoice_Country
, SUM(UnitPrice * Quantity) AS Invoice_Total
FROM Invoices
WHERE Discount > 0
AND OrderDate<=DATETIME('1997-12-31 00:00:00')
--or use OrderDate<=DATE('1997-12-31')
--Valid formats are yyyy-MM-dd, yyyy-MM-dd HH:mm:ss or yyyy-MM-dd HH:mm:ss.fff (where fff is milliseconds)
GROUP BY Country
HAVING SUM(UnitPrice * Quantity) > 1000
ORDER BY
  Invoice_Total DESC
--,DATETIME(OrderDate)
LIMIT 3
CONNECTION(Provider='OData', ServiceUrl='https://services.odata.org/V3/Northwind/Northwind.svc');

Open this example in the help

This example shows how to write CASE statement to select / output different value based on multiple conditions.

SELECT
  CustomerID
, CompanyName
, Country
, Region
--Pattern#1 - Match by clause
, CASE
   WHEN Country IN('UK','Germany','France') OR Region='BC' THEN 'EU'
   WHEN Country IN('USA','Maxico','Canada') THEN 'NA'
   WHEN Country IN('Brazil','Argentina','Venezuela') THEN 'SA'
   ELSE 'Unknown'
 END Pattern1

--Pattern#2 - Match by static value or simple expression
, CASE Country
   WHEN 'U'+'K' THEN 'EU'
   WHEN 'USA' THEN 'EU'
   WHEN 'Brazil' THEN 'SA'
   WHEN 'Argentina' THEN 'SA'
   WHEN 'Venezuela' THEN 'SA'
   ELSE 'Unknown'
 END Pattern2

FROM Customers
CONNECTION(Provider='OData', ServiceUrl='https://services.odata.org/V3/Northwind/Northwind.svc')

Open this example in the help

This example shows how to use SELECT INTO statement and use of multiple statements in the same batch. Output from only last SELECT is returned if you have multiple SELECT * FROM in the same batch. Each statement must be separated by semi-colon ( ; ).

SELECT * INTO #tmpCustomers FROM Customers
--WITH (MyParam1='something1', MyParam2='something2')
CONNECTION(Provider='OData', ServiceUrl='https://services.odata.org/V3/Northwind/Northwind.svc');

SELECT * INTO #tmpOrders FROM Orders
--WITH (MyParam1='something1', MyParam2='something2')
CONNECTION(Provider='OData', ServiceUrl='https://services.odata.org/V3/Northwind/Northwind.svc');

--JOIN 2 temporary tables
select c.CompanyName,Count(*) TotalOrders
FROM #tmpCustomers c
INNER JOIN #tmpOrders o ON o.CustomerID=c.CustomerID
WHERE o.OrderDate<=DATETIME('1997-12-31 23:12:59') --date format must be yyyy-MM-dd or yyyy-MM-dd HH:mm:ss[.fff]
GROUP BY  c.CompanyName;

Open this example in the help

This example shows how to write UNION or UNION ALL statement to combine results from multiple SQL queries. To remove duplicate rows from result use UNION rather than UNION ALL.

SELECT * into #tmpCustomersUK FROM Customers Where Country='UK'
--WITH (MyParam1='something1', MyParam2='something2');

SELECT * into #tmpCustomersUSA FROM Customers WHERE Country='USA'
--WITH (MyParam1='something1', MyParam2='something2');

select * from #tmpCustomersUK
UNION ALL
select * from #tmpCustomersUSA;

Open this example in the help

Query the driver from SQL Server through the Data Gateway. The driver does not have to be installed on SQL Server.

-- SQL Server: create a linked server to the ZappySys Data Gateway (here [ZAPPY_GATEWAY]),
-- then run the driver query through OPENQUERY.
SELECT *
FROM OPENQUERY([ZAPPY_GATEWAY], 'SELECT * FROM Customers CONNECTION (Provider = ''OData'', ServiceUrl = ''https://services.odata.org/V3/Northwind/Northwind.svc'')')

using System;
using System.Data.Odbc;

// Use a DSN ("DSN=MyZappyDsn;") or name the driver directly
using (var conn = new OdbcConnection("DRIVER={ZappySys API Driver};"))
{
    conn.Open();
    var cmd = new OdbcCommand(@"SELECT * FROM Customers CONNECTION (Provider = 'OData', ServiceUrl = 'https://services.odata.org/V3/Northwind/Northwind.svc')", conn);
    using (var rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            for (int i = 0; i < rdr.FieldCount; i++)
                Console.Write("{0}={1}  ", rdr.GetName(i), rdr[i]);
            Console.WriteLine();
        }
    }
}

import pyodbc

# Use a DSN ("DSN=MyZappyDsn;") or name the driver directly
conn = pyodbc.connect("DRIVER={ZappySys API Driver};")
cursor = conn.cursor()
cursor.execute(r"""
SELECT * FROM Customers CONNECTION (Provider = 'OData', ServiceUrl = 'https://services.odata.org/V3/Northwind/Northwind.svc')
""")
for row in cursor.fetchall():
    print(row)
conn.close()

$conn = New-Object System.Data.Odbc.OdbcConnection
$conn.ConnectionString = "DRIVER={ZappySys API Driver};"   # or "DSN=MyZappyDsn"
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = @'
SELECT * FROM Customers CONNECTION (Provider = 'OData', ServiceUrl = 'https://services.odata.org/V3/Northwind/Northwind.svc')
'@
$table = New-Object System.Data.DataTable
(New-Object System.Data.Odbc.OdbcDataAdapter($cmd)).Fill($table) | Out-Null
$conn.Close()
$table | Format-Table

// Java uses the ZappySys Data Gateway and the Microsoft SQL Server JDBC driver.
// On the gateway, add a data source (here "MyZappySource") that uses the ZappySys API Driver.
import java.sql.*;

String url = "jdbc:sqlserver://localhost:5000;databaseName=MyZappySource;user=MyGatewayUser;password=MyPassword;encrypt=false";
String sql = "SELECT * FROM Customers CONNECTION (Provider = 'OData', ServiceUrl = 'https://services.odata.org/V3/Northwind/Northwind.svc')";
try (Connection conn = DriverManager.getConnection(url);
     Statement st = conn.createStatement();
     ResultSet rs = st.executeQuery(sql)) {
    while (rs.next()) {
        System.out.println(rs.getString(1));
    }
}

<?php
$conn = odbc_connect("DRIVER={ZappySys API Driver};", "", "");   // or "DSN=MyZappyDsn"
$sql = <<<'SQL'
SELECT * FROM Customers CONNECTION (Provider = 'OData', ServiceUrl = 'https://services.odata.org/V3/Northwind/Northwind.svc')
SQL;
$rs = odbc_exec($conn, $sql);
while ($row = odbc_fetch_array($rs)) {
    print_r($row);
}
odbc_close($conn);