[exp/sql] usability questions

[Mark Severson]

Short back history:

I've been using Go for just a short while now and already it has
displaced Python as my go-to language for writing something quickly. I
absolutely love working in Go!

A few months ago I decided to start trying to write some simple
utilities to help us manage our customers' databases. As all of our
database connections are done using ODBC, a quick search resulted in
two:

* godbc - http://github.com/BenoyRNair/godbc/
* go-odbc - https://github.com/weigj/go-odbc

The first has all but been abandoned and the second didn't compile (at
the time) in the latest release of Go. After some tweaks I finally did
get it to compile, but quickly learned that it has some rather leaky
abstractions. The underlying C types are quite often exposed and I
caused several panics while trying to bind parameters.

Shortly after these frustrations, I discovered exp/sql and decided to
create an ODBC driver for it. After some holiday distractions, I sat
down for a few latish nights and came up with mgodbc (https://
bitbucket.org/miquella/mgodbc/). I'm still missing the time
integration and I might need to change a thing or two about the
implementation (I've only tested it on 32-bit Windows...), but it's
been much easier to work with than the others I've tried (granted,
this may just be because I wrote it!).

----

This brings me to my questions:

1. While writing the driver, I noted that the documentation states
that strings cannot be returned by the driver.Rows.Next() function.
Why is that?

2. Why is it not possible to use **int or **string to receive nullable
fields in sql.Rows.Next()?
For example:
rows, err := db.Query("SELECT t.id, rel.id, rel.name FROM t LEFT
OUTER JOIN rel ...")
...
for rows.Next() {
var id int
var relID *int
var relName string
err = rows.Scan(&id, &relID, &relName)
}

3. Has there been any consideration to having an interface that can be
implemented to deserialize a row directly into an object rather than
having to query all of the fields through sql.Rows.Next() and then
populate the object?

I apologize for the lengthy post, these questions have been swimming
around my head for several days... But thank you for any answers you
may have!

[ziutek]

> 1. While writing the driver, I noted that the documentation states
> that strings cannot be returned by the driver.Rows.Next() function.
> Why is that?

It is that to avoid unnecessary conversion from []byte to string.
During i/o you always receive/send []byte not string.

[frederickm]

Thanks for posting this, I was looking for an SQL Server driver about a week ago. Works great so far.

Are you planning on adding support for time or should I look into it?

[numenor]

Hi Mark,

I'm afraid I am a bit of a newbie looking at go, and was looking at a
project to write, unfortunately I need to connect to a ms sql 2005 db
via odbc.

Found your post, which sounded well thought out, so I downloaded it.
Any chance of posting an example on how to use or including one in
your repository, I spent some time sunday, but unfortunately too daft
to work it out.

Hopefully Lee

On Jan 19, 10:52 am, Mark Severson <mique...@gmail.com> wrote:
> Short back history:
>
> I've been using Go for just a short while now and already it has
> displaced Python as my go-to language for writing something quickly. I
> absolutely love working in Go!
>
> A few months ago I decided to start trying to write some simple
> utilities to help us manage our customers' databases. As all of our
> database connections are done using ODBC, a quick search resulted in
> two:
>
>  * godbc -http://github.com/BenoyRNair/godbc/

>  * go-odbc -https://github.com/weigj/go-odbc

[Mark Severson]

Gotcha.

I think it just stood out to me more because I'm using Microsoft
unicode APIs that return everything in UTF16, so I was having to do
conversion anyway, but that makes sense.

[Mark Severson]

Yes, I've been planning on adding time support. Just got a bit swamped
at work...

I'm planning on trying to get time support as well as a few other
issues I've identified. I'm aiming to have the changes in there by the
end of this coming weekend. I'll come report back here when I get all
of the changes pushed.

[Mark Severson]

My apologies Lee, I've been planning on testing a few more
configurations (64-bit, *nix, etc) and getting at least a little
documentation in there to help people get started with it.

Maybe this little (completely untested) example will help you get
going in the meantime:


package main

import (
"exp/sql" // Note: this has changed to "database/sql" in the
latest weekly, but I have not pushed the change to support "database/
sql" yet
_ "bitbucket.org/miquella/mgodbc"
)

func main() {
db, err := sql.Open("mgodbc", "DSN=dsn;UID=uid;PWD=pwd")
// perform error handling
defer db.Close()

stmt, err := db.Prepare("SELECT id, name FROM names WHERE id = ?")
// perform error handling
defer stmt.Close()

rows, err := stmt.Query(10)
// perform error handling
defer rows.Close()

for rows.Next() {
id int
name string

err = rows.Scan(&id, &name)
// perform error handling

[frederickm]

I forked it and got something working (for me at least): https://bitbucket.org/grahf/mgodbc/changeset/a838a5ee0767

Feel free to take it if you don't see anything wrong.

[Lee Field]

Thanks Mark, I will try tomorrow. It's on a 32bit debian unix box, so
I 'll let you know how I get on.

Lee

[Mark Severson]

Alright, I've pushed my changes.

I included the time.Time handling, thank you frederickm for the
reference! I ended up not using your code verbatim because I wanted to
stick with the native structs and it didn't include the other side of
the conversion (binding into a parameter), but it was nice to have
someone else's code to double check mine against!

I also updated the import reference to match the change to database/
sql in weekly.2012-01-20 and changed the receivers of all the methods
to take pointers (prevents the handles in the structs from being
duplicated).

I hope this helps!

-Mark

P.S. Lee, I'm sorry I haven't gotten a chance to test this out on *nix
yet, I will still try to find time to do that and get back to you.

[numenor]

Hi Mark,

No worries, you have been helping me, I have tried the code on a 32
bit debian 5 (lenny) system.

The line: rows, err := stmt.Query(10) i changed to rows, err :=
stmt.Query() - The code was panicking with the '10' in, not sure
what it was there for?

I am using freetds 0.82-4

I have changed your code as follows:

package main:

import (
"database/sql"
_ "mgodbc"
"fmt"
"os"
)

var (
db *sql.DB
checkError = func(err error) {
if err != nil {
fmt.Println(err)
os.Exit(1)
}
}
)
func main() {
db, err := sql.Open("mgodbc", "DSN=monsoon;UID=python;PWD=python")
checkError(err)
defer db.Close()
stmt, err := db.Prepare("SELECT TOP (10) Sku FROM InventoryItem")
checkError(err)
defer stmt.Close()
rows, err := stmt.Query()
checkError(err)
defer rows.Close()
for rows.Next() {
var Id string
err = rows.Scan(&Id)
checkError(err)
println(Id)
}
err = rows.Err()
checkError(err)

Apologies for the layout.

Results as follows, I am thus far only testing selects against a ms
sql 2005 express running on win xp.

When the query selects an integer field results return absolutely
fine, however when selecting a varchar i get the following:

{HY003} [FreeTDS][SQL Server]Program type out of range

I also connect from this debian box to the same sql server using
python via freetds using pymssql, which has no issues.

Unfortunately, I cannot 100% say that it is not a freetds issue, I am
looking into it but at the minute cannot find a resolution, have
already tried: client charset = UTF-8 in freetds.conf


Lee

[Kees Varekamp]

Hi Mark,

I've tried to build on Ubuntu 11.04 32bit but gomake said:

mgodbc.go:616[_obj/mgodbc.cgo1.go:619]: cannot use _Ctype_SQLPOINTER(&colType) (type _Ctype_SQLPOINTER) as type *_Ctype_SQLINTEGER in function argument

I could get it to build by changing this line in Next() in mgodbc.go:

C.SQLPOINTER(&colType),

into this:

&colType,

But that broke the Next method. Open, Prepare, and Query seem fine.

Kees

[Mark Severson]

Looking into that a little further, it looks like that stems from the Microsoft and unixodbc headers being just slightly different.

The 32-bit version of the Microsoft headers are the ones I've done the majority of my work against. But the 64-bit and unixodbc versions of the headers have a few of the types different from the 32-bit ones. Because of this, it looks like I'll just need to spend a bit of time resolving the type differences as changing it either way will break the other...

I am working on an update as quickly as possible, but work has been taking more time than usual, so it's going slower than I was hoping. I'll post an update here as soon as I can get one done!

[Mark Severson]

Once I get some of the calling type issues worked out for unixodbc, I'll see if I can look into why you're getting the 'Program type out of range' error. All of my testing thus far has been on Windows against SQL Server using the SQL Server ODBC drivers. It's probably just something that FreeTDS does differently than the Microsoft driver.

What SQL data type is "InventoryItem"."Sku" in your database?

----

As for the 10 in the code I originally posted, that was intended to be an example of passing a parameter to the query (if you notice the SQL query had a parameter for the where clause). It just wasn't a very good example. :P

[lukem...@gmail.com]

I am relatively new to Go development but I would like to use the mgodbc package on Windows.  What commands do I need to issue to get/build/install mgodbc so that I can then use the example program listed above?

Luke

[numenor]

Hi Mark,

Apologies for the delay in the reply:

sku is varchar(63),Null

Hoping to get chance to look at this again this week.

Lee

[Mark Severson]

Kees,

The issue turned out to be an incompatibility between the 32-bit and 64-bit Microsoft headers (the unixodbc headers more closely follow the 64-bit Microsoft headers). I've added a C-based wrapper for the offending function that should allow it to compile 32 or 64-bit (following along similar lines to go-odbc's solution). Unfortunately, I can't get my 64-bit go compiler working, so I can't test it at the moment.

I did end up doing something similar to your fix, so I hope it hasn't broken the Next method. I don't see any reason it would have though.

I will see if I can find some time before the end of the week to try this on my Ubuntu machine and get it working there. I will come back here and report when I've got it fixed!

Thanks,

Mark

[Mark Severson]

numenor-

I've been looking into this for a couple weeks for you and I think I've figured out what the issue is.

As Go only has Unicode strings (encoded as UTF-8) not ASCII, I didn't use the ASCII versions of the ODBC APIs in mgodbc. So I checked go-odbc to see why it appeared to be working. It looks like go-odbc is using the ASCII versions of the API (I think UnixODBC ASCII APIs accept UTF-8, but the Windows ASCII APIs won't).

I tried using FreeTDS 0.82 (the most current package in Ubuntu 11.10 Oneiric Ocelot) and ended up with the same results as you. After some research, I came to the conclusion FreeTDS 0.82 just doesn't support the Unicode ODBC APIs properly. So I downloaded, compiled, and installed FreeTDS 0.91, which appears to have fixed the problem (I haven't had a problem since).

I checked Ubuntu 12.04 Precise Pangolin and it now includes FreeTDS 0.91 instead of 0.82, so after upgrading your Ubuntu (in a couple months) the error should just go away.

So if you update to FreeTDS 0.91, I don't think you'll have any more problems with it! :)

Let me know if this works for you!

-Mark

Note: you should probably update mgodbc as well, it won't change anything with FreeTDS or Unicode, but there are a few other fixes that have been made. You can update by running the following command:

go get -u bitbucket.org/miquella/mgodbc

[Mark Severson]

Kees-

I have updated mgodbc to build on Windows or Linux (32-bit or 64-bit) and to match the changes in weekly.2012.02.22. As well as a few other little fixes.

Just update to the latest version of mgodbc by running:

go get -u bitbucket.org/miquella/mgodbc 

Let me know if this helps you!

-Mark

[Kees Varekamp]

Hi Mark,

Better late than never: I've tested your lib on 32bit windows and 32bit linux and both work great. Awesome!

Thanks,

Kees

[Mark Severson]

Kees,

Excellent! I'm glad you came back to let us know!

Thanks,

Mark