Forum Discussion

smccafferty's avatar
smccafferty
Occasional Contributor
12 years ago

Trying to Assign an SQL returned value to a Project Variable

Hey there.
I've been trying for a while to assign a value (returned from a SQL query) to a local variable.

Here's the code:

function SetCourseID()
{
var aCon, aCmd, setCourseID;
var localCourseID;

// Creates ADO connection
aCon = ADO["CreateConnection"]();

// Sets up the connection parameters
aCon["ConnectionString"] = "Provider=SQLOLEDB.1;Password=****************;Persist Security Info=True;User ID=***********;Initial Catalog=**************************;Data Source=********************************";

// Opens the connection
aCon["Open"]();

//Set Course ID for test 2350: Initialize Command
setCourseID = ADO["CreateCommand"]();
setCourseID["ActiveConnection"] = aCon;
setCourseID["CommandType"] = adCmdText;

// Execute SQL Command to find and remove previous t2350 courses
setCourseID["CommandText"] = "select CourseID from course where name = 't2350 - Single Video - One Section - No Survey'";
localCourseID = setCourseID["Execute"]();

Project.Variables.AddVariable("t2350_CourseID","String");
Project.Variables.t2350_CourseID = localCourseID;

aCon["Close"]();
}

I know the query returns an integer and I know the connection string is spot on (I use it in other scripts to delete and add items).

The problem is a mismatch on this line:
Project.Variables.t2350_CourseID = localCourseID;

This is probably something very silly as I've been looking at this for ages!
Please help!

6 Replies

  • AlexKaras's avatar
    AlexKaras
    Community Hero
    12 years ago
    Hi Sean,



    > Project.Variables.AddVariable("t2350_CourseID","String");

    > I know the query returns an integer [...]

    > The problem is a mismatch on this line: Project.Variables.t2350_CourseID = localCourseID;



    What if you change

    Project.Variables.AddVariable("t2350_CourseID","String");

    to

    Project.Variables.AddVariable("t2350_CourseID","Integer");

    ?



    P.S. BTW, I believe that you checked the value of the localCourseID variable in the debugger, aren't you?
  • AlexKaras's avatar
    AlexKaras
    Community Hero
    12 years ago
    Hi Sean,



    > I haven't checked the value of localCourseID. [...] How do I get this value?

    Here you go:

    -- Put the cursor on the 'localCourseID = setCourseID["Execute"]();' line of code in the code editor;

    -- Select Debug|Toggle Breakpoint main menu command and check that the red circle appeared in the gutter;

    -- Run the test code until it stops on this line;

    -- Execute the Debug|Step Over main menu command;

    -- Double click on the 'localCourseID' variable so it is selected;

    -- Execute the Debug|Evaluate main menu command and check the value assigned to the localCourseID variable in the subsequent dialog window.



    > Can I assign the local value with something like Project.Variables.t2350_CourseID.local_value = localCourseID;

    Actually, it is not possible to assign the default value to the project and project suite variables from the test code. Default value can be assigned to the variable from the TestComplete IDE only, whereas all assignments done from the test code operate with variable's local value, so

    Project.Variables.t2350_CourseID = localCourseID;

    should work perfectly.

  • smccafferty's avatar
    smccafferty
    Occasional Contributor
    12 years ago
    Hey there and thanks for the quick reply!



    I haven't checked the value of localCourseID. Sorry about that. Very new to all this.



    How do I get this value?



    I know the QUERY generates the courseID (that col is assigned as an integer) as I've checked the SQL and it's sound.



    the Project.Variables.yourVariable has name type default value, local value. Can I assign the local value with something like...



    Project.Variables.t2350_CourseID.local_value = localCourseID; ?
  • smccafferty's avatar
    smccafferty
    Occasional Contributor
    12 years ago
    You sir are a gentleman and a scholar.

    Right.

    The reason it's not working is the line:
    localCourseID = setCourseID["Execute"]();

    is trying to set localCourseID to a type of [object] (ie the setCourseID["Execute"]() object)

    So it would never work...
    My mistake was thinking that my original code would return an integer as a result of my query.

    here's my problem in a nutshell.
    I'm testing a website attached to a database.
    On the website I create content / courses. They are assigned a new courseID (an integer) automatically.
    That id is then used in the construction of the web pages used in the site to view/edit that content.
    Therefore, everytime I run a test that creates said content, the url to edit or view that content is incremented and testcomplete has problems finding the pages it's meant to.

    I was going to query the courseID from the database, store that returned value in a project variable and reconstruct the url for editing / viewing the course during the test run.

    I wanted to avoid having to reset the database at the beginning of every test (although it would have to be done at some stage!)

    Any advise for this problem gratefully received and thanks so much for your help so far.
  • AlexKaras's avatar
    AlexKaras
    Community Hero
    12 years ago
    > [...] is trying to set localCourseID to a type of [object]

    When variable or expression evaluates to an object in the Evaluate dialog, the Inspect button becomes enabled. If you press it, you'll drill down to the window that might (depending on the evaluated object) show you the fields that can be accessed. Go through the fields' list and check if some of them exposes the CourseID value (most probably, this will be the OleValue one). In this case you should be able to execute something like this:

    Project.Variables.t2350_CourseID = localCourseID.OleValue;