0

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.

4
  • "Not working" is not helping us to help you. Do you get an error message? What is the complete error message - as text, not screenshot(s). Commented Feb 9, 2023 at 21:22
  • 1
    I will point out that the first "working" example is correctly delimiting the connection string properties with ; 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. Commented Feb 9, 2023 at 21:24
  • @AlwaysLearning Ahhh the semi-colon!!! the nightmare of every programmer. Thank you it worked. In the second option the delimiter was causing the error Commented Feb 9, 2023 at 21:36
  • @AlwaysLearning Thank you so much. I appreciate it. I just ignored the ; Commented Feb 9, 2023 at 21:37

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.