SplendidCRM Software

Forum Home

Re: Offline CRM

2153 posts

Joined
1/3/2006

Re: Offline CRM
Posted: 10 Jul 07 3:18 AM

We had a problem with the Sybase Adaptive Server ADO.NET driver. We use the common interfaces and the Sybase driver does not return stored procedure output parameters. We posted this bug on the Sybase forums, but after 8 months of no action, we got tired of checking.

Case: 11247617

Status: Open

Opened: 06/04/09 21:47 EST

Priority: (P3) Problem or question, acceptable workaround available

Contact: Bugs NA - EAServer / 919-604-1258

Staff: Electronic Case Management

Product: 60857 - EAServer Developer, Windows version: , Rev: , EB F: 0

Platform: PC

Parent: N/A

Description: The Sybase ADO.NET driver has a problem when parameters are used through the IDbDataParameter common interface. An Input/Output paramter throws an exception when the Value parameter is accessed after procedure execution.

Case Notes:

time/added by Note

06/04/09 21:48 EST
Electronic Case Management DBMS: Sybase ASE 12.x
DBMS Comment: ASE 15.0
Source Control: None
Network: Microsoft NT
CPU Type: Intel Xeon
Additional Comment: The problem can be duplicated in the Sybase Advanced sample.

Creating a parameter with the common interfaces causes a problem after execution:
IDbDataParameter parID = cmd.CreateParameter();

cmd.Parameters["@ID"].Value throws the exception.

06/04/09 21:52 EST
Electronic Case Management
FROM: Bugs NA - EAServer
EMAIL: paul@splendidcrm.com
PHONE: 919-604-1258

using(AseCommand cmd = new AseCommand("spCONTACTS_New", _conn))
{
cmd.CommandType = CommandType.StoredProcedure;

//AseParameter parID = new AseParameter("@ID", AseDbType.Char, 36);
// Using IDbDataParameter causes a failure.
IDbDataParameter parID = cmd.CreateParameter();
// Using the IDbCommand interface does not cause failure in itself.
//AseParameter parID = (cmd as IDbCommand).CreateParameter() as AseParameter;
parID.ParameterName = "@ID";
parID.DbType = DbType.AnsiStringFixedLength;
parID.Size = 36;
parID.Direction = ParameterDirection.InputOutput;
parID.Value = DBNull.Value;
cmd.Parameters.Add(parID);

AseParameter parMODIFIED_USER_ID = new AseParameter("@MODIFIED_USER_ID", AseDbType.Char, 36);
parMODIFIED_USER_ID.Direction = ParameterDirection.Input;
parMODIFIED_USER_ID.Value = DBNull.Value;
cmd.Parameters.Add(parMODIFIED_USER_ID);

AseParameter parFirstName = new AseParameter("@FIRST_NAME", AseDbType.Char, 36);
parFirstName.Direction = ParameterDirection.Input;
parFirstName.Value = txtFirstName.Text;
cmd.Parameters.Add(parFirstName);

AseParameter parLastName = new AseParameter("@LAST_NAME", AseDbType.NVarChar, 30);
parLastName.Direction = ParameterDirection.Input;
parLastName.Value = txtLastName.Text;
cmd.Parameters.Add(parLastName);

AseParameter parPhone = new AseParameter("@PHONE_WORK", AseDbType.NVarChar, 50);
parPhone.Direction = ParameterDirection.Input;
parPhone.Value = txtPhone.Text;
cmd.Parameters.Add(parPhone);

AseParameter parEmail = new AseParameter("@EMAIL1", AseDbType.NVarChar, 255);
parEmail.Direction = ParameterDirection.Input;
parEmail.Value = txtEmail.Text;
cmd.Parameters.Add(parEmail);
try
{
cmd.ExecuteNonQuery();
txtID.Text = (string) cmd.Parameters["@ID"].Value;
txtID.ForeColor = Color.Blue;
}
catch (AseException ex)
{
MessageBox.Show(ex.Source + " : " + ex.Message + " (" + ex.ToString() + ")", "Execute Stored Precedure failed.");
}
}

06/04/09 21:54 EST
Electronic Case Management
FROM: Bugs NA - EAServer
EMAIL: <No Email for this contact>
PHONE: 919-604-1258

Create Procedure dbo.spCONTACTS_New
( @ID char(36) output
, @MODIFIED_USER_ID char(36)
, @FIRST_NAME nvarchar(100)
, @LAST_NAME nvarchar(100)
, @PHONE_WORK nvarchar(25)
, @EMAIL1 nvarchar(100)
)

as
begin
set nocount on

begin tran

if @LAST_NAME is null or @LAST_NAME = N'' begin -- then
raiserror 99999 'Last name is required'
end else begin
if dbo.fnIsEmptyGuid(@ID) = 1 begin -- then
set @ID = newid()
end -- if
insert into CONTACTS
( ID
, CREATED_BY
, DATE_ENTERED
, MODIFIED_USER_ID
, DATE_MODIFIED
, ASSIGNED_USER_ID
, FIRST_NAME
, LAST_NAME
, PHONE_WORK
, EMAIL1
)
values
( @ID
, @MODIFIED_USER_ID
, getdate()
, @MODIFIED_USER_ID
, getdate()
, @MODIFIED_USER_ID -- By default, assign to creator.
, @FIRST_NAME
, @LAST_NAME
, @PHONE_WORK
, @EMAIL1
)

-- 03/04/2006 Paul. Add record to custom table.
if not exists(select * from CONTACTS_CSTM where ID_C = @ID) begin -- then
insert into CONTACTS_CSTM ( ID_C ) values ( @ID )
end -- if
end -- if

if @@ERROR = 0 begin -- then
commit tran
end else begin
rollback tran
end -- if
end
GO