State of ODBC Support in Go

[Luke Mauldin]

I would like to use Go to connect to a SQL Server database on
Windows. What is the current state of ODBC support in Go? I have
seen several packages referenced, including https://github.com/BenoyRNair/godbc/
and https://bitbucket.org/miquella/mgodbc/src/e4564dbbf2f7/mgodbc.go
but what is the recommended way to connect via ODBC in Go?

Luke

[Mark Severson]

Luke,

If you're using one of the latest weekly builds and have your environment setup properly (to use the 'go' tool) it's pretty straight forward.

Something like the following should get you started:

------

package main

import (

    "database/sql"

    _ "bitbucket.org/miquella/mgodbc"

)

func main() {

    db, err := sql.Open("mgodbc", "dsn=mydsn;uid=myuid;pwd=mypwd")

    if err != nil {

        panic(err)

    }

    defer db.Close()

    rows, err := db.Query("SELECT id, name FROM person")

    if err != nil {

        panic(err)

    }

    defer  rows.Close()

    for rows.Next() {

        var id int

        var name string

        err = rows.Scan(&id, &name)

        if err != nil {

            panic(err)

        }

    }

}

------

1. Note: I haven't yet fixed the compilation, so I believe the mgodbc package only works on Windows 386
2. Ensure the MinGW compiler is in your PATH
   
3. Save the above file in a path such as $GOPATH/src/myproj
   
4. Run 'go get' in the myproj folder - this should get the mgodbc package and install it into your GOPATH
   
5. Run 'go build' in the myproj folder - this should build myproj and you should be set to go!

Let me know if this doesn't quite do it for you and I'll see what I can do to help!

-Mark

[Luke Mauldin]

Mark,

Thank you very much for your help. Using the example code below, I
was able to connect to a SQL Server 2008 data from Windows X64 using
the AMD64 version of Go. In order to get it to work, I had to make
the following modifications:

1) Modify mgodbc.go line 469 and change C.SQLINTEGER to C.SQLLEN
2) Modify mgodbc.go line 714 and change C.SQLPOINTER(&colType) to
&colType

I am using MINGW64 with GCC version 4.7.0 20120113. The only problem
I am having now is that GDB crashes when I am trying to debug my
program and I submitted issue 3047 that has been accepted as a bug.

Again, thank you so much for your help, most of the work we do is on
Windows and is database-driven so it was essential for me to get Go
connected to SQL Server before I could begin using it in my daily
work. I do .NET development almost exclusively but there are alot of
aspects about Go that appeal to me and I am looking forward to using
it more. Are there any gotchas using mgodbc that I should know
about? Are there any plans to integrate it into the Go source tree?

Luke

>    1. *Note: I haven't yet fixed the compilation, so I believe the mgodbc
>    package only works on Windows 386*
>    2. Ensure the MinGW compiler is in your PATH
>    3. Save the above file in a path such as $GOPATH/src/myproj
>    4. Run 'go get' in the myproj folder - this should get the mgodbc

>    package and install it into your GOPATH

>    5. Run 'go build' in the myproj folder - this should build myproj and

[T.J. Yang]

On Friday, February 17, 2012 7:19:07 AM UTC-6, Luke Mauldin wrote:

Mark,

Thank you very much for your help.  Using the example code below, I
was able to connect to a SQL Server 2008 data from Windows X64 using
the AMD64 version of Go.  In order to get it to work, I had to make
the following modifications:

1)  Modify mgodbc.go line 469 and change C.SQLINTEGER to C.SQLLEN
2)  Modify mgodbc.go line 714 and change C.SQLPOINTER(&colType) to
&colType

I am using MINGW64 with GCC version 4.7.0 20120113.  The only problem
I am having now is that GDB crashes when I am trying to debug my
program and I submitted issue 3047 that has been accepted as a bug.

Luke, Thanks for the sharing.

With your pointers of two changes, I am able to compile odbc test code using  mgodbc.go on Fedora 16 386.

[weekly@640m odbc]$ ls

odbc  odbctest1.go

[weekly@640m odbc]$ ./odbc

panic: {IM002} [unixODBC][Driver Manager]Data source name not found, and no default driver specified

goroutine 1 [running]:

main.main()

        /home/weekly/gocode/src/odbc/odbctest1.go:17 +0xef

[weekly@640m odbc]$

tj

[Wei guangjing]

I update go-odbc <https://github.com/weigj/go-odbc> to tip and add
database/sql driver, tested on windows X64 for go amd64, you can try
it.

2012/2/17 Luke Mauldin <lukem...@gmail.com>:

[Luke Mauldin]

Wei,

I am trying to use your driver and it works correctly as long as I
read columns of type VARCHAR. However, I am trying to read a column
of type INT and the driver is giving me this error:
panic: sql: Scan error on column index 0: converting string "0xe9571b"
to a int: strconv.ParseInt: parsing "0xe9571b": invalid syntax

My code is below. The line that is causing hte problem is line 36 --
printing r.clmno

package main

import (
"database/sql"

"fmt"
_ "strconv"
_ "odbc/driver"
)

func main() {
db, err := sql.Open("odbc", "Driver={SQL Server Native Client
10.0};Server=XXXX;Database=XXX;Uid=XXXX;Pwd=XXXX")

if err != nil {
panic(err)
}
defer db.Close()

stmt, err := db.Prepare("select top 1 clmno, type as claimType from
clh where neg = 'LSL' and PendCode NOT IN ('Y', 'Z')")

if err != nil {
panic(err)
}

defer stmt.Close()

rows, err := stmt.Query()
defer rows.Close()

type row struct {
clmno int
claimType string
}

for rows.Next() {
var r row
err = rows.Scan(&r.clmno, &r.claimType)

if err != nil {
panic(err)
}

fmt.Println(r.clmno)
fmt.Println(r.claimType)
//fmt.Println(" Claim number: " + strconv.Itoa(r.clmno))

}
}

On Feb 20, 7:47 am, Wei guangjing <vcc....@gmail.com> wrote:
> I update go-odbc <https://github.com/weigj/go-odbc> to tip and add
> database/sql driver, tested on windows X64 for go amd64, you can try
> it.
>

> 2012/2/17 LukeMauldin<lukemaul...@gmail.com>:

[Luke Mauldin]

A little bit more information, if I use your ODBC driver directly, the
code works but the output is incorrect. The output is:
0 &{[0xe9571b 15 ]}

I expect the output to be:
0 {[15292187 15]}

The code is:
func useOdbcDirect() {
conn, err := odbc.Connect("Driver={SQL Server Native Client
10.0};Server=XXXX")

if err != nil {
panic(err)
}

defer conn.Close()

stmt, err := conn.Prepare("select top 1 clmno, type as claimType from
clh where neg = 'LSL' and PendCode NOT IN ('Y', 'Z')")

if err != nil {
panic(err)
}

defer stmt.Close()
stmt.Execute()

rows, err := stmt.FetchAll()
for i, row := range rows {
fmt.Println(i, row)

}
}

On Feb 20, 7:47 am, Wei guangjing <vcc....@gmail.com> wrote:

> I update go-odbc <https://github.com/weigj/go-odbc> to tip and add
> database/sql driver, tested on windows X64 for go amd64, you can try
> it.
>

> 2012/2/17 LukeMauldin<lukemaul...@gmail.com>:

[Paul Kline]

FYI, This works on my system. I have a complete example code for
windows x86 if you need it.

Regards,

Paul

[Luke Mauldin]

The code I posted above works on my system when I am just reading
strings but it does not work reading type INT. I am running on
Windows 7 X64, connecting to SQL Server 2008, using Microsoft's latest
ODBC driver for SQL server. I am using GCC version 4.7.0 20120216

Luke

[Wei guangjing]

Bug fixed, please pull and try again.

2012/2/22 Luke Mauldin <lukem...@gmail.com>:

[Luke Mauldin]

Wei,

Thank you for fixing the bug. I downloaded the updated code and tried
the same test again this morning and it worked perfectly, thank you.

Luke

On Feb 21, 9:22 pm, Wei guangjing <vcc....@gmail.com> wrote:
> Bug fixed, please pull and try again.
>

> 2012/2/22 Luke Mauldin <lukemaul...@gmail.com>:

>
>
>
>
>
>
>
> > The code I posted above works on my system when I am just reading
> > strings but it does not work reading type INT.  I am running on
> > Windows 7 X64, connecting to SQL Server 2008, using Microsoft's latest

> >ODBCdriver for SQL server.  I am using GCC version 4.7.0 20120216

>
> > Luke
>
> > On Feb 21, 3:34 pm, Paul Kline <pdkl...@gmail.com> wrote:
> >> FYI, This works on my system.  I have a complete example code for
> >> windows x86 if you need it.
>
> >> Regards,
>
> >> Paul
>
> >> On Feb 21, 11:20 am, Luke Mauldin <lukemaul...@gmail.com> wrote:
>

> >> > A little bit more information, if I use yourODBCdriver directly, the

> >> > code works but the output is incorrect.  The output is:
> >> > 0 &{[0xe9571b 15  ]}
>
> >> > I expect the output to be:
> >> > 0 {[15292187    15]}
>
> >> > The code is:
> >> > func useOdbcDirect() {

> >> >         conn, err :=odbc.Connect("Driver={SQL Server Native Client

[Mark Severson]

Luke,

I'm sorry it's taken me quite a few days to get back to you here... We had a customer's site go down, so I've been working late all week.

I wanted to let you know I've updated mgodbc to (hopefully) address the issues you were seeing:

Issue #1 was just a silly mistake I made when I was trying to hurry and add time.Time support.

Issue #2 is 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. But the solution seems sound, if you wanted to give it a shot! 

As far as issues with mgodbc, I use mgodbc quite regularly at work and I haven't run into any issues. But I am working through fixing an issue that appears to be an incompatibility with FreeTDS reported by numenor in a different thread (https://groups.google.com/d/msg/golang-nuts/zJACa9485gc/yMtx8aQRfK8J). I'm guessing that it's just the way I'm querying the ODBC APIs that's causing the issue. Being that it's an issue while using FreeTDS, it shouldn't affect you if you're working on Windows.

----

If you do end up using mgodbc, please let me know of any issues you run into! I'm trying to make sure I keep up on issues with mgodbc.

I originally created mgodbc because of how many problems I ran into trying to work with the other ODBC drivers that were available. I kept having problems with projects being abandoned and unable to compile or with types not being wrapped in go types which effectively prevented any useful work from being done with them.

[Luke Mauldin]

Mark,

Thank you for getting back to me and fixing the two issues. However,
I am now encountering the following errors during building:

c:\GoExternal\src>go install mgodbc
# mgodbc
mgodbc.go:347[C:\Users\lmauldin\AppData\Local\Temp\go-
build355433366\mgodbc\_obj\mgodbc.cgo1.go:370]: cannot use s (type
*stmt) as type driver.Stmt in return argument:
*stmt does not implement driver.Stmt (wrong type for Exec
method)
have Exec([]interface {}) (driver.Result, error)
want Exec([]driver.Value) (driver.Result, error)
mgodbc.go:670[C:\Users\lmauldin\AppData\Local\Temp\go-
build355433366\mgodbc\_obj\mgodbc.cgo1.go:781]: cannot use rows (type
*rows) as type driver.Rows in return argument:
*rows does not implement driver.Rows (wrong type for Next
method)
have Next([]interface {}) error
want Next([]driver.Value) error


I am using the latest version of the Go code from the repository and I
think the errors above could be related to some changes checked into
the Go trunk over the past couple of days dealing with the SQL
interfaces?

Thank you for addressing the issues as they have arisen on mgodbc, I
would hope that at some point mgodbc (or another odbc driver) would be
included standard in the Go trunk.

Luke

On Feb 22, 10:52 am, Mark Severson <mique...@gmail.com> wrote:
> Luke,
>
> I'm sorry it's taken me quite a few days to get back to you here... We had
> a customer's site go down, so I've been working late all week.
>
> I wanted to let you know I've updated mgodbc to (hopefully) address the
> issues you were seeing:

> *Issue #1* was just a silly mistake I made when I was trying to hurry and
> add time.Time support.
> *Issue #2* is an incompatibility between the 32-bit and 64-bit Microsoft

[Andy Balholm]

I get the same errors at tip; it works with weekly.

[Brad Fitzpatrick]

On Wed, Feb 22, 2012 at 9:57 AM, Luke Mauldin <lukem...@gmail.com> wrote:

Mark,

Thank you for getting back to me and fixing the two issues.  However,
I am now encountering the following errors during building:

c:\GoExternal\src>go install mgodbc
# mgodbc
mgodbc.go:347[C:\Users\lmauldin\AppData\Local\Temp\go-
build355433366\mgodbc\_obj\mgodbc.cgo1.go:370]: cannot use s (type
*stmt) as type driver.Stmt in return argument:
       *stmt does not implement driver.Stmt (wrong type for Exec
method)
               have Exec([]interface {}) (driver.Result, error)
               want Exec([]driver.Value) (driver.Result, error)
mgodbc.go:670[C:\Users\lmauldin\AppData\Local\Temp\go-
build355433366\mgodbc\_obj\mgodbc.cgo1.go:781]: cannot use rows (type
*rows) as type driver.Rows in return argument:
       *rows does not implement driver.Rows (wrong type for Next
method)
               have Next([]interface {}) error
               want Next([]driver.Value) error


I am using the latest version of the Go code from the repository and I
think the errors above could be related to some changes checked into
the Go trunk over the past couple of days dealing with the SQL
interfaces?

Yup, sorry about that.  This should be the last breaking change to the driver package, though.

 

Thank you for addressing the issues as they have arisen on mgodbc, I
would hope that at some point mgodbc (or another odbc driver) would be
included standard in the Go trunk.

All drivers will be out-of-tree.  Only the core will be included.

The go get tool is easy and powerful.  At most, we'll promote certain drivers on the wiki as having passed some compatibility & portability tests.

[Mark Severson]

Luke-

I updated mgodbc to match the changes in weekly.2012.02.22, that should fix your compilation error (I didn't want to update mgodbc until it was released in a weekly).

Let me know if you run into any other issues with it!

-Mark

[Luke Mauldin]

Mark,

I am using the library and I have run into a situation that I think is
a bug. Here is the situation: I have a prepared statement that is
deleting some rows from a table based on user input. Rows may or may
not exist in the table that matches the user criteria. If the user
enters criteria that matches rows in the table, the delete statement
deletes the rows. However, if the user enters criteria that does not
match any rows in the table, the statement returns an "error" or "no
data found". I think that it should not return an error of no data
found, but rather return that as "information" or maybe a warning. I
am listing the code below and I am also listingthe ODBC trace files
for you to examine. Thank you,

stmt, err := db.Prepare("delete from tblDukeWebToolboxUserColumns
where userid = ? and tabletype = ?")

if err != nil {
panic(err)
}

defer stmt.Close()

_, err = stmt.Exec("LSL", "ClaimResults-Pending")
if err != nil {
panic(err) //It is erroring here
}


First trace file (no error thrown:

test-database e10-1594 ENTER SQLAllocHandle
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 0x0000000000000000
SQLHANDLE * 0x000000F840042020

test-database e10-1594 EXIT SQLAllocHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 0x0000000000000000
SQLHANDLE * 0x000000F840042020 ( 0x00000000025E7EF0)

test-database e10-1594 ENTER SQLSetEnvAttr
SQLHENV 0x00000000025E7EF0
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 3 <SQL_OV_ODBC3>
SQLINTEGER 0

test-database e10-1594 EXIT SQLSetEnvAttr with return code 0
(SQL_SUCCESS)
SQLHENV 0x00000000025E7EF0
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 3 <SQL_OV_ODBC3>
SQLINTEGER 0

test-database e10-1594 ENTER SQLAllocHandle
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x00000000025E7EF0
SQLHANDLE * 0x000000F84002C220

test-database e10-1594 EXIT SQLAllocHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x00000000025E7EF0
SQLHANDLE * 0x000000F84002C220 ( 0x00000000027984F0)

test-database e10-1594 ENTER SQLDriverConnectW
HDBC 0x00000000027984F0
HWND 0x0000000000000000
WCHAR * 0x000007FEF3718F08 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x000007FEF3718F08
SWORD -3
SWORD * 0x0000000000000000
UWORD 0 <SQL_DRIVER_NOPROMPT>

test-database e10-1594 EXIT SQLDriverConnectW with return code 1
(SQL_SUCCESS_WITH_INFO)
HDBC 0x00000000027984F0
HWND 0x0000000000000000
WCHAR * 0x000007FEF3718F08 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x000007FEF3718F08 <Invalid buffer length!> [-3]
SWORD -3
SWORD * 0x0000000000000000
UWORD 0 <SQL_DRIVER_NOPROMPT>

DIAG [01000] [Microsoft][SQL Server Native Client 10.0][SQL
Server]Changed database context to 'DukeAccount'. (5701)

DIAG [01000] [Microsoft][SQL Server Native Client 10.0][SQL
Server]Changed language setting to us_english. (5703)

test-database e10-1594 ENTER SQLAllocHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00000000027984F0
SQLHANDLE * 0x000000F840042030

test-database e10-1594 EXIT SQLAllocHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00000000027984F0
SQLHANDLE * 0x000000F840042030 ( 0x000000000279CF50)

test-database e10-1594 ENTER SQLPrepareW
HSTMT 0x000000000279CF50
WCHAR * 0x000000F84004A000 [ 75] "delete from
tblDukeWebToolboxUserColumns where userid = ? and tabletype = ?"
SDWORD 75

test-database e10-1594 EXIT SQLPrepareW with return code 0
(SQL_SUCCESS)
HSTMT 0x000000000279CF50
WCHAR * 0x000000F84004A000 [ 75] "delete from
tblDukeWebToolboxUserColumns where userid = ? and tabletype = ?"
SDWORD 75

test-database e10-1594 ENTER SQLNumParams
HSTMT 0x000000000279CF50
SWORD * 0x000000F840042038

test-database e10-1594 EXIT SQLNumParams with return code 0
(SQL_SUCCESS)
HSTMT 0x000000000279CF50
SWORD * 0x000000F840042038 (2)

test-database e10-1594 ENTER SQLFreeHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x000000000279CF50

test-database e10-1594 EXIT SQLFreeHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x000000000279CF50

test-database e10-1594 ENTER SQLAllocHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00000000027984F0
SQLHANDLE * 0x000000F840042048

test-database e10-1594 EXIT SQLAllocHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00000000027984F0
SQLHANDLE * 0x000000F840042048 ( 0x000000000279CF50)

test-database e10-1594 ENTER SQLPrepareW
HSTMT 0x000000000279CF50
WCHAR * 0x000000F84004A0A0 [ 75] "delete from
tblDukeWebToolboxUserColumns where userid = ? and tabletype = ?"
SDWORD 75

test-database e10-1594 EXIT SQLPrepareW with return code 0
(SQL_SUCCESS)
HSTMT 0x000000000279CF50
WCHAR * 0x000000F84004A0A0 [ 75] "delete from
tblDukeWebToolboxUserColumns where userid = ? and tabletype = ?"
SDWORD 75

test-database e10-1594 ENTER SQLNumParams
HSTMT 0x000000000279CF50
SWORD * 0x000000F840042050

test-database e10-1594 EXIT SQLNumParams with return code 0
(SQL_SUCCESS)
HSTMT 0x000000000279CF50
SWORD * 0x000000F840042050 (2)

test-database e10-1594 ENTER SQLFreeHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x000000000279CF50

test-database e10-1594 EXIT SQLFreeHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x000000000279CF50

test-database e10-1594 ENTER SQLAllocHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00000000027984F0
SQLHANDLE * 0x000000F840042058

test-database e10-1594 EXIT SQLAllocHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00000000027984F0
SQLHANDLE * 0x000000F840042058 ( 0x000000000279CF50)

test-database e10-1594 ENTER SQLPrepareW
HSTMT 0x000000000279CF50
WCHAR * 0x000000F84004A140 [ 75] "delete from
tblDukeWebToolboxUserColumns where userid = ? and tabletype = ?"
SDWORD 75

test-database e10-1594 EXIT SQLPrepareW with return code 0
(SQL_SUCCESS)
HSTMT 0x000000000279CF50
WCHAR * 0x000000F84004A140 [ 75] "delete from
tblDukeWebToolboxUserColumns where userid = ? and tabletype = ?"
SDWORD 75

test-database e10-1594 ENTER SQLBindParameter
HSTMT 0x000000000279CF50
UWORD 1
SWORD 1 <SQL_PARAM_INPUT>
SWORD -8 <SQL_C_WCHAR>
SWORD -9 <SQL_WVARCHAR>
SQLULEN 6
SWORD 0
PTR 0x000000F840042068
SQLLEN 6
SQLLEN * 0x000000F840042060

test-database e10-1594 EXIT SQLBindParameter with return code 0
(SQL_SUCCESS)
HSTMT 0x000000000279CF50
UWORD 1
SWORD 1 <SQL_PARAM_INPUT>
SWORD -8 <SQL_C_WCHAR>
SWORD -9 <SQL_WVARCHAR>
SQLULEN 6
SWORD 0
PTR 0x000000F840042068
SQLLEN 6
SQLLEN * 0x000000F840042060 (6)

test-database e10-1594 ENTER SQLBindParameter
HSTMT 0x000000000279CF50
UWORD 2
SWORD 1 <SQL_PARAM_INPUT>
SWORD -8 <SQL_C_WCHAR>
SWORD -9 <SQL_WVARCHAR>
SQLULEN 40
SWORD 0
PTR 0x000000F8400404B0
SQLLEN 40
SQLLEN * 0x000000F840042070

test-database e10-1594 EXIT SQLBindParameter with return code 0
(SQL_SUCCESS)
HSTMT 0x000000000279CF50
UWORD 2
SWORD 1 <SQL_PARAM_INPUT>
SWORD -8 <SQL_C_WCHAR>
SWORD -9 <SQL_WVARCHAR>
SQLULEN 40
SWORD 0
PTR 0x000000F8400404B0
SQLLEN 40
SQLLEN * 0x000000F840042070 (40)

test-database e10-1594 ENTER SQLExecute
HSTMT 0x000000000279CF50

test-database e10-1594 EXIT SQLExecute with return code 0
(SQL_SUCCESS)
HSTMT 0x000000000279CF50

test-database e10-1594 ENTER SQLRowCount
HSTMT 0x000000000279CF50
SQLLEN * 0x000000F840042080

test-database e10-1594 EXIT SQLRowCount with return code 0
(SQL_SUCCESS)
HSTMT 0x000000000279CF50
SQLLEN * 0x000000F840042080 (31)

test-database e10-1594 ENTER SQLFreeHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x000000000279CF50

test-database e10-1594 EXIT SQLFreeHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x000000000279CF50

test-database e10-1594 ENTER SQLDisconnect
HDBC 0x00000000027984F0

test-database e10-1594 EXIT SQLDisconnect with return code 0
(SQL_SUCCESS)
HDBC 0x00000000027984F0

test-database e10-1594 ENTER SQLFreeHandle
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x00000000027984F0

test-database e10-1594 EXIT SQLFreeHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x00000000027984F0



Second trace file (error reported)

test-database b88-1328 ENTER SQLAllocHandle
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 0x0000000000000000
SQLHANDLE * 0x000000F840042020

test-database b88-1328 EXIT SQLAllocHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 0x0000000000000000
SQLHANDLE * 0x000000F840042020 ( 0x0000000000327EF0)

test-database b88-1328 ENTER SQLSetEnvAttr
SQLHENV 0x0000000000327EF0
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 3 <SQL_OV_ODBC3>
SQLINTEGER 0

test-database b88-1328 EXIT SQLSetEnvAttr with return code 0
(SQL_SUCCESS)
SQLHENV 0x0000000000327EF0
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 3 <SQL_OV_ODBC3>
SQLINTEGER 0

test-database b88-1328 ENTER SQLAllocHandle
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x0000000000327EF0
SQLHANDLE * 0x000000F84002C220

test-database b88-1328 EXIT SQLAllocHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x0000000000327EF0
SQLHANDLE * 0x000000F84002C220 ( 0x00000000002E84F0)

test-database b88-1328 ENTER SQLDriverConnectW
HDBC 0x00000000002E84F0
HWND 0x0000000000000000
WCHAR * 0x000007FEF3718F08 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x000007FEF3718F08
SWORD -3
SWORD * 0x0000000000000000
UWORD 0 <SQL_DRIVER_NOPROMPT>

test-database b88-1328 EXIT SQLDriverConnectW with return code 1
(SQL_SUCCESS_WITH_INFO)
HDBC 0x00000000002E84F0
HWND 0x0000000000000000
WCHAR * 0x000007FEF3718F08 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x000007FEF3718F08 <Invalid buffer length!> [-3]
SWORD -3
SWORD * 0x0000000000000000
UWORD 0 <SQL_DRIVER_NOPROMPT>

DIAG [01000] [Microsoft][SQL Server Native Client 10.0][SQL
Server]Changed database context to 'DukeAccount'. (5701)

DIAG [01000] [Microsoft][SQL Server Native Client 10.0][SQL
Server]Changed language setting to us_english. (5703)

test-database b88-1328 ENTER SQLAllocHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00000000002E84F0
SQLHANDLE * 0x000000F840042030

test-database b88-1328 EXIT SQLAllocHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00000000002E84F0
SQLHANDLE * 0x000000F840042030 ( 0x00000000002ECF50)

test-database b88-1328 ENTER SQLPrepareW
HSTMT 0x00000000002ECF50
WCHAR * 0x000000F84004A000 [ 75] "delete from
tblDukeWebToolboxUserColumns where userid = ? and tabletype = ?"
SDWORD 75

test-database b88-1328 EXIT SQLPrepareW with return code 0
(SQL_SUCCESS)
HSTMT 0x00000000002ECF50
WCHAR * 0x000000F84004A000 [ 75] "delete from
tblDukeWebToolboxUserColumns where userid = ? and tabletype = ?"
SDWORD 75

test-database b88-1328 ENTER SQLNumParams
HSTMT 0x00000000002ECF50
SWORD * 0x000000F840042038

test-database b88-1328 EXIT SQLNumParams with return code 0
(SQL_SUCCESS)
HSTMT 0x00000000002ECF50
SWORD * 0x000000F840042038 (2)

test-database b88-1328 ENTER SQLFreeHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00000000002ECF50

test-database b88-1328 EXIT SQLFreeHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00000000002ECF50

test-database b88-1328 ENTER SQLAllocHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00000000002E84F0
SQLHANDLE * 0x000000F840042048

test-database b88-1328 EXIT SQLAllocHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00000000002E84F0
SQLHANDLE * 0x000000F840042048 ( 0x00000000002ECF50)

test-database b88-1328 ENTER SQLPrepareW
HSTMT 0x00000000002ECF50
WCHAR * 0x000000F84004A0A0 [ 75] "delete from
tblDukeWebToolboxUserColumns where userid = ? and tabletype = ?"
SDWORD 75

test-database b88-1328 EXIT SQLPrepareW with return code 0
(SQL_SUCCESS)
HSTMT 0x00000000002ECF50
WCHAR * 0x000000F84004A0A0 [ 75] "delete from
tblDukeWebToolboxUserColumns where userid = ? and tabletype = ?"
SDWORD 75

test-database b88-1328 ENTER SQLNumParams
HSTMT 0x00000000002ECF50
SWORD * 0x000000F840042050

test-database b88-1328 EXIT SQLNumParams with return code 0
(SQL_SUCCESS)
HSTMT 0x00000000002ECF50
SWORD * 0x000000F840042050 (2)

test-database b88-1328 ENTER SQLFreeHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00000000002ECF50

test-database b88-1328 EXIT SQLFreeHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00000000002ECF50

test-database b88-1328 ENTER SQLAllocHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00000000002E84F0
SQLHANDLE * 0x000000F840042058

test-database b88-1328 EXIT SQLAllocHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00000000002E84F0
SQLHANDLE * 0x000000F840042058 ( 0x00000000002ECF50)

test-database b88-1328 ENTER SQLPrepareW
HSTMT 0x00000000002ECF50
WCHAR * 0x000000F84004A140 [ 75] "delete from
tblDukeWebToolboxUserColumns where userid = ? and tabletype = ?"
SDWORD 75

test-database b88-1328 EXIT SQLPrepareW with return code 0
(SQL_SUCCESS)
HSTMT 0x00000000002ECF50
WCHAR * 0x000000F84004A140 [ 75] "delete from
tblDukeWebToolboxUserColumns where userid = ? and tabletype = ?"
SDWORD 75

test-database b88-1328 ENTER SQLBindParameter
HSTMT 0x00000000002ECF50
UWORD 1
SWORD 1 <SQL_PARAM_INPUT>
SWORD -8 <SQL_C_WCHAR>
SWORD -9 <SQL_WVARCHAR>
SQLULEN 6
SWORD 0
PTR 0x000000F840042068
SQLLEN 6
SQLLEN * 0x000000F840042060

test-database b88-1328 EXIT SQLBindParameter with return code 0
(SQL_SUCCESS)
HSTMT 0x00000000002ECF50
UWORD 1
SWORD 1 <SQL_PARAM_INPUT>
SWORD -8 <SQL_C_WCHAR>
SWORD -9 <SQL_WVARCHAR>
SQLULEN 6
SWORD 0
PTR 0x000000F840042068
SQLLEN 6
SQLLEN * 0x000000F840042060 (6)

test-database b88-1328 ENTER SQLBindParameter
HSTMT 0x00000000002ECF50
UWORD 2
SWORD 1 <SQL_PARAM_INPUT>
SWORD -8 <SQL_C_WCHAR>
SWORD -9 <SQL_WVARCHAR>
SQLULEN 40
SWORD 0
PTR 0x000000F8400404B0
SQLLEN 40
SQLLEN * 0x000000F840042070

test-database b88-1328 EXIT SQLBindParameter with return code 0
(SQL_SUCCESS)
HSTMT 0x00000000002ECF50
UWORD 2
SWORD 1 <SQL_PARAM_INPUT>
SWORD -8 <SQL_C_WCHAR>
SWORD -9 <SQL_WVARCHAR>
SQLULEN 40
SWORD 0
PTR 0x000000F8400404B0
SQLLEN 40
SQLLEN * 0x000000F840042070 (40)

test-database b88-1328 ENTER SQLExecute
HSTMT 0x00000000002ECF50

test-database b88-1328 EXIT SQLExecute with return code 100
(SQL_NO_DATA_FOUND)
HSTMT 0x00000000002ECF50

test-database b88-1328 ENTER SQLGetDiagFieldW
SQLSMALLINT 3
SQLHANDLE 0x00000000002ECF50
SQLSMALLINT 0
SQLSMALLINT 2
SQLPOINTER 0x000000F840042078
SQLSMALLINT 4
SQLSMALLINT * 0x0000000000000000

test-database b88-1328 EXIT SQLGetDiagFieldW with return code 0
(SQL_SUCCESS)
SQLSMALLINT 3
SQLHANDLE 0x00000000002ECF50
SQLSMALLINT 0
SQLSMALLINT 2
SQLPOINTER 0x000000F840042078
SQLSMALLINT 4
SQLSMALLINT * 0x0000000000000000

test-database b88-1328 ENTER SQLFreeHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00000000002ECF50

test-database b88-1328 EXIT SQLFreeHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00000000002ECF50

test-database b88-1328 ENTER SQLDisconnect
HDBC 0x00000000002E84F0

test-database b88-1328 EXIT SQLDisconnect with return code 0
(SQL_SUCCESS)
HDBC 0x00000000002E84F0

test-database b88-1328 ENTER SQLFreeHandle
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x00000000002E84F0

test-database b88-1328 EXIT SQLFreeHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x00000000002E84F0

> ...
>
> read more »

[Mark Severson]

Luke,

My apologies! I missed that the SQLExecute function could return a no data result, which should still be treated as a success.

I have pushed the fix if you want to update and try again.

Thanks,

Mark

[Luke Mauldin]

Mark,

Thank you for making that update. However, I have a question, how can
I check the error object returned by stmt.Exec to determine which
error codes are being returned? I know this sounds simple but I am
relatively new to Go programming and I am unsure how to do it since
it.

Luke

On Mar 5, 1:04 am, Mark Severson <mique...@gmail.com> wrote:
> Luke,
>

> ...
>
> read more »

[Luke Mauldin]

Mark,

I think I have discovered a performance issue. I am attaching the
code below. For the for-range loop between "After execute prepare"
and "After execute", mgodbc is generating inefficient SQL like:
declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P1 nvarchar(8),@P2
nvarchar(12)',N'delete from MRASystem.dbo.WebToolboxUserColumns where
UserName = @P1 and TableType = @P2',N'luke',N'fromws'
select @p1
exec sp_unprepare 1
declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N'@P1 nvarchar(8),@P2 nvarchar(12),@P3
nvarchar(22),@P4 bigint,@P5 bit,@P6 bigint',N'insert into
MRASystem.dbo.WebToolboxUserColumns (UserName, TableType, ColumnName,
ColumnOrder,  ColumnVisible, UpdateDate, IsActive, Width) values (@P1,
@P2, @P3, @P4, @P5, SYSDATETIME(), 1,
@P6)',N'luke',N'fromws',N'ClaimNumber',1,1,120
select @p1
xec sp_unprepare 2
declare @p1 int
set @p1=3
exec sp_prepexec @p1 output,N'@P1 nvarchar(8),@P2 nvarchar(12),@P3
nvarchar(18),@P4 bigint,@P5 bit,@P6 bigint',N'insert into
MRASystem.dbo.WebToolboxUserColumns (UserName, TableType, ColumnName,
ColumnOrder, ColumnVisible, UpdateDate, IsActive, Width) values (@P1,
@P2, @P3, @P4, @P5, SYSDATETIME(), 1,
@P6)',N'luke',N'fromws',N'IsChecked',2,1,80
select @p1
exec sp_unprepare 3

I think mgodbc should be generating SQL like:
declare @p1 int
set @p1=4
exec sp_prepexec @p1 output,N'@UserName varchar(80),@TableType
varchar(30),@ColumnName varchar(80),@ColumnOrder int,@ColumnVisible
bit,@Width int',N'insert into MRASystem.dbo.WebToolboxUserColumns
(UserName, TableType, ColumnName, ColumnOrder, ColumnVisible,
UpdateDate, IsActive, Width)
values(@UserName,
@TableType, @ColumnName, @ColumnOrder, @ColumnVisible, sysdatetime(),
1,
@Width)',@UserName='luke',@TableType='test.net',@ColumnName='First',@ColumnOrder=1,@ColumnVisible=0,@Width=5
select @p1
exec sp_execute
4,@UserName='luke',@TableType='test.net',@ColumnName='Second',@ColumnOrder=2,@ColumnVisible=0,@Width=10
exec sp_execute
4,@UserName='luke',@TableType='test.net',@ColumnName='Second',@ColumnOrder=3,@ColumnVisible=0,@Width=10
exec sp_execute
4,@UserName='luke',@TableType='test.net',@ColumnName='Second',@ColumnOrder=4,@ColumnVisible=0,@Width=10


It looks like mgodbc is not correctly re-using the prepared
statement. Code:
//Add new entries
const insertSql = "insert into MRASystem.dbo.WebToolboxUserColumns
(UserName, TableType, ColumnName, ColumnOrder, " +
" ColumnVisible, UpdateDate, IsActive, Width) values
(?, ?, ?, ?, ?, SYSDATETIME(), 1, ?)"
stmtInsert, err := db.Prepare(insertSql)

if err != nil {
panic(err)
}

defer stmtInsert.Close()

fmt.Println("After execute prepare: %v",
time.Now().Sub(timeStart).Nanoseconds() / 1000000)

for _, column := range columns {
_, err = stmtInsert.Exec(userName, tableType, column.Name,
column.Order, column.Visible, column.Width)

if err != nil {
panic(err)
}
}

fmt.Println("After execute: %v",
time.Now().Sub(timeStart).Nanoseconds() / 1000000)

> ...
>
> read more »

[Mark Severson]

Luke-

This is a slightly more complex question, but I'll attempt to answer it briefly :)

1. database/sql is intended as an abstraction layer so you don't have to know much about your underlying database driver, so getting to more information than what the error interface provides (a string) is not intended to be simple
2. The error codes/information other than a string representation wasn't exported by mgodbc, so you wouldn't have been able to get at it before

With these two points in mind, I have exported the ODBCError object I use internally in mgodbc. In order to get information out of an error though, you will need to perform a type assertion on the returned error object.

Something like this may get you started if you still want to try (disclaimer: I have not tried this):

    _, err := db.Exec("...")

    if err != nil {

        odbcerr := err.(mgodbc.ODBCError)

        if odbcerr != nil {

            // do something with the ODBCError

        }

    }

With that being said, it starts to get more complex from there. ODBC returns errors as a list of errors, error codes, and the corresponding ODBC state, so it may get difficult to extract the information you're looking for (as I can't enumerate all the possible states and their corresponding error codes, there are A LOT of them).

I hope this has either helped or given you enough information to understand what you were looking for!

-Mark

[Mark Severson]

Luke,

The strange behavior you were seeing was from the simple way I implemented mgodbc (to get it working). It would just re-prepare a statement each time the statement was Exec'd or Query'd. I did it this way first to ensure I stayed within the requirements of database/sql.

As of this morning, I have updated mgodbc to support reusing a statement properly. Give it a shot and let me know if it's behaving the way you expected now. :)

Thanks,

Mark

P.S. mgodbc doesn't generate SQL; any extraneous SQL you're seeing in the trace is either generated by the driver or the database engine you're using

[lukem...@gmail.com]

Mark,

I suspected the reason below was causing the behavior I was seeing.  I checked out the new code and although it is better, I think the SQL that is generated it still could be made more efficient. I understand that MGODBC does not generate the SQL, but from my understanding, the calls to the ODBC layer do generate the SQL.  I created two programs, one in Go and one in C# and connected to SQL Server using ODBC in both programs.  I have attached both program files as reference.  The C# program uses ODBC and appears to prepare the statement once, executes it using the handle 3 times and then unprepares the handle.  The Go program appears to still be executing three statements.  As an observation, I think in order for the Go program to prepare the statement, I think we will need a way to pass in the size of the parameters, as in the C# program.  Also, the Go version appears to be preparing 3 statements but is only issues "sp_unprepare" once, so I wonder if the Go version is leaking statements?

Go ODBC SQL Trace:

declare @p1 int

set @p1=1

exec sp_prepexec @p1 output,N'@P1 nvarchar(20)',N'select top 1 id from MRASystem.dbo.ApplicationLog where ComputerName = @P1',N'10.150.0.1'

select @p1

declare @p1 int

set @p1=2

exec sp_prepexec @p1 output,N'@P1 nvarchar(24)',N'select top 1 id from MRASystem.dbo.ApplicationLog where ComputerName = @P1',N'10.150.2.130'

select @p1

declare @p1 int

set @p1=3

exec sp_prepexec @p1 output,N'@P1 nvarchar(46)',N'select top 1 id from MRASystem.dbo.ApplicationLog where ComputerName = @P1',N'NCNLTLUKE.multiplan.com'

select @p1

exec sp_unprepare 3

C# ODBC SQL Trace:

declare @p1 int

set @p1=1

exec sp_prepexec @p1 output,N'@P1 varchar(80)',N'select top 1 id from MRASystem.dbo.ApplicationLog where ComputerName = @P1','10.150.0.1'

select @p1

exec sp_execute 1,'10.150.2.130'

exec sp_execute 1,'NCNLTLUKE.multiplan.com'

exec sp_unprepare 1