[MO-java-users] Testing multiple sequential SQL statements in one call.

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Hi,

I (Roger) have a similar problem like Keith using multiple sequential SQL
statements.

The code fragment to be tested is:

				rs.next();

customerAddress.setStreetName(rs.getString("STREET_NAME"));
				logger.debug("Street name: " +
customerAddress.getStreetName());

customerAddress.setHouseNumberExtension(rs.getString("HOUSE_NO_EXT"));

customerAddress.setHouseNumber(rs.getInt("HOUSE_NO"));

customerAddress.setPostcode(rs.getString("POSTCODE"));

customerAddress.setCity(rs.getString("CITY"));

customerAddress.setCountry(rs.getString("COUNTRY"));

customerAddress.setType(rs.getString("TYPE"));

The test code is:

		// FORM STATEMENTS SETUP
		mockSingleRowResultSet.addExpectedNamedValues(new String[]
			{
				"ID", "OUTLET_CODE", "STATE",
"DECLINE_REASON", "ADVISOR",
				"CREATED", "LAST_CHANGED", "FORM_TYPE",
"FORM_DESCRIPTION",
				"SHORT_FORM_DESCRIPTION",
"SUPPORTS_MULTIPLE_MSISDN",
				"PROCESSING_TIME", "PROCESSING_TIME_UNIT",
				"AUTOMATIC_PROCESSING",
"ADVISOR_AUTHORIZATION_REQ",
				"PRINT_CONTRACT_ON_SUBMIT", "BUSINESS_NAME",
"PHONE_NUMBER",
				"PHONE_NUMBER_CS", "FAX_NUMBER", "WEBSITE",
"EMAIL",
				"CONTACT_TITLE", "CONTACT_FIRST_NAME",
"CONTACT_INITIALS",
				"CONTACT_MIDDLE_NAME", "CONTACT_LAST_NAME",
"STREETNAME_VISITOR",
				"HOUSENO_VISITOR", "HOUSENO_EXT_VISITOR",
"POSTCODE_VISITOR",
				"CITY_VISITOR", "COUNTRY_VISITOR",
"ADDRESS_TYPE_VISITOR",
				"STREETNAME_MAIL", "HOUSENO_MAIL",
"HOUSENO_EXT_MAIL",
				"POSTCODE_MAIL", "CITY_MAIL",
"COUNTRY_MAIL",
				"ADDRESS_TYPE_MAIL"
			},
			new Object[]
			{
				"15667", "00822048",
				"blah",
				new Integer(1), "AdvisorName",
				new java.sql.Timestamp(new
java.util.Date().getTime()), // created
				new java.sql.Timestamp(new
java.util.Date().getTime()), // last_changed
				"blah",
				"Formulieromschrijving",
"KorteOmschrijving", "Y",
				new Integer(11), "Minuten", "Y", "Y", "Y",
"BusinesSName",
				"PhoneNo", "PhoneNoCS", "FaxNo",
"http://WEbsite.nl",
				"my...@my...", "", "", "", "", "",
"StreetVisitor",
				new Integer(12), "HouseNoExtVisitor",
"9999VV", "CityVisitor",
				"CountryVisitor", "AddrTypeVisitor",
"StreetMail",
				new Integer(13), "HouseNoExtMail", "9999MM",
"CityMail",
				"CountryMailr", "AddrTypeMail"
			});
		mockPreparedStatement.addResultSet(mockSingleRowResultSet);

mockConnection.setupAddPreparedStatement(JDBCFormService.QUERY_FORM,
			mockPreparedStatement);

		// CUSTOMER_FORM STATEMENTS SETUP

		MockPreparedStatement mockPreparedStatement = new
MockPreparedStatement();
		MockSingleRowResultSet mockSingleRowResultSet = new
MockSingleRowResultSet();

		mockSingleRowResultSet.addExpectedNamedValues(new String[]
			{
				"FORM_ID", "MSISDN", "BUSINESS_NAME",
"FIRST_NAME", "INSERTIONS", 
				"LAST_NAME", "CUSTOMER_NUMBER", "PASSWORD",
"TYPE", "STREET_NAME",
",      msisdn" +
				"HOUSE_NO", "HOUSE_NO_EXT", "POSTCODE",
"CITY", "COUNTRY"
			},
			new Object[]
			{
				"15667", "0612345678", "BusinessName",
"FirstNAme", "Insertions"
			,	"LastName", new Integer(19283745), "pwd",
"VISITOR", "StreetName"
			,	new Integer(5), "a", "9999ZZ", "MESTREECH",
"NL0"
			});

		mockPreparedStatement.addResultSet(mockSingleRowResultSet);

		mockConnection.setupAddPreparedStatement(
			JDBCCustomerFormService.GET_ALL_CUSTOMER_INFO_SQL 
			, mockPreparedStatement);

		formService.setConnection(mockConnection);

		returnedXML = formService.getFormData("24").getXML();

When I run this code, the following assertion fails:

junit.framework.AssertionFailedError: CommonMockSingleRowResultSet.SqlRow
keys did not receive an expected item
Unexpected:HOUSE_NO
	at junit.framework.Assert.fail(Assert.java:47)
	at junit.framework.Assert.assertTrue(Assert.java:20)
	at
com.mockobjects.ExpectationSet.checkImmediateValues(ExpectationSet.java:17)
	at
com.mockobjects.AbstractExpectationCollection.addActual(AbstractExpectationC
ollection.java:17)
	at com.mockobjects.ExpectationMap.get(ExpectationMap.java:25)
	at
com.mockobjects.sql.ExpectationSqlRow.get(ExpectationSqlRow.java:53)
	at
com.mockobjects.sql.CommonMockSingleRowResultSet.getObject(CommonMockSingleR
owResultSet.java:186)
	at com.mockobjects.sql.MockResultSet.getInt(MockResultSet.java:216)
	at
nl.vodafone.dss.dataservices.jdbc.JDBCCustomerFormService$2.convert(JDBCCust
omerFormService.java:408)
	at
nl.vodafone.mercury.dataservices.jdbc.JDBCHelper.callSql(JDBCHelper.java:100
)
	at
nl.vodafone.mercury.dataservices.jdbc.JDBCHelper.performQuery(JDBCHelper.jav
a:64)
	at
nl.vodafone.dss.dataservices.jdbc.JDBCCustomerFormService.populate(JDBCCusto
merFormService.java:442)
	at
nl.vodafone.dss.dataservices.jdbc.JDBCCustomerFormService.getForm(JDBCCustom
erFormService.java:320)
	at
nl.vodafone.dss.dataservices.jdbc.JDBCCancelFormService.getForm(JDBCCancelFo
rmService.java:67)
	at
nl.vodafone.dss.dataservices.jdbc.JDBCCancelFormService.getFormData(JDBCCanc
elFormService.java:46)
	at
nl.vodafone.dss.dataservices.jdbc.JDBCCancelFormServiceTest.testLoadFormWith
ID(JDBCCancelFormServiceTest.java:296)
	at java.lang.reflect.Method.invoke(Native Method)
	at junit.framework.TestCase.runTest(TestCase.java:154)
	at junit.framework.TestCase.runBare(TestCase.java:127)
	at junit.framework.TestResult$1.protect(TestResult.java:106)
	at junit.framework.TestResult.runProtected(TestResult.java:124)
	at junit.framework.TestResult.run(TestResult.java:109)
	at junit.framework.TestCase.run(TestCase.java:118)
	at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRu
nner.java:392)
	at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.
java:276)
	at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner
.java:167)

The problem is that it seems the "HOUSE_NO" column does not exist in the
second SQL statement, but then again, how could it ever find the
"STREETNAME" column (in fact every other column) but not the "HOUSE_NO"? I
suspect that the mockobjects does not handle multiple SQL statements very
well, though obviously I do not exclude an error in the way of thinking by
myself.

Help! I'm lost; this has taken excessive time already and I still cannot
work around it. I have - as displayed in the test code - tried initiating
new instances of preparedstatement and singlerowresultset, but even that
fails.

Kind regards,

Roger Stoffers