Database connection error: Cannot create, delete tables, or retrieve data in SQL Server

[hautvph17241]

I am trying to create a temporary table in Katalon, and it reflect an issue.
The query shown below is working on SQL Server but when I’m trying to run it on Katalon it doesn’t work.
Here’s my sample query:’

CREATE TEMPORARY TABLE RandomClinics AS
SELECT id
FROM tbl_clinic
WHERE status IN (1, 2, 3, 5) AND is_work = 1
ORDER BY RAND()
LIMIT 20;
CREATE TEMPORARY TABLE PriceRequired1 AS
SELECT 
    p.id AS price_id, 
    pp.doctor_id, 
    p.required
FROM tbl_price p
JOIN tbl_price_package pp ON p.package_id = pp.id
WHERE pp.doctor_id IN (SELECT id FROM RandomClinics)
  AND p.status = 1
  AND pp.status = 1
  AND p.required = 1
ORDER BY p.id DESC;
DROP TEMPORARY TABLE IF EXISTS RandomClinics;

Then here is the Katalon output:

The details of the error:

java.sql.SQLException: Statement.executeQuery() cannot issue statements that do not produce result sets. Query: CREATE TEMPORARY TABLE RandomClinics AS
SELECT id
FROM tbl_clinic
WHERE status IN (1, 2, 3, 5) AND is_work = 1
ORDER BY RAND()
LIMIT 20;
CREATE TEMPORARY TABLE PriceRequired1 AS
SELECT 
    p.id AS price_id, 
    pp.doctor_id, 
    p.required
FROM tbl_price p
JOIN tbl_price_package pp ON p.package_id = pp.id
WHERE pp.doctor_id IN (SELECT id FROM RandomClinics)
  AND p.status = 1
  AND pp.status = 1
  AND p.required = 1
ORDER BY p.id DESC;

CREATE TEMPORARY TABLE PriceRequired0 AS
SELECT 
    p.id AS price_id, 
    pp.doctor_id, 
    p.required
FROM tbl_price p
JOIN tbl_price_package pp ON p.package_id = pp.id
WHERE pp.doctor_id IN (SELECT id FROM RandomClinics)
  AND p.status = 1
  AND pp.status = 1
  AND p.required = 0
ORDER BY p.id DESC;
CREATE TEMPORARY TABLE UniquePriceRequired1 AS
SELECT doctor_id, MIN(price_id) AS price_id
FROM PriceRequired1
GROUP BY doctor_id;

CREATE TEMPORARY TABLE UniquePriceRequired0 AS
SELECT doctor_id, MIN(price_id) AS price_id
FROM PriceRequired0
GROUP BY doctor_id;
(
    SELECT 
        Place.name AS namePlace,
        Clinic.name AS nameDoctor,
        Specialist.name AS specialistName,
        Package.note AS notePackage,
        Price.name AS priceName,
        'bắt buộc' AS requiredPrice,
        Clinic.url AS doctorUrl,
        Clinic.id AS idDoctor,
        Price.discounted_price_min AS priceMinSale,
        Price.discounted_price_max AS priceMaxSale,
        Price.promotion_start_date AS timeStartSale,
        Price.promotion_end_date AS timeEndSale,
        Price.price_min AS priceMin,
        Price.price_max AS priceMax,
        Price.note AS notePrice
    FROM tbl_clinic AS Clinic
    JOIN tbl_price_package AS Package ON Package.doctor_id = Clinic.id 
    JOIN tbl_price AS Price ON Package.id = Price.package_id 
    JOIN tbl_clinic_place AS Place ON Clinic.place_ids LIKE CONCAT('%"', Place.id, '"%')
    JOIN tbl_clinic_specialist AS Specialist ON Package.specialist_ids LIKE CONCAT('%"', Specialist.id, '"%')
    JOIN UniquePriceRequired1 AS PR1 ON Price.id = PR1.price_id
    WHERE Clinic.status IN (1, 2, 3, 5) 
      AND Clinic.is_work = 1
    ORDER BY Clinic.id,
             Price.id DESC
)
UNION ALL
(
    SELECT 
        Place.name AS namePlace,
        Clinic.name AS nameDoctor,
        Specialist.name AS specialistName,
        Package.note AS notePackage,
        Price.name AS priceName,
        'không bắt buộc' AS requiredPrice,
        Clinic.url AS doctorUrl,
        Clinic.id AS idDoctor,
        Price.discounted_price_min AS priceMinSale,
        Price.discounted_price_max AS priceMaxSale,
        Price.promotion_start_date AS timeStartSale,
        Price.promotion_end_date AS timeEndSale,
        Price.price_min AS priceMin,
        Price.price_max AS priceMax,
        Price.note AS notePrice
    FROM tbl_clinic AS Clinic
    JOIN tbl_price_package AS Package ON Package.doctor_id = Clinic.id 
    JOIN tbl_price AS Price ON Package.id = Price.package_id 
    JOIN tbl_clinic_place AS Place ON Clinic.place_ids LIKE CONCAT('%"', Place.id, '"%')
    JOIN tbl_clinic_specialist AS Specialist ON Package.specialist_ids LIKE CONCAT('%"', Specialist.id, '"%')
    JOIN UniquePriceRequired0 AS PR0 ON Price.id = PR0.price_id
    WHERE Clinic.status IN (1, 2, 3, 5) 
      AND Clinic.is_work = 1
    ORDER BY Clinic.id,
             Price.id DESC
);
DROP TEMPORARY TABLE IF EXISTS RandomClinics;
DROP TEMPORARY TABLE IF EXISTS PriceRequired1;
DROP TEMPORARY TABLE IF EXISTS PriceRequired0;
DROP TEMPORARY TABLE IF EXISTS UniquePriceRequired1;
DROP TEMPORARY TABLE IF EXISTS UniquePriceRequired0; Parameters: []
	at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392)
	at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:351)
	at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:226)
	at com.kms.katalon.core.db.SqlRunner.query(SqlRunner.java:117)
	at com.kms.katalon.core.testdata.DBData.fetchData(DBData.java:122)
	at com.kms.katalon.core.testdata.DBData.<init>(DBData.java:32)
	at com.kms.katalon.composer.testdata.parts.DBTestDataPart$7.run(DBTestDataPart.java:306)
	at org.eclipse.swt.widgets.Display.runTimers(Display.java:4601)
	at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3980)
	at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine$5.run(PartRenderingEngine.java:1155)
	at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:338)
	at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine.run(PartRenderingEngine.java:1046)
	at org.eclipse.e4.ui.internal.workbench.E4Workbench.createAndRunUI(E4Workbench.java:155)
	at org.eclipse.ui.internal.Workbench.lambda$3(Workbench.java:643)
	at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:338)
	at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:550)
	at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:171)
	at com.kms.katalon.core.application.WorkbenchApplicationStarter.start(WorkbenchApplicationStarter.java:23)
	at com.kms.katalon.application.Application.runGUI(Application.java:209)
	at com.kms.katalon.application.Application.start(Application.java:114)
	at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:203)
	at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:136)
	at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:104)
	at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:402)
	at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:255)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:659)
	at org.eclipse.equinox.launcher.Main.basicRun(Main.java:596)
	at org.eclipse.equinox.launcher.Main.run(Main.java:1467)

[system]

Hi there,

Thank you very much for your topic. Please note that it may take a little while before a member of our community or from Katalon team responds to you.

Thanks!

[grylion54]

@hautvph17241

cannot issue statements that do not produce result sets

If you read the error message, it is saying that you cannot issue an SQL statement that does not return a “result set”, like a “SELECT blah FROM TABLENAME”. Since you are only trying to create the table, there would be no “result set” returned. Can we incorporate your table creation within (or INTO) a query that returns a “result set”?
Edit: You need a license to use KS with SQL Server.

[hautvph17241]

In MYSQL, I can run the above deviation set but Katalon cannot
SQL:

[hautvph17241]

No sir, full code SQL in file TestData in my project :

CREATE TEMPORARY TABLE RandomClinics AS
SELECT id
FROM tbl_clinic
WHERE status IN (1, 2, 3, 5) AND is_work = 1
ORDER BY RAND()
LIMIT 20;
CREATE TEMPORARY TABLE PriceRequired1 AS
SELECT 
    p.id AS price_id, 
    pp.doctor_id, 
    p.required
FROM tbl_price p
JOIN tbl_price_package pp ON p.package_id = pp.id
WHERE pp.doctor_id IN (SELECT id FROM RandomClinics)
  AND p.status = 1
  AND pp.status = 1
  AND p.required = 1
ORDER BY p.id DESC;

CREATE TEMPORARY TABLE PriceRequired0 AS
SELECT 
    p.id AS price_id, 
    pp.doctor_id, 
    p.required
FROM tbl_price p
JOIN tbl_price_package pp ON p.package_id = pp.id
WHERE pp.doctor_id IN (SELECT id FROM RandomClinics)
  AND p.status = 1
  AND pp.status = 1
  AND p.required = 0
ORDER BY p.id DESC;
CREATE TEMPORARY TABLE UniquePriceRequired1 AS
SELECT doctor_id, MIN(price_id) AS price_id
FROM PriceRequired1
GROUP BY doctor_id;

CREATE TEMPORARY TABLE UniquePriceRequired0 AS
SELECT doctor_id, MIN(price_id) AS price_id
FROM PriceRequired0
GROUP BY doctor_id;
(
    SELECT 
        Place.name AS namePlace,
        Clinic.name AS nameDoctor,
        Specialist.name AS specialistName,
        Package.note AS notePackage,
        Price.name AS priceName,
        'bắt buộc' AS requiredPrice,
        Clinic.url AS doctorUrl,
        Clinic.id AS idDoctor,
        Price.discounted_price_min AS priceMinSale,
        Price.discounted_price_max AS priceMaxSale,
        Price.promotion_start_date AS timeStartSale,
        Price.promotion_end_date AS timeEndSale,
        Price.price_min AS priceMin,
        Price.price_max AS priceMax,
        Price.note AS notePrice
    FROM tbl_clinic AS Clinic
    JOIN tbl_price_package AS Package ON Package.doctor_id = Clinic.id 
    JOIN tbl_price AS Price ON Package.id = Price.package_id 
    JOIN tbl_clinic_place AS Place ON Clinic.place_ids LIKE CONCAT('%"', Place.id, '"%')
    JOIN tbl_clinic_specialist AS Specialist ON Package.specialist_ids LIKE CONCAT('%"', Specialist.id, '"%')
    JOIN UniquePriceRequired1 AS PR1 ON Price.id = PR1.price_id
    WHERE Clinic.status IN (1, 2, 3, 5) 
      AND Clinic.is_work = 1
    ORDER BY Clinic.id,
             Price.id DESC
)
UNION ALL
(
    SELECT 
        Place.name AS namePlace,
        Clinic.name AS nameDoctor,
        Specialist.name AS specialistName,
        Package.note AS notePackage,
        Price.name AS priceName,
        'không bắt buộc' AS requiredPrice,
        Clinic.url AS doctorUrl,
        Clinic.id AS idDoctor,
        Price.discounted_price_min AS priceMinSale,
        Price.discounted_price_max AS priceMaxSale,
        Price.promotion_start_date AS timeStartSale,
        Price.promotion_end_date AS timeEndSale,
        Price.price_min AS priceMin,
        Price.price_max AS priceMax,
        Price.note AS notePrice
    FROM tbl_clinic AS Clinic
    JOIN tbl_price_package AS Package ON Package.doctor_id = Clinic.id 
    JOIN tbl_price AS Price ON Package.id = Price.package_id 
    JOIN tbl_clinic_place AS Place ON Clinic.place_ids LIKE CONCAT('%"', Place.id, '"%')
    JOIN tbl_clinic_specialist AS Specialist ON Package.specialist_ids LIKE CONCAT('%"', Specialist.id, '"%')
    JOIN UniquePriceRequired0 AS PR0 ON Price.id = PR0.price_id
    WHERE Clinic.status IN (1, 2, 3, 5) 
      AND Clinic.is_work = 1
    ORDER BY Clinic.id,
             Price.id DESC
);
DROP TEMPORARY TABLE IF EXISTS RandomClinics;
DROP TEMPORARY TABLE IF EXISTS PriceRequired1;
DROP TEMPORARY TABLE IF EXISTS PriceRequired0;
DROP TEMPORARY TABLE IF EXISTS UniquePriceRequired1;
DROP TEMPORARY TABLE IF EXISTS UniquePriceRequired0;

[albert.vu]

Hi @hautvph17241,

Just checking in to see if you have been able to resolve your error yet.

Did you know, that we are currently running our quarterly Ask Katalon Anything (AKA) activity on the forum? From now till 2024-06-19T16:59:00Z, feel free to ask our Product Support team any Katalon-related questions about setting up & optimize Katalon for your projects, alongside best practices for Web/Mobile/API Testing.

Join AKA by visiting the thread below (simply copy and paste your question in the thread)

[AKA Jun '24] Katalon Studio | Integrations | Web/API/Mobile Testing

[Main Social] Hi folks, Once again, we are thrilled to welcome you to our Ask Katalon Anything activity where you can ask our team about anything Katalon-related, from tips, tricks, and best practices when testing with the Katalon Platform, to industry and general questions, you name it. Within this thread, you will be able to ask our KShare contributors (from our Product Support team) about the following topics: Katalon Studio (and more specifically Integrations & Setup) Web Testing …