Help in Bulk Insert with ODBC connection

New to Julia
,
1

Hi guys! Me again :grin:

Now i need help to make an Insert Statement, i’m trying with:

insert_query = "INSERT INTO resumoValores (Data, Unidade, Total_Fechamento_SIDG) 
    values(?,?,?)"

stmt = DBInterface.prepare(cnn, insert_query)

DBInterface.executemany(stmt, [df_pronto.DATA, df_pronto.UNIDADE,  df_pronto.Total_Fechamento])

DBInterface.close!(stmt)

---

MethodError: no method matching getconnection(::ODBC.Statement)

Stacktrace:
  [1] executemany(stmt::ODBC.Statement, params::Vector{AbstractVector})

I dont know why i’m having this error, i’m connected in a SqlServer using ODBC.

connection_string = "DRIVER="*driver*";PORT=port;SERVER="*server*";PORT=1443;DATABASE="*database*";UID="*username*
                  ";PWD="*password

cnn = ODBC.Connection(connection_string)

Thanks for all help, and sorry about a lot of questions haha.

3

Your connection string is wrong, it has two PORTs in it and one is the string “port”

also, use interpolation rather than multiple concatenation e.g.

connection_string = "DRIVER=$driver;SERVER=$server;PORT=1443;DATABASE=$database;UID=$username;PWD=$password"

Not only is it easier to read (re-writing it made me spot the port thing) but concatenation creates multiple strings and then joins them, rather than a single string.

If that is the source of the problem, then I suspect DBInterface is giving you the wrong error message, which is worth looking into, I shall do that when I get to work.

You can also test for the existence of the function yourself, and see the result

DBInterface.getconnection(stmt)
5

OK, it’s not the connection string.

calling the function by itself

julia> DBInterface.getconnection(stmt)
ERROR: MethodError: no method matching getconnection(::ODBC.Statement)
Stacktrace:
 [1] top-level scope
   @ REPL[22]:

and when I went to report it …

https://github.com/JuliaDatabases/DBInterface.jl/issues/43

as a workaround, you can define this yourself

DBInterface.getconnection(stmt::ODBC.Statement) = stmt.dsn

although in my case it turned into another problem

julia> DBInterface.executemany(stmt, [["a", 1], ["b", 2]])
ERROR: 22018: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Conversion failed when converting the varchar value 'b' to data type int.
Stacktrace:

and if I changed it to

julia> DBInterface.executemany(stmt, [["a", 1], [1, 2]])

The insert completed with 1 inserted as a string into the first column

but that’s something else to find

I created a PR to fix it in ODBC

https://github.com/JuliaDatabases/ODBC.jl/pull/336

6

Thx @lawless-m for the help!

I changed my code and did the bulk insert with a for loop:

insert_query = "INSERT INTO resumoValores (Data, Unidade, Total_Fechamento_SIDG) 
    values(?,?,?)"

stmt = DBInterface.prepare(cnn, insert_query)

@simd for i in eachrow(df_pronto)
     DBInterface.execute(stmt, (i.DATA, i.UNIDADE,  i.Total_Fechamento))
end
DBInterface.close!(stmt)

But now i have another problem, look the names in the VsCode print:

unique(filter(x -> x.UNIDADE =="TERMINAL JARDIM ÂNGELA" || x.UNIDADE == "TERMINAL SACOMÃ" || x.UNIDADE =="TERMINAL GRAJAÚ" , df_pronto),:UNIDADE)

	     DATA	      UNIDADE	         total_fechamento_SPTRANS	Total_Fechamento
Date	String	Float64	Float64
1	2021-02-01	TERMINAL GRAJAÚ	                  38728.6	           38728.6
2	2021-02-01	TERMINAL JARDIM ÂNGELA	          33710.7	           33710.7
3	2021-02-01	TERMINAL SACOMÃ	                  12543.1	           11066.2

and now look them in the SQLServer Database

Unidade
TERMINAL SACOMÃ
TERMINAL JARDIM ÂNGELA
TERMINAL GRAJAÚ - RECEBEDORIA
TERMINAL SACOMÃ - RECEBEDORIA
TERMINAL GRAJAÚ

I used the for loop to insert but the names are strange, why this occurs?

Thx for Help!

7

I guess you need to set the “;String Types=Unicode” in the Connection String

https://www.devart.com/odbc/sqlserver/docs/connection_options.htm

(I have not tried this, it might be incorrect advice)