ODBC Connectivity

brew install unixodbc

sudo apt-get install unixodbc unixodbc-dev

sudo yum install unixODBC unixODBC-devel

[Spice]
Driver = Arrow Flight SQL ODBC Driver
Host = localhost
Port = 50051
UseEncryption = 0
AuthenticationType = 1
UID = your-username
PWD = your-password

Driver={Arrow Flight SQL ODBC Driver};
Host=localhost;
Port=50051;
UseEncryption=0;
UID=your-username;
PWD=your-password;

UID=your-username
PWD=your-password

AuthenticationType=3
Token=your-api-key

AuthenticationType=0

pip install pyodbc

import pyodbc

# Connect using DSN
conn = pyodbc.connect('DSN=Spice')
cursor = conn.cursor()

# Execute query
cursor.execute('SELECT * FROM taxi_trips LIMIT 10')
for row in cursor.fetchall():
    print(row)

cursor.close()
conn.close()

import pyodbc

conn_str = (
    'Driver={Arrow Flight SQL ODBC Driver};'
    'Host=localhost;'
    'Port=50051;'
    'UID=username;'
    'PWD=password'
)

conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

cursor.execute('SELECT * FROM taxi_trips LIMIT 10')
for row in cursor.fetchall():
    print(row)

cursor.close()
conn.close()

import pyodbc

conn = pyodbc.connect('DSN=Spice')
cursor = conn.cursor()

# Parameterized query
query = 'SELECT * FROM taxi_trips WHERE passenger_count = ? LIMIT ?'
cursor.execute(query, (2, 10))

for row in cursor.fetchall():
    print(row)

cursor.close()
conn.close()

install.packages("odbc")
install.packages("DBI")

library(DBI)
library(odbc)

# Connect using DSN
con <- dbConnect(odbc::odbc(), "Spice")

# Execute query
result <- dbGetQuery(con, "SELECT * FROM taxi_trips LIMIT 10")
print(result)

# Parameterized query
result <- dbGetQuery(
  con,
  "SELECT * FROM taxi_trips WHERE passenger_count = ? LIMIT ?",
  params = list(2, 10)
)

dbDisconnect(con)

using System;
using System.Data.Odbc;

class Program
{
    static void Main()
    {
        string connStr = "DSN=Spice";
        using (OdbcConnection conn = new OdbcConnection(connStr))
        {
            conn.Open();

            string sql = "SELECT * FROM taxi_trips LIMIT 10";
            using (OdbcCommand cmd = new OdbcCommand(sql, conn))
            using (OdbcDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine(reader[0]);
                }
            }
        }
    }
}

string sql = "SELECT * FROM taxi_trips WHERE passenger_count = ? LIMIT ?";
using (OdbcCommand cmd = new OdbcCommand(sql, conn))
{
    cmd.Parameters.Add("@p1", OdbcType.Int).Value = 2;
    cmd.Parameters.Add("@p2", OdbcType.Int).Value = 10;

    using (OdbcDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine(reader[0]);
        }
    }
}

# Test connection
isql Spice username password

# If successful, you'll see SQL prompt
SQL> SELECT 1;

# List installed drivers
odbcinst -q -d

[ODBC]
Trace = Yes
TraceFile = /tmp/odbc.log

import pyodbc

conn = pyodbc.connect('DSN=Spice')
cursor = conn.cursor()

# Set array size for batch fetching
cursor.arraysize = 1000

cursor.execute('SELECT * FROM large_table')
while True:
    rows = cursor.fetchmany(1000)
    if not rows:
        break
    # Process rows

import pyodbc

# Enable connection pooling
pyodbc.pooling = True

# Connections are now pooled
conn = pyodbc.connect('DSN=Spice')