Conference orarep::nomahs::sql_services

    I am trying to set up a system for testing this. I think I have the
    kit, but I can't find any installation instructions. Could someone give
    me a pointer?
    
    The kit I have is SQSOSFCLI.TAR. When I unpack it with tar I get
    INSTCTRL, SQSOSFCLI610, and instctrl created.
    
    Peter

    
    http://longs.us.oracle.com:8000/htbin/rdbbooks
    
    choose 6.1  then Installation Guide OSF Final
    
    I don't know if there is one for 7.0 on the net, but that should give
    you an idea. 
    
    /Regina
    

    It needed the SQL Services Installation and Configuration Guide
    appendix F.
    
    It failed saying
    
>    For SQSOSFCLI610 to be installed, you must install OSFBASE3xx
>    or higher.
    
    We believe this is because the system has V4 of DEC UNIX. I will try
    again on a system running V3.2.
    
    Peter

    6.1 will not install on Unix V4.0, personal experience.
    
    And 7 needs 4.0A or higher, personal experience as well (the A or
    higher for a bugfix we need, we run with 4.0B).
    
    /Regina
    

    Does the Digital UNIX client include the demo program? If so where does
    it put it? Can you give me instructions for building it? (You may
    assume that I know very little about UNIX :-)
    
    Peter

   The demo is in /usr/opt/SQS610/examples/.
   I got built by doing the following
   
   cp /usr/opt/SQS610/examples/sqsdrvu.c sqsdrvu.c
   cp /usr/opt/SQS610/examples/sqsdynu.c sqsdynu.c
   cp /usr/opt/SQS610/examples/sqsosfsample.mak sqsosfsample.mak
   make -f sqsosfsample.mak
   
   The program is called sqsdynu.
   
   Peter

    I can fetch 64k+1 rows using sqsdynu. The customer is going to send me
    his source code.
    
    Peter

    I can't think of any reason why they should be having a problem
    fetching more that 64k 'rows', but they'll definitely have a problem if
    they try to fetch a bunch of rows that requires more than 64k
    SQL/Services network 'packets'. The SQL/Services protocol uses a 16-bit 
    integer to store the packet sequence number, which starts at 0 and
    increments by 1 for each continuation packet in a request or response.
    If this wraps back to 0 again, then the protocol will fail, because a 0
    value indicates the start of a new request or response so the parse
    code resets itself to start parsing what it expects to be the beginning
    of a new request or response. Hopefully they're not running into this,
    as that's an awful lot of packets and it clearly won't go very fast.
    Howeve, they might want to try increasing their packet size to see if
    it has any effect. 
    
    Si

>    of a new request or response. Hopefully they're not running into this,
>    as that's an awful lot of packets and it clearly won't go very fast.
>    Howeve, they might want to try increasing their packet size to see if
>    it has any effect. 
    
    Their network is a fibre optic link between 2 Alphas. If he changes the
    repeat count for fetch_many from 0 (all) to 32k-1 (the max) he can
    fetch 70k rows in 46s seconds.
    
    I've asked for a potion of a client log to see how many rows he is
    getting per packet. He has the buffer size set to 5000.
    
    My test database had very small rows.
    
    They would like the restriction on the number of packets documented,
    and would like to be able to specify a higher repeat count for
    fetch_many.
    
    Peter

    Their packet size is 4996. There are at least 11 records in the first
    packet.
    
    They are not using the version they told me.
    
    ROUTINE LEVEL LOG at 11:18:00 on 10-Mar-1997
    ----FETCH ROW NUMBER
    --------SQLSRV_GENERALIZED_NUMBER, len: 5
    ------------len: 5, value: 65535
    ----SELECT LIST DATA
    --------len: 2, value: 1
    ----SQLVAR
    --------len: 2, value: 0
    ----SQLDATA
    --------SQLSRV_GENERALIZED_NUMBER, len: 7
    ------------len: 7, value: 4096295
    ----SQLIND
    --------SQLSRV_GENERALIZED_NUMBER, len: 1
    ------------len: 1, value: 0
    ------------END OF DATA
    
    ROUTINE LEVEL LOG at 11:18:00 on 10-Mar-1997
    ----SELECT LIST SQLDA
    --------SQLDA: SQLD 1
    ------------[0].SQLTYPE: SQLSRV_GENERALIZED_NUMBER, SQLIND: 0
    ----------------len: 7, value: 4096295
    
    ROUTINE LEVEL LOG at 11:18:00 on 10-Mar-1997
    ----FETCH ROW NUMBER
    --------SQLSRV_GENERALIZED_NUMBER, len: 5
    ------------len: 5, value: 65536
    ----SELECT LIST DATA
    --------len: 2, value: 1
    ----SQLVAR
    --------len: 2, value: 0
    ----SQLDATA
    --------SQLSRV_GENERALIZED_NUMBER, len: 7
    ------------len: 7, value: 4096296
    ----SQLIND
    --------SQLSRV_GENERALIZED_NUMBER, len: 1
    ------------len: 1, value: 0
    --------END OF MESSAGE
    
    ROUTINE LEVEL LOG at 11:18:00 on 10-Mar-1997
    ----SELECT LIST SQLDA
    --------SQLDA: SQLD 1
    ------------[0].SQLTYPE: SQLSRV_GENERALIZED_NUMBER, SQLIND: 0
    ----------------len: 7, value: 4096296
    
    ROUTINE LEVEL LOG at 11:18:00 on 10-Mar-1997
    
    Internal error -2007 in SQL/Services client version T7.0-07 at line
    1271 in module SRVPRSMS
    Error_text: unexpected end-of-buffer at 14000f151 end 14000f151
    starting parse
    
    ROUTINE LEVEL LOG at 11:18:00 on 10-Mar-1997
    ----SQLSRV_CLOSE_CURSOR
    --------CURSOR NAME
    ------------SEL
    

The log file helped a lot. It turns out the problem is not with the row number
coming back, which was all I could think of, but with the total number of rows.

The reason the client is getting an error is that it is expecting more data
while the server thinks it has finished...

    ROUTINE LEVEL LOG at 11:18:00 on 10-Mar-1997
    ----FETCH ROW NUMBER
    --------SQLSRV_GENERALIZED_NUMBER, len: 5
    ------------len: 5, value: 65535
    ----SELECT LIST DATA
    --------len: 2, value: 1
    ----SQLVAR
    --------len: 2, value: 0
    ----SQLDATA
    --------SQLSRV_GENERALIZED_NUMBER, len: 7
    ------------len: 7, value: 4096295
    ----SQLIND
    --------SQLSRV_GENERALIZED_NUMBER, len: 1
    ------------len: 1, value: 0
    ------------END OF DATA		    <-- end of data for this response

...but, the client is still expecting to parse more fetch data when it gets the
next message from the server, hence the parse error.

However, the fundamental reason for this problem, the reason why the server
thinks that it has finished after row 65535, is that the server uses a 16-bit
integer to keep track of the current row number. The server stops sending when
the current row count = the number of rows requested. To fetch all rows, the
client specifies 0. Therefore, when the 16-bit current row count wraps back to
0 from 65535, the server thinks it has got to the end. I'd expect this to be a
problem any version of the server; this code, or rather the definition of the
row counts, hasn't changed in years, if ever. Although it doesn't affect this
particular customer, there's also a related problem, in that the number of rows
to fetch for a fetch-many is limited to 65535 if the client wants to specify a
maximum. This is because because the protocol uses a 16-bit integer to send the
requested row count to the server at the start of the fetch many operation.

The only temporary workaround I can suggest is for the customer to put their
fetch-many code into a loop that gets 65535 rows at a time. 

Si
    
ps, I don't work on SQL/Services any more (haven't done for a while) so will 
forward this to Sue re: a fix at some time.

pps, although its not going to help them solve this problem, and I know its
stating the obvious, but they really shouldn't be using FT7 anymore...

    The customer has implemented a workaround.
    
    Do I need to bug this to get it fixed/documented in a future version.
    
    Peter

  Just to clarify a couple of points - since I'm at the customer site where
  this has been reported from:

  1. The T7.07 version identified in the log was taken from the Rdb V7 CD
     received here in January this year - it was not delivered as a Field
     test kit.

  2. This has also been demonstrated in V6.1-1, so confirming the reasoning
     presented in the analysis of the log - the T7 installation was the the
     easiest system from which to generate the log.

  3. As Peter suggests we do have a workaround - call fetch_many with a
     repeat count of 32k-1, anything higher will cause the function to
     fail with an invalid parameter value. Probably due to repeat count
     being a signed short integer value and so effectively having a
     negative count when the MSB is set.

  Cheers!
  Andrew

    Re. :.13:
    
  >>>1. The T7.07 version identified in the log was taken from the Rdb V7 CD
  >>>   received here in January this year - it was not delivered as a Field
  >>>   test kit.
    
    Was Sql Services reinstalled as well when the Rdb was installed with the
    V7 CD?  This has caused grieve to some people (including me).
    
    regards,
    Regina
     
    

    Only the SQL Services client is on the UNIX box. Rdb is not installed.
    
    Peter

In 7.0 eco01 and later versions, the server will store the current row count 
in a 32 bit integer.  This solves the -2007 problem.  

Sue

    Re .16
    
    Thanks
    
    Peter