@@rowcount changed before Stored Proc. can check it

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

We have a simple LoginUser stored proc that returns the
user name and other info to the calling code.
BEGIN Tran
SELECT ...
FROM...
WHERE...
IF @@error <> 0
...
IF @@rowcount <> 1
begin
RAISERROR..
RETURN -1
end
COMMIT TRAN
RETURN 1

The interesting thing is that even though the Fill method
on the .net side retrieves a record and continues without
exception (see .net code below), the @@rowcount check
returns 0 and gives RAISERROR (but the client doesn't
catch it). The SP runs perfectly in Interactive SQL and
rowcount returns 1.

The theory is that the Fill method opens and closes a
connection and as such resets the @@rowcount in the db
prior to the SP checking it. Is that possible ? What are
we doing wrong ??? Note, all our data retrieval,
transaction handling, and business rule checking is done
in stored procs, should we use the DataReader instead of
the DataAdapter... how would any links or sample code
pointers are appreciated.

Thanks, Peter

public LoginUserWrapper()
{
this.connection = (OdbcConnection)
DbConnection.CreateDbConnection();
dataSet = new DataSet();
adapter = new OdbcDataAdapter();
adapter.SelectCommand =
createSelectCommand();

dataTable = null;
currentRow = -1;
}

// Create Select Command
private OdbcCommand createSelectCommand()
{
String cmdText = "CALL LoginUser
(?, ?)";
OdbcCommand cmd = new OdbcCommand
(cmdText, connection);
OdbcParameter p;
p = cmd.Parameters.Add(new
OdbcParameter("@Email", OdbcType.Char, 60));
p.Direction =
ParameterDirection.Input;
p = cmd.Parameters.Add(new
OdbcParameter("@pwd", OdbcType.Char, 20));
p.Direction =
ParameterDirection.Input;
return cmd;
}

// Create Insert Command

// Create Insert1 Command

// Create Update Command

// Create Delete Command

// Create Delete1 Command

// Create Empty Table
public void createEmptyTable()
{
dataTable = new DataTable("data");
DataColumn column;
// Create and add columns
column = new DataColumn();
column.DataType = System.Type.GetType
("System.String");
column.AllowDBNull = true;
column.Caption = "USER_NAME";
column.ColumnName = "USER_NAME";
// column.DefaultValue = 0;
dataTable.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType
("System.Int32");
column.AllowDBNull = true;
column.Caption = "USER_ID";
column.ColumnName = "USER_ID";
// column.DefaultValue = 0;
dataTable.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType
("System.Int32");
column.AllowDBNull = true;
column.Caption = "MODEL_ID";
column.ColumnName = "MODEL_ID";
// column.DefaultValue = 0;
dataTable.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType
("System.String");
column.AllowDBNull = true;
column.Caption = "LANG_ID";
column.ColumnName = "LANG_ID";
// column.DefaultValue = 0;
dataTable.Columns.Add(column);

}

// Select Command
public void Select(string email, string
pwd)
{
try
{

adapter.SelectCommand.Parameters["@Email"].Value = email;

adapter.SelectCommand.Parameters["@pwd"].Value = pwd;

connection.Open();
try
{

adapter.SelectCommand.Prepare();
adapter.Fill
(dataSet, "data");
dataTable =
dataSet.Tables[0];
if
(dataTable.Rows.Count > 0)
{
currentRow
= 0;
}
}
finally
{
connection.Close();
}
}
catch (OdbcException e1)
{
if (errors != null)
{
for (int i = 0; i
< e1.Errors.Count; i++)
{

Utils.AddError(ref errors, Utils.GetErrorMessage(e1.Errors
));
}
}
}
catch (Exception e1)
{
if (errors != null)
{
Utils.AddError(ref
errors, "General Error: " + e1.Message);
}
}
}
 
Peter,

try this.

DECLARE @MyRowCount int
DECLARE @MyError int

SELECT .... FROM MyUserTable ...
-- preserve the error and rowcount for subsequent checking... as it may
change!
SELECT @MyRowCount = @@ROWCOUNT, @MyError = @@ERROR

Also, unless you have a good reason, you do not need to put SELECT
statements inside transactions. The rule of thumb with transactions is to
keep them as short as possible, this way locks optained as late as possible
and are released as early as possible. However if you wish to knowingly
cause a lock, then obvisouly you are aware of this.

- Tim



Peter said:
We have a simple LoginUser stored proc that returns the
user name and other info to the calling code.
BEGIN Tran
SELECT ...
FROM...
WHERE...
IF @@error <> 0
...
IF @@rowcount <> 1
begin
RAISERROR..
RETURN -1
end
COMMIT TRAN
RETURN 1

The interesting thing is that even though the Fill method
on the .net side retrieves a record and continues without
exception (see .net code below), the @@rowcount check
returns 0 and gives RAISERROR (but the client doesn't
catch it). The SP runs perfectly in Interactive SQL and
rowcount returns 1.

The theory is that the Fill method opens and closes a
connection and as such resets the @@rowcount in the db
prior to the SP checking it. Is that possible ? What are
we doing wrong ??? Note, all our data retrieval,
transaction handling, and business rule checking is done
in stored procs, should we use the DataReader instead of
the DataAdapter... how would any links or sample code
pointers are appreciated.

Thanks, Peter

public LoginUserWrapper()
{
this.connection = (OdbcConnection)
DbConnection.CreateDbConnection();
dataSet = new DataSet();
adapter = new OdbcDataAdapter();
adapter.SelectCommand =
createSelectCommand();

dataTable = null;
currentRow = -1;
}

// Create Select Command
private OdbcCommand createSelectCommand()
{
String cmdText = "CALL LoginUser
(?, ?)";
OdbcCommand cmd = new OdbcCommand
(cmdText, connection);
OdbcParameter p;
p = cmd.Parameters.Add(new
OdbcParameter("@email", OdbcType.Char, 60));
p.Direction =
ParameterDirection.Input;
p = cmd.Parameters.Add(new
OdbcParameter("@pwd", OdbcType.Char, 20));
p.Direction =
ParameterDirection.Input;
return cmd;
}

// Create Insert Command

// Create Insert1 Command

// Create Update Command

// Create Delete Command

// Create Delete1 Command

// Create Empty Table
public void createEmptyTable()
{
dataTable = new DataTable("data");
DataColumn column;
// Create and add columns
column = new DataColumn();
column.DataType = System.Type.GetType
("System.String");
column.AllowDBNull = true;
column.Caption = "USER_NAME";
column.ColumnName = "USER_NAME";
// column.DefaultValue = 0;
dataTable.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType
("System.Int32");
column.AllowDBNull = true;
column.Caption = "USER_ID";
column.ColumnName = "USER_ID";
// column.DefaultValue = 0;
dataTable.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType
("System.Int32");
column.AllowDBNull = true;
column.Caption = "MODEL_ID";
column.ColumnName = "MODEL_ID";
// column.DefaultValue = 0;
dataTable.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType
("System.String");
column.AllowDBNull = true;
column.Caption = "LANG_ID";
column.ColumnName = "LANG_ID";
// column.DefaultValue = 0;
dataTable.Columns.Add(column);

}

// Select Command
public void Select(string email, string
pwd)
{
try
{

adapter.SelectCommand.Parameters["@email"].Value = email;

adapter.SelectCommand.Parameters["@pwd"].Value = pwd;

connection.Open();
try
{

adapter.SelectCommand.Prepare();
adapter.Fill
(dataSet, "data");
dataTable =
dataSet.Tables[0];
if
(dataTable.Rows.Count > 0)
{
currentRow
= 0;
}
}
finally
{
connection.Close();
}
}
catch (OdbcException e1)
{
if (errors != null)
{
for (int i = 0; i
< e1.Errors.Count; i++)
{

Utils.AddError(ref errors, Utils.GetErrorMessage(e1.Errors
));
}
}
}
catch (Exception e1)
{
if (errors != null)
{
Utils.AddError(ref
errors, "General Error: " + e1.Message);
}
}
}
Click to expand...
 
Tim,

thanks, I did that right after the SELECT statement as you
suggested and the same situation, rowcount checks out to
zero even though the record is retrieved.

The key question is really, does the Fill method (behind
the scenes/implicitly) open and close a connection and as
such affect/reset the global, connection-specific
@@rowcount variable ? That would explain why the SP only
sees zero, even though we check right after the SELECT
command.

Thanks!
 
Tim, thanks...here is the code. We're using Sybase
SQLAnywhere, an ODBC Level 2 compliant database along with
the latest .net releases and latest MDAC, ODBC.net. I
haven't tested it against MS-SQL, but suspect the same
behavior due to the implicit open/close connection as part
of the DataAdapter Fill command

CREATE Procedure LoginUser
(
@email char(60) ,
@pwd char(20)
)
AS

commit

BEGIN

DECLARE @ERROR_OBJECT CHAR(15) ,
@ERROR_PARMS CHAR(80) ,
@ERROR_CODE INT ,
@ERROR_SPROC CHAR(10) ,
@ERROR_SQLCODE INT ,
@ERROR_ROWCOUNT INT ,
@TESTTIME DATETIME ,
@USER_NAME CHAR(20) ,
@USER_ID INT ,
@MODEL_ID INT ,
@LANG_ID CHAR(12) ,
@SP_OUT_VAL INT

SELECT @ERROR_OBJECT = 'User Login'
SELECT @ERROR_SPROC = 'LoginUser'
SELECT @ERROR_SQLCODE = 0
SELECT @ERROR_ROWCOUNT = 0
SELECT @SP_OUT_VAL = 0
SELECT @USER_NAME = ''


BEGIN TRAN

SET TRANSACTION ISOLATION LEVEL 1

IF @email IS NULL
SELECT @email = ''

IF @pwd IS NULL
SELECT @pwd = ''

select USER_NAME = t1.CMS_USER_NAME ,
USER_ID = t1.ID ,
MODEL_ID = t1.MODEL_ID ,
LANG_ID = t1.LANG_ID
from USE_TAB t1
where t1.EXT_EMAIL = @email
and t1.PWORD = @pwd

SELECT @ERROR_SQLCODE = @@ERROR,
@ERROR_ROWCOUNT= @@ROWCOUNT

--PRINT '@@Rowcount ' + LTRIM(str(@ERROR_ROWCOUNT))

IF @ERROR_SQLCODE <> 0
begin
SELECT @ERROR_CODE = 100016
GOTO ERROR_EXIT
end

IF @ERROR_ROWCOUNT <> 1
begin
SELECT @ERROR_CODE = 100017
GOTO ERROR_EXIT
end


COMMIT TRAN

SET TRANSACTION ISOLATION LEVEL 0

RETURN 1

-- should never get here as part of normal flow

ERROR_EXIT:
SELECT @ERROR_PARMS
= '(email/pword/@@error/@@rowcount): ' + CAST(@email AS
CHAR) + '/' + CAST(@pwd AS CHAR) + '/'+ LTRIM(str
(@error_sqlcode)) + '/'+ LTRIM(str(@error_rowcount))

EXECUTE ERROR_EXIT @ERROR_CODE =
@ERROR_CODE ,
@ERROR_OBJECT =
@ERROR_OBJECT ,
@ERROR_PARMS =
@ERROR_PARMS ,
@ERROR_SOURCE =
@ERROR_SPROC ,
@MOD_ID = 0
,
@USER_ID = 0
,
@USE_MODE = 0
, -- 0 = non-explosion use mode, will cause
ROLLBACK of last transaction
@USE_PARM1 = 0
,
@USE_PARM2 = 0
,
@USE_PARM3 = 0
,
@USE_PARM4 = 0
,
@PRIOR_LOG_ID = 0
, -- reports log record written out by SP that
was called, or zero if error inside this SP
@SP_OUT_VAL =
@SP_OUT_VAL OUTPUT


--shouldn't return here, but doesn't hurt, just in
case
--Print 'about to RAISERROR'
RAISERROR @ERROR_CODE 'Log Record: %1!',
@SP_OUT_VAL
--Print 'Error Log Record #: ' + LTRIM(str
(@SP_OUT_VAL))
RETURN -1
END
GO
GRANT EXECUTE ON LoginUser TO PUBLIC
GO
 
Peter,

First, apart from Sybase being supposedly common code base with SQL Server
up until recently, the only issues I see are:

1. You have no rollback
2. You state that you are getting Zero as your rowcount, which you might
since you log it.
Is the rowcount in the log actually Zero? Perhaps it is > 1?
I made this exact mistake in a Logon SP 2 weeks ago :)
Either way, it is an error.
3. You start with a commit? OK, so this may be standard good coding on
Sybase.. is it? Why do you have it?
4. I would use a data reader in preference to a data adapater as the latter
gives abilities not required, and the former will essentially result in a
firehose cursor (under MS that is).

5. Does the Sybase product have a Profiler? What do you see as the SP call,
what happens if you run that interactively.
6. You surely do not need BEGIN TRAN / COMMIT if you are only selecting
records?
7. Since you are only selecting records, why are you tinkering with the
TRANSACTION ISOLATION LEVEL?
Are you wishing to allow a logon while a user account is being created
and not yet committed?
The settings on MS SQL have names such as "READ UNCOMMITTED" and
"REPEATABLE READ".


To get this going, I would prune it back to a simple select with 2
RAISERROR's after it. Something like this:
.... SP header
AS
select USER_NAME = t1.CMS_USER_NAME ,
USER_ID = t1.ID ,
MODEL_ID = t1.MODEL_ID ,
LANG_ID = t1.LANG_ID
from USE_TAB t1
where t1.EXT_EMAIL = @email
and t1.PWORD = @pwd

SELECT @ERROR_SQLCODE = @@ERROR, @ERROR_ROWCOUNT= @@ROWCOUNT
IF @ERROR_SQLCODE <> 0
begin
RAISERROR('Error fetching User Record.', 16, 1)
RETURN 1
end

IF @ERROR_ROWCOUNT <> 1
begin
RAISERROR('User record not found or more than one user record', 16,
1)
RETURN 1
end

.... end of SP

Is there different behaviour if you have the connection properties set to
'SET NOCOUNT ON' vs. 'SET NOCOUNT OFF'? Again, I am not familiar with
Sybase.

- Tim
 
Tim, thanks for looking at it. Quick response, ROLLBACK
happens in the error handling sproc. The rowcount is
really zero and the whole sproc works in ISQL with
rowcount being 1. We're using SQLAnywhere, a record-level
locking, SQL/92 compliant db engineered by WATCOM. The
native interface is their Level 2 compliant ODBC
interface. SQLAnywhere was later bought by Sybase, it's
not the same codebase as Sybase SQL Server. We're using
the DataAdapter.Fill method (see earlier post of code) and
I guess that's the culprit in terms of causing a reset of
the connection-level specific @@rowcount variable.
Unfortunately, I can't prove it. THANKS AGAIN FOR YOUR
HELP !!!
 
Back
Top