AdoDaoSupport (AdoTemplate) with output parameters

[SilvioSantoZ]

Hello all,

I am sucessfully using AdoDaoSupport as base class for my DAO classes, invoking such methods as ExecuteNonQuery, QueryWithResultSetExtractor, etc on a MS-SQL server.
Now I am trying to implement two methods I can't figure out: Save() and StoredProcedure execution with output parameters.

In the Save() case, I must code a pseudo-procedure in which I fist INSERT the record and then retrieve the generated ID via SCOPE_IDENTITY() or something like that via output parameter.

In the SP-with-OUTPUT case, I need to do a SP invocation (which returns a resultset, so I use ResultSetExtractor), but also need to be able to get at the output parameters.

Is there a way to do these things?
Thanks a lot.

[Mark Pollack]

Hi,

Getting at the generated value is a specific case that I would like to have more explicity support for in the API, as in the Java version via the KeyHolder interface. This will be addressed for the RC1 release. I've made a JIRA issue to keep track of it.

However, for now there are two options I can think of. The first is to use the method

IList QueryWithCommandCreator(IDbCommandCreator commandCreator,
IResultSetExtractor resultSetExtractor,
IDictionary returnedParamters,
IDbParameters declaredParameters)


or


IDictionary QueryWithCommandCreator(IDbCommandCreator commandCreator,
IList resultProcessors,
IDbParameters declaredParameters)

I realize using IDbCommandCreator is less convenient than the other "one-liner" methods. In the second QueryWithCommandCreator case the list contains an instance of NamedResultSetProcessor which associates a name (key) with either a IRowMapper, IResultSetExtractor, or IRowCallback (value). You get the results from the result processor via the returned IDictionary in addition to any other output or input-output parameters.

If you are saving in the stored procedure you can return SCOPE_IDENTITY() as an output parameter or you can use Sql Server batch sql if you are using CommandText. This should work for both the cases you mention.

Another alternative is to use the class StoredProcedure which uses QueryWithCommandCreator in its implementation. In this case you create an instance of the class declare the in/out parameters (either explicitly or via DeriveParameters), then call AddResultSetExtractor(string name, IResultSetExtractor resultSetExtractor), and finally "Compile". See the reference docs for a basic example. Using the StoredProcedure class can be a real time-saver if you use DeriveParameters and pass in the input parameters in the same order as they are declared in the stored procedure to one of the ExecuteXXX methods.

Mark

[SilvioSantoZ]

Hello Mark, thanks for your reply.

I came back to this subject after a few weeks. I found a little inconvenience: I'm using the Generic version of AdoDaoSupport/AdoTemplate, and it does not have the QueryWithCommandCreator methods. No matter, I realized I can get to the non-generic version via the ClassicAdoTemplate property.

But it seems my brain doesn't allow me to comprehend IDbCommandCreator and QueryWithCommandCreator; I tried to find an example looking through the source code but can't find one. To be truthful, I found some usages in the Spring.Data.Objects namespace (AdoQuery/StoredProcedure as you mentioned), but those are using an IDbCommandCreatorFactory - which looks like an interface but seems to be a class, adding to my confusion.

Could you please provide an example for a simple batch-sql INSERT+SCOPE_IDENTITY-as-output-parameter case?

Thanks a lot.

[Mark Pollack]

Hi,
Ya know, I don't know wht the hell I was thinking before - what I suggested is probably the ultimate in flexibility but hard to use and in fact the QueryWithCommandCreator is essentially a method to support the Spring.Data.Objects functionality and not for typical spring data access code. BTW, there probably will be a version of that method in the generic version of AdoTemplate so that the generic delegates for rowmapper's etc can be specified instead of the interface based definitions.

Here is an easier approach. You can find the code in the Spring.Data.Integration.Tests assembly, namespace Spring.Data.Northwind, class AdoTemplaetShipperDao.


public Shipper Create(string name, string phone)
{
string sql = "INSERT INTO Shippers (CompanyName, Phone) VALUES (@CompanyName, @Phone) SET @ShipperID = SCOPE_IDENTITY()";

IDbParameters dbParameters = AdoTemplate.CreateDbParameters();
dbParameters.Add("CompanyName", SqlDbType.NVarChar, 40).Value = name;

if (phone.Length == 0)
dbParameters.Add("Phone", SqlDbType.NVarChar, 24).Value = DBNull.Value;
else
dbParameters.Add("Phone", SqlDbType.NVarChar, 24).Value = phone;



dbParameters.AddOut("ShipperID", SqlDbType.Int);

int id = AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, dbParameters);

return new Shipper(id, name, phone);
}


Sorry for the confusion.

Cheers,
Mark

[vansickle]

i think instead of

int id = AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, dbParameters);

it must be for example
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, dbParameters);

int id = (int)dbParameters["@ShipperID"].Value;

and can we use the same scenario with DbParametersBuilder?

[Mark Pollack]

Hi,
Yes, that was a mistake using the return value. You can also use DbParametersBuilder, passing in the instance of IDbParameter from the call

IDbParameters dbParameters = builder.GetParameters();

Cheers,
Mark

[mmistroni]

Mark,
still no luck
i m using driver for sybase 12.5.
here's code

IDbParameters dbParameters = adoTemplate.CreateDbParameters();
dbParameters.Add("@TaskID", DbType.Int64).Value = 28070;
IList result = adoTemplate.QueryWithRowMapper(
CommandType.StoredProcedure,
"sp_bat_get_sql_tsk",
new TestRowMapper(),
dbParameters);


and here's exception

SpringNba.Tests.SpringAdoTest.testCallStoredProced ure : Spring.Data.UncategorizedAdoException : CommandCallback; uncategorized DataException for SQL [sp_bat_get_sql_tsk]; ErrorCode [<no error code>]; ERROR [HY000] [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Procedure sp_bat_get_sql_tsk expects parameter @TaskID, which was not supplied.

----> System.Data.Odbc.OdbcException : ERROR [HY000] [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Procedure sp_bat_get_sql_tsk expects parameter @TaskID, which was not supplied.


do i need to upgrade my driver?

thanks an dregards
marco

[Mark Pollack]

Hi Marco,

There is some overlap with this thread (http://forum.springframework.net/showthread.php?p=9763#post9763).

Try passing in the command text "sp_bat_get_sql_tsk @TaskID". I've verified this to work with ASE 15.

Cheers,
MArk