Julia ReadOnlyMemoryODBC SQL查询出错

Question

我正在编写一个查询SQL数据库的函数，遇到了一个ReadOnlyMemoryError((问题。问题是，当我将代码作为一个简单的脚本运行时，一切都会按预期运行。但当我试图在函数中包装完全相同的代码时，我会得到ReadOnlyMemoryError((。

这是我代码的脚本版本：

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

这正如预期的那样工作，结果是一个具有大约500k行的数据帧df。然而，当我尝试使用相同的代码来制作一个可重复使用的函数时，我会得到错误：

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

在这种情况下，当我尝试从REPL get_cf_data((调用时，我会得到ERROR:ReadOnlyMemoryError((。我对朱莉娅有些陌生，所以任何见解都将不胜感激。非常感谢。

Answer 1

如前所述，大多数编程语言在集成ODBC连接等API时的最佳实践是在使用后关闭并释放其资源。

此外，请考虑参数化(在任何运行传递文字值的SQL的语言中的最佳实践(，在该语言中设置准备好的SQL语句并在随后的execute调用中绑定值。

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