I think you would have better luck using EXECUTE "SELECT... and READ
rather than parsing the result of an SQL statement
jonathanm wrote:
This is for UniData 6.0 on hpux.
I have a program running some SQL statements and returning data. The problem
is the data (dates and numeric) is in internal format. I want it in output
format. The SQL statement is variable, so I can't hardcode conversions.
Here's an example:
The DICT for file EMPLOYEE contains entries for fields HIRE_DATE and SALARY.
HIRE_DATE has a "D4/" conversion in attribute 3, and SALARY has a "MD2,"
conversion.
Here's code from the UniBasic program:
-------------------------------------------------------------------------
.
.
.
SQLCMD='SELECT HIRE_DATE, SALARY FROM EMPLOYEE TO MY_CAPTURE_FILE;'
EXECUTESQL SQLCMD
IF NOT( STATUS() ) THEN
RESULT.DATA=''
ROW.DELIM=CHAR(10)
END.OF.LIST=0
LOOP WHILE NOT(END.OF.LIST) DO
READNEXTTUPLE ROW.ITM FROM 'MY_CAPTURE_FILE' THEN
RESULT.DATA=RESULT.DATA:ROW.ITM:ROW.DELIM
END ELSE
END.OF.LIST=1
END
REPEAT
END ELSE
.
.
.
-------------------------------------------------------------------------
While this works just fine, the data for HIRE_DATE and SALARY are all in
internal format. I want them in output format.
I can't just OCONV them, because in practice, the SQLCMD changes based on
user input, so it's not always the same fields being SELECTed.
Right now, I'm accomplishing the goal of output formatted data by doing it
the hard way: I'm parsing SQLCMD, getting the field names and the file name,
then going and reading the DICT entry for each field and applying the
correct output conversion, if any.
That approach doesn't work, though, when the SQLCMD contains expressions
involving multiple fields like "SELECT PART, QTY_INHOUSE - QTY_COMMITTED
FROM INVENTORY TO MY_CAPTURE_FILE;"
I know I can tell the users (programmers) to excessively use OCONV in the
SELECT command, but that doesn't seem realistic: they shouldn't have to use
ugly commands like "SELECT OCONV(HIRE_DATE,"D4/") ...". That's what
dictionaries are for.
Bottom line is I just need an option to tell the SQL engine to write the
data in output format.
I looked into UDT.OPTIONS 91 and UDT.OPTIONS 76. Neither made a difference.
Any ideas?
--
------------------------------------------------------------------------
Jeff Schasny - Denver, Co, USA
jschasny at gmail dot com
------------------------------------------------------------------------
_______________________________________________
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
