Dawn Sekel wrote:
> We are working with a customer who has a legacy application written in
> a Progress Database that uses SQL-89 query language. In their
> customer table the address field (which has a column heading of ADDR)
> is an array field. If we use CFDump to output the query structure
> <CFDUMP var="qryCustomer"> we can see the column name information.
> And it shows the array field elements as having an @ symbol in its
> column heading:
>
> NAME [EMAIL PROTECTED] [EMAIL PROTECTED]
> CITY ....
> XYZ Company 111 Main Street [empty string] New York
Looks like Progress did something weird with their array implementation if you
get that back. (Or quite likely with the array implementation in their JDBC
driver.) They seem to be translating the array type to a composite type. I
wonder how that works if different rows have a different number of array
elements. I suppose the standard is not completely foolproof if that is SQL-89,
if I use an array in PostgreSQL it is translated back to a ColdFusion array
automatically, and that is supposed to be SQL-89 (actually SQL-2003) too. For
example, the code below outputs "Yes Yes".
> But, we can't figure out the correct syntax for a CFoutput statement
> for the individual elements of the ADDR array. The PROGRESS SQL-89
> user guide says it should be #qryCustomer.addr__1#, but that causes an
> "element undefined" error. Does anyone have an example of a select
> statement for a Progress SQL-89 array field that they can share? Is
> it possible to use an @ symbol as a field name using some sort of
> escape sequence?
Can't you get at it using #queryname["[EMAIL PROTECTED]"][1]#?
Jochem
<cftransaction>
<cfquery datasource="pg_scorpio">
CREATE TABLE arrayTest (
ID INTEGER,
ADDRESS VARCHAR[]
)
</cfquery>
<cfquery datasource="pg_scorpio">
INSERT INTO arrayTest (ID, ADDRESS)
VALUES (1, ARRAY['1630 Revello Drive', 'Sunnydale', 'CA'])
</cfquery>
<cfquery name="test" datasource="pg_scorpio">
SELECT *
FROM arrayTest
</cfquery>
<cfoutput>
#IsQuery(test)#<br />
#IsArray(test.address)#<br />
</cfoutput>
<cftransaction action="rollback">
</cftransaction>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:260521
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
