go-mssqldb
go-mssqldb copied to clipboard
denisenkom
Table Valued Parameters support
Is there a way to pass table valued parameters to stored procedures using go-mssqldb? I briefly checked the code and couldn't find TVP support, just want to double check if I missed it.
https://msdn.microsoft.com/en-us/library/dd304813.aspx
There is no TVP support at this time. Support will now be possible starting in go1.9 due to the recent work to allow driver specific parameters. So this is an optimal time to start investigating them.
TVP is very expected feauture. It will be awesome to implement it in golang!
@vadimi @kardianos @alexbrainman @denisenkom Any advice on where to get started? I've wanted this feature for almost 2 years, and I'm keen to get this year's Hacktoberfest t-shirt 😄
Where the right place to hook in would be a good start. I can probably figure out the wire protocol, TDS is pretty well documented.
@judwhite I am not an expert in this area (I have never heard about TVP until now), so take my advise with a grain of salt.
Do other common databases (Mysql, PostgreSQL, IBM DB2, Oracle and others) support TVP? If not, is it worth making database/sql and database/sql/driver syntax and semantics complicated just for one single vendor?
As to where to start to make this change, It has been a while since I looked at database/sql package, so hopefully @kardianos will help you here.
Other then that, I suggest you look at the database/sql code and see how it can be implemented yourself - your guess is as good as everyone else. If you come up with some plan, I suggest you discuss it here first, so you don't waste too much time and then your code will be rejected.
Feel free to ask questions here.
Alex
@judwhite For input parameter support, you'll want to add code to: https://github.com/denisenkom/go-mssqldb/blob/master/mssql_go19.go#L14 CheckNamedValue switch.
Due to how TVP are rather custom types, you'll probably want to define some type of wrapper type that the CheckNamedValue method can recognize. You can also have that type implement the Scanner type so you can scan parameters into it too.
You'll want to decide if you want to support an array of structs using reflection or using some type of generic table structure. I have a generic table structure here you could use as a base for a generic structure: https://godoc.org/bitbucket.org/kardianos/table if you want to support that direction.
@judwhite @alexbrainman Just to be clear, no changes are needed in database/sql or database/sql/driver as of go1.9 to support parameters such as TVP. Custom types, both in and out, are fully possible now.
Just to be clear, no changes are needed in database/sql or database/sql/driver as of go1.9 to support parameters such as TVP. Custom types, both in and out, are fully possible now.
SGTM
I did not know about that. And I did not want to confuse people.
Alex
@kardianos @alexbrainman Thanks for the guidance, I'll make a [WIP] PR shortly or continue the conversation here if there are any fundamental issues.
I have never heard about TVP until now
@alexbrainman It's been a while, at least 😄 https://groups.google.com/d/msg/golang-nuts/_stSBcCkwyE/IbWSv0KvEdEJ I agree with ilusharulkov, it's an expected feature when working with MS SQL Server. Porting code is more difficult than it should be if the old code leveraged TVPs. TVPs are often parameters in stored procs and used with MERGE for batch "upserts".
Thanks again. I'll get to this this week.
Porting code is more difficult than it should be if the old code leveraged TVPs. TVPs are often parameters in stored procs and used with MERGE for batch "upserts".
Thanks for explaining.
Alex
@ilusharulkov No news yet. Keep pinging me, I may have some time next week.
This commit added it to the python mssql driver. https://github.com/denisenkom/pytds/commit/e33f91f4722dd6fce41c5564f3ae54351f9d0a51
@judwhite did you get anywhere with this? id be happy to take a partial solution i can continue :)
TVP is working in my pull request, it would be good if a few people could try it, see how it goes.
Since https://github.com/denisenkom/go-mssqldb/pull/447 was merged on 4 Feb 2019, should we consider this issue as fixed?
