× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.