Re: Informix-4gl Reports - Question re: Accessing Table Data

On 7 Jun 97 at 17:44, Davy P wrote: > I would like to use Informix-4GL to create a printed report. I would > like to write the report so it is able to read the systables and > syscolumns tables to figure out what tables and fields are defined > and then SELECT FROM those tables and fields. In my experience I've > always needed to specifically indicate the table name and I haven't > been able to figure out how to select data from a dynamically > defined table name. Sounds like you're just buggering around... :-) Anyway, the way you do this is to build up your SQL statement as a character string, PREPARE and DECLARE a cursor and then FOREACH or FETCH the cursor, something like this (assuming multiple columns from one table): FUNCTION reportDriver() DEFINE sqlString CHAR(2048) DEFINE numOfCols SMALLINT DEFINE counter SMALLINT DEFINE selectedColumn ARRAY OF CHAR(18) DEFINE selectedTable CHAR(18) DEFINE results RECORD field1 VARCHAR(255), field2 VARCHAR(255), field3 VARCHAR(255), field4 VARCHAR(255), field5 VARCHAR(255), field6 VARCHAR(255), field7 VARCHAR(255), . . . fieldN VARCHAR(255) END RECORD -- some code to select the table and columns, followed by: LET sqlString = "SELECT" FOR counter = 1 TO numOfCols - 1 LET sqlString = sqlString CLIPPED, " ", selectedColumn[counter] CLIPPED, "," END FOR LET sqlString = sqlString CLIPPED, " ", selectedColumn[numOfCols] CLIPPED, " FROM ", selectedTable CLIPPED PREPARE preparedCursor FROM sqlString DECLARE tableCursor CURSOR FOR preparedCursor START REPORT tableDump TO PRINTER FOREACH tableCursor INTO results.* OUTPUT TO REPORT tableDump(results.*) END FOREACH FINISH REPORT tableDump END FUNCTION -- reportDriver REPORT tableDump(results) DEFINE results RECORD field1 VARCHAR(255), field2 VARCHAR(255), field3 VARCHAR(255), field4 VARCHAR(255), field5 VARCHAR(255), field6 VARCHAR(255), field7 VARCHAR(255), . . . fieldN VARCHAR(255) END RECORD FORMAT EVERY ROW END REPORT Mind you, I have just sucked this out of my thumb, with no manuals handy, no 4GL compiler, etc., etc. HTH. -- Ciao, Billy Nose to the ground and ear to the grindstone in: Kuwait, 40 Degrees Celsius - say no more! GEMINI May 21 - June 20 You are quick and intelligent and a thinker. People like you because you are bisexual. However, you are inclined to expect too much for too little. This means you are a cheap bastard. Geminis are liars, notoriously lousy lovers and thrive on incest.