Table Value Parameter for SQL server

--00000000000004909a05ecbc466f
Content-Type: text/plain; charset="UTF-8"

Hi All,

I am attempting to bulk merge a lot of rows quickly to an mssql server and
I understand that table value parameters are a possible option for doing so.

I set up the following test objects on the sql server (modeled after a
python example of sorts from
https://github.com/mkleehammer/pyodbc/issues/595#issuecomment-584761512):

create table testtable (
  id int not null,
  primary key clustered (id asc)
);
GO

create type dbo.testtype as table (
  id int not null,
  primary key clustered (id asc)
);
GO

create procedure dbo.testproc(@tvp dbo.testtype READONLY)
as begin
  set nocount on;
  insert into testtable (id)
  select id from @tvp
end;
GO

And am attempting to use the following script (name tvf):

#!/usr/bin/perl

use DBI;

our $userid;
our $password;
our $dsn;

do( './tvf.conf' );

my $dbh = DBI->connect( $dsn, $userid, $password, { RaiseError => 1 });

my $sth = $dbh->prepare( qq{ exec dbo.testproc ? } );

my $table_values = [
                     [1],
                     [2],
                   ];

$sth->execute( $table_values );

And I receive the following error:
Cannot bind a plain reference at ./tvf line 20.

I believe that error comes from the odbc driver and am uncertain what, if
anything, can be done about it.

My DSN starts with "dbi:ODBC:DRIVER=tds;database=" which I believe means
that this driver is used:
/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

Has anyone had success in passing a tvf from perl via DBI?  Could anyone
share an example, please?

-- 
Thank you.

Regards,
Rich

--00000000000004909a05ecbc466f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi All,<div><br></div><div>I am attempting to bulk merge a=
 lot of rows quickly to an mssql server and I understand that table value p=
arameters are a possible option for doing so.</div><div><br></div><div>I se=
t up the following test objects on the sql server (modeled after a python e=
xample of sorts from=C2=A0<a href=3D"https://github.com/mkleehammer/pyodbc/=
issues/595#issuecomment-584761512">https://github.com/mkleehammer/pyodbc/is=
sues/595#issuecomment-584761512</a>):<br></div><div><br></div><div><span st=
yle=3D"font-family:monospace">create table </span>testtable<span style=3D"f=
ont-family:monospace"> (</span><br></div><div><font face=3D"monospace">=C2=
=A0 id int not null,<br>=C2=A0 primary key clustered (id asc)<br>);<br>GO<b=
r><br>create type dbo.testtype as table (<br>=C2=A0 id int not null, <br>=
=C2=A0 primary key clustered (id asc)<br>);<br>GO<br><br>create procedure d=
bo.testproc(@tvp dbo.testtype READONLY)<br>as begin<br>=C2=A0 set nocount o=
n;<br>=C2=A0 insert into testtable (id)<br>=C2=A0 select id from @tvp<br>en=
d;<br>GO<br></font></div><div><br></div><div>And am attempting to use the f=
ollowing script (name tvf):</div><div><br></div><div><div><font face=3D"mon=
ospace">#!/usr/bin/perl<br><br>use DBI;<br><br>our $userid;<br>our $passwor=
d;<br>our $dsn;<br><br>do( &#39;./tvf.conf&#39; );<br><br>my $dbh =3D DBI-&=
gt;connect( $dsn, $userid, $password, { RaiseError =3D&gt; 1 });<br><br>my =
$sth =3D $dbh-&gt;prepare( qq{ exec dbo.testproc ? } );<br><br>my $table_va=
lues =3D [<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0[1],<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0[2],<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0];<br><br>$sth-&gt;execute( $table_values );=
<br></font></div><div><font face=3D"monospace"><br></font></div><div>And I =
receive the following error:</div><div><span style=3D"font-family:monospace=
">Cannot bind a plain reference at ./tvf line 20.</span><br></div><div><spa=
n style=3D"font-family:monospace"><br></span></div><div>I believe that erro=
r comes from the odbc driver and am uncertain what, if anything, can be don=
e about it.</div><div><br></div><div>My DSN starts with &quot;dbi:ODBC:DRIV=
ER=3Dtds;database=3D&quot; which I believe means that this driver is used:<=
/div><div><font face=3D"monospace">/usr/lib/x86_64-linux-gnu/odbc/libtdsodb=
c.so</font></div><div><br></div><div><span style=3D"font-family:arial,sans-=
serif">Has anyone had success in passing a tvf from perl via DBI?=C2=A0 Cou=
ld anyone share an example, please?</span><br></div><div><font face=3D"aria=
l, sans-serif"><br></font></div>-- <br><div dir=3D"ltr" class=3D"gmail_sign=
ature" data-smartmail=3D"gmail_signature">Thank you.<br><br>Regards,<br>Ric=
h</div></div></div>

--00000000000004909a05ecbc466f--