0

I am trying to call a stored procedure through groovy in ReadyAPI. I am doing it this way, because the built in JDBC request does not seem to handle INOUT parameters.

The call seems like it works, but it doesn't actually make the call from what I can tell. One of the parameters is an INOUT parameter and after the call, I just get back what I passed in. There is supposed to be a conversion of the value passed. Also, there is some database logging that should happen that is not. However, the 2 OUT parameters are returned as expected. If I take the same call from the script and run it in a database tool, everything works as expected, so there is not an issue with the stored proc itself.

Here is the code from groovy script:

import groovy.sql.Sql

def globalUser = context.expand('${#Global#Username}')
def globalPassword = context.expand('${#Global#Password}')

def sql = Sql.newInstance("jdbc:as400://server/library", globalUser, globalPassword, "com.ibm.as400.access.AS400JDBCDriver")

sql.call("{call sp_storedproc('inparm1', 'inparm2', 'inparm3', 'inparm4', ${Sql.inout(Sql.VARCHAR('inout5'))}, ${Sql.VARCHAR},${Sql.VARCHAR})}") { parm5, parm6, parm7->
log.info parm5
log.info parm6
log.info parm7
}

I have also tried calling the stored procedure like this, but I always get data type mismatch errors. I don't know what the real difference is.

sql.call '{call storedproc(?,?,?,?,?,?,?)}', 
["inparm1", "inparm2", "inparm3", "inparm4", "${Sql.inout(Sql.VARCHAR('inout5'))}", "${Sql.VARCHAR}", "${Sql.VARCHAR}"]

Error message for Data Mismatch:

Wed Jun 21 08:46:01 EDT 2017:ERROR:java.sql.SQLException: Data type mismatch.


java.sql.SQLException: Data type mismatch.
    at com.ibm.as400.access.JDError.throwSQLException(JDError.java:385)
    at com.ibm.as400.access.SQLChar.set(SQLChar.java:152)
    at com.ibm.as400.access.AS400JDBCPreparedStatement.setValue(AS400JDBCPreparedStatement.java:3173)
    at com.ibm.as400.access.AS400JDBCPreparedStatement.setObject(AS400JDBCPreparedStatement.java:2640)
    at groovy.sql.Sql.setObject(Sql.java:4137)
    at groovy.sql.Sql.setParameters(Sql.java:4102)
    at groovy.sql.Sql.call(Sql.java:3007)
    at groovy.sql.Sql$call$3.call(Unknown Source)
    at Script13.run(Script13.groovy:28)
    at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.run(SoapUIGroovyScriptEngine.java:92)
    at com.eviware.soapui.support.scripting.groovy.SoapUIProGroovyScriptEngineFactory$SoapUIProGroovyScriptEngine.run(SoapUIProGroovyScriptEngineFactory.java:79)
    at com.eviware.soapui.impl.wsdl.teststeps.WsdlGroovyScriptTestStep.run(WsdlGroovyScriptTestStep.java:156)
    at com.eviware.soapui.impl.wsdl.panels.teststeps.GroovyScriptStepDesktopPanel$RunAction$1.run(GroovyScriptStepDesktopPanel.java:274)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)

Thanks in advance for any help.

JesseN
  • 47
  • 1
  • 9
  • 1
    Could you add declaration of your stored procedure. – daggett Jun 21 '17 at 12:25
  • I am not following what you are asking, but I updated my original post with my complete groovy script. The "{call sp_storedproc" is the actual name of the stored procedure, I just edited the name to not use our real names. It is not being passed a variable of any kind. – JesseN Jun 21 '17 at 12:35
  • 1
    And also provide an error message. – daggett Jun 21 '17 at 12:41
  • There is not an error message for the first block of code, which is what I am concerned with as I have got the furthest with that. As mentioned above, I am just not getting expected results/ behavior. I added the second block of code just to show what else I have tried. I am getting an error there, but less concerned with that, unless it can be fixed and give me the results I am expecting. – JesseN Jun 21 '17 at 12:50
  • 1
    I mean could we see how parameters declared in your stored procedure? – daggett Jun 21 '17 at 12:53
  • The stored proc calls an external RPG program that I do not have access to the code. I can post the parameters expected and the data types if you want, but they are all "CHARACTER" types of different lengths. – JesseN Jun 21 '17 at 13:03
  • May be you do not have access the stored procedure which is agreed. But at least, you should be aware of how many arguments along with their data types to be passed to the stored procedure. If all are of character type, then the error is not expected. Probably you may want to talk to your team or service provide regarding this; possibly a defect. – Rao Jun 22 '17 at 04:14
  • By the way, what is the 4th argument in code snippet? – Rao Jun 22 '17 at 04:16
  • The first 4 are IN parameters, the 5th is an INOUT, and the last 2 are OUT parameters, all character types. I have access to the stored proc because I can call it and get its expected results directly through the iNavigator tool. Just groovy is giving me a problem. – JesseN Jun 22 '17 at 13:17

1 Answers1

0

I got it to work finally. I used the Sql.newInstance format of (url, properties, driver) rather than (url, user, password, drivers) and passed everything that would normally be passed in a JDBC connection string in ReadyAPI.

When I set the libraries property, I added a couple of other libraries that exist on the server.

Seems that the problem was not passing every library that I needed into the newInstance connection string. I was only passing the library where the stored proc itself existed. There probably is a bug in stored proc as mentioned above because an error was not being returned and probably should have.

JesseN
  • 47
  • 1
  • 9