Calling stored procedures with inout parameters in C#

[denhoffk]

I'm calling a pervasive stored procedure from a c# app and it requires an "inout" parameter. My code appears to be working fine but the returned parameter value returned is always null even if I hard set it in the procedure. Any ideas?
Im running PVSW 8.6

 

[mirtheil]

How are you calling the procedure?
Are you using ODBC, OLEDB, or the Managed Provider?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[denhoffk]

I'm using odbc namespace. I just experimented with the demo data where I wrote the outval into a table and that worked fine but the C# code still returns null.
snippet:
OdbcConnection conn = new OdbcConnection(ConfigurationManager.ConnectionStrings["PVSWDemoData"].ToString());
OdbcCommand cmd = new OdbcCommand("{ CALL ProcOut(?,?) }", conn);

cmd.CommandType = CommandType.StoredProcedure;
OdbcParameter par1 = cmd.Parameters.Add
("@id", OdbcType.Int);
par1.Value = 132;
OdbcParameter par = cmd.Parameters.Add
("@outval", OdbcType.Int);
par.Direction = ParameterDirection.Output;


try
{
conn.Open();
OdbcDataReader dr = cmd.ExecuteReader();
dr.Read();
if (dr.HasRows && cmd.Parameters["@outval"].Value != null)
{

bReturnValue = true;
}

 

[mirtheil]

And what does your CREATE PROCEDURE look like?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[denhoffk]

CREATE PROCEDURE PROCOUT (in :id Int, out utval INTEGER)
AS BEGIN
SELECT COUNT(*) INTO utval FROM Enrolls WHERE Class_Id = :id;

Drop Table t2;

CREATE TABLE t2 DCOMPRESS PAGESIZE=1024 (c1 INT DEFAULT 10);
INSERT INTO t2(c1)
VALUES outval);

END;

 

[mirtheil]

I changed your code to the following and it returned "45" (the value from the OUT parameter:

conn.Open();
cmd.ExecuteNonQuery();
if (cmd.Parameters["@outval"].Value !=null)
	{
		MessageBox.Show (cmd.Parameters["@outval"].Value.ToString());
	}

The problem in your code is the ExecuteReader. The SP does not return a result set so the .HasRows will always be false.
Unless your SP has a RETURNS clause, a resultset is not returned and the ExecuteNonQuery would be a better choice.



Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[denhoffk]

This was just a test case. My actual procedure does return a value. The only reason I'm using the out parameter is because for some reason in the pervasive the datareader hasrows property always returns a true even though the recordset returns 0 records as verified in the data manager.

 

[denhoffk]

Unfortunately I still have the problem; I do not see a return value. I thought possibly because I was in an asp.net app so I tried a windows app with still the same results. I am running in VS.net 2005. What environment are you running in?

 

[mirtheil]

My test was against VS.NET 2003 initially but I just tried it with VS.NET 2005 and it works for me still.
Does the ExecuteNonQuery work for you (and return the OUT value)?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[denhoffk]

Yoops, the coffee kicked in. using the cmd.ExecuteNonQuery does return a value. I guess I'll have to change my methodoly. thanks for the help!