DBD::ODBC, bind_param

ixemul has asked for the wisdom of the Perl Monks concerning the following question:

Hello,
I'm using DBD::ODBC with unixODBC and FreeTDS from CVS (today's special blend...)
I have the following code:

$DDI = 1;
$CPIN = 0;
$MPIN = 0;
$Info = " ";
$ResRef = 0;
$ConfRef = 0;
$ActDuration = 0;
$AvailInd = 0;
$rc = 0;
$dbh->do('use BSRes2');
$sth = $dbh->prepare('
       declare @DDI UDTDDI, @CPIN UDTPIN, @MPIN UDTPIN, @Information U
+DTComment, 
@ReservationRef int, @ConferenceRef int, @ActualDuration int, @Availab
+leInd UDTFlag
       select @DDI = ?, @CPIN = ?, @MPIN = ?, @Information = ?, @Reser
+vationRef = ?, 
@ConferenceRef = ?, @ActualDuration = ?, @AvailableInd = ?
       execute p_ccs_MakeBooking @ClientAccount="xxxxxx", [...] @DDI=@
+DDI OUTPUT, 
@CPIN=@CPIN OUTPUT, @MPIN=@MPIN OUTPUT, @Information=@Information OUTP
+UT, 
@ReservationRef=@ReservationRef OUTPUT, @ConferenceRef=@ConferenceRef 
+OUTPUT, 
@ActualDuration=@ActualDuration OUTPUT, @AvailableInd=@AvailableInd OU
+TPUT');

$sth->bind_param_inout(1, \$DDI, 32, DBI::SQL_VARCHAR);
$sth->bind_param_inout(2, \$CPIN, 12, DBI::SQL_VARCHAR);
$sth->bind_param_inout(3, \$MPIN, 12, DBI::SQL_VARCHAR);
$sth->bind_param_inout(4, \$Info, 255, DBI::SQL_VARCHAR);
$sth->bind_param_inout(5, \$ResRef, 10, DBI::SQL_INTEGER);
$sth->bind_param_inout(6, \$ConfRef, 10, DBI::SQL_INTEGER);
$sth->bind_param_inout(7, \$ActDuration, 10, DBI::SQL_INTEGER);
$sth->bind_param_inout(8, \$AvailInd, 1, DBI::SQL_BIT);
$sth->execute();
[download]

And I see values coming back in the driver trace (small excerpt):

Received packet @ 2003-01-07 01:40:15.715145
0000  43 50 49 4e 01 00 00 00  00 27 0c 05 32 36 31 30   |CPIN.... .'.
+.2610|
0010  39 ac 00 13 05 40 4d 50  49 4e 01 00 00 00 00 27   |9....@MP IN.
+....'|
0020  0c 05 38 33 30 30 37 ac  00 a4 0c 40 49 6e 66 6f   |..83007. ...
+@Info|
0030  72 6d 61 74 69 6f 6e 01  00 00 00 00 27 ff 8f 20   |rmation. ...
+.'..  
0040  20 20 44 44 49 73 20 3a  20 4e 6f 20 44 44 49 20   |  DDIs :  No
+ DDI  
0050  6d 61 74 63 68 65 73 20  74 68 69 73 20 43 6f 6e   |matches  thi
+s Con|
0060  66 65 72 65 6e 63 65 54  79 70 65 2c 20 4c 61 6e   |ferenceT ype
+, Lan|
0070  67 75 61 67 65 54 79 70  65 2c 20 4d 6f 64 65 72   |guageTyp e, 
+Moder|
0080  61 74 6f 72 20 61 6e 64  20 44 65 6d 61 6e 64 20   |ator and  De
+mand  
0090  73 65 6c 65 63 74 69 6f  6e 2e 0d 0a 44 44 49 73   |selectio n..
+.DDIs|
00a0  20 3a 20 20 44 44 49 73  20 3a 20 44 44 49 73 20   | :  DDIs  : 
+DDIs  
00b0  61 72 65 20 69 6e 61 63  74 69 76 65 20 66 6f 72   |are inac tiv
+e for|
00c0  20 31 35 20 6d 69 6e 75  74 65 73 2e 0d 0a ac 00   | 15 minu tes
+.....|
[download]

but the bound variables do not get updated. I suspect it has to do with the way I have the query written, with the placeolders in the 'select' line, but if I put them in the 'execute' line, I get an error about not being able to use the OUTPUT word when passing in a constant, and if I leave OUTPUT out of the statement, no data comes back. Can anyone help me?

Thanks,
Chris McDaniel

Comment on DBD::ODBC, bind_param_inout Select or Download Code

Replies are listed 'Best First'.
Re: DBD::ODBC, bind_param_inout by mpeppler (Vicar) on Jan 07, 2003 at 18:21 UTC
I don't really know how DBD::ODBC works, but based on my understanding of the TDS protocol I suspect that you can't use bind_param_inout() with a multi-statement request (using the declare in the code above makes this a multi-statement request). However, as you don't get an error from DBD::ODBC itself during the prepare() I would think that this is either a bug in DBD::ODBC, or a problem with FreeTDS that doesn't quite handle the OUTPUT parameters as it should (the fact that the data is returned in the driver trace doesn't necessarily mean that the driver decodes the data correctly.) As FreeTDS is very much a work in progress, and as OUTPUT parameters from MS-SQL have only just been added I would suspect the latter. I think you've already posted this to the FreeTDS mailing list - I suggest following up over there... Michael	[reply]
Re: DBD::ODBC, bind_param_inout by ixemul (Novice) on Jan 07, 2003 at 23:27 UTC
As a follow up, I've found a way to get values out of a stored proc without using bind_param_inout - $sth = $dbh->prepare(' declare @DDI UDTDDI, ... @AvailableInd UDTFlag execute p_ccs_MakeBooking @ClientAccount="xxxxxx", ... @DDI=@DDI OUTPUT, @CPIN=@CPIN OUTPUT, ... @AvailableInd=@AvailableInd OUTPUT select @DDI as DDI, @CPIN as CPIN, ... @AvailableInd as AVIND'); Using this query I can just read my outputs using regular fetch statements. Thanks	[reply]