FreeTDS Development Group

["Chris McDaniel" <Chris.McDaniel AT telus.com>]

Hello,

I've gotten around the print statements, they were only used in test
procedures, and as I mentioned I get results back when the stored procedures
just call SELECTs.  I am now trying to get returns from @params declared
OUTPUT, with no luck...  Can anyone point me in the right direction?

My code (fragment):
----------------------------
$DDI = 1;
$CPIN = 0;
$MPIN = 0;
$Info = " ";
$ResRef = 0;
$ConfRef = 0;
$ActDuration = 0;
$AvailInd = 0;

$sth = $dbh->prepare('execute p_ccs_MakeBooking @ClientAccount="xxxxxx",
@PaymentNumber="xxxxxx", @ConferenceName="Scripts: 06:47 Booked",
@ConferenceComments="Created by Chris McDaniel", @ConferenceType=1,
@LanguageType=1, @PriorityType=2, @StartDateTime="2003-02-01 12:30:00",
@Duration=10, @Conferees=3, @SubConferees=0, @TollFreeInd=1,
@RecordingInd=1, @EnterToneInd=1, @LeavingToneInd=1, @QandAInd=1,
@PollingInd=1, @RollCallInd=1, @BroadcastInd=1, @DemandInd=0,
@ModeratorInd=1, @AutoPINInd=1, @DDI=?, @CPIN=?, @MPIN=?,
@RecordingPIN="555", @Information=?, @ReservationRef=?, @ConferenceRef=?,
@ActualDuration=?, @AvailableInd=?');

$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();
...
warn $sth->err. " ", $sth->errstr if ($sth->err);
$sth->finish();

# close DB connection
$dbh->disconnect();

print "DDI ", $DDI,"\n";
print "CPIN ", $CPIN,"\n";
print "MPIN ", $MPIN,"\n";
print "Info ", $Info,"\n";
print "ResRef ", $ResRef,"\n";
print "ConfRef ", $ConfRef,"\n";
print "ActDuration ", $ActDuration,"\n";
print "AvailInd ", $AvailInd,"\n";
--------------------------------------------

I just get the initial values set near the top back at the end - they are
not modified in the course of the ->execute();

The driver trace is quite long, so I'l summarize, if somebody wants the
whole thing, let me know.
-------------------------------------------
Sending packet @ 2003-01-02 16:50:00.538556
0000  01 00 02 00 00 00 00 00  65 78 65 63 75 74 65 20   |........ execute |
0010  70 5f 63 63 73 5f 4d 61  6b 65 42 6f 6f 6b 69 6e   |p_ccs_Ma keBookin|
0020  67 20 40 43 6c 69 65 6e  74 41 63 63 6f 75 6e 74   |g @Clien tAccount|
0030  3d 22 33 36 32 31 31 38  22 2c 20 40 50 61 79 6d   |="xxxxxx ", @Paym|
0040  65 6e 74 4e 75 6d 62 65  72 3d 22 38 30 38 30 38   |entNumbe r="xxxxx|
0050  30 22 2c 20 40 43 6f 6e  66 65 72 65 6e 63 65 4e   |x", @Con ferenceN|
0060  61 6d 65 3d 22 53 63 72  69 70 74 73 3a 20 30 36   |ame="Scr ipts: 06|
0070  3a 34 37 20 42 6f 6f 6b  65 64 22 2c 20 40 43 6f   |:47 Book ed", @Co|
0080  6e 66 65 72 65 6e 63 65  43 6f 6d 6d 65 6e 74 73   |nference Comments|
0090  3d 22 43 72 65 61 74 65  64 20 62 79 20 43 68 72   |="Create d by Chr|
00a0  69 73 20 4d 63 44 61 6e  69 65 6c 22 2c 20 40 43   |is McDan iel", @C|
00b0  6f 6e 66 65 72 65 6e 63  65 54 79 70 65 3d 31 2c   |onferenc eType=1,|
00c0  20 40 4c 61 6e 67 75 61  67 65 54 79 70 65 3d 31   | @Langua geType=1|
00d0  2c 20 40 50 72 69 6f 72  69 74 79 54 79 70 65 3d   |, @Prior ityType=|
00e0  32 2c 20 40 53 74 61 72  74 44 61 74 65 54 69 6d   |2, @Star tDateTim|
00f0  65 3d 22 32 30 30 33 2d  30 32 2d 30 31 20 31 32   |e="2003- 02-01 12|
0100  3a 33 30 3a 30 30 22 2c  20 40 44 75 72 61 74 69   |:30:00",  @Durati|
0110  6f 6e 3d 31 30 2c 20 40  43 6f 6e 66 65 72 65 65   |on=10, @ Conferee|
0120  73 3d 33 2c 20 40 53 75  62 43 6f 6e 66 65 72 65   |s=3, @Su bConfere|
0130  65 73 3d 30 2c 20 40 54  6f 6c 6c 46 72 65 65 49   |es=0, @T ollFreeI|
0140  6e 64 3d 31 2c 20 40 52  65 63 6f 72 64 69 6e 67   |nd=1, @R ecording|
0150  49 6e 64 3d 31 2c 20 40  45 6e 74 65 72 54 6f 6e   |Ind=1, @ EnterTon|
0160  65 49 6e 64 3d 31 2c 20  40 4c 65 61 76 69 6e 67   |eInd=1,  @Leaving|
0170  54 6f 6e 65 49 6e 64 3d  31 2c 20 40 51 61 6e 64   |ToneInd= 1, @Qand|
0180  41 49 6e 64 3d 31 2c 20  40 50 6f 6c 6c 69 6e 67   |AInd=1,  @Polling|
0190  49 6e 64 3d 31 2c 20 40  52 6f 6c 6c 43 61 6c 6c   |Ind=1, @ RollCall|
01a0  49 6e 64 3d 31 2c 20 40  42 72 6f 61 64 63 61 73   |Ind=1, @ Broadcas|
01b0  74 49 6e 64 3d 31 2c 20  40 44 65 6d 61 6e 64 49   |tInd=1,  @DemandI|
01c0  6e 64 3d 30 2c 20 40 4d  6f 64 65 72 61 74 6f 72   |nd=0, @M oderator|
01d0  49 6e 64 3d 31 2c 20 40  41 75 74 6f 50 49 4e 49   |Ind=1, @ AutoPINI|
01e0  6e 64 3d 31 2c 20 40 44  44 49 3d 27 31 27 2c 20   |nd=1, @D DI='1', |
01f0  40 43 50 49 4e 3d 27 30  27 2c 20 40 4d 50 49 4e   |@CPIN='0 ', @MPIN|


Sending packet @ 2003-01-02 16:50:00.542906
0000  01 01 00 a8 00 00 00 00  3d 27 30 27 2c 20 40 52   |........ ='0', @R|
0010  65 63 6f 72 64 69 6e 67  50 49 4e 3d 22 35 35 35   |ecording PIN="555|
0020  22 2c 20 40 49 6e 66 6f  72 6d 61 74 69 6f 6e 3d   |", @Info rmation=|
0030  27 20 20 20 20 20 20 20  20 20 20 20 20 20 20 20   |'                |
0040  20 20 20 20 20 20 20 20  20 20 20 20 20 20 20 20   |                 |
0050  20 20 20 20 20 20 20 20  20 20 20 20 20 20 27 2c   |               ',|
0060  20 40 52 65 73 65 72 76  61 74 69 6f 6e 52 65 66   | @Reserv ationRef|
0070  3d 30 2c 20 40 43 6f 6e  66 65 72 65 6e 63 65 52   |=0, @Con ferenceR|
0080  65 66 3d 30 2c 20 40 41  63 74 75 61 6c 44 75 72   |ef=0, @A ctualDur|
0090  61 74 69 6f 6e 3d 30 2c  20 40 41 76 61 69 6c 61   |ation=0,  @Availa|
00a0  62 6c 65 49 6e 64 3d 30                            |bleInd=0|


Received header @ 2003-01-02 16:50:00.828606
0000  04 00 02 00 00 00 00 00                            |........|


Received packet @ 2003-01-02 16:50:00.829025
0000  ff 00 51 00 c1 00 00 00  01 ff 00 51 00 c1 00 00   |..Q..... ...Q....|
0010  00 01 ff 00 51 00 c1 00  00 00 01 ff 00 51 00 c1   |....Q... .....Q..|
0020  00 00 00 01 ff 00 51 00  c1 00 00 00 01 ff 00 51   |......Q. .......Q|
0030  00 c1 00 00 00 01 ff 00  51 00 c1 00 00 00 01 ff   |........ Q.......|
0040  00 51 00 c1 00 00 00 01  ff 00 51 00 c1 00 00 00   |.Q...... ..Q.....|
0050  01 ff 00 51 00 c1 00 00  00 01 ff 00 51 00 c1 00   |...Q.... ....Q...|
0060  00 00 01 ff 00 51 00 c1  00 00 00 01 ff 00 51 00   |.....Q.. ......Q.|
0070  c1 00 00 00 01 ff 00 51  00 c1 00 00 00 01 ff 00   |.......Q ........|
0080  51 00 c1 00 00 00 01 ff  00 51 00 c1 00 00 00 01   |Q....... .Q......|
0090  ff 00 51 00 c1 00 00 00  01 ff 00 51 00 c1 00 00   |..Q..... ...Q....|
00a0  00 01 ff 00 51 00 c1 00  00 00 01 fe 00 09 00 e0   |....Q... ........|
00b0  00 00 00 01 ff 00 41 00  c0 00 00 00 00 ff 00 51   |......A. .......Q|
00c0  00 c1 00 00 00 01 ff 00  41 00 c0 00 00 00 00 ff   |........ A.......|
00d0  00 51 00 c1 00 00 00 01  ff 00 41 00 c0 00 00 00   |.Q...... ..A.....|
00e0  00 ff 00 41 00 c0 00 00  00 00 ff 00 41 00 c0 00   |...A.... ....A...|
00f0  00 00 00 ff 00 51 00 c3  00 00 00 01 ff 00 51 00   |.....Q.. ......Q.|
0100  c1 00 00 00 01 ff 00 41  00 c0 00 00 00 00 ff 00   |.......A ........|
0110  51 00 c1 00 00 00 01 ff  00 51 00 c1 00 00 00 01   |Q....... .Q......|
0120  ff 00 51 00 c1 00 00 00  01 ff 00 51 00 c1 00 00   |..Q..... ...Q....|
0130  00 01 ff 00 51 00 c1 00  00 00 01 ff 00 51 00 c1   |....Q... .....Q..|
0140  00 00 00 01 ff 00 51 00  c1 00 00 00 01 ff 00 51   |......Q. .......Q|
0150  00 c1 00 00 00 01 ff 00  51 00 c1 00 00 00 01 ff   |........ Q.......|
0160  00 51 00 c1 00 00 00 01  ff 00 51 00 c1 00 00 00   |.Q...... ..Q.....|
0170  01 ff 00 51 00 c1 00 00  00 01 ff 00 51 00 c1 00   |...Q.... ....Q...|
0180  00 00 01 ff 00 51 00 c1  00 00 00 01 ff 00 51 00   |.....Q.. ......Q.|
0190  c1 00 00 00 01 ff 00 51  00 c1 00 00 00 01 ff 00   |.......Q ........|
01a0  51 00 c1 00 00 00 01 ff  00 51 00 c1 00 00 00 01   |Q....... .Q......|
01b0  ff 00 51 00 c1 00 00 00  01 ff 00 51 00 c1 00 00   |..Q..... ...Q....|
01c0  00 01 ff 00 51 00 c1 00  00 00 01 ff 00 51 00 c1   |....Q... .....Q..|
01d0  00 00 00 01 fe 00 09 00  e0 00 00 00 01 ff 00 41   |........ .......A|
01e0  00 c0 00 00 00 00 ff 00  41 00 c0 00 00 00 00 ff   |........ A.......|
01f0  00 51 00 c1 00 00 00 01                            |.Q......|



2003-01-02 16:50:00.833150 processing result tokens.  marker is  ff
2003-01-02 16:50:00.833447 inside tds_process_end() more_results = 1,
was_cancel
led = 0
--------------
Those three lines repeat 47 times
--------------
Received header @ 2003-01-02 16:50:00.867484
0000  04 00 02 00 00 00 00 00                            |........|


Received packet @ 2003-01-02 16:50:00.867863
0000  ff 00 41 00 c0 00 00 00  00 ff 00 51 00 c1 00 00   |..A..... ...Q....|
0010  00 01 ff 00 51 00 c1 00  00 00 01 ff 00 51 00 c1   |....Q... .....Q..|
0020  00 00 00 01 ff 00 51 00  c1 00 00 00 01 ff 00 51   |......Q. .......Q|
0030  00 c1 00 00 00 01 ff 00  51 00 c1 00 00 00 01 ff   |........ Q.......|
0040  00 51 00 c1 00 00 00 01  ff 00 51 00 c1 00 00 00   |.Q...... ..Q.....|
0050  01 ff 00 51 00 c1 00 00  00 01 ff 00 51 00 c1 00   |...Q.... ....Q...|
0060  00 00 01 ff 00 51 00 c1  00 00 00 01 ff 00 51 00   |.....Q.. ......Q.|
0070  c1 00 00 00 01 ff 00 51  00 c1 00 00 00 01 ff 00   |.......Q ........|
0080  51 00 c1 00 00 00 01 ff  00 51 00 c1 00 00 00 01   |Q....... .Q......|
0090  ff 00 51 00 c1 00 00 00  01 ff 00 51 00 c1 00 00   |..Q..... ...Q....|
00a0  00 01 ff 00 51 00 c1 00  00 00 01 ff 00 51 00 c1   |....Q... .....Q..|
00b0  00 00 00 01 ff 00 51 00  c1 00 00 00 01 ff 00 51   |......Q. .......Q|
00c0  00 c1 00 00 00 01 ff 00  51 00 c1 00 00 00 01 ff   |........ Q.......|
00d0  00 51 00 c1 00 00 00 01  ff 00 51 00 c1 00 00 00   |.Q...... ..Q.....|
00e0  01 fe 00 09 00 e0 00 00  00 01 ff 00 51 00 c1 00   |........ ....Q...|
00f0  00 00 01 ff 00 51 00 c1  00 00 00 01 ff 00 41 00   |.....Q.. ......A.|
0100  c0 00 00 00 00 ff 00 41  00 c0 00 00 00 00 ff 00   |.......A ........|
0110  51 00 c1 00 00 00 01 ff  00 51 00 c1 00 00 00 01   |Q....... .Q......|
0120  ff 00 41 00 c0 00 00 00  00 ff 00 41 00 c0 00 00   |..A..... ...A....|
0130  00 00 ff 00 41 00 ca 00  00 00 00 ff 00 41 00 c0   |....A... .....A..|
0140  00 00 00 00 ff 00 51 00  c1 00 00 00 01 ff 00 41   |......Q. .......A|
0150  00 c0 00 00 00 00 ff 00  51 00 c1 00 00 00 01 ff   |........ Q.......|
-------------
and that sort of repeats, the "A.....Q.....A....Q" section followed by the
"2003-01-02 16:50:00.866827 processing result tokens.  marker is  ff"
section

The last few lines of the trace:
------------
2003-01-02 16:50:01.406948 processing result tokens.  marker is  79
2003-01-02 16:50:01.407253 processing result tokens.  marker is  fe
2003-01-02 16:50:01.407535 inside tds_process_end() more_results = 0,
was_cancel
led = 0
2003-01-02 16:50:01.407850 inside tds_process_result_tokens() state is
COMPLETED
SQLFreeHandle(2, 0xfe880)
SQLFreeHandle(1, 0xf04b0)
------------

Any ideas?

-----Original Message-----
From: Lowden, James K [mailto:LowdenJK AT bernstein.com]
Sent: January 2, 2003 12:36 PM
To: 'freetds AT lists.ibiblio.org'
Subject: RE: [freetds] DBD::ODBC (0.45), MS SQL, unixODBC, FreeTDS
(CVS), bind_param_ino ut


> From: Chris McDaniel [mailto:Chris.McDaniel AT telus.com]
> Sent: January 2, 2003 10:09 AM
> 
> It looks like result sets
> are coming back OK now which is great

Good! :)  

> looks like I'm missing output where
> the stored proc uses the (T-SQL) 'print' statement.  

A "print" statement's output would be passed as an out-of-band
server-originated message, not as data, similar to the "Changed language
setting to us_english." message you may see when you log on.  One small
difference: "print" statement messages have a "message number" of zero.  

In db-lib, the client provides a "message handler" callback function that
the library invokes whenever a server message arrives.  I don't know how
that's done in ODBC, or how well we support it.  

tsql used to ignore them; I just committed a change to print them instead.  

--jkl

> 
> Thanks,
> 
> Chris
> 
> -----Original Message-----
> From: James K. Lowden [mailto:jklowden AT schemamania.org]
> Sent: January 1, 2003 9:11 PM
> To: freetds AT lists.ibiblio.org
> Subject: Re: [freetds] DBD::ODBC (0.45), MS SQL, unixODBC, FreeTDS
> (CVS), bind_param_ino ut
> 
> 
> On Wed, 1 Jan 2003 09:00:25 -0700, "Chris McDaniel"
> <Chris.McDaniel AT telus.com> wrote:
> > 
> > I got a fresh cvs checkout, recompiled and tried it again. 
> > Unfortunately I cannot use tdsver 7.0 as it is an MS SQL 
> 6.5 server and
> > I get login failure if I try higher tds versions (and a 
> core dump).  As
> > before, I still get no results back from the proc.  
> 
> Chris:  Does "no results back from the proc" mean no result 
> set (no data)
> or no return code?
> 
> Frediano:  Our return code processing is broken in ct-lib; it may be
> affecting ODBC, too.    
> 
> I thought we needed a unittest for return code processing, so 
> I modified
> src/ctlib/unittests/t0002.c.  It calls "exec sp_who" and 
> prints the result
> set and the return code status (which of course should always 
> be zero for
> sp_who).  
> 
> I'm no ct-lib maven; I just tried to follow instructions and 
> rely on the
> rest of the unittests for examples.  AFAICT, I've coded it 
> correctly, but
> ct_results() fails to retreive the return code. 
> tds_process_result_tokens() does not place the return code in 
> the buffer
> provided by ct_bind(), so the return code cannot be retrieved with
> ct_fetch().  
> 
> The sequence of events:
> 
> 1.  sp_who result set arrives and is processed normally (7 columns).
> 2.  ct_results() is called again.  
> 3.  TDS_RETURNSTATUS_TOKEN (0x79) arrives.  
> 4.  tds_process_result_tokens() records the status in the 
> TDSSOCKET and
> returns a type 4043.  It should do more: set the column count 
> and create
> the row.  
> 5.  ct_results() returns CS_STATUS_RESULT, indicating a status row is
> available.  
> 6.  t0002.c calls ct_describe(), which says there are (still) 
> 7 columns, a
> lie.  
> 7.  t0002.c calls ct_fetch().  It doesn't know it's supposed 
> to return a
> status row.  It calls tds_process_row_tokens() and returns CS_END_DATA
> without returning any rows.  
> 
> Nota Bene.  Other recent logs have shown 
> TDS_RETURNSTATUS_TOKEN markers
> arriving from a TDS 8.0 server when *no* stored procedure was 
> executed. 
> Return code processing will have to tolerate that.  We'll 
> have to set up
> the rows for retrieval and discard them if they're not requested.  
> 
> HTH.
> 
> --jkl
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
> 
> 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
> 


The information contained in this transmission may contain privileged and
confidential information and is intended only for the use of the person(s)
named above.  If you are not the intended recipient, or an employee or agent
responsible for delivering this message to the intended recipient, any
review, dissemination, distribution or duplication of this communication is
strictly prohibited. If you are not the intended recipient, please contact
the sender immediately by reply e-mail and destroy all copies of the
original message. Please note that we do not accept account orders and/or
instructions by e-mail, and therefore will not be responsible for carrying
out such orders and/or instructions.


_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds