DB2 Data Provider for .NET

[Jens Junker]

Hi NG,

the ExecuteScalar - Method of the DB2 Data Provider for .NET seems to work
different than
the ExecuteScalar - Method of the OleDB or the SqlDB Data Provider. I was
not able to return
the result value with the method invocation only (see # 1). (Did I something
wrong...?)

The result value could be only returned with # 2

Dim cn As DB2Connection = New DB2Connection(" bla bla bla")
cn.Open()

Dim rv as object

Dim cmd As DB2Command = cn.CreateComman d()
cmd.CommandText = "SP_USERlog On"
cmd.CommandType = CommandType.Sto redProcedure

Dim parm = cmd.Parameters. Add("p_Email", DB2Type.Char, 50)
parm.Direction = ParameterDirect ion.Input
parm.Value = "xyz@aol.co m"

parm = cmd.Parameters. Add("p_Password ", DB2Type.Char, 50)
parm.Direction = ParameterDirect ion.Input
parm.Value = "Password"

#1) rv = CType(cmd.Execu teScalar(), String) 'Neither works with OUT
Parameters nor with dynamic resultsets
#2) rv = CType(cmd.Param eters(0).Value, String) 'this returns the 1x1
Resultset only...

[Christian Maslen]

Re: DB2 Data Provider for .NET

Hi Jens,
[color=blue]
> #1) rv = CType(cmd.Execu teScalar(), String) 'Neither works with OUT
> Parameters nor with dynamic resultsets
> #2) rv = CType(cmd.Param eters(0).Value, String) 'this returns the 1x1
> Resultset only...[/color]


What exception is being raised? Have you looked at the Errors
collection? Is the stored proc returning a result set or is it just
populating output parameters?

[color=blue]
> parm = cmd.Parameters. Add("p_Password ", DB2Type.Char, 50)[/color]

I thought the DB2 provider required parameters to be named as
":p_Passwor d".

Christian.

[Jens Junker]

Re: DB2 Data Provider for .NET

Hi Christian,

unfortunately there is no condition or exception raised and everything
seems to be fine (not to mention the missing retrun value). The stored
procedure works properly with OUT parameters and in another version
with a dynamic resultset.

By the way, in VB.net you don't have to prefix 'host variables' with a
colon.

Jens

christian.masle n@techie.com (Christian Maslen) wrote in message news:<b9c8cfba. 0401142059.5d53 0f34@posting.go ogle.com>...[color=blue]
> Hi Jens,
>[color=green]
> > #1) rv = CType(cmd.Execu teScalar(), String) 'Neither works with OUT
> > Parameters nor with dynamic resultsets
> > #2) rv = CType(cmd.Param eters(0).Value, String) 'this returns the 1x1
> > Resultset only...[/color]
>
>
> What exception is being raised? Have you looked at the Errors
> collection? Is the stored proc returning a result set or is it just
> populating output parameters?
>
>[color=green]
> > parm = cmd.Parameters. Add("p_Password ", DB2Type.Char, 50)[/color]
>
> I thought the DB2 provider required parameters to be named as
> ":p_Passwor d".
>
> Christian.[/color]

[Christian Maslen]

Re: DB2 Data Provider for .NET

Hi Jens,

It's only just occurred to me you may be after the return value of the
stored proc and not the first column of the first row returned from a
result set (I got this from your use of ExecuteScalar). That being the
case you need to add a parameter to store the return value. You
indicate this by setting the direction to ReturnValue.

' Not sure what datatype your sproc returns.
parm = cmd.Parameters. Add("return_val ", DB2Type.Char, 50)
parm.Direction = ParameterDirect ion.ReturnValue
parm.Value = ""


Christian.

[Jens Junker]

Re: DB2 Data Provider for .NET

Hi Christian,

after reviewing the documentation again my impression is that the
ExecuteScalar Method doesn't support stored procedures (the
dataprovider for MS-SQL do). The documentation comes with one
'typical' example only ( Select count(*) from ...).

I am afraid that I have to rewrite the code ...

Thanks for your assistence.

Jens