Refer to your IT Support if you need this step.

ODBC AND PYTHON CONNECTION STRINGS

Below are sample ways to create SQL code in SQL Files for various DBMS types

1.  ODBC connection and SQL to read an MS ACCESS database:

/* Report for Sample ODBC */
/* Driver:
$type=ODBC,
$conn=
Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\temp\d4e\FinancialData.accdb;
Persist Security Info=False;
=$conn,
*/
SELECT * FROM Budgets

EXPLANATION: In the DRIVER SECTION, $type=ODBC indicates that this is an ODBC connection, and the connection string is indicated between "$conn=" and "=conn,"

2. Python method to read a CSV file:

/* GL Bals SYD CY */
/* Driver: 
$type=Python,
$dbms=CSV,
$file_path=<<#td#>>gl_balances_data.csv,
$results=keep,
$showresults=yes,
*/
SELECT 
Company as Company_key,
Branch as Branch_key,
Account as Branch_key,
Premium_Class,
RI_CC,
Source_Employee,
UW_Year,
Intercompany,
Spare1,
Spare2,
Currency,
DR,
CR,
Net_Amt,
Year as Year_key,
Period as Period_key,
Account_Description
FROM data WHERE Year = 2025

EXPLANATION: Because of $results=keep and $showresults=yes, D4E shows the results in Excel as well as write the results in the TEMP folder (C:\TEMP\D4E\). So this result will be found in file <SQL_sheetname>__<column_char of SQL code)>.csv  (e.g.,SALES__F.csv)

3. Python method of reading results files or previous extracts using D4E:  In the examples below, the 3 queries are in column A, B and C in sheet SALES.  

An important use case of this is that you can effectively JOIN 2 entirely different DBMS and process them as one.  Examples:

a.  SQL A - Get PRODUCT DATA from Oracle, SQL B - Get Account Descriptions from SAP GL, SQL C - Join A and B for reporting.

b.  SQL A - Get daily TRANSACTIONS from PRODUCT DB (Oracle), SQL B - Get similar TRANSACTIONS from Data Ware House (Google Big Query), SQL C - Reconcile A and B transactions to identify timing differences and/or errors.

Sheet SALES Column A:
/* Ver F  Python-CSV*/
/* Driver:
$type=Python,
$dbms=CSV,
$file_path=C:\temp\sample_data.csv,
$results=keep,
$showresults=yes,

*/

SELECT * FROM data WHERE Age > 30

EXPLANATION: With $results=keep, the output will be written to the temp foider as SALES__A.csv

Sheet SALES Column B:

/* Ver G  Python-CSV*/
/* Driver:
$type=Python,
$dbms=CSV,
$file_path=C:\temp\sample_data.csv,
$results=keep,
$showresults=yes,
*/

SELECT * FROM data WHERE Age > 30

EXPLANATION: With $results=keep, the output will be written to the temp foider as SALES__B.csv

Sheet SALES Column C:
/* Ver K  Python-CSV*/
/* Driver:
$type=Python,
$dbms=Results,
$filefolder=c:\temp\d4e,
$results=keep,
$showresults=yes,
$filetables=Sales__A;Sales__B,
*/
SELECT * FROM Sales__A
UNION ALL
SELECT * FROM  Sales__B

EXPLANATION: In the DRIVER SECTION, $fileholder and $filetables, refer to the location and names of previously created results in D4E.  By default D4E write output results to the C:\Temp\D4E\ folder and names the files as <SQL sheet>__<column char>.CSV

4. Python extract for a Google BigQuery database:

/* Ver E  Python-BigQuery */
/* Driver: 
$type=Python,
$dbms=BigQuery,
*/
SELECT * FROM `bigquery-public-data.samples.shakespeare` LIMIT 10

EXPLANATION:  There is no connection string as BigQuery may automatically check your Windows credential and so only need to give correct SQL statement.  Please check with your local support.

5. Python extract for a Oracle database:

/* Sample Oracle DB */
/* Driver: 
$type=Python,
$dbms=Oracle,

$your_host=<yourhost>,

$your_port=<yourport>,

$your_service_name=<servicename>,

$your_user=<userid>,

$your_password=<password>,
*/
SELECT * FROM TABLE LIMIT 10

EXPLANATION: Based on the inputs above, D4E will construct and submit a Python script like:
           import cx_Oracle
           dsn = cx_Oracle.makedsn('<your_host>', <your_port>, service_name='<your_service_name>')
           conn = cx_Oracle.connect(user='<your_user>', password='<your_password>', dsn=dsn)

6. ODBC extract for MySQL database:

/* Sample MySQL ODBC public.opendatasoft.com */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={MySQL ODBC 8.0 Unicode Driver};

Server=public.opendatasoft.com;Port=3306;Database=dataset_name;User=guest;Password=guest;
=$conn,
*/
SELECT * FROM cities LIMIT 10;

EXPLANATION: With ODBC, the connection string already defines the type of DBMS, so only need ot give a correct connection string in $conn

7. ODBC Extract for IBM DB2 Database:

/* Sample IBM DB2 */
/* Driver:
$type=ODBC,
$conn=
Driver={IBM DB2 ODBC DRIVER};
Database=mydatabase;
Hostname=mydbhost;
Port=50000;
Protocol=TCPIP;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM MYTABLE

8. ODBC Extract for PostgreSQL Database:  

/* Sample PostgreSQL ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={PostgreSQL ODBC Driver(UNICODE)};
Server=mydbhost;
Port=5432;
Database=mydbname;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM employees LIMIT 10;

9. ODBC Extract for Microsoft SQL Server Database:

/* Sample SQL Server ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={ODBC Driver 17 for SQL Server};
Server=mydbhost;
Database=mydbname;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM employees WHERE department = 'HR';

10. ODBC Extract for SQLite Database:

/* Sample SQLite ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={SQLite3 ODBC Driver};
Database=C:\path\to\your\database.db;
=$conn,
*/
SELECT * FROM customers LIMIT 10;

11. ODBC Extract for Apache Hive Database:

/* Sample Apache Hive ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={Cloudera ODBC Driver for Apache Hive};
Host=mydbhost;
Port=10000;
Schema=default;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM sales_data LIMIT 10;

12. ODBC Extract for SAP HANA Database:

/* Sample SAP HANA ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={HDBODBC};
ServerNode=mydbhost:30015;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM sales LIMIT 10;

13. ODBC Extract for Amazon Redshift Database:

/* Sample Amazon Redshift ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={Amazon Redshift ODBC Driver};
Server=mydbhost;
Port=5439;
Database=mydbname;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM users LIMIT 10;

14. ODBC Extract for Google BigQuery Database

/* Sample Google BigQuery ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={Simba ODBC Driver for Google BigQuery};
OAuthMechanism=0;
ProjectID=myprojectid;
Dataset=mydataset;
PrivateKeyFile=C:\path\to\your\privatekeyfile.json;
=$conn,
*/
SELECT * FROM `mydataset.mytable` LIMIT 10;

Explanation:
The Simba ODBC Driver for Google BigQuery is used to connect to BigQuery.
The OAuthMechanism=0 parameter enables authentication using OAuth 2.0.
ProjectID and Dataset refer to your Google Cloud Project and BigQuery dataset, respectively.
PrivateKeyFile specifies the location of the service account’s private key file (a JSON file from Google Cloud).

15. ODBC Extract for MongoDB (via MongoDB ODBC Connector)

/* Sample MongoDB ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={MongoDB ODBC 1.4.2 Driver};
Server=mydbhost;
Port=27017;
Database=mydbname;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM customers LIMIT 10;

Explanation:
The MongoDB ODBC 1.4.2 Driver is used to connect to MongoDB.
Server specifies the host of the MongoDB instance, and Port is the default (27017).
MongoDB does not traditionally follow the relational model, so SQL queries can only be performed on collections in a format that the ODBC connector supports.

16. ODBC Extract for Snowflake Database

/* Sample Snowflake ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={Snowflake ODBC Driver};
Server=myaccount.snowflakecomputing.com;
Warehouse=mywarehouse;
Database=mydbname;
Schema=myschema;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM orders LIMIT 10;

17. ODBC Extract for Couchbase Database

/* Sample Couchbase ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={Couchbase ODBC Driver};
Host=mydbhost;
Bucket=mybucket;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM `mybucket` LIMIT 10;

Explanation:
The Couchbase ODBC Driver is used for connecting to Couchbase.
Host refers to the Couchbase server host, Bucket specifies the bucket (a container for data in Couchbase), and authentication details are provided via Uid and Pwd.

18. ODBC Extract for Cassandra Database (via Datastax ODBC Connector)

/* Sample Cassandra ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={DataStax ODBC Driver for Apache Cassandra};
Host=mydbhost;
Port=9042;
Keyspace=mykeyspace;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM customer_data LIMIT 10;

Explanation:
The DataStax ODBC Driver for Apache Cassandra is used to connect to Apache Cassandra.
Host and Port specify the Cassandra instance and port (9042 is the default).
Keyspace is the equivalent of a database in Cassandra, and the Uid and Pwd provide authentication credentials.

19. ODBC Extract for MariaDB Database

/* Sample MariaDB ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={MariaDB ODBC 3.1 Driver};
Server=mydbhost;
Port=3306;
Database=mydbname;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM employees LIMIT 10;

Explanation:
The MariaDB ODBC 3.1 Driver is used to connect to MariaDB, a MySQL-compatible database.
The connection string includes Server, Port (3306 is the default for MariaDB/MySQL), Database, and user authentication (Uid and Pwd).

20. ODBC Extract for Teradata Database

/* Sample Teradata ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={Teradata};
DBCNAME=mydbhost;
UID=myuser;
PWD=mypassword;
=$conn,
*/
SELECT * FROM customers LIMIT 10;

Explanation:
Driver: Specifies the ODBC driver for Teradata (Teradata).
DBCNAME: The host where the Teradata instance is running (replace mydbhost with your actual host or IP).
UID and PWD: Username and password for authentication.

SOME DBMS SHARE THE SAME ODBC DRIVER

Some DBMS can share the same ODBC Drivers because they are from the same vendor (MIcrosoft) or from the same open-source code base (PostGRE).  Below are examples:

1. Amazon Aurora MySQL (MySQL ODBC Driver 8.0 Unicode)

/* Sample Amazon Aurora MySQL ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={MySQL ODBC 8.0 Unicode Driver};
Server=mydbhost;
Port=3306;
Database=mydbname;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM customers LIMIT 10;

2. MySQL (MySQL ODBC Driver 8.0 Unicode)

/* Sample MySQL ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={MySQL ODBC 8.0 Unicode Driver};
Server=mydbhost;
Port=3306;
Database=mydbname;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM employees LIMIT 10;

3. MariaDB (MySQL ODBC Driver 8.0 Unicode)

/* Sample MariaDB ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={MySQL ODBC 8.0 Unicode Driver};
Server=mydbhost;
Port=3306;
Database=mydbname;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM products LIMIT 10;


4. Greenplum (PostgreSQL ODBC Driver 8.4 or later)

/* Sample Greenplum ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={PostgreSQL ODBC Driver(UNICODE)};
Server=mydbhost;
Port=5432;
Database=mydbname;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM sales LIMIT 10;


5. CockroachDB (PostgreSQL ODBC Driver 8.4 or later)

/* Sample CockroachDB ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={PostgreSQL ODBC Driver(UNICODE)};
Server=mydbhost;
Port=26257;
Database=mydbname;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM users LIMIT 10;


6. Azure SQL Database (ODBC Driver 17 for SQL Server)

/* Sample Azure SQL Database ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={ODBC Driver 17 for SQL Server};
Server=myserver.database.windows.net;
Database=mydbname;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM orders LIMIT 10;

7. SQL Server (ODBC Driver 17 for SQL Server)


/* Sample SQL Server ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={ODBC Driver 17 for SQL Server};
Server=mydbhost;
Database=mydbname;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM employees LIMIT 10;

8. Apache Hive (Cloudera ODBC Driver for Apache Hive)

/* Sample Apache Hive ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={Cloudera ODBC Driver for Apache Hive};
Host=mydbhost;
Port=10000;
Schema=default;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM sales_data LIMIT 10;

9. PrestoDB (Trino) (Cloudera ODBC Driver for Apache Hive)

/* Sample PrestoDB (Trino) ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={Cloudera ODBC Driver for Apache Hive};
Host=mydbhost;
Port=8080;
Catalog=default;
Schema=public;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM sales LIMIT 10;


10. TimescaleDB (PostgreSQL ODBC Driver 8.4 or later)

/* Sample TimescaleDB ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={PostgreSQL ODBC Driver(UNICODE)};
Server=mydbhost;
Port=5432;
Database=mydbname;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM hypertable LIMIT 10;


11. Citus (PostgreSQL ODBC Driver 8.4 or later)

/* Sample Citus ODBC Connection */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={PostgreSQL ODBC Driver(UNICODE)};
Server=mydbhost;
Port=5432;
Database=mydbname;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM my_table LIMIT 10;

HOW TO CREATE APPS THAT EXTRACT FROM DBMS

DATA MART FOR EXCEL (D4E SQL)

The SQL Developer creates SQL code and stores them in spreadsheets, by allocating one column for one SQL code. The column has 3 sections:
1. TITLE SECTION - consist of a title in row 1 enclosed as comments, e.g., /* Sales Report */ in row 1
2. DRIVER SECTION - consist of multi-line comments enclosed by one row equal to "/* DRIVER:" at the beginning of the DRIVER SECTION and another row for "*/" as the end of the DRIVER SECTION
3. CODE SECTION - consist of a valid and tested SQL code

The sheet name for SQL codes, can be any name (e.g., SALES, CUSTOMER, GL) so long as it does not begin with menu*, docs*, Sheet*, and Macro* which are reserved for other components in D4E.

General Format Example:
/* SQL SAMPLE REPORT NAME */
/* Driver:
connections strings
*/
SELECT * FROM TABLE

The SQL developer can test the whole column by cutting and pasting to the DBMS IDE (Oracle PL/SQL, or Google BigQuery IDE, etc).  Once tested ok, SQL developer can collaborate with business user who will execute the SQL within Excel as a self-service tool.

Depending on the DBMS or SQL source type, the DRIVER SECTION will configured correspondingly.  Within the DRIVER SECTION, you can have optional variables (with format $variable=value,  or $variable=value=$variable,) to be used depending on the DBMS:
$type=value,     (where value can be ODBC, Python, Excel, CSV  default is ODBC)
$dbms=value,      (where value can be BigQuery, PostgreSQL,MySQL, SQLite, SQLServer, Oracle)
$file_path=value,     (paths and filenames are used in reading output results)
$filetables=table1;table2,
$results=keep,      (write output results to tempdir C:\temp\d4e\)
$showresults=yes,     (display results in Excel, default if no tempdir outputs)
$conn=value=$conn,
$your_host=value,     ("your" variables are used in Python DBMSs)
$your_port=value,
$your_service_name=value,
$your_user=value,
$your_password=value,

Note that ODBC drivers must be installed via the Windows ODBC Control Panel before they can be used in Excel and D4E. Check the ODBC Administrator screen, as shown below, to verify that your specific ODBC driver is installed.