Tárolt eljárás meghívása C# és Java kódból

1. Feladat:

Az ügyfél csodálatos alkalmazása Back Office adatok szinkronizálásra biztosít tárolt eljárásokat. (Eredetileg úgy wolt, hogy a WS getter metódusai szarok, hibásak és lassúak voltak. Erre kitalálták, hogy úr isten, itt nagyon nagy mennyiségű adat van, meg hogy milyen nehéz már a WS-t kijavítani, és mennyivel egyszerűbb írni néhány tárolt eljárást. A business unit érje el közvetlenül a DB-t, és hívja meg az erre gyártott tárolt eljárásokat. Advanced Architecture!) A verzióváltás miatt ezeket is le akartam tesztelni. Még a végén nem működik az új rendszeren valamelyik, és a BU jön nekem sírni, hogy de miért…

2. A tárolt eljárás

A tárolt eljárás egy kicsit egyszerűsítve következőképpen van létrehozva:

CREATE PROCEDURE [bosuser].Bos_GetData @RequestType VARCHAR(32), @RequestFlag VARCHAR(5) OUTPUT

AS

BEGIN

DECLARE @xmlOut XML

IF @RequestType='Accounts'

EXEC Bos_GetAllAccounts @RequestType, @xmlOut OUTPUT, @RequestFlag OUTPUT

ELSE IF @RequestType='Groups'

EXEC Bos_GetAllGroups @RequestType, @xmlOut OUTPUT, @RequestFlag OUTPUT

ELSE IF @RequestType='Users'

EXEC Bos_GetAllUsers @RequestType, @xmlOut OUTPUT, @RequestFlag OUTPUT

ELSE

RAISERROR('Invalid RequestType %s.',16,1,@RequestType)

SELECT @xmlOut AS 'XmlData'

END

GO

Ahogy látható, a tárolt eljárásunk belső tárolt eljárásokat hív meg. A paraméterei:

• “RequestType” input paraméter: Melyik belső SP-t hívja meg.
• “RequestFlag” in/out paramétere: Pager megoldás. Ha több mint X record van amit vissza kellene adni, akkor visszaadja az első X-et, és a “RequestFlag” false lesz. Ilyenkor akkor újra meg kell hívni az SP-nket az előzőleg használt “RequestType”-pal és erre visszatér a következő X recorddal.
• XML visszatérési érték.

3. C# megoldás

Először lefuttattam azt a C# kódot, amit még az előző verzióhoz csináltam a business unitnak. (Igen, kicsit kézi vezérlésűek, meg kell nekik mutatni, hogy hogyan kell csinálni.) A pagerrel (“RequestFlag”) most nem foglalkozunk. Ez a következőképpen néz ki:

string connect = System.Configuration.ConfigurationManager.AppSettings["connection"];

SqlConnection sqlConnection = new SqlConnection(connect);

sqlConnection.Open();

SqlCommand sqlCommand = new SqlCommand("Bos_GetAtlasData", sqlConnection);

sqlCommand.CommandType = CommandType.StoredProcedure;

sqlCommand.Parameters.Add("@RequestType", SqlDbType.VarChar, 32);

sqlCommand.Parameters["@RequestType"].Value = requestType;

sqlCommand.Parameters.Add("@RequestFlag", SqlDbType.VarChar, 5);

sqlCommand.Parameters["@RequestFlag"].Direction = ParameterDirection.Output;

XmlReader xmlReader = sqlCommand.ExecuteXmlReader();

xmlDocument.Load(xmlReader);

xmlReader.Close();

if ("FALSE".Equals(sqlCommand.Parameters["@RequestFlag"].Value.ToString()))

{

// Skip this case

}

sqlConnection.Close();

Ne felejtsük el az App.config-ba betenni a DB connection részleteit:

<appSettings>

<add key="connection" value="Data Source='server.db.int, 1302';Initial Catalog=db;User ID=user;Password=password" />

</appSettings>

Ha lefuttatjuk pl.: “Users” requestType-pal, akkor az XmlDocument-ünk tartalmazni fogja a usereket.

4. A Java megoldás

Kis naívan azt hittem, hogy a Connection-ön meghívom a prepareCall() metódust ami ugye létrehozza nekem a CallableStatementet, ezen beállítom a paramétereit, hívok rajta egy execute()-ot, és a ResultSet-ből kiolvasom a végeredményt. Csak hogy ez így nem működik. Futottam vele néhány kört, mire debuggolás közben rájöttem, hogy ha sikerül feltartanom a programot az execute és a ResultSet processzálása között, akkor a ResultSet-ben feltűnik egy “XmlData” property amiben benne van a várt eredmény. Megpróbáltam az MS jdbc drivere helyett a JTDS jdbc drivert is használni, de azzal is hasonló lett az eredmény.

A következző thread segített megérteni mi is a probléma: http://dbaspot.com/forums/ms-sqlserver/223916-problem-boolean-return-parameter-callablestatement-execute.html (Thanks to Joe Weinstein @ BEA Systems).

A mi esetünkben a példa Java kód a következő:

CallableStatement callableStatement = null;

ResultSet resultSet = null;

Connection con = null;

try {

con = getConnection();

String sql = "{call Bos_GetData(?, ?)}";

callableStatement = con.prepareCall(sql);

callableStatement.setString(1, requestType);

callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);

boolean getResultSetNow = callableStatement.execute();

int updateCount = -1;

DocumentBuilderFactory dbfac = DocumentBuilderFactory.newInstance();

DocumentBuilder docBuilder = dbfac.newDocumentBuilder();

Document doc = null;

while (true) { // handle all in-line results from any procedure

if (getResultSetNow) {

resultSet = callableStatement.getResultSet();

while (resultSet.next()) {

InputStream is = resultSet.getAsciiStream(1);

doc = docBuilder.parse(is);

}

resultSet.close();

} else {

updateCount = callableStatement.getUpdateCount();

// if (updateCount != -1) {

// process update count if desired

// }

}

if ((!getResultSetNow) && (updateCount == -1))

break; // done with loop

getResultSetNow = callableStatement.getMoreResults();

// go to next return if a Callable statement, get any output

// parameters after the loop.

}

boolean hasAllElementRetrived = Boolean.parseBoolean(callableStatement.getString(2));

callableStatement.close();

return new AtlasDataResult(hasAllElementRetrived, doc);

} catch (Exception e) {

log.error(e);

throw new RuntimeException(e);

} finally {

if (con != null) {

try { con.close(); } catch (SQLException e) {

log.error(e); }

}

}

Némi magyarázat: Azért van erre az egészre szükség, mert a tárolt eljárás végrehajtása elég sokáig is eltarthat. A CallableStatement getMoreResults() metódusa true-val tér vissza, ha a következő result egy ResultSet object. Az getUpdateCount() -1 -gyel tér vissza, ha befejeződött a tárolt eljárás. Tehát a kilépési feltételünk: nincs több result (a getMoreResult() false-sal tért vissza), és nincs is több updatelendő sora az SP-nek (a getUpdateCount() -1-et adott.)

A szép az egészben hogy a coding standards alapján ezért a while(true)/break megoldásért minimum nyilvánosan kivégeznének (De ahogy Joe@BEA írja, ez a legjobb kód feldolgozni bármilyen statementet).