"SELECT *" is good form if you can handle the results generically (as with 
SQLDA) and when you really do need to fetch all columns.  

And, you can use a host structure even when you select columns by name:

D HostStr      ds          Qualified
d  fielda              10a
d  fieldb              10a

  "Select user, program from file"
  "Fetch into HostStr"

But any time you "SELECT *" into a character field you get an error that you 
don't have enough host variables or something like that.  It's been years since 
I played around with that, I just know it doesn't work unless the host variable 
is like an external DS that has enough fields to contain the results.

Doug,
I cannot use Steve's idea because of packed fields being unpacked by SQL. I 
don't even need QUSLFLD if I am going to use SQLDA (which it looks like I am 
going to have to do).


** Here is my solution:  I will perform "SELECT *" and use the SQLDA after the 
FETCH to reconstruct the record buffer.  This means that I have to pack fields 
that are suppose to be packed and I have to handle variable length fields.

I want the returned data to look EXACTLY like a record buffer would look in a 
trigger or what you would see if you did a DSPPFM, unformatted and raw records 
from a file that is not known until run time.

This will work, I've done 99% of this before.  The only new code I need to 
write is handling variable length fields, but that shouldn't be a big deal.  I 
was just hoping there was another solution that would reduce the size of my 
code without adding a lot of extra overhead.

I wish IBM had some keyword for the SELECT that would tell SQL to return all 
column in a single unformatted character string...this isn't the first time I 
have wanted to do something like this.

Fish,

I don't use "SELECT *" since its not good form, so I haven't used a host
structure before. 

[EDIT] Okay, i tried it, it failed. You can't do SELECT * into a structure
itself. Only the first field  is returned (or number of fields in the DS, I had
one.)

So the SQLDA is your only choice at this point...

[original post for archive follow]
But, have you tried just doing a "SELECT * INTO :HOSTVAR..."
where the HOSTVAR is just a big DS (bigger than necessary) and see if it runs?
If so, then that would mean that it doesn't expend the data structure into
individual subfields names are runtime/compiletime and just copies the resulting
buffer into the DS. That's my suggestion--experiment if that.

It sounds like Fish is looking to make this generic, so a host structure would 
not work.

He could probably use Steve's concatenation solution, using dynamic SQL.  He 
could loop through the QADBIFLD system file or the output of the QUSLFLD API, 
building an SQL statement like Steve's.  Then fetch each record into a single 
field.  (Though this may present problems with packed data...)

Bob,
That is what I am asking...how can I use some Host structure (or a simple 
character string) to fetch records from any file into?  I don't think you can.

I am thinking I will have to use the SQL descriptor (SQLDA) and reconstruct the 
raw record buffer after the FETCH.

Another idea I had, again by using SQLDA, was to create an SQL INSERT statement 
to send to the remote system instead of record buffers.  I don't like that 
option because the length of these statements will be more than twice the 
length of the record buffer in almost every case.

So would a Host Structure be your solution? That is what is usually used when
"SELECT *" is specified. (Although all SQL-fanboys frown on using "SELECT *".)

Bob,
It will require dynamic SQL, so yes I will be using PREPARE.

Fish, how are you handling the dynamic file name?
Prepare?

I don't want to have to reference all 10, 50, 500 (however many) columns there 
may be in the file.  This is going to be a generic process and I simply want 
the applications to tell me what file to retrieve the data from, provide me 
with a "where clause", and tell me where they want the results of the query 
copied to.

If I am going to get into dealing with each column name then I am probably 
going to be better off sticking with the SQLDA.

Using external data structures and all the "normal" techniques will NOT work 
since I do not not what the file name is until runtime.  Then again, maybe 
using a language like REXX would handle this nicely.

D fileds     e ds               extfile(myfile)

exec sql select * from myfile into :fileds where something = somethingelse;


This works for a single return.  Multiple records you'd need to use a cursor, 
but my dim'ing the ds to sufficient size, you can fetch in one block.

If RcdBuffer was an external data structure, could you not do your fetch as you 
have described, then work with RcdBuffer directly?  Doesn't RcdBuffer in and of 
itself contain your unformatted record?

In other words, your single host variable is a data structure, which can be 
manipulated as a whole (and if it can't, base another variable - a standalone 
LIKE the DS - on the address of the DS).

The SQL is creating one large CAST'ed column by concatenating the individual 
columns from the_table.  I use this technique to create EDI records for 
transmission all the time.  CHAR(packed_col) will get the numeric data 
unpacked.

i am probably not following this
And i have never done this on an iseries
but why not just a 
insert mysystem1.mylib1.myfile1
(a1,a2,a3,...)
select (b1,b2,b3,...)
From mysystem2.mylib2.myfile2
where some condition


i think i used a 
 jdbc_ExecQry
to get data from a remote system

A little more leg work, but that might work.

Create a duplicate object...Insert into the dup object...create a flat file 
with the same record length...CPYF using *NOCHK...then select * from the flat 
file. 

This will work, I just don't like having to build in the extra overhead. I sure 
wish I could just create the flat file and insert directly into it from the 
database I am trying to extract from.

You could also create and fill an externally defined file that has the layout 
you want, then copy it to a "flat file" of equal rec len using CPYF *NOMAP.  
Then use the "single field" records from the "flat file" in your pgm.

SteveCCNJ 
Wouldn't that mean having to reference each column in the file in the SQL 
statement instead of 'Select *'?  Would this return unformatted data or would 
packed decimal fields be returned unpacked?

SELECT   CAST (
         first_col    || 
         second_col   || 
         third_col    || 
         ...etc...
	   AS CHAR(####)  -- whatever size
         ) AS BIG_TEXT_COL	
FROM  the_table

I don't think there is an "easy" way to do what I want using straight up SQL.  
I need to be able to run an SQL statement to select records and have the 
records returned like a record buffer would appear in a trigger program.  These 
records will be moved to remote systems and inserted into files on the remote 
systems as part of a promotion process (we're copying account setups from one 
system to another basically).

I know that I could use the SQL descriptor (SQLDA) to do this, but it would 
require a lot more code and I was hoping there was an easier way.  Does anyone 
know of a way to do like "Select * from somefile where somefield = 'Value'" and 
Fetch the records into a single host variable?  After doing a 'Fetch cursor 
into :RcdBuffer' I would want to have the entire record as it would appear in a 
trigger program or when you display the unformatted data using DSPPFM.

I have already started designing this processing using SQLDA because I know I 
can do it that way.  Hopefully one of you can think of an easier way to do this.