ODBC example for prepared statement doesn't work

General Usage
1

Hello,

this example in the ODBC documentation for how to execute a prepared statement doesn’t seem to work

# prepare a statement with 3 parameters marked by the '?' character
stmt = ODBC.prepare(dsn, "INSERT INTO cool_table VALUES(?, ?, ?)")

# a DataFrame with data we'd like to insert into a table
df = DataFrame(col1=[1,2,3], col2=[4.0, 5.0, 6.0], col3=["hey", "there", "sailor"])

for row = 1:size(df, 1)
    # each time we execute the `stmt`, we pass another row to be bound to the parameters
    ODBC.execute!(stmt, [df[row, x] for x = 1:size(df, 2)])
end

ERROR: ODBC.ODBCError("API.SQLExecute(stmt) failed; return code: -1 => SQL_ERROR")

Can someone check the syntax in the documentation?

http://juliadatabases.github.io/ODBC.jl/latest/

2

I should note, that inserting the values directly works OK, so it’s not the table definition or the values. It looks like the prepared statement doesn’t work as documented.

julia> for row in eachrow(df)
           println(row)
           col1 = row[:col1]
           col2 = row[:col2]
           col3 = row[:col3]
           ODBC.execute!(dsn, "INSERT INTO cool_table (col1, col2, col3) VALUES ($col1, $col2, '$col3')")
       end

DataFrameRow (row 1)
col1  1
col2  4.0
col3  hey

DataFrameRow (row 2)
col1  2
col2  5.0
col3  there

DataFrameRow (row 3)
col1  3
col2  6.0
col3  sailor

SQL> select * from cool_table;
  COL1   COL2 COL3     
     1      4 hey      
     2      5 there    
     3      6 sailor
3

bumping this question in case someone can take a look.

4

I have updated the example using Octo, to less labor on handwritten SQL queries.

https://github.com/wookay/Octo.jl/blob/master/test/adapters/odbc/dataframes_test.jl#L35

you could debug the generated SQL queries during the database execution.

~/.julia/dev/Octo/test $ julia --color=yes runtests.jl adapters/odbc/dataframes_test.jl
1/1 adapters/odbc/dataframes_test.jl
[ Info: DROP TABLE IF EXISTS cool_table
┌ Info: CREATE TABLE cool_table (
│     ID SERIAL,
│     col1 INTEGER,
│     col2 FLOAT(8),
│     col3 VARCHAR(255),
│     PRIMARY KEY (ID)
└ )
[ Info: INSERT INTO cool_table (col1, col2, col3) VALUES (?, ?, ?)   (col1 = 1, col2 = 4.0, col3 = "hey")
[ Info: INSERT INTO cool_table (col1, col2, col3) VALUES (?, ?, ?)   (col1 = 2, col2 = 5.0, col3 = "there")
[ Info: INSERT INTO cool_table (col1, col2, col3) VALUES (?, ?, ?)   (col1 = 3, col2 = 6.0, col3 = "sailor")
[ Info: SELECT * FROM cool_table
    Pass 2  (18.89 seconds)
✅  All 2 tests have been completed.  (19.21 seconds)

to see how it works in the backend, please refer to
src/Repo.jl#L342 and
src/Backends/ODBC.jl#L67.

thanks.

5

Sorry, I’m not clear on your response. Are you saying that the documented example in the ODBC online documentation is correct and working for you, or are you saying that it is wrong, and it will be changed?

I’m specifically asking about the prepared statement example shown in the ODBC documentation here :Home · ODBC.jl

Under the section that starts “ODBC.prepare”

# prepare a statement with 3 parameters marked by the '?' character
stmt = ODBC.prepare(dsn, "INSERT INTO cool_table VALUES(?, ?, ?)")

# a DataFrame with data we'd like to insert into a table
df = DataFrame(col1=[1,2,3], col2=[4.0, 5.0, 6.0], col3=["hey", "there", "sailor"])

for row = 1:size(df, 1)
    # each time we execute the `stmt`, we pass another row to be bound to the parameters
    ODBC.execute!(stmt, [df[row, x] for x = 1:size(df, 2)])
end
6

I think that you are missing an important part from the docs.
http://juliadatabases.github.io/ODBC.jl/latest/#ODBC.prepare-1

…
Consult your DB/vendor-specific SQL syntax for the exact specifications for parameters.
Examples:
… code …

so SQL syntax for the prepared statement is varying by the context of the DB that you use.
I commented about the another package to solve this problem a bit.

7

This SQL syntax is correct as I mentioned previously, if I run it manually, it is successful.

"INSERT INTO cool_table VALUES(?, ?, ?)"

It’s the last execution of the prepared statement that isn’t working. The following part shouldn’t be from the DB vendor. This is pure Julia syntax.

for row = 1:size(df, 1)
    # each time we execute the `stmt`, we pass another row to be bound to the parameters
    ODBC.execute!(stmt, [df[row, x] for x = 1:size(df, 2)])
end
1 Like
8

I get the same issue. Could it be because I’m on 1.0.2 Julia and ODBC is 0.8.1

I’m also struggling to find a way to use this to connect to servers without being forced into using a system DSN (windows 10)

For example we are using FQD name to find out SQL boxes such as abc2012-sql.company.com,1234 this way we can move our servers and all our applications keep on working as we update the DNS on our network. I’d ideally need to have this directly in my Julia code. When I tried using the System DSN, julia ignores the supplied user+password because it gets it from the DSN instead which is too limiting (DSN is setup to read only for a reason on our machines across our network)