Re: Still problems with SQLCLI

[Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>]

Hi Tony,

You probably already know this, but it leads into my point:  In order to 
do a multiple-row fetch, the SQL CLI requires that all columns be in 
contiguous storage.

For example, if you're doing this:

  SELECT CustNo, Name, Address from CUSTMAS

Your result set will consist of three columns. Let's say Custno is 
Numeric(4,0), Name is Char(25) and Address is Char(30).  In order to do 
a multi-row fetch, you'd have to allocate 59 bytes of memory for each 
row of the result set.  Those 59 bytes have to be contiguous and laid 
out with Custno in positions 1-4, Name in positions 5-29 and Address in 
positions 30-59.

So even though you have to call SQLBindCol() three times, you 
technically need your data to all be in one block of memory.  If there's 
a second row, the CustNo for the second row will occupy positions 60-63, 
Name will occupy positions 64-88, and Address occupies 89-118

In RPG terms, your result has to look like this:

     D Row             ds                  qualified dim(50)
     D  CustNo                        4s 0
     D  Name                         25a
     D  Address                      30a

And CANNOT look like this:

     D CustNo          s              4s 0 dim(50)
     D Name            s             25a   dim(50)
     D Address         s             30a   dim(50)

Even though you call SQLBindCol() once for each column, if you want to 
do a multi-row fetch, the columns have to be contiguous in memory, so 
that the second element of the array starts after the last field of the 
first record.

Honestly, at this point I'm just hoping you've followed this.  If you 
don't understand it the first time, it's REAALLY hard to explain it further.

ANYWAY...

The point is, the LENGTHs for the 6th parameter to SQLBindCol() also 
have to be laid out this way.  Granted, they're always integers, but 
other than that, they have to be laid out the same way, like this:

      D Lengths         ds                  qualified dim(50)
      D   CustNo                      10i 0
      D   Name                        10i 0
      D   Address                     10i 0

You CANNOT lay them out like this (unless I'm wrong, heh):

     D LenCust         s             10i 0 dim(50)
     D LenName         s             10i 0 dim(50)
     D LenAddr         s             10i 0 dim(50)

They MUST be laid out the same way as the result set, as in the data 
structure example.  Therefore, even though each call to SQLBindCol() 
only passes an array for ONE COLUMN, you effectively HAVE to allocate 
space for all columns, because otherwise it'd change the layout in memory.

In my three column example, above, the SQL engine is setting the length 
for one column.  In order to set the length for same column in the next 
row, it has to advance 12 bytes in memory (since an int is 4 bytes, and 
there are three columns).

This is not a great area for experimentation -- because even if passing 
a single array SEEMS to work, it might be overwriting memory that just 
doesn't affect your program (for now).

I know that the length information is returned in contiguous rows 
because I tried the example I'm discussing in this message where I have 
a 4 byte column, a 25 byte column, and a 30 byte column.  If I use a 
simple array, I get data back in the form of "4, 25, 30, 4, 25, 30" in 
the array that SHOULD have only returned 4,4,4,4.  But if I bind it as a 
n array of data sturctures, I get 4 in the right places, 25 in the right 
places and 30 in the right places.

Oh yeah... don't forget to disable null-termation.  By default, the CLI 
will return null-terminated strings... to disable this, call 
SQLSetEnvAttr() and set SQL_ATTR_OUTPUT_NTS to SQL_FALSE.


Tony.Weston@xxxxxxxxxxxxxx wrote:
> Hi Scott,
>
> I think you're right..... Though, I think, maybe the 4th parameter of the 
> SQLBindCol should hold an array of integers, just for that column the 
> SQLBindCol is binding, not for all columns, as you seam to imply, can you 
> clarify please!.
>
> I am calling SQLBindCol for each column, and I'm wondering if I can get 
> away with using the same array for each column anyhow  (as I don't need 
> the size of the data returned). I'll try this route, and If this doesn't 
> work, then I'll have to use a unique array for each column.
>
> Thankyou so much for your time,  I definatly owe you a drink if/when I see 
> you at Common again!
>
> Cheers!
>
>
> Tony Weston
> Senior Analyst/Programmer - IT Systems
> Fusion House, Mile Lane, Coventry
> Tel: 02476 497079    Internal: 77079
>
> This message is intended for the stated addressee(s) only and may be 
> confidential. Access to this email by anyone else is unauthorised. Any 
> opinions expressed in this email do not necessarily reflect the opinions 
> of BISL. Any unauthorised disclosure, use or dissemination, either whole 
> or in part is prohibited. If you are not the intended recipient of this 
> message, please notify the sender immediately.
>
> Please scan all attachments for viruses.