This is a follow up to this question: Working with reactiveValues and ggplot in shiny. I am making a shiny app that queries an SQL database. In my working example for the question, I managed to avoid changing the format of reactive data within shiny by using CAST to automatically import data with a specific format:
Query without cast
b <- reactive({ paste("select Wafer, Batch, MeasurementA, MeasurementB from dd where ID=",e()," Order by ID asc ;", sep="") })
Query with cast to automatically change column format
b <- reactive({ paste("select cast(Wafer as varchar) as Wafer, cast(Batch as varchar) as Batch, MeasurementA, MeasurementB from dd where ID=",e()," Order by ID asc ;", sep="") })
This worked fine in the working example I used for my question. However, when I came to apply this to my real app I get a syntax error:
b <- reactive({ paste("select cast(e.erf_num as varchar) as ERF, cast(w.wafer_id as varchar) as Wafer, d.name,d.row,d.col,d.width,d.length,t.test_date,
t.von_fwd,t.vth_fwd,t.sts_fwd,t.on_off_fwd,t.curr_on_fwd,t.curr_off_fwd,t.rds_on_fwd, t.rds_on_fwd,
t.von_rev,t.vth_rev,t.sts_rev,t.on_off_rev,t.curr_on_rev,t.curr_off_rev,t.rds_on_rev, t.rds_on_rev, t.delta_vth, t.id
from transfer_data_temp AS t
inner join wafer AS w ON t.wafer_id=w.id
inner join device AS d ON t.device_id=d.id
inner join erf AS e on t.wafer_id=e.wafer_id
where
t.wafer_id=",e()," Order by d.name asc , t.test_date;", sep="") })
Unhandled error in observer: could not run statement: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'varchar), cast(wafer.wafer_id as varchar), d.name,d.row,d.col,d.width,d.length,t' at line 1
I know the query works without the CAST because ive used it successfully before. So I draw attention to this part of the query:
b <- reactive({ paste("select cast(e.erf_num as varchar) as ERF, cast(w.wafer_id as varchar) as Wafer,
Any ideas why this works in the former but not the latter? I can post all my code to give a full working example if it helps (avoided ATM because its a tad long). Thanks. Pete
