I am trying to connect to SQL Server Database by passing in the database name from the front-end. I tried two options one of the is working and another one is not. Lets say Method 1: The below method is working:
app.post('/getDropDown/crop',async(req,res)=>{
let dropdownModel = {}
await sql.connect(
`Server=cloud.com,1433;
Database=${req.params.crop};
User Id=imuser;Password=abc;
trustServerCertificate=true`,err=>{
if (err) console.log(err)
new sql.Request().query(`
SELECT DISTINCT TBNAME AS "Experiment", TBYEAR AS "Year", TBFLD_NAME AS "Trait"
FROM dbo.EXP01 t1
JOIN dbo.EXP02 t2 ON t1.TBID = t2.TBKEXPT
JOIN dbo.TRD01 t3 ON t2.TBKTRAIT = t3.TBID`,(err,result)=>{
dropdownModel['Year']= Array.from(new Set(result.recordsets[0].map((item)=>item.Year)))
dropdownModel['Trait'] = Array.from(new Set(result.recordsets[0].map((item)=>item.Trait)))
dropdownModel['Exp'] = Array.from(new Set(result.recordsets[0].map((item)=>item.Experiment)))
res.send({"data":dropdownModel})
})
})
})
However, I am trying to separate out the connection to a different function or module so that I do not have to write the whole connection string every time. Method 2: The below method is not working:
function createDB(dname){
let connection=
`Server=cloud.com,1433,
Database=${dname},
User Id=imuser,Password=abc,
trustServerCertificate=true`
return connection
}
app.get('/getDropDown/:crop',async(req,res)=>{
await sql.connect(createDB(`${req.params.crop}`),err=>{
if (err) console.log(err)
new sql.Request().query(`
SELECT DISTINCT TBNAME AS "Experiment", TBYEAR AS "Year", TBFLD_NAME AS "Trait"
FROM dbo.EXP01 t1
JOIN dbo.EXP02 t2 ON t1.TBID = t2.TBKEXPT
JOIN dbo.TRD01 t3 ON t2.TBKTRAIT = t3.TBID`,(err,result)=>{
console.log(result)
})
}
)
})
I referenced this answer to create Method 2, but has no luck. Any help is appreciated how to separate out the connection string from the service and use it as a function.
;characters and the second "not working" example is incorrectly delimiting the connection string properties with,characters. Both examples here are using a string whereas in the other link you reference all examples are using Javascript objects.;