How to insert rows with NULL values using prepared statements?

[amellnik]

I can't figure this out for the life of me, and as far as I can tell it doesn't occur in the tests. Say for example that I have a table called "test" with two columns, "nums" and "letters". I can prepare a statement like

nums = collect(1:3)
letters = @data ["A", NA, "C"]
test = DataFrame(nums=nums, letters=letters)
statement = "REPLACE INTO test (nums,letters) values (?,?);"
sqlTypes = UInt32[MYSQL_TYPE_LONG,MYSQL_TYPE_VARCHAR]
mysql_stmt_prepare(conn,  statement)

but then I can't execute on each row like shown on the docs -- I've tried setting the NA item in the tuple to DataFrame's NA, nothing and Nullable{UTF8String}() with things like

for r in eachrow(test)
    try
        mysql_execute(conn, sqlTypes, tuple([isna(r[c]) ? nothing : r[c] for c in names(r)]...))
    catch err
        println(tuple([r[c] for c in names(r)]...), " - ", err, " - ")
    end
end

and

values = [(1, "A"), (2, Nullable{UTF8String}())]
for val in values
    mysql_execute(conn, sqlTypes, val)
end

but none of these work. What's the recommended way of doing this?

[amellnik]

After more digging I realized I needed to specify the type as MYSQL_TYPE_NULL and also pass the value as a AbstractString "NULL":

for r in eachrow(test)
    try
        mysql_execute(conn, [isna(r[c]) ? MYSQL_TYPE_NULL : t for (t, c) in zip(sqlTypes, names(r))],
            tuple([isna(r[c]) ? "NULL" : r[c] for c in names(r)]...))
    catch err
        println(tuple([r[c] for c in names(r)]...), " - ", err, " - ")
    end
end

I think I can spare others a similar pain in the neck by adding a check for nothings, nullables and NAs in mysql_bind_array. I'll leave this open pending that pull request.