Forum Discussion

baba_k's avatar
baba_k
Occasional Contributor
14 years ago

Unable to pass the variables in SQL query through test complete script

Hi,

Can anyone please explain how to pass variables into SQL query through script:

Below is the syntax that we are using and getting an error:

Qry.SQL = "Select * from Aq1lookupalarapart where apar_id=CASID and dde_level_over= DDE_Updated_Value order by last_update desc"

**CASID, DDE_Updated_Value are the variables that we have captured from our application and we are trying to pass values captured through thses variables into SQL query and getting failed to get the result.

We also tried to add these varibale to project variable list and then pass them to the SQl query but it this attemp was also failed.

Below is the complete script:


Qry.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Agresso_QA;Data Source=GLNWD021\QA"


' Specify the SQL expression

Qry.SQL = "Select * from Aq1lookupalarapart where apar_id=CASID and dde_level_over= DDE_Updated_Value order by last_update desc"



' Execute the query

Qry.Open



' Process results and insert data into the test log

Qry.First



While Not Qry.EOF


Log.Message Qry.FieldByName("apar_id").Value


Qry.Next

Wend



Closes the query

Qry.Close


  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    You'll need to do something more like the following



    Qry.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Agresso_QA;Data Source=GLNWD021\QA"

    ' Specify the SQL expression

    Qry.SQL = "Select * from Aq1lookupalarapart where apar_id= " & CASID & " and dde_level_over="& DDE_Updated_Value & " order by last_update desc"

    ' Execute the query

    Qry.Open

    ' Process results and insert data into the test log

    Qry.First

    While Not Qry.EOF

    Log.Message Qry.FieldByName("apar_id").Value

    Qry.Next

    Wend

    Qry.Close




    Because CASID and DDE_Updated_Value are variables only in the context of the script, you need to concatenate them as strings into your query.
  • Juls's avatar
    Juls
    Occasional Contributor
    Hello,



    I have the same problem. Could you please tell me how to this in c# skript?

    So how can I do for example this: apar_id= " & CASID & " in c#?



    Thanks alot

  • Hi Juls,





    In C#Script, just replace & with + :





    Qry.SQL = "Select * from Aq1lookupalarapart where apar_id= " + CASID + " and dde_level_over=" + DDE_Updated_Value + " order by last_update desc"
  • Hello,



    I have a problem. I want to pass the name of a database into a variable but I don't know how. I do like this in my code:


    var



    commands = new Array(); 


    var variable="@DBNAME";


     commands[commands.length] = "DECLARE " + variable + " NVARCHAR(max) select " + variable + " = name from sys.databases where name like xxx_%' print @DBNAME";


    // Create a new object


      var Cmd =


    ADO["CreateADOCommand"]();


    // Specify the connection string



     Cmd["ConnectionString"]="Driver={SQL Server};Server=xxx;Uid=xxx;Pwd=xxx";


    // Specify the command type



     Cmd["CommandType"]=cmdText;


    for(var i = 0; i < commands.length; i++)


     {


    // Specify the command text (an SQL expression)


       Cmd.CommandText = commands;


    // Execute the command


    Cmd.Execute();


     }


    Log["Message"](variable);


     return variable;



    But the result is '@DBNAME not ''xxx_' as I expect.



    Regards,

    Raluca




  • Hello,



    I have a problem. I want to pass the name of a database into a variable but I don't know how. I do like this in my code:


    var



    commands = new Array(); 


    var variable="@DBNAME";


     commands[commands.length] = "DECLARE " + variable + " NVARCHAR(max) select " + variable + " = name from sys.databases where name like xxx_%' print @DBNAME";


    // Create a new object


      var Cmd =


    ADO["CreateADOCommand"]();


    // Specify the connection string



     Cmd["ConnectionString"]="Driver={SQL Server};Server=xxx;Uid=xxx;Pwd=xxx";


    // Specify the command type



     Cmd["CommandType"]=cmdText;


    for(var i = 0; i < commands.length; i++)


     {


    // Specify the command text (an SQL expression)


       Cmd.CommandText = commands;


    // Execute the command


    Cmd.Execute();


     }


    Log["Message"](variable);


     return variable;



    But the result is '@DBNAME not ''xxx_' as I expect.



    Regards,

    Raluca




  • Hello again,

    I found the solution in the help files and I did like this:

    function



    GetLastDatabase()


    {


      var Qry;


    // Create a query


      Qry =


    ADO["CreateADOQuery"]();


    // Specify the connection string


      Qry["ConnectionString"] = "Driver={SQL Server};Server=xxx;Uid=xxx;Pwd=xxx";


    // Specify the SQL expression


      Qry["SQL"] = "select name from sys.databases where name like :Database_Name";


    // Execute the query


    // Specify the parameter value


      Qry["Parameters"]["ParamByName"]("Database_Name")["Value"] = xxx_%';


      Qry["Open"]();


    // Process results and insert data into the test log


      Qry["First"]();


      while (!Qry["EOF"])


     {


       var result=Qry["FieldByName"]("name")["Value"];


       Qry["Next"]();


     }


    // Closes the query


      Qry["Close"]();


    Log["Message"](result)


     return result;






    Regards,

    raluca