JDBC (Oracle) Connect/Disconnect in SoapUI

blockquote>IT WON’T RUN!?   IT WON’T WORK!?

For Oracle users you need the Oracle Driver that doesn’t come with SoapUI package. Go to oracle.com or for your convenience download here:
ojdbc6

For MySQL Issues – click here: mysql-connector-java-3.0.14-production-bin

Below are the two paths you need the ojdbc6 file in.

JDBC Test Step
C:\Program Files\SmartBear\soapUI-Pro-4.5.1\bin\ext

Groovy Script Driver Directory
C:\Program Files\SmartBear\soapUI-Pro-4.5.1\lib

Particularly – I don’t use the Database Groovy Script at work because I don’t feel like teaching everyone to put the jar file in both directories. The JDBC TestStep works fine anyway, but if I have issues with Oracle TimeOut then I’ll go that route to open/close connections through SETUP SCRIPTS.

JDBC Test Step

If you had SoapUI open during the placement of the oracle jar you have to close SoapUI and reopen.

WRITING SSTATEMENTS

Tip 1:  Don’t use query builder, it takes forever to open and drains your memory.
Tip 2:  If you have a Property created in the test step you HAVE TO USE! If you don’t the query will not run. i.e. if you have firstName & lastName in properties and you run [SELECT * FROM customer where first_name = :firstName] it will not work because you’re not using :lastName
Tip 3:  If you have a property you don’t use tics such as ‘lastName’. You use :lastName
Tip 4:  Do not close the query with a semicolon
Tip 5:  Error message is subtle, it’s located under the Assertions/Request Log tabs below and in black text. So if you run the query and nothing happens, look there.

JDBC through Groovy

You will have to edit the def JDBC string username/password/hostname and servicename:

import groovy.sql.Sql
import oracle.jdbc.driver.OracleTypes
def JDBC = "jdbc:oracle:thin:username/password@//hostname.net:1521/SERVICENAME"

def sql = Sql.newInstance( JDBC )
def result = sql.eachRow(“SELECT mac_address FROM WHERE =” AND rownum < 2″)
{
row -> log.info(row.mac_address)
}