CODEWITHSUNDEEP
odbcjulia
DaKlingons
DaKlingons

Reputation: 101

Julia ODBC Invalid Cursor State

Sorry a newbie question. I am using ODBC.jl, to try and automate some current SQL queries. I am getting an "[ODBC] 24000: [Microsoft][ODBC SQL Server Driver]Invalid cursor state" response.

/******************************************************/

Establish the Libraries we will use

using ODBC
using DataFrames
using DataStreams

Create Connection to the server

Note an ODBC connection needs to be created first for this to work

db = ODBC.DSN("XX.X.XXXX.2")

Establish the holding Database on the server if not already present

This will error but will work if not present.

TEST = ODBC.query(db, "SET NOCOUNT ON; if db_id('ReportingTables_TEMP') is null begin create database Rate_Tables_TEMP end")

/*************************************************/

When I run I get " ODBC.ODBCError("API.SQLFetchScroll(q.stmt, API.SQL_FETCH_NEXT, 0) failed; return code: -1 => SQL_ERROR") " and then I check the REPL ad see the Invalid Cursor state response. What I'd like to do is make my query code more location indifferent by checking and establishing the repository for the data first.

Welcome thoughts as online hasn't been useful at this point (table size isn't an issue, and no triggers are present as this is very new SQL set up).

Thanks

Upvotes: 0

Views: 472

Answers (1)

DaKlingons
DaKlingons

Reputation: 101

I've managed to solve this question, by changing the statement from query to execute!.

ODBC.execute!(db, "if db_id('ReportingTables') is null begin create database ReportingTables print 'ReportingTables created' end else if db_id('ReportingTables') is not null print 'ReportingTables exist'")

As the ODBC.jl documentation outlines, execute! is where we do not expect a response. In my mind what was being run was generating a response (creating a database in SQL), but I'm surmising that its a response into Julia.

This minor change is opening up the ability for me push & pull data from different SQL Servers & databases.

This could be of great benefit to me as I have 10-15 different tables that I currently have an SQL process to download, join, manipulate.

From my experiments Julia is marginally faster for copying data from 1 server/database into another. It seems faster at joins compared to SQL. I am yet to experiment on the manipulation side, but am hopeful Julia to be faster here as well.

Hope this is useful for others.

Upvotes: 2

Related Questions