JDBC Request - Excel data processing

Since you successfully connect database, I recommend that firstly you should use SQL to query database see if have data return. and then try to read data from Excel. I am not sure if you can get data from Excel, but here is an example, maybe will help you find the solution.
http://www.soapui.org/forum/viewtopic.php?f=5&t=14751&sid=0ecb1915c0f5c103d3b47b840ba2766b

Yes. I'm already doing this...I have two Groovy Scripts, one reads from the Excel, the other writes to the Excel.

What I'm trying to do is replicate that behavior using the JDBC Request Test Step(s)...Not everyone in my group knows (or likes) Groovy scripting...

Here's what I have so far:

read-uat-data - Groovy

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

//
// SCRIPT : read-uat-data
// DATE : 09/13/2012
// DEVELOPER : Adym S. Lincoln
// PURPOSE :
// Will open and read an Excel spreadsheet file using JDBC:ODBC drivers. It
// assumes the Excel Spreadsheet has a key column named tc (test case). It
// picks up the key value from the local soapUI Test Case tc-properties file,
// tc-properties#tc.
//
// It then reads that row from the Excel and populates the soapUI Test Case
// Properties in tc-properties...
//

// KEY : Pull the key value from the local properties Test Step...
def keyValue = context.expand( '${tc-properties#tc}' );

// EXCEL : Where is the Excel file located, fully qualified path...
//def xlsFile = "C:\\My Data\\projects\\ao\\uat-data.xls"
def xlsFile = context.expand( '${tc-properties#file}' );

// JDBC : Defined and instantiate tje JDBC:ODBC driver...
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

// JDBC : Define the connection string...
Connection jdbcConnection = null
// STATEMENT : Create a database statement object...
Statement stmt = null
// READ : Read in the record from the Excel file...if any...
ResultSet recordSet = null

try {
// JDBC : Define the connection string...
jdbcConnection = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" + xlsFile + ";ReadOnly=0", "", "");
// STATEMENT : Create a database statement object...
stmt = ((Connection) jdbcConnection).createStatement();
// READ : Read in the record from the Excel file...if any...
recordSet = stmt.executeQuery("Select * from [Sheet1\$] where tc like '" + keyValue + "%'");
//recordSet = stmt.executeQuery("Select * from [Sheet1\$]");

def llngRows = 0

while ( recordSet.next() ) {
log.info(recordSet.getString("tc") + " - " + recordSet.getString("ticket"))
testRunner.testCase.testSteps["tc-properties"].setPropertyValue( "logical-name", recordSet.getString("logical-name") )
testRunner.testCase.testSteps["tc-properties"].setPropertyValue( "asset-id", recordSet.getString("asset-id") )
testRunner.testCase.testSteps["tc-properties"].setPropertyValue( "office-id", recordSet.getString("office") )
testRunner.testCase.testSteps["tc-properties"].setPropertyValue( "subject", recordSet.getString("subject") )
testRunner.testCase.testSteps["tc-properties"].setPropertyValue( "priority", recordSet.getString("priority") )
testRunner.testCase.testSteps["tc-properties"].setPropertyValue( "ticket-ebond", recordSet.getString("ebond-ticket") )
testRunner.testCase.testSteps["tc-properties"].setPropertyValue( "open-comment", recordSet.getString("open-comment") )
testRunner.testCase.testSteps["tc-properties"].setPropertyValue( "resolve-comment", recordSet.getString("resolve-comment") )
testRunner.testCase.testSteps["tc-properties"].setPropertyValue( "comment-comment", recordSet.getString("comment-comment") )
testRunner.testCase.testSteps["tc-properties"].setPropertyValue( "escalate-comment", recordSet.getString("escalate-comment") )
llngRows++
}

if ( llngRows == 0 ) {
log.warn ( "No records found for Test Case ID [" + keyValue + "]" )
}
testRunner.testCase.testSteps["tc-properties"].setPropertyValue( "rowcount", llngRows.toString() )
}
catch (ltheXcp) {
log.warn ( ltheXcp )
}
finally {
recordSet.close();
stmt.close();
jdbcConnection.close();
}


write-uat-data - Groovy

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

//
// SCRIPT : write-uat-data
// DATE : 09/13/2012
// DEVELOPER : Adym S. Lincoln
// PURPOSE :
// Will open and write to an Excel spreadsheet file using JDBC:ODBC drivers. It
// assumes the Excel Spreadsheet has a key column named tc (test case). It
// picks up the key value from the local soapUI Test Case tc-properties file,
// tc-properties#tc.
//
// It then write data to that row as needed...you supply the UPDATE statement...
//

// KEY : Pull the key value from the local properties Test Step...
def keyValue = context.expand( '${tc-properties#tc}' );

// EXCEL : Where is the Excel file located, fully qualified path...
//def xlsFile = "C:\\My Data\\projects\\ao\\uat-data.xls"
def xlsFile = context.expand( '${tc-properties#file}' );

def ticketNumber = context.expand( '${tc-properties#ticket-lmig}' );

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection jdbcConnection = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" + xlsFile + ";ReadOnly=0", "", "");
Statement jdbcStatement = ((Connection) jdbcConnection).createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

q2 = "update [Sheet1\$] set [ticket] = '" + ticketNumber + "' where tc like '" + keyValue + "%'"
jdbcStatement.executeUpdate(q2)

jdbcStatement.close();
jdbcConnection.close();