It seems that if one uses Python and the pyodbc module to submit an SQL query, one can obtain an attribute called "description" that includes the names of the columns in the query. It seems that the REBOL ODBC does not. It occurred to me that if one were disciplined and constructed the SQL query with the "AS" option on all column names, then one could parse the SQL query, locate all the "as" keywords prior to the "from" keyword, and then pick off whatever followed the "as" keyword and safely assume it is a column name. So I did that using my preferred method of brute force, but I am wondering if it could be done with a parse rule, and if anyone has any suggestions. A sample script follows to show what I mean. The brute-force code probably is not much longer that a parse rule, but the parsing would be a good learning experience.
Thank you.
R E B O L [
Title: "SQL AS scanner"
Purpose: {Scan all the "as (column-name)" items from SQL that has been
carefully written to include the "as" option for all selected columns.}
]
SQL-CMD: {
select
COLUMN1 as COLUMN1
,COLUMN2 as COLUMN2
,COLUMN3 as 'COLUMN3'
,COLUMN4 AS 'COLUMN4'
from TABLE1 as T1
inner join TABLE2 AS T2
on T1.COLUMN1 = T2.COLUMN1
order by COLUMN1
}
COLNAMES: []
;; The result of this parsing should be:
;; COLNAMES: ["COLUMN1" "COLUMN2" "COLUMN3" "COLUMN4"]
;; Here is the "REBOL way with parsing:
;parse SQL-CMD [
;]
;; Here is the "brute force" way familiar to 3GL programmers:
WORDS: parse SQL-CMD none
LGH: length? WORDS
POS: 1
while [POS < LGH] [
if equal? "from" pick WORDS POS [
break
]
either equal? "as" pick WORDS POS [
POS: POS + 1
append COLNAMES trim/with pick WORDS POS "'"
POS: POS + 1
] [
POS: POS + 1
]
]
;;Uncomment to test
probe COLNAMES
halt