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

From: "Rean Griffith" <rg2023(at)columbia(dot)edu>
To: <pgsql-odbc(at)postgresql(dot)org>
Subject: executing insert sproc returns -1 rows affected even though new row inserted
Date: 2004-01-13 15:36:29
Message-ID: 000001c3d9eb$13d453d0$0100a8c0@heritage0
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

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---------------------------------------------------------------------
------------------------------------------------------------------------
-----------------------------------------------------------------

Browse pgsql-odbc by date

  From Date Subject
Next Message Andreas 2004-01-14 03:29:30 How to check if a connection is alive ?
Previous Message Sven Köhler 2004-01-12 22:37:48 psqlodbc msi version 7.3.200