Julia ReadOnlyMemoryError with ODBC SQL query

Question

I'm working on writing a function that queries a SQL database and I'm encountering a ReadOnlyMemoryError() that has me stumped. The issue is that when I run my code as a simple script everything functions as expected. But when I try to wrap the exact same code in a function I get the ReadOnlyMemoryError().

Here's the script version of my code:

using ODBC
using DBInterface
using Dates
using DataFrames

server = "server string "
username = "username "
password = " password"
db = " db name"

start_date=Nothing
end_date=Nothing

if start_date == Nothing || typeof(start_date) != "Date"
    start_date = Dates.today() - Dates.Day(30)
end

if end_date == Nothing || typeof(end_date) != "Date"
    end_date = Dates.today()
end

query = """ SQL SELECT statement """

connect_string = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=" * server *
                ";DATABASE=" * db *
                ";UID=" * username *
                ";PWD=" * password
conn = ODBC.Connection(connect_string)

df = DBInterface.execute(conn, query) |> DataFrame

This works as expected, the result is a dataframe df with about 500k rows. However, when I try use this same code to make a reusable function I get the error:

using ODBC
using DBInterface
using Dates
using DataFrames

function get_cf_data(start_date=Nothing, end_date=Nothing)
    server = " server string "
    username = " user name"
    password = " password"
    db = " db "

    if start_date == Nothing || typeof(start_date) != "Date"
        start_date = Dates.today() - Dates.Day(30)
    end

    if end_date == Nothing || typeof(end_date) != "Date"
        end_date = Dates.today()
    end

    query = """  SQL SELECT statement """

    connect_string = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=" * server *
                    ";DATABASE=" * db *
                    ";UID=" * username *
                    ";PWD=" * password
    conn = ODBC.Connection(connect_string)

    df = DBInterface.execute(conn, query) |> DataFrame

    return df
end

In this case, when I try to call from REPL get_cf_data() I get ERROR: ReadOnlyMemoryError(). I'm somewhat new to Julia, so any insight would be very much appreciated. Thank you!

user_1422451 · Answer

As commented, best practice in most programming languages when integrating APIs like ODBC connections is to close and release its resource after usage.

Additionally, consider parameterization (best practice in any language running SQL that passes literal values) where you set up a prepared SQL statement and bind values in a subsequent execute call.

function get_cf_data(start_date=Nothing, end_date=Nothing)
    server = " server string "
    username = " user name"
    password = " password"
    db = " db "

    if isnothing(start_date) || typeof(start_date) != "Date"
        start_date = Dates.today() - Dates.Day(30)
    end

    if isnothing(end_date) || typeof(end_date) != "Date"
        end_date = Dates.today()
    end

    # PREPARED STATEMENT WITH QMARK PLACEHOLDERS
    sql = """SELECT Col1, Col2, Col3, ...
             FROM myTable
             WHERE myDate BETWEEN ? AND ?
          """

    connect_string = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=" * server *
                    ";DATABASE=" * db *
                    ";UID=" * username *
                    ";PWD=" * password
    conn = ODBC.Connection(connect_string)

    # PREPARE STATEMENT AND BIND PARAMS
    stmt = DBInterface.prepare(conn, sql)
    df = DBInterface.execute(stmt, (start_date, end_date)) |> DataFrame

    DBInterface.close(stmt)                   # CLOSE STATEMENT
    DBInterface.close(conn)                   # CLOSE CONNECTION
    stmt = Nothing; conn = Nothing            # UNINTIALIZE OBJECTS

    return df
end

Julia ReadOnlyMemoryError with ODBC SQL query

1 Answers