Closed GoogleCodeExporter closed 9 years ago
I didn't look at your problem properly, but it sounds like it works fine on
Windows. If that is true, you can achieve similar behaviour on Linux by
changing NewVariableWidthColumn function. Just go for NonBindableColumn not
only when colWidth is 0, but also when it is “very large”. Pick a number
for a “very large” - maybe 2MB or something - it is up to you.
I will think about this more, but I don’t see any better solution for it. If
it is not too much trouble, please provide small example program for me to try.
Thank you.
Alex
Original comment by alex.bra...@gmail.com
on 18 Oct 2014 at 12:13
I cannot reproduce your problem here. I made changes similar to yours:
diff -r 78314be168c8 column.go
--- a/column.go Tue Sep 02 11:28:47 2014 +1000
+++ b/column.go Mon Oct 20 12:43:03 2014 +1100
@@ -192,6 +192,7 @@
}
func NewVariableWidthColumn(b *BaseColumn, ctype api.SQLSMALLINT, colWidth api.SQLULEN) Column {
+ fmt.Printf("col: %+v type: %v width: %v\n", b, ctype, colWidth)
if colWidth == 0 {
b.CType = ctype
return &NonBindableColumn{b}
diff -r 78314be168c8 mssql_test.go
--- a/mssql_test.go Tue Sep 02 11:28:47 2014 +1000
+++ b/mssql_test.go Mon Oct 20 12:43:03 2014 +1100
@@ -1344,3 +1344,21 @@
exec(t, db, "drop table dbo.temp")
}
+
+func TestALEX(t *testing.T) {
+ db, sc, err := mssqlConnect()
+ if err != nil {
+ t.Fatal(err)
+ }
+ defer closeDB(t, db, sc, sc)
+
+ query := fmt.Sprintf("select cast('hello' as xml)")
+ var s string
+ err = db.QueryRow(query).Scan(&s)
+ if err != nil {
+ t.Fatal(err)
+ }
+ if s != "hello" {
+ t.Errorf("expected \"hello\", but received %v", s)
+ }
+}
and new test prints this on both linux and windows:
# go test -mssrv=xxx -msdb=xxx -msuser=xxxx -mspass=xxx -v -run=ALEX
=== RUN TestALEX
col: &{name: CType:0} type: -8 width: 0
--- PASS: TestALEX (0.01s)
PASS
ok code.google.com/p/odbc 0.011s
I use freetds-0.91 and unixODBC-2.3.1 on linux.
Alex
Original comment by alex.bra...@gmail.com
on 20 Oct 2014 at 1:47
I copied your test into my clone and got this:
$ TDSVER=7.2 go test -run TestALEX -mssrv=xxx -msdb=xxx -msuser=xxx -mspass=xxx
col: &{name: CType:0} type: 1 width: 2147483647
PASS
ok code.google.com/p/odbc 0.009s
The client is running CentOS release 6.4 (Final) with FreeTDS 0.91 and
unixODBC-2.3.1 both installed from source. Note that I have to set the TDSVER
to get the test to work at all. I tried to set the default TDSVER during
FreeTDS install, but it didn't seem to take.
The DB server is running MS SQL Server 2012.
Original comment by chris.cs...@gmail.com
on 20 Oct 2014 at 3:46
TDSVER=7.2 does the trick. Thank you.
I will fix it when I have time.
Alex
Original comment by alex.bra...@gmail.com
on 21 Oct 2014 at 5:06
Here https://codereview.appspot.com/161270043 is the fix. Does it work for you?
Alex
Original comment by alex.bra...@gmail.com
on 22 Oct 2014 at 11:19
This solves the out of memory problem, but creates a new problem. When scanning
an XML value with more than 1024 bytes of data the code panics in
NonBindableColumn.Value().
To diagnose I inserted: "fmt.Println(len(b), l)" at column.go:267 and ran a
test program that scans some real XML data from a table. This is the output:
1024 997
1024 379
1024 311
1024 185
1024 648
1024 416
1024 1597
panic: runtime error: slice bounds out of range
goroutine 16 [running]:
runtime.panic(0x57f2a0, 0x887a4f)
/usr/local/go/src/pkg/runtime/panic.c:279 +0xf5
code.google.com/p/odbc.(*NonBindableColumn).Value(0xc20803a038, 0x1ee5900, 0x2,
0x0, 0x0, 0x0, 0x0)
/home/chines/go/src/code.google.com/p/odbc/column.go:268 +0x3ce
code.google.com/p/odbc.(*Rows).Next(0xc20803a040, 0xc20800e7e0, 0x3, 0x3, 0x0,
0x0)
/home/chines/go/src/code.google.com/p/odbc/rows.go:34 +0x1aa
database/sql.(*Rows).Next(0xc208004420, 0xc20800eb40)
/usr/local/go/src/pkg/database/sql/sql.go:1542 +0xe5
main.main()
Note that the same program works fine from a Windows client. It looks like we
are hitting the second part of the problem with FreeTDS mentioned in the link I
provided in my original bug report. Specifically:
> Second, SQLGetData always returns SQL_SUCCESS, even when the buffer is too
> small. It NULL terminates at the given buffer length but gives no
> indication more should be read.
But it seems that var l (the BufferLen) does get the proper length, even when
SQL_SUCCESS is returned. I tried to use that clue to get the rest of the data,
but haven't had any success. I get the feeling that we can't call GetData again
on the same column once it returns SQL_SUCCESS.
Original comment by chris.cs...@gmail.com
on 23 Oct 2014 at 3:19
I agree with your assessment. We could return truncated data silently, but I
think we should just report error instead.
I can see how my suggestion is not helping you.
I can only reproduce your error, if I set TDSVER=7.2 in
$ TDSVER=7.2 go test ...
It works find otherwise, even on linux. Why cannot you do the same?
Alex
Original comment by alex.bra...@gmail.com
on 23 Oct 2014 at 5:20
Starting fresh:
$ cd $GOPATH
$ rm -rf pkg
$ rm -rf src/code.google.com/p/odbc/
$ go get code.google.com/p/odbc
$ go test -run=MS code.google.com/p/odbc -mssrv=xxx -msdb=xxx -msuser=xxx
-mspass=xxx
--- FAIL: TestMSSQLCreateInsertDelete (0.01 seconds)
mssql_test.go:192: db.Prepare("create table dbo.temp (name varchar(20), age int, isGirl bit, weight decimal(5,2), dob datetime, data varbinary(10) null, canBeNull varchar(10) null)") failed: SQLDriverConnect: {08001} [unixODBC][FreeTDS][SQL Server]Unable to connect to data source
{01000} [unixODBC][FreeTDS][SQL Server]Unexpected EOF from the server
{01000} [unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed
--- FAIL: TestMSSQLTransactions (0.00 seconds)
mssql_test.go:192: db.Prepare("create table dbo.temp (name varchar(20))") failed: SQLDriverConnect: {08001} [unixODBC][FreeTDS][SQL Server]Unable to connect to data source
{01000} [unixODBC][FreeTDS][SQL Server]Unexpected EOF from the server
{01000} [unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed
.... <snip> ....
--- FAIL: TestMSSQLSingleCharParam (0.00 seconds)
mssql_test.go:192: db.Prepare("create table dbo.temp(name nvarchar(50), age int)") failed: SQLDriverConnect: {08001} [unixODBC][FreeTDS][SQL Server]Unable to connect to data source
{01000} [unixODBC][FreeTDS][SQL Server]Unexpected EOF from the server
{01000} [unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed
FAIL
FAIL code.google.com/p/odbc 0.210s
$ TDSVER=7.2 go test -run=MS code.google.com/p/odbc -mssrv=CSIADTSA01
-msdb=Borg_QA -msuser=anthill -mspass=4nTh177
ok code.google.com/p/odbc 12.265s
Based on that result, I assumed I would need "TDSVER=7.2" when running my app
as well. In fact, my app does get the same error without TDSVER=7.2 set in the
environment ... unless I set TDS_Version=7.2 in the connection string. With
TDS_Version=7.2 in the connection string my app will connect and retrieve data,
but with code.google.com/p/odbc at tip XML columns use a 2GB buffer. If I then
apply the patch to add "|| colWidth > 1024" to func NewVariableWidthColumn,
then the app panics when it attempts to scan an XML column with more than 1K of
data:
panic: runtime error: slice bounds out of range
goroutine 16 [running]:
runtime.panic(0x57f1e0, 0x8879cf)
/usr/local/go/src/pkg/runtime/panic.c:279 +0xf5
code.google.com/p/odbc.(*NonBindableColumn).Value(0xc20803a038, 0xc44900, 0x2,
0x0, 0x0, 0x0, 0x0)
/home/chines/go/src/code.google.com/p/odbc/column.go:267 +0x338
code.google.com/p/odbc.(*Rows).Next(0xc20803a040, 0xc20800e7e0, 0x3, 0x3, 0x0,
0x0)
/home/chines/go/src/code.google.com/p/odbc/rows.go:34 +0x1aa
database/sql.(*Rows).Next(0xc208004420, 0xc20800eb40)
/usr/local/go/src/pkg/database/sql/sql.go:1542 +0xe5
main.main()
(Note, I use goimports, which adds a blank line in the imports section of
column.go when I manually add the patch, so my column.go:267 is probably your
column.go:266.)
Summary: Without TDSVER=7.2 my app cannot connect to the DB, with TDSVER=7.2 my
app uses too much memory for XML columns, with TDSVER=7.2 and the proposed
patch my app panics on XML columns with more than 1K of data.
Original comment by chris.cs...@gmail.com
on 23 Oct 2014 at 2:12
Moving on to other strategies.
I did some poking around in the FreeTDS code, and it looks like it has proper
support for SQL_WLONGVARCHAR, so I changed my query to cast my XML column like
so:
select ... cast(XMLDATA as nvarchar(max)) ...
With code.google.com/p/odbc at tip, printing the parameters to
NewVariableWidthColumn produces:
col: &{name: CType:0} type: -8 width: 536870911
The key win here is the type coming back as -8 (SQL_WCHAR) because that's what
a Windows client gets for an XML column. Unfortunately the width is still a big
number. The Windows client gets width=0, which triggers the NonBindableColumn
logic. Adding the proposed patch deals with that nicely. I have verified that
the proposed patch in combination with the work around of casting XML columns
to nvarchar(max) works as desired (memory <= to actual data size) and supports
XML data at least 20,305 characters (which used a buffer 39,590 bytes on the
second call to SQLGetData) without any data truncation. I was concerned about
the 8,000 byte limit often associated with varchar types, but that doesn't seem
to be a problem, at least not for basic queries.
Original comment by chris.cs...@gmail.com
on 23 Oct 2014 at 5:49
I have updated https://codereview.appspot.com/161270043 to return error if
buffer overflow.
I still don't see why you cannot connect without TDSVER environment variable
set. I tried connecting to MS SQL Server version 12 here, and I have no problem.
Up to you, but maybe you can check your log file - just set
TDSDUMP=/tmp/freetds.log or similar.
You also might try latest dev version of freetds.
Also
http://www.mssqltips.com/sqlservertip/2198/determine-which-version-of-sql-server
-data-access-driver-is-used-by-an-application might be helpful.
Please, review https://codereview.appspot.com/161270043 , and I will submit it.
Thank you.
Alex
Original comment by alex.bra...@gmail.com
on 24 Oct 2014 at 12:22
Thanks for the tip about TDSDUMP.
The TDSVER environment variable is needed because my FreeTDS install defaults
to TDS version 5.0 (as shown by `tsql -C`). I don't know why either, I followed
these directions: https://code.google.com/p/odbc/wiki/InstallingFreeTDS.
Setting the version in the connection string works just fine, so I'm not going
to lose sleep over it.
Original comment by chris.cs...@gmail.com
on 24 Oct 2014 at 2:32
This issue was closed by revision c7df2c6eae12.
Original comment by alex.bra...@gmail.com
on 26 Oct 2014 at 11:35
Original issue reported on code.google.com by
chris.cs...@gmail.com
on 17 Oct 2014 at 4:11