Collaborator Database Queries
Collaborator has a robust, built-in Report tool, offering many selections and filtering options to gather the information that is important to your review process. However, there are times when the one option you are looking for isn't one of the available selections or filters. Here's where Collaborator's associated SQL database comes in for generating custom reports. Specific information on the database schema and table relationships are foundhere in the online documentation. Additionally, SmartBear's Support pages for Collaborator offers many useful links to videos and other artifacts to help with your Collaborator deployment. I recently had a customer request for some assistance on verifying if specific commits had been associated with reviews. Collaborator has a report generation option for this specific request, but the request was based on some scripting they were doing to enhance their CI/CD process. I put together a quick general query that they can use as a basis for refining exactly what they need for their script. The SQL query below is used to look at your review IDs, and output a table showing the title, phase, and associated commits with those reviews. To get this report, I used the schema diagram to find my associated SQL tables and how they linked together, then used a few nested JOIN statements for the formatting I needed. Collaborator also provides SQL output scripts used to generate many of the reports in the Reports tab. These can be used as great starting points to develop the queries you need for your reports and data parsing, and how I got started on the development of the script for this request. In short, take advantage of the information in the database to enhance your reporting and automated workflows. Sample SQL query: SELECT review.review_id AS `Review ID` ,review.review_title AS `Review Title` ,phase.phase_title AS `Phase` ,version.version_scmversionname AS `Commit ID` FROM review INNER JOIN joinreviewchangelist ON review.review_id = joinreviewchangelist.joinreviewchangelist_reviewid INNER JOIN changelist ON joinreviewchangelist.joinreviewchangelist_changelistid = changelist.changelist_id INNER JOIN version ON changelist.changelist_id = version.version_changelistid INNER JOIN phase ON review.review_phaseid = phase.phase_id ORDER BY review.review_id DESC Sample output in MySQL Workbench:Dynamic query, temp table, insert select
Hi, I went thru this older post https://community.smartbear.com/t5/SoapUI-Pro/Stored-Proc-DYNAMIC-SQL-returns-no-rows/td-p/42621 And i also have the similar problem, I am creating a temp table, inserting the data using a SP and then doing some select based on my criteria I am using JDBC test step in my test case to execute this, whenever I am executing it, I am not getting any error, instead i m getting <Results> <UpdateCount>0</UpdateCount> </Results> Although, I expect the output of my Select query which is my last statement. I can see it use to be a bug in earlier version. Not sure if that is still an issue, or am I missing something. Thanks in advance. P.S: It is working fine if I am executing the same in SSMS(Sql Server Management Studio) Regards, Andy
ReadyAPI 1.9.0 JDBC Request converted all received SQL column names to the upper case
I have recently upgraded from 1.8.5 to 1.9.0 and see this issue - now i have to rebuild all my assertions - Please let me know is there any global setting which can control this behaviour? Product - Ready API 1.9.0 - SOAPUI NG
Issue in SOAP UI with SQL using Grovvy Script
I am hitting DB2 database using grovvy scripts in SOAPUI, the below code was working fine for me for sometime. However suddenly its not working and I am stuck and unable to find a solution The query:SELECT Column_ID FROM Table_Name where Column_TIMESTAMP>'2016-10-26 05:37:22' Below is the code def sqlQueryPopID = "SELECT Column_ID FROM Table_Name where Column_TIMESTAMP>'2016-10-26 05:37:22'" def popID // The code is not entering in the below loop, however was working fine a day back, not sure what happened sqlITOD.query(sqlQueryPopID) {resultSet -> while (resultSet.next()) { popID = resultSet.getString(1) log.info("Popultauin ID is:"+popID) } }//end of result set log.info("The Sql Query:"+sqlITOD.query(sqlQueryPopID) )//This line is giving below Error ERROR: Wed Oct 26 05:37:58 EDT 2016:INFO:groovy.lang.MissingMethodException: No signature of method: groovy.sql.Sql.query() is applicable for argument types: (java.lang.String) values: [SELECT Column_ID FROM Table_Name where Column_TIMESTAMP>'2016-10-26 05:37:22'] Possible solutions: query(java.lang.String, groovy.lang.Closure), query(groovy.lang.GString, groovy.lang.Closure), query(java.lang.String, java.util.List, groovy.lang.Closure), query(java.lang.String, java.util.Map, groovy.lang.Closure), query(java.util.Map, java.lang.String, groovy.lang.Closure), every() Please help me what went wrong as the current code was working fine, somehow it stopped working
Unable to transfer data from a data source into SQL query
I'm trying totransfer data from a data source to be used in a where statement of a SQL query. Essentially my script does the following: Holds “ID” numbers in a grid data source Executes a SOAP request and one of the fields uses the “ID” numbers from the grid data source ${DataSource#IDNumber} (have looped this at the end of the test case to go back and use the next ID number and run again) Uses a Microsoft SQL Server driver to connect to a DB and run a Select SQL query on one of the tables and compare this against some of the data in the SOAP request I can do this for one “ID” number if I write it as where = '123' however I want to be able to use the ID number that was used in the test case for that loop i.e ${DataSource#IDNumber}. I have tried it writing the below but it doesn’t work can someone help please? Select * From OpsControl.Piece Where OpsControl.Piece.TrackingNumber = 'DataSource#IDNumber' Many thanks Chris Hi,
How to address properties in MySQL query
Hi there, I hope someone could help, I have the following scenario: I am using DataSource "Data Connection" to connect to our database. I have a global property on TestSuite level named "propEntityID" w/ value 20 for example. Now I have the following SQL select statement: SELECT * FROM databasetable WHERE entity_id = ${#TestSuite#propEntityID} When I run this I get a Soap error: "Can't get the Connection for specified properties; com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column '$' in 'where clause'" as well as a Java Error "Unexpected token "{" at line 3, pos 50". How do I correctly address the TestSuite property and how do I mask/escape the special characters such as "$" and "{" respectively? Help appreciated. Kind regards
