Thread: executing insert sproc returns -1 rows affected even though new row inserted

executing insert sproc returns -1 rows affected even though new row inserted

From

"Rean Griffith"

Date:

18 January 2004, 20:17:19

I’m running PostgreSQL 7.4.1 on RHL 9 and using the psqlodbc-07_03_0200.zip odbc package on a Windows XP box running an ASP.Net 1.1 web app – I’m using the System.Data.Odbc classes and NOT the Microsoft.Data.Odbc classes.

Below is a snippet of some unit test code I’m running, here’s the problem, calling a stored procedure that does an insert succeeds but the rows affected (what gets returned from cmd.ExecuteNonQuery) is always -1. The sproc itself is very simple (also below).

I turned on logging in the odbc driver, the log is also below.

Any suggestions/ideas as to why the rows affected isn’t coming back correctly and possible fixes etc.?

Can replies be cc’ed to me as well as the list?

Thx rg

Snip start---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

OdbcConnection conn = new OdbcConnection( "DSN=<dbname>;UID=<dbuser>;PWD=<dbpasswd>;DATABASE=<dbname>" );

conn.Open();

OdbcCommand cmd = new OdbcCommand();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "{ call sp_CreateUser(?,?,?,?) }";

cmd.Parameters.Add( "userName", OdbcType.VarChar );

cmd.Parameters.Add( "userEmail", OdbcType.VarChar );

cmd.Parameters.Add( "userPswd", OdbcType.VarChar );

cmd.Parameters.Add( "userID", OdbcType.VarChar );

cmd.Parameters[0].Value = "c";

cmd.Parameters[1].Value = "c";

cmd.Parameters[2].Value = "c";

cmd.Parameters[3].Value = "c";

cmd.Connection = conn;

int nRes = 0;

nRes = cmd.ExecuteNonQuery();

conn.Close();

Snip end-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sproc start----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

create or replace function sp_CreateUser(varchar,varchar,varchar,varchar) returns integer as

declare

userName alias for $1;

userEmail alias for $2;

userPswd alias for $3;

userID alias for $4;

begin

insert into Users(USER_NAME,USER_EMAIL,USER_PSWD,USER_ID) values (userName,userEmail,userPswd,userID);

return 0;

end;

'language 'plpgsql'

Sproc end-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Log start-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

conn=119028656, PGAPI_DriverConnect( in)='DSN=<edited>;UID=<edited>;PWD=<edited>;DATABASE=<edited>;', fDriverCompletion=0

DSN info: DSN='<edited>',server='<edited>',port='<edited>',dbase='<edited>',user='<edited>',passwd='<edited>'

onlyread='0',protocol='6.4',showoid='0',fakeoidindex='0',showsystable='0'

conn_settings='',conn_encoding='OTHER'

translation_dll='',translation_option=''

Global Options: Version='07.03.0200', fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190

disable_optimizer=1, ksqo=1, unique_index=1, use_declarefetch=0

text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64

extra_systable_prefixes='dd_;', conn_settings='' conn_encoding='OTHER'

conn=119028656, query=' '

conn=119028656, query='select version()'

[ fetched 1 rows ]

[ PostgreSQL version string = 'PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5)' ]

[ PostgreSQL version number = '7.4' ]

conn=119028656, query='set DateStyle to 'ISO''

conn=119028656, query='set geqo to 'OFF''

conn=119028656, query='set extra_float_digits to 2'

conn=119028656, query='select oid from pg_type where typname='lo''

[ fetched 0 rows ]

conn=119028656, query='select pg_client_encoding()'

[ fetched 1 rows ]

[ Client encoding = 'UNICODE' (code = 6) ]

conn=119028656, PGAPI_DriverConnect(out)='(NULL)'

CONN ERROR: func=PGAPI_GetInfo30, desc='', errnum=209, errmsg='Unrecognized key passed to SQLGetInfo30.'

------------------------------------------------------------

henv=119028600, conn=119028656, status=1, num_stmts=16

sock=119021336, stmts=119021408, lobj_type=-999

---------------- Socket Info -------------------------------

socket=600, reverse=0, errornumber=0, errormsg='(NULL)'

buffer_in=119039656, buffer_out=119043760

buffer_filled_in=61, buffer_filled_out=0, buffer_read_in=61

CONN ERROR: func=SQLGetInfo30, desc='', errnum=209, errmsg='Unrecognized key passed to SQLGetInfo30.'

------------------------------------------------------------

henv=119028600, conn=119028656, status=1, num_stmts=16

sock=119021336, stmts=119021408, lobj_type=-999

---------------- Socket Info -------------------------------

socket=600, reverse=0, errornumber=0, errormsg='(NULL)'

buffer_in=119039656, buffer_out=119043760

buffer_filled_in=61, buffer_filled_out=0, buffer_read_in=61

conn=119028656, query='SELECT sp_CreateUser('c','c','c','c') '

[ fetched 1 rows ]

conn=119028656, PGAPI_Disconnect

Log end--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------