RE: Does anyone have CFoutput example for SQL89 Array field?
> 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 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 > > 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? Thank you in advance! Dawn Did you try array notation? #qryCustomer["[EMAIL PROTECTED]"][CurrentRow]# I know that works with spaces, not sure about invalid characters, though. Andy ~| 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:260524 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Does anyone have CFoutput example for SQL89 Array field?
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 > 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 CREATE TABLE arrayTest ( ID INTEGER, ADDRESS VARCHAR[] ) INSERT INTO arrayTest (ID, ADDRESS) VALUES (1, ARRAY['1630 Revello Drive', 'Sunnydale', 'CA']) SELECT * FROM arrayTest #IsQuery(test)# #IsArray(test.address)# ~| 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
Does anyone have CFoutput example for SQL89 Array field?
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 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 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? Thank you in advance! Dawn ~| 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:260516 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
