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.
As an Amazon Associate we earn from qualifying purchases.