Another ADO.NET (v3.0.0.9) Stored Procedure issue

Last post 06-17-2008 10:31 AM by Mirtheil. 2 replies.

Page 1 of 1 (3 items)
	Sort Posts: Previous Next

06-16-2008 5:10 PM

The Dread Pirate Roberts
Joined on 05-02-2008
Posts 13

Another ADO.NET (v3.0.0.9) Stored Procedure issue

Reply Contact

Assume the following stored procedure

CREATE PROCEDURE AddStoreAddress2 (inout :ObjectID uniqueidentifier)

AS

BEGIN...

Calling the stored proc from ADO.NET with the following code:
DbConnection conn = PsqlFactory.Instance.CreateConnection();
DbCommand command = PsqlFactory.Instance.CreateCommand();
command.CommandText = "AddStoreAddress2";command.CommandType = CommandType.StoredProcedure;
command.Connection = conn;

conn.ConnectionString = connString;
DbParameter param = PsqlFactory.Instance.CreateParameter();
param.DbType = DbType.Guid;

param.ParameterName = "ObjectID";

conn.Open();

command.ExecuteNonQuery();

Throws an exception ("Specified cast is not valid")

It works fine when :ObjectID is ParameterDirection.Input and also when ParameterDirection.Output (with corresponding SP change), but does not work on the InputOutput ParameterDirection.

Theoretically, I could pass it as a char and change the proc to type char, but then I have to cast it in the proc both coming and going.

Any suggestions?

06-17-2008 9:52 AM In reply to

The Dread Pirate Roberts
Joined on 05-02-2008
Posts 13

Re: Another ADO.NET (v3.0.0.9) Stored Procedure issue

Reply Contact

Well, it's not limited to uniqueidentifier (GUID) types. Although some other types do not throw an exception, they also do not return a value. For example, if I change the stored proc to accept a inout varchar(255), after running the c# code, the value in the command.parameter collection for that variable is null. Same if I set it to char(xx). It does work correctly for integer. Those are the only types I have tested to date.

This is just one of several issues I have experienced with the ADO.NET driver. Another issue relates to the DeriveParameters method of the PsqlCommandBuilder object. It returns the correct name and order of parameters, but the datatype and direction are often wrong.

Has anyone else experienced any of these issues? Do you know of any workarounds? I am beginning a major re-architecture project of an older system and need to resolve these issues.

06-17-2008 10:31 AM In reply to

Mirtheil
Joined on 07-09-2007
Posts 2,903

Re: Another ADO.NET (v3.0.0.9) Stored Procedure issue

Reply Contact

I haven't seen these specific issues you describe but I also haven't used Stored Procedures or some of the functions you're using.

If this is critical for you, I would suggest opening a formal support ticket with Pervasive.

Mirtheil
I do not answer question via email. Please post questions in the forum.

Page 1 of 1 (3 items)