database/sql: DB.Exec leaving SQL Server connections in an "Invalid cursor state" if they have multiple statements #28472
Comments
kardianos
added
the
NeedsInvestigation
|
What do driver package are you using? |
kardianos
changed the title
|
https://github.com/alexbrainman/odbc
I imagine fixing the invalid cursor state would be there, and not in the
core go SQL library, but in the same way the go SQL library needed a
NextResultSet method for Query results, it may be helpful to have something
similar for Exec results.
…On Mon, Oct 29, 2018, 9:12 PM Daniel Theophanes ***@***.***> wrote:
What do driver package are you using?
—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
<#28472 (comment)>, or mute
the thread
<https://github.com/notifications/unsubscribe-auth/AT6ZCnz7SsX5p40aUJyT36WtXBaTqDRtks5up6d1gaJpZM4X_7LL>
.
|
|
I would like to support returning individual result counts and other meta informational or messages as they come off the wire. But that won't land until v2 of the package. If you are accessing a SQL Server database, I would highly recommend accessing it through the native TDS interface using https://github.com/denisenkom/go-mssqldb . TDS supports multiple statements and even multiple results from a single query. It sounds like this is an odbc client issue. @alexbrainman may be able to comment more. |
I am second that. If github.com/denisenkom/go-mssqldb works for you, just use it.
Maybe it is. @sponeil I do not have much free time now days, but if you create an issue here https://github.com/alexbrainman/odbc/issues/new with complete steps for me to reproduce your issue, I will investigate it. Thank you Alex |
|
Thanks. As Alex requested, I have created a new issue on his project with steps and a Go unit test file that reproduces the error. While I'm not against using a native TDS library, I chose ODBC first because I need to connect to multiple types of database servers, and in cases like that I prefer to minimize dependencies on third-party libraries. |
Fair enough. But database/sql package drivers are easy to replace. You could still use github.com/alexbrainman/odbc package to access non MS SQL databases. Does not mean you have use github.com/alexbrainman/odbc for MS SQL too. Alex |
|
"Does not mean you have use github.com/alexbrainman/odbc for MS SQL too." Yes, I know. Just because I would prefer to avoid to add (yet another) third-party library doesn't mean I can't. As a side note, I tried downloading "github.com/denisenkom/go-mssqldb" to run a unit test with it, and simply importing it into my package (without even using it) causes an immediate "exit status 4294967295" any time I try running "go test" on that package. Comment out the import line, and the test runs just fine. IMO that is really odd given the fact that the library is pure Go. Can't seem to find any command-line args that will give me any more info than that exit status -1. EDIT: Sorry, the exit code -1 was mine. I was unable to see what was happening because "go test" caches/hides all log messages I try to send to stdout (I really wish I could disable that), it was aborting in TestMain before the first test even started, and for some reason trying to set a breakpoint on any line in TestMain is causing the debugger to hang. Debugging tests seems to work fine if I set a breakpoint inside a test func, but not in TestMain. EDIT2: FYI, once I got it running, Denise's mssqldb driver ran 12.5% slower than Alex's ODBC driver. Everything else was identical except for the fact that I had to replace "?" parameters with "@p" parameters in the query strings. |
github.com/denisenkom/go-mssqldb package is pure Go. Written by people who had to guess how communication between your program and MS SQL Server works. While github.com/alexbrainman/odbc is a thin wrapper around ODBC code written by Microsoft, probably, in C, and super optimised because every man and his dog uses it. Alex |
|
Yes, I understand that. I wasn't bashing the other library, I was merely pointing out that there's more than one reason for me to prefer the ODBC driver for this particular app's needs. |
|
I suspect this is caused by an issue with the ODBC driver. At the very least, I know of nothing actionable by |
Please answer these questions before submitting your issue. Thanks!
What version of Go are you using (
go version)?go1.10.3
Does this issue reproduce with the latest release?
Yes
What operating system and processor architecture are you using (
go env)?windows/amd64
What did you do?
Tried to execute SQL server statements with multiple INSERT/UPDATE calls. One simple justification for this is a query like this to avoid multiple round trips to UPDATE a single ROW, but then INSERT it if the row does not exist:
DECLARE @id INT, @a VARCHAR(255)SELECT @id = ?, @val = ?UPDATE [my_table] SET a = @a WHERE id = @idIF @@ROWCOUNT = 0INSERT INTO [my_table] (id, a) VALUES (@id, @a)The query above does not return any result sets, but in ODBC it returns multiple "affected row" counts. If I try to Exec the statement above, it will work the first time (and return the first count), but it will leave the connection in an "Invalid cursor state", and any queries I try to run after that will fail.
I would've left a play.golang.org link, but it provides no way to test ODBC connections to SQL Servers.
What did you expect to see?
I expected the ODBC driver to NOT leave the connection in an invalid cursor state I can't recover from.
What did you see instead?
It left the the connection in an invalid cursor state.
IMO the ideal fix would be to add one more method to the sql.Result interface that can return an array of counts. It would be analogous to the new NextResultSet method for sql.Rows, but it would keep the client code simple. However, it would be acceptable to simply fix the ODBC driver so it stops leaving the connection in an invalid cursor state (even if it must drop the rest of the counts).
The text was updated successfully, but these errors were encountered: