SQL 2008 passing Table-Value-parameters to stored proc via ODBC
in [Programming SQL]
|
From: Leon McCalla on 19 Dec 2009 22:48 Can we use row-wise binding instead of column binding with TVPs? Basically the C code I have generates a 25 column temp-array with 1-16 rows. The documentation for TVPs seems require 25 separate single column arrays. Help.......... Leon
From: m on 20 Dec 2009 18:46 I have not used TVPs in ODBC, but in C code, it is irrelevant which is necessary since the only effect on the code necessary is the way that the parameters are passed in. Passing parameters by ordinal is the usual practice in C and it is trivial to construct a transpose function - two loops if you want to make it fully portable, and just one if you know about the internal layout of arrays in C "Leon McCalla" <leonmccalla(a)drop.hotmail.com> wrote in message news:#iGGadSgKHA.2104(a)TK2MSFTNGP05.phx.gbl... > Can we use row-wise binding instead of column binding with TVPs? > Basically the C code I have generates a 25 column temp-array with 1-16 > rows. The documentation for TVPs seems require 25 separate single column > arrays. > > Help.......... > > Leon >
From: Leon McCalla on 21 Dec 2009 19:41 thanks for replying but there is a significant difference in the way the info is laid out and passed to the drivers. With typical array binding you indicate to the drivers the first position of each element. as rows of data are passed to the driver, in row-wise binding, the same element in the next row is approximately sizeof(ROW) further along in memory. In column-wise binding, the same element in the next row is approximately sizeof(element) further along in memory. this is why there is a switch for row-wise or column-wise binding. in passing TVP parameters to the driver, all the examples use row-wise binding. If you can show me how to get this to work I'll appreciate a great deal. Leon "m" <m(a)b.c> wrote in message news:O6tl26cgKHA.5568(a)TK2MSFTNGP02.phx.gbl... >I have not used TVPs in ODBC, but in C code, it is irrelevant which is >necessary since the only effect on the code necessary is the way that the >parameters are passed in. > > Passing parameters by ordinal is the usual practice in C and it is trivial > to construct a transpose function - two loops if you want to make it fully > portable, and just one if you know about the internal layout of arrays in > C > > "Leon McCalla" <leonmccalla(a)drop.hotmail.com> wrote in message > news:#iGGadSgKHA.2104(a)TK2MSFTNGP05.phx.gbl... >> Can we use row-wise binding instead of column binding with TVPs? >> Basically the C code I have generates a 25 column temp-array with 1-16 >> rows. The documentation for TVPs seems require 25 separate single >> column arrays. >> >> Help.......... >> >> Leon >>
From: m on 21 Dec 2009 20:04 Maybe I am confused, but I assumed that you were writing some parameter mapping logic and the input array was columns of rows whereas the needed format for TVPs is rows of columns. If that is the case, then assuming that the total size of the array is reasonable, then you can simply transpose the array and use it (If the array is large, then you can use data at exec and pass it in pieces to avoid double buffering) I have never used TVPs in ODBC, but the transpose (piecewise or complete) is simple and requires only memcpy + modulus in C (or x86 / x64 ASM) "Leon McCalla" <leonmccalla(a)drop.hotmail.com> wrote in message news:#NXR19pgKHA.3888(a)TK2MSFTNGP02.phx.gbl... > thanks for replying but there is a significant difference in the way the > info is laid out and passed to the drivers. With typical array binding you > indicate to the drivers the first position of each element. as rows of > data are passed to the driver, in row-wise binding, the same element in > the next row is approximately sizeof(ROW) further along in memory. In > column-wise binding, the same element in the next row is approximately > sizeof(element) further along in memory. this is why there is a switch for > row-wise or column-wise binding. > > in passing TVP parameters to the driver, all the examples use row-wise > binding. If you can show me how to get this to work I'll appreciate a > great deal. > > Leon > > > "m" <m(a)b.c> wrote in message news:O6tl26cgKHA.5568(a)TK2MSFTNGP02.phx.gbl... >>I have not used TVPs in ODBC, but in C code, it is irrelevant which is >>necessary since the only effect on the code necessary is the way that the >>parameters are passed in. >> >> Passing parameters by ordinal is the usual practice in C and it is >> trivial to construct a transpose function - two loops if you want to make >> it fully portable, and just one if you know about the internal layout of >> arrays in C >> >> "Leon McCalla" <leonmccalla(a)drop.hotmail.com> wrote in message >> news:#iGGadSgKHA.2104(a)TK2MSFTNGP05.phx.gbl... >>> Can we use row-wise binding instead of column binding with TVPs? >>> Basically the C code I have generates a 25 column temp-array with 1-16 >>> rows. The documentation for TVPs seems require 25 separate single >>> column arrays. >>> >>> Help.......... >>> >>> Leon >>> > >
From: Michael Coles on 21 Dec 2009 20:58 OK, you say you have C code. Would that be C++ (managed/unmanaged), C#, or do you really mean unmanaged "C"? If you really do mean unmanaged "C", pop the link up here to the docs you're looking at. If you're actually using managed code (managed C++ or C#), your best bet is probably to use a .NET DataTable. -- Thanks Michael Coles SQL Server MVP Author, "Expert SQL Server 2008 Encryption" (http://www.apress.com/book/view/1430224649) ---------------- "Leon McCalla" <leonmccalla(a)drop.hotmail.com> wrote in message news:%23iGGadSgKHA.2104(a)TK2MSFTNGP05.phx.gbl... > Can we use row-wise binding instead of column binding with TVPs? > Basically the C code I have generates a 25 column temp-array with 1-16 > rows. The documentation for TVPs seems require 25 separate single column > arrays. > > Help.......... > > Leon >
|
Next
|
Last
Pages: 1 2 Prev: How do I creat a auto increment for my key field Next: loop between 2 datetime field values |