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