You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-cs@ibatis.apache.org by Habib Ahmed Qureshi <TC...@emirates.com> on 2005/09/26 12:37:33 UTC
DataAccess_ADO_Oracle_Cursor
Hi again,
I am using IBATIS DataAccess dao infrastructure only not data-mapper.
Using Oracle. I am using ADO implementation. I want to retrieve multiple
data i.e. cursor
And then get a dataset or else and then make a arraylist/ilist of the
business entity myself.
I am doing it all like (VB Dotnet):-
SAMPLE:
1)
Dim command As IDbCommand
command =
Me.GetContext().CreateCommand(CommandType.StoredProcedure)
command.CommandText =
StoredProcedureName.SP_GETAPPLICATIONUSER
AddInputParameter(command, VAR_USERID, strUserId)
AddInputParameter(command, VAR_PASSWORD,
strPassword)
AddOutputParameter(command, VAR_USERNAME,
DbType.AnsiString, 100)
AddOutputParameter(command, VAR_SUCCESS,
DbType.AnsiString, 100)
command.ExecuteNonQuery()
strSuccess =
command.Parameters.Item(VAR_SUCCESS).Value
If strSuccess <> RETURNED_FAILED Then
strUserName =
command.Parameters.Item(VAR_USERNAME).Value
Else
Return Nothing
End If
2)
Public Shared Sub AddInputParameter(ByVal command As IDbCommand, ByVal
paramName As String, ByVal paramValue As String)
Dim dbParam As IDbDataParameter
dbParam = command.CreateParameter()
dbParam.ParameterName = paramName
dbParam.Value = IIf(paramValue.Equals(String.Empty),
DBNull.Value, paramValue)
dbParam.Direction = ParameterDirection.Input
command.Parameters.Add(dbParam)
End Sub
Public Shared Sub AddOutputParameter(ByVal command As IDbCommand,
ByVal paramName As String, ByVal paramType As String, ByVal paramSize As
Integer)
Dim dbParam As IDbDataParameter
dbParam = command.CreateParameter()
dbParam.ParameterName = paramName
dbParam.DbType = paramType
dbParam.Size = paramSize
dbParam.Direction = ParameterDirection.Output
command.Parameters.Add(dbParam)
End Sub
3)
NOW, Problem.
Public Function GetDocumentInfo(ByVal intDocumentId As Integer) As
BusinessEntities.Document Implements Interfaces.IDocumentDao.GetDocumentInfo
Try
Dim strDocumentId As String = intDocumentId
Dim command As IDbCommand
command =
Me.GetContext().CreateCommand(CommandType.StoredProcedure)
command.CommandText =
StoredProcedureName.SP_GETEBI_DOCUMENT
AddInputParameter(command, VAR_DOCUMENTID,
strDocumentId)
AddOutputParameter(command, VAR_ARG_RS,
DbType.CURSOR) '!!! Cursor , ITS NOT THERE
'AddOutputParameter(command, VAR_ARG_RS, , 100)
'THEN I WILL DO SOMEHITNG LIKE THIS
...objDataAdapter = New DataAdapter(command)
objDataSet = New DataSet
objDataAdapter.Fill(objDataSet)
'THEN I WILL FILL ARRAYLIST WITH BUSINESS ENTITES.
'BUT I M STUCK WITH CURSOR.
'HELP ME ASAP PLEASE.
Catch ex As Exception
Throw ex
End Try
Habib Ahmed Qureshi
Software Engineer
Mob: 00971503966137
Re: DataAccess_ADO_Oracle_Cursor
Posted by Ron Grabowski <ro...@yahoo.com>.
This article:
http://tinyurl.com/8qvsp
http://64.233.187.104/search?q=cache:aYimzXAypr0J:dotnetjunkies.net/WebLog/rtgurskevik/archive/2004/12/03/34843.aspx+DbType+cursor+oracle+ado.net&hl=en
has this comment:
"
// Need to pass as DbType.Object here since .Cursor type doesn't exist
as DbType.
// This will be converted in Oracle class because .IsOutputCursor is
True.
param = paramCol.Add("p_data_set_out", DbType.Object);
"
What happens when you try DbType.Object?
AddOutputParameter(command, VAR_ARG_RS, DbType.Object)
--- Habib Ahmed Qureshi <TC...@emirates.com> wrote:
> Hi again,
>
> I am using IBATIS DataAccess dao infrastructure only not data-mapper.
> Using Oracle. I am using ADO implementation. I want to retrieve
> multiple
> data i.e. cursor
> And then get a dataset or else and then make a arraylist/ilist of the
> business entity myself.
>
> I am doing it all like (VB Dotnet):-
>
> SAMPLE:
> 1)
> Dim command As IDbCommand
> command =
> Me.GetContext().CreateCommand(CommandType.StoredProcedure)
> command.CommandText =
> StoredProcedureName.SP_GETAPPLICATIONUSER
> AddInputParameter(command, VAR_USERID, strUserId)
> AddInputParameter(command, VAR_PASSWORD,
> strPassword)
> AddOutputParameter(command, VAR_USERNAME,
> DbType.AnsiString, 100)
> AddOutputParameter(command, VAR_SUCCESS,
> DbType.AnsiString, 100)
> command.ExecuteNonQuery()
> strSuccess =
> command.Parameters.Item(VAR_SUCCESS).Value
> If strSuccess <> RETURNED_FAILED Then
> strUserName =
> command.Parameters.Item(VAR_USERNAME).Value
> Else
> Return Nothing
> End If
> 2)
> Public Shared Sub AddInputParameter(ByVal command As IDbCommand,
> ByVal
> paramName As String, ByVal paramValue As String)
> Dim dbParam As IDbDataParameter
> dbParam = command.CreateParameter()
> dbParam.ParameterName = paramName
> dbParam.Value = IIf(paramValue.Equals(String.Empty),
> DBNull.Value, paramValue)
> dbParam.Direction = ParameterDirection.Input
> command.Parameters.Add(dbParam)
> End Sub
>
> Public Shared Sub AddOutputParameter(ByVal command As IDbCommand,
> ByVal paramName As String, ByVal paramType As String, ByVal paramSize
> As
> Integer)
> Dim dbParam As IDbDataParameter
> dbParam = command.CreateParameter()
> dbParam.ParameterName = paramName
> dbParam.DbType = paramType
> dbParam.Size = paramSize
> dbParam.Direction = ParameterDirection.Output
> command.Parameters.Add(dbParam)
> End Sub
>
> 3)
> NOW, Problem.
>
> Public Function GetDocumentInfo(ByVal intDocumentId As Integer) As
> BusinessEntities.Document Implements
> Interfaces.IDocumentDao.GetDocumentInfo
> Try
> Dim strDocumentId As String = intDocumentId
>
> Dim command As IDbCommand
> command =
> Me.GetContext().CreateCommand(CommandType.StoredProcedure)
> command.CommandText =
> StoredProcedureName.SP_GETEBI_DOCUMENT
> AddInputParameter(command, VAR_DOCUMENTID,
> strDocumentId)
> AddOutputParameter(command, VAR_ARG_RS,
> DbType.CURSOR) '!!! Cursor , ITS NOT THERE
> 'AddOutputParameter(command, VAR_ARG_RS, , 100)
> 'THEN I WILL DO SOMEHITNG LIKE THIS
> ...objDataAdapter = New DataAdapter(command)
> objDataSet = New DataSet
> objDataAdapter.Fill(objDataSet)
> 'THEN I WILL FILL ARRAYLIST WITH BUSINESS
> ENTITES.
> 'BUT I M STUCK WITH CURSOR.
> 'HELP ME ASAP PLEASE.
> Catch ex As Exception
> Throw ex
> End Try
>
>
>
> Habib Ahmed Qureshi
> Software Engineer
> Mob: 00971503966137
>
>