Home   Archive   Permalink



Setting data types at run time

I want to take some data from an SQL query and produce a CSV file. I can do that, no problem, but the data sometimes comes in a type that I don't want and so I have to convert it. For example, a number that really is an integer might have a decimal in the raw SQL output and I have to convert it to an integer. I can do that, no problem, but I have to know what is happening and code for it in the program, which means that every time I want to do this operation of converting SQL output to a CSV file I have to write a separate program. I would like to generalize this operation and write just one program. I have an idea, as shown in the following sample, but I can't make it work. Basically, I have a table of column names and data types, and for each row of SQL data, I convert each column based on the data type in the table. I thought that one "to" function on one line of code would do the trick. I could take a more brute-force approach but I wonder if there is some simple trick I am missing.
    
Thank you. Sample follows (a bit long I'm afraid).
    
R E B O L [
     Title: "Generalized data format test"
]
    
;; Column headers and data types for each field
COLUMNS: [
     ["PID" integer!]
     ["DATE" string!]
]
COLCOUNT: length? COLUMNS
    
;; Final output file
CSVOUT: ""
    
;; Raw data to turn into a CSV file
RAWDATA: [
     [93.0 20190101]
     [100.0 20190101]
]
    
;; Emit headings
CNT: 0
foreach COL COLUMNS [
     CNT: CNT + 1
     append CSVOUT COL/1
     if lesser? CNT COLCOUNT [
         append CSVOUT ","
     ]
]
append CSVOUT newline
    
;; Emit data
foreach REC RAWDATA [
     CNT: 0
     foreach FLD REC [
         CNT: CNT + 1
    
;; -- Here we want to make sure that the fields in the raw data
;; -- have the data types specified in the COLUMNS block.
         probe COLUMNS/:CNT/2 ;; for debugging
    
;     append CSVOUT FLD ;; This puts it out as it comes in
    
;     append CSVOUT to COLUMNS/:CNT/2 FLD ;; this produces an error
    
;;     ...and this does not produce an error, but does not
;;     give the hoped-for result
;     if equal? COLUMNS/:CNT/2 integer! [
;         append CSVOUT to-integer FLD
;     ]
;     if equal? COLUMNS/:CNT/2 string! [
;         append CSVOUT rejoin [ {"} to-string FLD {"}]
;     ]
            
         if lesser? CNT COLCOUNT [
             append CSVOUT ","
         ]
     ]
     append CSVOUT newline
]
    
;; Check the result
;; Hoping for:
;; PID,DATE
;; 93,"20190101"
;; 100,"20190101"
    
print CSVOUT
halt
    
    
    
    


posted by:   Steven White     9-Jan-2019/13:14:12-8:00



This would be the brute-force way and it does work, but I was hoping the one-liner approach would work also.
    
;; Column headers and data types for each field
COLUMNS: [
     ["PID" "integer"]
     ["DATE" "string"]
]
...
         if equal? COLUMNS/:CNT/2 "integer" [
             append CSVOUT to-integer FLD
         ]
         if equal? COLUMNS/:CNT/2 "string" [
             append CSVOUT rejoin [ {"} to-string FLD {"}]
         ]

posted by:   Steven White     9-Jan-2019/13:28:46-8:00



I am not 100% sure if the goal is to generalize the column count COLUMNS/:CNT/2
    
If so I would access COLUMNS/:CNT/n with
    
pick COLUMNS/:CNT n
    
Also use switch or case instead of if statements?

posted by:   VIDpuzzle     16-Jan-2019/7:46:12-8:00