|
Interesting. I know at least one of the manuals I read said that if I used the SQLDA I could not use the INTO clause on the FETCH, so I hadn't tried it. Apparently, that is wrong since you are doing it. I agree that I don't won't to use externally described DS's. I'm trying to get away from that. The only way I've found to do that was with pre-defined work fields of every possible type & size that the SQLDA might return to me. That would seem to also be required your way too, isn't it? I mean, what do you parse out the big character field into? I started out using the SYSTABLES catalog file and right away I needed binary fields, varying fields, TimeStamps, etc. I was toying with the idea of useing Based arrays of different types so I could Allocate and Reallocate additional instances of them as needed. It's still going to take a lot of arrays. How are you handling this requirement? Also, where did you find the data type chart you were referring to earlier? The one I'm using is an equivalency chart of SQL to RPG data types in chapter 14 of DB2 for AS/400 SQL Programming. However, it seems to be rather limited, in that I'm running into many types that are not on that chart. The same is true of the SQLCODE's I got out of the SQL reference manual. I'm getting many codes not on their list, however when in DEBUG, the descriptions are put into the joblog so at least they are manageable. > -----Original Message----- > From: Ron@cpumms.com [SMTP:Ron@cpumms.com] > Sent: Thursday, December 12, 2002 7:40 PM > To: rpg400-l@midrange.com > Subject: RE: SQLDA > > > That's correct, I'm not loading pointer to the data fields. The data is > loaded into a single alpahnumeric data structure, that I then parse out > based on the the values returned from the describe statement that tell me > the size/type of each field that is in the data structure. So I don't use > SQLDATA and SQLIND because I could never figure out how to do it that way. > And yes, SQLN has to be set, but SQLD tells me how many fields were in the > Select statement that I used. So I use it to set up a loop to parse the > data out of the data structure. > > I don't know if it's the right way or not. But it is the only way that I > could get it to work. It is dynamic, I don't know ahead of time the fields > in the select statment, nor the file. > > One thing to be aware of if you use the external data structure is that > you > can't have derived columns in the result because these fields wouldn't > exist in the data base file. (or you'd have to know in advance what the > derived fields were which kind of defeats the purpose). So you couldn't do > something like - if the transaction date is between this range, put the > amount in this new field. > > > Ron Hawkins > > > > > Very interesting....you are doing it a little different than all the > examples I've been able to find. > > First, when you include SQLDS it brings in a DIM constant by the name of > SQL_NUM which has to be defined in your program to the max number of > SQLVAR's (or fields) you are going to allow. That same constant is what I > set SQLD & SQLN to prior to the DESCRIBE statement. > > When the DESCRIBE statement returns, SQLD then has the actual number of > fields in it for the file in the Select statement and that many SQL_VAR > elements are loaded with field data. All the examples then require me to > set > the SQLDATA & SQLIND in each array element to pointers that point to > storage > fields that match the Size & Type of each field. Doing this dynamically > is > what is causing me grief. I've got it working using an externally > described > DS, but I want it to be dynamic. I won't know ahead of time what file I'm > working with in the ultimate version. > > So, are you not having to load any pointers into the SQL_VAR elements > between the Describe and the Fetch? > > > -----Original Message----- > > From: Ron@cpumms.com [SMTP:Ron@cpumms.com] > > Sent: Thursday, December 12, 2002 10:51 AM > > To: rpg400-l@midrange.com > > Subject: Re: SQLDA > > > > > > > > <<Does anyone have a good sample of Using the SQLDA in an RPG program > > where > > <<the file name is completely dynamic? In other words, to determine the > > field > > <<names and values on-the-fly, after determining the file name. Or, a > > good > > <<book of examples in RPG? > > > > Nelson, > > > > I have a work in progress that does that, but it's thousands of lines > > long. > > So I just pulled out parts of it here, but hopefully it will help some. > > > > The basic idea here is to fetch a record into a long data structure and > > then parse it out (accounting for packed fields, date fields, etc.) The > > descriptions of the fields come from the data structure SQLVAR which > comes > > from the SQL_VAR array. Each element in the SQL_VAR array holds an > > occurance of the data structure SQLVAR. In this example there is only > one > > occurance for each field (or column) in the file (or row). It doesn't > have > > to be a one to one relationship - it depends on the USING clause of the > > DESCRIBE statement. > > > > SQLVAR has fields SQLNAME, SQLLEN, and SQLTYPE (and others). > > Unfortunately, > > the SQLTYPE is not RPG types (P,N, etc). You have to interpret the type > > code but theres a good table in the SQL manual that does that. SQLLEN > also > > needs some tweaking before you can use it. I show in this example how to > > unpack a field (which I think I picked up from Barbra from this list). > > > > HTH > > > > Ron Hawkins > > > > > > D FinalDs Ds 5000 > > D FF1 512 > > > > C/Exec SQL > > C+ Include SQLDA > > C/End-Exec > > > > * Maximum of 500 fields in the file > > C Eval SQLN = 500 > > > > * Open cursor > > C/Exec SQL > > C+ Open C7 > > C/End-Exec > > * Open cursor > > C/Exec SQL > > C+ Describe TotalRead Into :SqlDa Using System Names > > C/End-Exec > > * Get the record > > C/Exec SQL > > C+ FETCH NEXT FROM C7 for 1 ROWS USING DESCRIPTOR :SQLDA INTO > > C+ :Finalds:fieldnull > > C/End-Exec > > c If SqlCod <> 100 > > > > * Format the final line > > c Eval Ix = 1 > > c Eval Ix2 = 1 > > * Must parse out each field to account for packed data fields > > c do SQLD Fd > > c Eval SqlVar = Sql_Var(FD) > > > > * Packed > > c Select > > c When Sqltype = 0485 > > c SqlLen Div 256 PackLen > > c Div 2 PackLen > > c mvr PackRmd > > c eval PackLen = PackLen + PackRmd > > c Eval PackedDs = *allx'00' > > c Evalr PackedDs = %subst(FinalDs:Ix2:PackLen) > > c Eval ZonedData = PackedData > > > > > > > > > > _______________________________________________ > > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing > list > > To post a message email: RPG400-L@midrange.com > > To subscribe, unsubscribe, or change list options, > > visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l > > or email: RPG400-L-request@midrange.com > > Before posting, please take a moment to review the archives > > at http://archive.midrange.com/rpg400-l. > > > ************************************************************************** > ************************************************************************** > ******************************************************** > > This message originates from Lincare Holdings Inc. It contains information > which may be confidential or privileged and is intended only for the > individual or entity named above. > It is prohibited for anyone else to disclose, copy, distribute or use the > contents of this message. > All personal messages express views solely of the sender, which are not to > be attributed to Lincare Holdings Inc., and may not be copied or > distributed without this disclaimer. > If you received this message in error, please notify us immediately at > MailAdmin@lincare.com or (800) 284-2006. > ************************************************************************** > ************************************************************************** > ******************************************************** > > > _______________________________________________ > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list > To post a message email: RPG400-L@midrange.com > To subscribe, unsubscribe, or change list options, > visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l > or email: RPG400-L-request@midrange.com > Before posting, please take a moment to review the archives > at http://archive.midrange.com/rpg400-l. > > > > > > _______________________________________________ > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list > To post a message email: RPG400-L@midrange.com > To subscribe, unsubscribe, or change list options, > visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l > or email: RPG400-L-request@midrange.com > Before posting, please take a moment to review the archives > at http://archive.midrange.com/rpg400-l.
As an Amazon Associate we earn from qualifying purchases.
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.
midrange.com
