cancel
Showing results for 
Search instead for 
Did you mean: 

ReadyAPI - SQL Query Prepared Parameters for Schema and Table Name

Highlighted
Senior Member

ReadyAPI - SQL Query Prepared Parameters for Schema and Table Name

ProjectDBSchema prepared property gets its value from a global ENV variable which is toggled by switching the environment.  Switching environments changes the schema name and the connection details.  I had a few problems putting the schema name in the connection string so i am using Prepared Properties.  Can anyone assist?

 

Running this SQL:

SELECT ROW
FROM : ProjectDBSchema.TABLEA
FETCH FIRST 1 ROWS ONLY

 

returns

DB2 Error, SQL Code -104,  SQL State = 42601.

 

If i use "get data" to find the Prepared Param 

SELECT ROW
FROM ${JDBC Request#ProjectDBSchema}.TABLEA
FETCH FIRST 1 ROWS ONLY

 

then i see this error

ProjectDBSchema was not found in the SQL Query

 

 

2 REPLIES 2
Highlighted
Community Hero

Re: ReadyAPI - SQL Query Prepared Parameters for Schema and Table Name

Hi @GKS,

Its been about 4 years since i touched db2, but here are my thoughts. Firstly i need to point out that db2 is more fiddly than most. Doing anything in db2 is a nightmare relative to other RDBMSs such as oracle or sqlserver. Db2 actually supports different SQL methods (e.g. LTRIM, RTRIM, TRIM etc.) depending on what operating system the db is running on! (I know, right? Crazy!).

Firstly it looks like even when youre not parameterising the schema name value, youre still getting the -104 error response.
Whenever i am struggling to get my jdbc steps working in ReadyAPI!/SoapUI, (especially when db2!) I always try to execute my query in a separate db interrogation tool that uses the same mechanism to connect to the database (same driver, same jdbc connection string).

So...don't use SPUFI or whatever is the mainframe option you have to query a database, use IBM Data Studio (Data Studio is actually Eclipse with a couple of additional .jar files) or something equivalent that uses the same java, .jar driver files and jdbc connection string as you have setup in ReadyAPI!/SoapUI

Just execute a simple query - e.g.

Select current date from sysibm.sysdummy1

If this query works in Data Studio or whatever other.tool your using to query your db2 database, execute the same query in ReadyAPI!/SoapUI's jdbc test step to compare. If you have the same results....great! If you have different results youll know you have to look at your ReadyAPI!/SoapUI config.

Taken from your post you state the following:

Running this SQL:

SELECT ROW
FROM : ProjectDBSchema.TABLEA
FETCH FIRST 1 ROWS ONLY

Is this a straight copy from ReadyAPI!/SoapUI cos you have an erroneous : character in between the words 'FROM' and 'ProjectDBSchema'. This colon character needs to be removed.

Also in both your queries you specify you seem to be retrieving the ROW attribute from the table. Is there definitely a column entitled ROW in your TableA table? If there isn't, then this query wont work. You need to either specify column names and asterisk or say 1 (or change ROW to rowid IF YOU ACTUALLY WANT TO USE ROWID).

Also. Ensure you finish the query off with a semi colon (';'). I know they relaxed the requirement to finish a query with semi colons years ago but it still helps the query analyser/parser if you include the semi colon.

Oh. One more thing. Dependinf on thr implementation of RDBMS, you might be able to add in a parameter to your jdbc connection string to include the schema name. You might have to add in a new db2 connection string to ReadyAPI!/SoapUI yourself, but if you can add in thr schema name to your connection string and details it would mean that if youre only querying a single schema that you dont have to include the schema name as table locators in your queries so instead of

select * from schema.table;

You could instead write your queries as

select * from table;

Summary of above
1. Run the query in IBM Data Studio or another db interrogation tool.to check your db connection is setup ok in soapui
2. Fix the mistakes in your SQL i mentioned (remove : char and replace ROW with genuine attribute name)
3. Add semi colon to the end of all queries
4. Investigate adding schema parameter to jdbc connection string enabling you to remove requirement of specifying table locator in queries

Hope this helps,

Rich

if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta
Highlighted
Community Manager

Re: ReadyAPI - SQL Query Prepared Parameters for Schema and Table Name

Thank you richie for the reply. 

Hi @GKS , did you find the suggestion useful? 


Sonya Mihaljova
Community and Education Specialist

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors