Ask a Question

I need to run a SQL update command once keyword test completes its run. I have DB connected to TC

contacteswart
Occasional Contributor

I need to run a SQL update command once keyword test completes its run. I have DB connected to TC

Hello Coomunity,

I have a keyword test which runs for a product purchase. Before continuing with another run I need to run an Update SQL query to ensure the product is deleted from users purchase list. How can I do that.

I mean I want to insert this at the end of script run so that Product is deleted automatically every time when script runs.

Thanks in advance for your help

Eswara V

18 REPLIES 18
tristaanogre
Community Hero

Re: I need to run a SQL update command once keyword test completes its run. I have DB connected to T

Honestly, it's a LOT easier to write SQL interaction code in Script rather than trying to shoe-horn it into the format of keyword testing.

 

Check out https://support.smartbear.com/testcomplete/docs/testing-with/advanced/working-with-external-data-sou...

 

OR, follow the link in my signature... there's a script extension I wrote that encapsulates a lot of this stuff for executing SQL queries.


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
contacteswart
Occasional Contributor

Re: I need to run a SQL update command once keyword test completes its run. I have DB connected to T

Hello 

Thanks for your reply. 

I went through the first link given by you. Not sure where to include my query which is to be executed?

Also 

Went through the script extension given in your signature. I am unable to compile that code after updating it for my requirement. I use Oracle SQL Developer to run my query. Details look like this 

SQL Type: MSDASQL.1

Database Name: ENTUAT_SRV

Server Name: UAT4

 

While updating above details and executing the script I am getting the following error 

Statement is not valid in a namespace.

Thanks in advance for your time and help. 

Now lot of automation I have done in Keyword driven. Now I can not revert back my changes to Scripting 

At this point in time I am planning to include it as a Run Script at the place where I want this script to be executed 

 

Thanks 

Eswara V 

tristaanogre
Community Hero

Re: I need to run a SQL update command once keyword test completes its run. I have DB connected to T

The first link is simply a starting point, describing the ADO objects available in TestComplete and giving a quick overview on how to use them for writing a query.  Basically, what is in that link is what I used to build the script extension.

 

As for the script extension... what do you mean by "unable to compile that code after updating it for my requirement"?  What did you change?  How did you "compile" it?  The extension is provided as-is so, any alterations you made to it I cannot support directly but I'd be happy to help you figure things out.


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
tristaanogre
Community Hero

Re: I need to run a SQL update command once keyword test completes its run. I have DB connected to T

FYI, the extension as it is currently written only supports MSSQL and MYSQL.  Support for Oracle would need to be added so, if you did so, I'd like to see what code you changed and how you implemented it.


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
contacteswart
Occasional Contributor

Re: I need to run a SQL update command once keyword test completes its run. I have DB connected to T

Hello 

I am not sure what I have to update for Oracle SQL. I was under impression that I should give required parameters for Databse Type and Name. I didn't modify the code given except inputting servernames and credentials. I have attached the properties details of my Oracle SQL developer. 

I've attached the file too to this post. 

Please give a look and advise me further 

Thanks a lot 

Regards

Eswara V 

 

 

 

 

tristaanogre
Community Hero

Re: I need to run a SQL update command once keyword test completes its run. I have DB connected to T

OK... first of all.... you copied my sample code from bitbucket... which, by itself, isn't bad... however, that code is in JavaScript/JScript... based upon the extension of the file you sent me, you're writing your code in VBScript.  Syntax is ENTIRELY different so, that's why it won't work as written.  You'll need to adapt my code for VBScript.

 

Secondly, since you need this done for Oracle, I can modify my extension to support Oracle connection strings...  This will take a bit to do so, probably a few hours, but I don't mind.  However, understand that I don't have an Oracle DB server to test this on so I can make what LOOKS to be right but I can't make any guarentees it works until you try it.

 

Thirdly, the following line is incorrect, even in Javascript:

 

queryObject = SQLUtilities.NewQueryObject(Update student_course_enrolment 
      Set WITHDRAWN_DATE = '01-AUG-18' where STUDENT_COURSE_ENROLLEMNT_ID IN (Select STUDENT_COURSE_ENROLLEMNT_ID from student_course_enrolment where student_id = XXXXXXX AND WITHDRAWN_DATE IS null ;	))

The reason being is that the query that you send to NewQueryObject needs to be a string.  There are no string symbols around your query so it's not going to be able to determine what's the query... it's going to just fail at this point.  Again, you'll need to make the necessary changes for VBScript but, in JavaScript, it should look like

 

queryObject = SQLUtilities.NewQueryObject("Update student_course_enrolment 
      Set WITHDRAWN_DATE = '01-AUG-18' where STUDENT_COURSE_ENROLLEMNT_ID IN (Select STUDENT_COURSE_ENROLLEMNT_ID from student_course_enrolment where student_id = XXXXXXX AND WITHDRAWN_DATE IS null ;	"))

So... give me some time to get the extension updated for Oracle...  


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
tristaanogre
Community Hero

Re: I need to run a SQL update command once keyword test completes its run. I have DB connected to T

FYI, one of the pros of script extensions is that ANYONE can edit a script extension, not just the developer.  Change the extension from tcx to zip, open it in your favorite compression utility, find the code file, edit, update the zip, change the extension to tcx... and you're golden.  


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
contacteswart
Occasional Contributor

Re: I need to run a SQL update command once keyword test completes its run. I have DB connected to T

Hello 

Appreciate your patience. Thanks for detailed extension.

I dont have any restrictions if it is VB or Java. I want to insert it as a Run Script and call it after every execution so that Withdrawn date is populated with some previous date so that product gets un-enrolled from user.

For the ease of connection string , I am able to connect to database to add a Db check point. Connection string is as given below 

Provider=MSDASQL.1;Persist Security Info=False;User ID=enterprise_uat4;Data Source=UAT4.

Password you can keep it blank I will fill it out 

 

I am also exploring it from db check point side where in I can write a custom Query in the last page of the wizard. However after writing it I am receiving the following error message 

Database check point > Connection details > Custom Query > paste the following query 

"Unable to execute SQL query

Command Text doesnt not return a result set "

 

SQL statement 

Update student_course_enrolment
Set WITHDRAWN_DATE = '01-AUG-18'
Where STUDENT_COURSE_ENROLLEMNT_ID IN
(Select STUDENT_COURSE_ENROLLEMNT_ID from student_course_enrolment where student_id = XXXXXXX AND WITHDRAWN_DATE IS null );

 

My query doesn't return any value instead it will update Withdrawn date with some previous date so that product gets un-enrolled.

 

Thanks 

Eswara V

 

contacteswart
Occasional Contributor

Re: I need to run a SQL update command once keyword test completes its run. I have DB connected to T

Okay. Thanks for telling that. Our environment is very restricted if download a file from net and try opening I will be prompted with Admin credentials which I dont have

Thanks 

Eswara V

cancel
Showing results for 
Search instead for 
Did you mean: