Forum Discussion

marroth_ds's avatar
marroth_ds
Occasional Contributor
12 years ago

Creating database checkpoint by script

Hi,

I'm looking for a way to create a database checkpoint by script which should use a custom SQL query.

Because this way I could use my variables in the SQL query and to set which database to use. If you have a better way of achieving this, I'm all ears.

8 Replies

  • TanyaYatskovska's avatar
    TanyaYatskovska
    Icon for Alumni rankAlumni
    12 years ago

    Hi Martin,


     


    You can change the connection string, login and password at run time via the DBTable object's properties. However, SQL queries cannot be changed dynamically. There is a suggestion to implement this feature in our DB - I've increased its rating. 


     


    Currently, if you need to execute dynamic SQL statements, I recommend that you use one of the approaches described in the "Working With Databases" article.


     

  • marroth_ds's avatar
    marroth_ds
    Occasional Contributor
    12 years ago
    Thank you Tanya, however I'm finding the documentation on this a bit confusing.



    What I'm trying to do is a simple query  "SELECT * FROM table WHERE id = X", the problem is the X as this is unique to each test run. I want to use this to check that some fields has correct data in them.



    Is there a way to use the database table checkpoint wizard and get a variable from the project for the SQL query? (what I'm asking is if there is a non-script way of doing this)

  • kirk_bottomley's avatar
    kirk_bottomley
    Contributor
    12 years ago
    "SQL queries cannot be changed dynamically. There is a suggestion to implement this feature in our DB - I've increased its rating."



    Chalk up another request on that, please. That would make some of my projects much simpler.
  • TanyaYatskovska's avatar
    TanyaYatskovska
    Icon for Alumni rankAlumni
    12 years ago

    Hi,


     


    Kirk, I've added your vote!


     


    Martin, at the moment, you can accomplish your task only with scripting.


     

  • Colin_McCrae's avatar
    Colin_McCrae
    Community Hero
    11 years ago
    I use parameterised SQL DB queries in my tests all the time.



    But mine are all scripts.



    If you are able to use script units, Tanya's link contains all the info you need. I'm using an ADO connection and have a central script function which does all my queries.



    What I don't like so much is the database record(s) object it returns. I find that very cumbersome to work with. I normally end up dumping the results into a string array and working with that instead as I find it's actually easier.



    Worth a look even if you don't normally script. You obviously already know the SQL queries, so you just need to work out the connection string to use and then a few commands around interacting with the DB. Not that tough if you already have a reasonable understanding of how DB's work in the first place ...
  • dcaboni's avatar
    dcaboni
    New Contributor
    11 years ago
    Thx Colin.

    This morining iI just start to create a function to use dynamic SQL. It will be easier to do it this way I guess.
  • Colin_McCrae's avatar
    Colin_McCrae
    Community Hero
    11 years ago
    Cool.



    Bear in mind my comment about the returned record set object. Horrible to work with in my opinion. String array is much easier. From memory (I don't have TC loaded up at the moment) there is a method of the record set object to dump the entire output into an array in one go. That's what I then do comparisons against.
  • dcaboni's avatar
    dcaboni
    New Contributor
    11 years ago
    Hi all,



    Tanya, could you please add one vote for me, i'm aslo interested in this (futur ?)  functionality. Thx :)