Database Connection Error: Unable to fetch data from the table SQLServer

[hannah.martin]

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 TABLE #TEST
(
[TEST_DATA] VARCHAR(256)
)
INSERT INTO #TEST VALUES (‘TEST DATA’)
SELECT * FROM #TEST
DROP TABLE IF EXISTS #TEST

Then here is the Katalon output:

The details of the error:

java.sql.SQLException: Invalid object name ‘#TEST’. Query: CREATE TABLE #TEST
(
[TEST_DATA] VARCHAR(256)
)
INSERT INTO #TEST VALUES (‘TEST DATA’)
SELECT * FROM #TEST
DROP TABLE IF EXISTS #TEST 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.(DBData.java:32)
at com.kms.katalon.composer.testdata.parts.DBTestDataPart$7.run(DBTestDataPart.java:306)
at org.eclipse.swt.widgets.Display.runTimer(Display.java:4128)
at org.eclipse.swt.widgets.Display.messageProc(Display.java:3252)
at org.eclipse.swt.internal.win32.OS.DispatchMessage(Native Method)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3628)
at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine$5.run(PartRenderingEngine.java:1158)
at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:338)
at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine.run(PartRenderingEngine.java:1047)
at org.eclipse.e4.ui.internal.workbench.E4Workbench.createAndRunUI(E4Workbench.java:155)
at org.eclipse.ui.internal.Workbench.lambda$3(Workbench.java:658)
at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:338)
at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:557)
at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:154)
at com.kms.katalon.core.application.WorkbenchApplicationStarter.start(WorkbenchApplicationStarter.java:23)
at com.kms.katalon.application.Application.runGUI(Application.java:191)
at com.kms.katalon.application.Application.start(Application.java:102)
at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:203)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:137)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:107)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:401)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:255)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:657)
at org.eclipse.equinox.launcher.Main.basicRun(Main.java:594)
at org.eclipse.equinox.launcher.Main.run(Main.java:1447)

Is anyone familiar on the encountered issue and the solution? Would appreciate your help. Thank you!

[kazurayam]

Try enclosing the table name in “…”

CREATE TABLE "#TEST"

or just remove special character “#”

CREATE TABLE TEST

[hannah.martin]

I am tying to create a temporary table on Katalon, and have tried this method but it also didn’t work. But when I’ve ran it on the SQL Server, it shown the created temporary table.

[kazurayam]

@hannah.martin

have tried this method but it also didn’t work.

You didn’t enclose the table name with “…”, did you?

[kazurayam]

Each DBMS tends to have differences in the SQL syntax.

I know, SQL Server accepts a # character in a table name. But it is rather exceptional.

MySQL does not accept a # character in a table name without quotes.

Also the JDBC drivers does not allow # in table name WITHOUT quotes.

Now you want to execute SQL in Katalon Studio. This means you will inevitably use the JDBC driver.
You have to obey the syntactical rules asked by JDBC driver. So, you should enclose the table name with double quotes like “#TEST”.

[hannah.martin]

I have tried the both method but it still reflects the same issue. Here’s an output:

Aside from those option, do you have more suggestions that I can try to make work?

[kazurayam]

Please join,

@duyluong
@Brandon_Hein

[loc.nguyen]

@kazurayam,

Mainly, SQL Syntax has 3 types of command: DCL, DDL, DML.
Up to now, I guess that just support DML only
For DCL & DDL we cannot use it.

FYI

[kazurayam]

I do not have SQL Server in hand.
So I can not contribute to this topic any more.

[hannah.martin]

thanks for the clarification though @kazurayam @loc.nguyen, i’ve temporarily change the temporary table into table variable for the meantime to fetch data.