nimforum mirror - New high level ODBC library for SQL Server

coffeepot (orginal) [2015-09-16T13:05:43+02:00] view original

Hello!

I've written a library for accessing SQL Server in Nim. It should work with other ODBC providers, but I haven't tested this (any suggestions to work with other providers welcome).

The repo is here: https://github.com/coffeepots/odbc

The lib supports:

Queries

Named parameters
- Typed parameters rather than string replaces in the query text
- Includes multiple use of the same parameter name without having to make a new parameter with the same value

Transactions

'Nesting' queries (you can open a query then open others whilst the first one is still open on the same connection)

Output results to JSON

Native data types for results rather than just using strings
- Supports byte sequences for BLObs, time, int, int64, float and bool

Unicode

Reporting errors or info messages to multiple destinations
- Reporting to: file, echo, store as seq[string] and pass to a callback proc

I have not used this in anger yet so consider this in alpha, however it should work as expected.

Any feedback is welcome! :)

Example use:

qry.statement = "SELECT 'A' AS A, ?myparam AS B, 0.8 AS C"
qry.params["myparam"] = 5

var results = qry.executeFetch                      # fetch all results
echo results.len, " total row(s)"                   # outputs "1 total row(s)
echo "First field's data is ", results[0][0].data   # outputs "First field's data is A"

# template for processing each result row. Performs open/close for you
qry.withExecute(row):
  for item in row:
    if item.field.fieldname == "A": echo "Field A is found!"
    else: echo item.field.fieldname, " is found, and of type ", item.field.dataType
    echo "Item data: ", item.data

for row in results:
  echo row.len, " fields in this row"               # outputs "3 fields in this row"

Further examples of use can be found in the readme at the repo home page and in the testing module here https://github.com/coffeepots/odbc/blob/master/private/tryodbc.nim

doubank (orginal) [2015-09-24T18:52:22+02:00] view original

The library doesn't work with Nim 0.11.2, right?

After updating Nim to the devel version, got the following error when trying to compile tryodbc.nim:

lib/wrappers/odbcsql.nim(683, 64) Error: undeclared identifier: 'WideCString'

The problem isn't specific to the library. What am I missing?

coffeepot (orginal) [2015-09-25T10:47:39+02:00] view original

Hi doubank. Yes this lib needs the devel version of Nim as there are a couple of inclusions to odbcsql.nim that it requires.

However, the error you're getting is odd because WideCString should be part of Nim's system. You should be able to do this from an empty Nim module without any imports:

var a: WideCString
a = newWideCString("Hello")
echo a

When you say the problem isn't specific to the library, is this issue happening all the time? As in, you can't access WideCString at all?

Araq (orginal) [2015-09-25T11:54:03+02:00] view original

WideCString is only exposed on Windows. I guess it's useful in general... (PRs welcome!)

coffeepot (orginal) [2015-09-25T12:24:47+02:00] view original

Hmm that explains it.

I've only tested this on Windows so there might be some changes needed to support Linux/Mac. Unfortunately I don't have an alternate Linux/Mac setup to develop on at the moment.

michael (orginal) [2017-10-02T07:01:49+02:00] view original

Thanks coffeepot for providing this library.

I'm having some problem when trying the following with nim 0.17.2

import odbc-master/odbc.nim

var
    con = newODBCConnection()
    con.driver = "ODBC Driver 11 for SQL Server"
    con.host = "myHost"
    con.database = "myDB"

if not con.connect:
    echo "Could not connect to database."
else:
    echo "Connection ok"

I get the following error:

test.nim(5, 8) Error: implicit object field construction requires a .partial object, but got ODBCConnection:ObjectType

I'm a newcomer to nim, so this is not familiar to me - any tips appreciated.

I know the driver string is ok, since I can use this with success with python (pyodbc).

Thanks.

cdome (orginal) [2017-10-02T14:19:31+02:00] view original

Wonderful news,

Thanks coffeepot, I'll definately going to give a try once I get back to work. I do have MSSQL as data provider in some of my projects. I quickly glaced the API, I generally tend to have a lot of SQL queries in const strings that are ready to be executed and they don't have extra parameters, I would benefit from a simple helper than can create, execute and handle result of statement in one line. Something like:

con.withStatement(sql_query_string, row):
    handle a row

And even like this

con.withStatement(sql_query_string, args, row):
    handle a row

LeuGim (orginal) [2017-10-02T15:03:38+02:00] view original

@michael, are those indentations before con.something needed? If those are just value assignments to fields of an object, you should do that outside of a variable definition block.

coffeepot (orginal) [2017-10-02T15:31:40+02:00] view original

Hi guys, I've actually restructured this library so it fits the nimble folder format, but haven't reuploaded it to github yet. There are a few minor changes that come along with this. For instance in results, now fields are attached to the result set rather than each row.

I've tested this up to Nim 0.17.1. I'll download the latest version, check for any problems then upload this to github.

@micheal, I think your problem is related to trying to set fields inside a var statement, so:

var
    con = newODBCConnection()
    con.driver = "ODBC Driver 11 for SQL Server"
    con.host = "myHost"
    con.database = "myDB"

This is trying to declare a variables called "con.driver" and "con.host" which isn't possible.

Should be:

var
    con = newODBCConnection()
con.driver = "ODBC Driver 11 for SQL Server"
con.host = "myHost"
con.database = "myDB"

michael (orginal) [2017-10-03T11:35:17+02:00] view original

@LeuGim, @coffeepot: Thanks so much for your guidance. I'm an idiot - you were right the indentation was the issue.

@coffeepot: thanks so much for a really useful library.

Mirror of forum.nim-lang.org

1640 :: New high level ODBC library for SQL Server