Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Wrong number of parameters SQLSTATE=07001

1,948 views
Skip to first unread message

Gerald Bauer

unread,
Sep 23, 2003, 12:52:18 PM9/23/03
to
I downloaded the latest version of DB2.Net (v1.1.4322) and am working with
it in Visual Studios 2003.

I am trying to access a store procedure. In the server browser, and the
documentation, there are 12 fields (three as input and nine as output). I
have added 12 parameters with the appropriate datatype and length. I have
set the directions. When I go and ExecuteNonQuery(), I am getting a
DB2Exception message of:
"ERROR [07001] [IBM][CLI Driver] CLI0100E Wrong number of parameters.
SQLSTATE=07001"
with a native error of -99999.

I am perplexed with this message because I have all the parameters with the
same datatype. I am not doing a transaction; my commandtext is set to:
"{call PSNK0001(?,?,?,?,?,?,?,?,?,?,?,?)}"

I have a commandtype of storedprocedure. What am I missing here?


I am assuming that this version will work in the .Net 1.1 Framework. Is
this a correct assumption? Thank you for your assistance.


Bill McIntyre

unread,
Sep 24, 2003, 9:10:03 AM9/24/03
to
Gerald,

I have successfully used the ibm.net provider to call stored procedures I wrote on a 7.2 db2 running os390. I run the 8.1 fp3 admin client on the webserver and it connects through an 8.1 fp 3 db2connect gateway. The only time I received the error you received is when my parameneter number do not match. My code is in asp.net using vb.net. Let me know if you would like to see a sample of it.

Note : if you are using db2connect version 7 the oledb provider works.

Bill

>>> Gerald Bauer<bauer....@mayo.edu> 09/23/03 12:52PM >>>

Gerald Bauer

unread,
Sep 24, 2003, 5:49:23 PM9/24/03
to
Bill,

Thank you for your reply.

How are you connecting to DB2 through .Net? In the web form or through
code? I am do it by code. Here is a snip of my C# code:


const string strMethodName = "SP_PSNK0001";
string strConn = "Database=DB2D;UID=xxxxxxxx;PWD=xxxxxxxxx";
string strMessageBack = "Stub for SP_PSNK0001()";

DB2Connection cn = null;
DB2Command cmd;


try
{
cn = new DB2Connection(strConn);
cmd = cn.CreateCommand();
cn.Open();

cmd.CommandText = "{call PSNK001(?,?,?,?,?,?,?,?,?,?,?,?)}";
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new DB2Parameter("@GRP-IDIN", DB2Type.Char, 8));
cmd.Parameters.Add(new DB2Parameter("@CHAR-IDIN", DB2Type.Char, 30));
cmd.Parameters.Add(new DB2Parameter("@EFF-DTIN", DB2Type.Char, 8));
cmd.Parameters.Add(new DB2Parameter("@RC", DB2Type.Integer));
cmd.Parameters.Add(new DB2Parameter("@GRP-IDOUT", DB2Type.Char, 8));
cmd.Parameters.Add(new DB2Parameter("@CHAR-IDOUT", DB2Type.Char, 30));
cmd.Parameters.Add(new DB2Parameter("@SQLCODE", DB2Type.SmallInt));
cmd.Parameters.Add(new DB2Parameter("@SQLERRMC", DB2Type.Char, 70));
cmd.Parameters.Add(new DB2Parameter("@SQLERRD3", DB2Type.Integer));
cmd.Parameters.Add(new DB2Parameter("@MODULE", DB2Type.Char, 8));
cmd.Parameters.Add(new DB2Parameter("@PARAGRAPH", DB2Type.Char, 4));
cmd.Parameters.Add(new DB2Parameter("@STATEMENT", DB2Type.Char, 3));

cmd.Parameters["@GRP-IDIN"].Direction = ParameterDirection.Input;
cmd.Parameters["@CHAR-IDIN"].Direction = ParameterDirection.Input;
cmd.Parameters["@EFF-DTIN"].Direction = ParameterDirection.Input;
cmd.Parameters["@RC"].Direction = ParameterDirection.Output;
cmd.Parameters["@GRP-IDOUT"].Direction = ParameterDirection.Output;
cmd.Parameters["@CHAR-IDOUT"].Direction = ParameterDirection.Output;
cmd.Parameters["@SQLCODE"].Direction = ParameterDirection.Output;
cmd.Parameters["@SQLERRMC"].Direction = ParameterDirection.Output;
cmd.Parameters["@SQLERRD3"].Direction = ParameterDirection.Output;
cmd.Parameters["@MODULE"].Direction = ParameterDirection.Output;
cmd.Parameters["@PARAGRAPH"].Direction = ParameterDirection.Output;
cmd.Parameters["@STATEMENT"].Direction = ParameterDirection.Output;

cmd.Parameters["@GRP-IDIN"].Value = this.m_strSearchValue;
cmd.Parameters["@CHAR-IDIN"].Value = this.m_strSearchWith;
cmd.Parameters["@EFF-DTIN"].Value = "00000000"; // stored proc default
value for today
cmd.Prepare();

cmd.ExecuteNonQuery();

if (cmd.Parameters["@RC"].Value.ToString() != "01")
strMessageBack =
CheckReturnValueCode(cmd.Parameters["@RC"].Value.ToString()); // method to
get rc message
else
strMessageBack = cmd.Parameters["@GRP-IDOUT"].Value.ToString();


}

catch (DB2Exception ex)
{
strMessageBack = "[" + m_c_strClassName + ": " + strMethodName + "] " +
"DB2DB Exception: " + ex.Message.ToString();
}
catch(FormatException ex)
{
strMessageBack = "[" + m_c_strClassName + ": " + strMethodName + "] " +
"Format Exception: " + ex.Message.ToString();
}
catch(Exception ex)
{
strMessageBack = "[" + m_c_strClassName + ": " + strMethodName + "] " +
"Exception: " +ex.Message.ToString();
}

finally
{
if (cn.State == ConnectionState.Open)
cn.Close();
}
return strMessageBack;

<end snip>

One item someone mentioned was that when we look at the properties, via a
form, we see that the CommandText is SYSPROC."PSNK0001"
Is that how I am suppose to set my command text? (Putting quotes in my
string.) Another issue I see is that the ExecuteNonQuery() returns an int.
I do not see documentation saying that I am suppose to catch this. We even
renamed the stored proc with a wrong name and we get the "ERROR [07001]


[IBM][CLI Driver] CLI0100E Wrong number of parameters. SQLSTATE=07001"

message. Is DB2 sending me the wrong message? More questions than answers.


Any help will be greatly appreciated.


Bill McIntyre

unread,
Sep 25, 2003, 9:45:42 AM9/25/03
to
Gerald,

I too use code...Just can't get myself to do the drag and drop objects method. Here is a simple example. I use VB.NET but they appear similar enough.

also try not to laugh too loud at my coding ;)

Dim dbConn1 As New IBM.Data.DB2.DB2Connection("Password=xxxxxxx;User ID=xxxxxxxx;Database=db2xxx")
Dim CheckSQL As New IBM.Data.DB2.DB2Command("CSXT.CSXTSQ97_IDCHK", dbConn1)
Dim CheckReader As IBM.Data.DB2.DB2DataReader
CheckSQL.CommandType = CommandType.StoredProcedure
Dim pUID, pLNAME, pZIP As IBM.Data.DB2.DB2Parameter

pUID = CheckSQL.Parameters.Add("@SUID", IBM.Data.DB2.DB2Type.Char, 10)
pLNAME = CheckSQL.Parameters.Add("@SLNAME", IBM.Data.DB2.DB2Type.Char, 10)
pZIP = CheckSQL.Parameters.Add("@SZIP", IBM.Data.DB2.DB2Type.Char, 10)

pUID.Value = Me.tbEmpNo.Text
pLNAME.Value = Mid(Me.tbLName.Text.ToUpper, 1, 10)
pZIP.Value = Me.tbZip.Text

dbConn1.Open()
CheckReader = CheckSQL.ExecuteReader()
While CheckReader.Read()
sHead = (CheckReader.GetString(1))
sSec = (CheckReader.GetString(2))
End While
CheckReader.Close()


I have other more complicated stored procs with much more parms both in and out but they all are basically the same and work.

>>> Gerald Bauer<bauer....@mayo.edu> 09/24/03 05:49PM >>>

Gerald Bauer

unread,
Sep 25, 2003, 3:09:47 PM9/25/03
to
Bill,

Thanks again for the input. I changed the code to emulate your example.
Still getting the same exception. I am going to have to get my DBA
involved. which inturn will get IBM involved because this does not make any
sense. Either I am overlooking some detail or there is a residual error in
the system. Thank you for your assistance.


Gerald Bauer

unread,
Sep 30, 2003, 9:58:24 AM9/30/03
to
Another piece of information. Working with my system DB2 DBA, we discovered
that I am not connecting to DB2 via the application. I am seen when using
DB2 Connect. I am starting to think that something is not properly
configured right within visual studios. Then one would think I would not be
able to see in the explorer the tables, stored procedures, and view. Hmmm.
Has anyone else seen this problem connecting to DB2 from code?

Thank you for your time.


Bill McIntyre

unread,
Sep 30, 2003, 10:55:35 AM9/30/03
to
Gerald,

I connect from my application to the db2connect admin client (8.1 fp3) on the webserver through a db2connect gateway (8.1 fp3) to an IBM mainframe running os/390 with db2 7.

Using in and out parameters work fine using .net. Their are a few of us who do this here. Some use notepad to do their coding I use vs but my connections are made via code as I do not drag&drop objects.

Bill

>>> Gerald Bauer<bauer....@mayo.edu> 09/30/03 09:58AM >>>

Gerald Bauer

unread,
Oct 2, 2003, 11:12:19 AM10/2/03
to
I just found out what my problem is. The problem is I was setting my
CommandText the standard way of "{call PSNK0001(?,?,?,?,?,?,?,?,?,?,?,?)}"
and decided to change it to a non-standard way of "PSNK0001" and it now
works. Perhaps there was something missed in the spec document stating that
you can make a stored procedure call using either method in setting the
CommandText statement.

The code that was throwing the exception is:

cmd.CommandText = "{call PSNK0001(?,?,?,?,?,?,?,?,?,?,?,?)}";
The code that works is:

cmd.CommandText = "PSNK0001";

Go figure that it would be an issue.


"Gerald Bauer" <bauer....@mayo.edu> wrote in message
news:blc264$4m0a$1...@news.boulder.ibm.com...

Michael Hoy

unread,
Oct 2, 2003, 12:15:16 PM10/2/03
to
Hi Gerald,

This is actually documented, take a look at the CommandType property
documentation.

If you set the cmd.CommandType to CommandType.StoredProcedure, then the
CommandText should contain the name of the stored procedure. If the
CommandType is CommandType.Text then the CommandText should contain the call
syntax.

-Michael

"Gerald Bauer" <bauer....@mayo.edu> wrote in message

news:blhf8p$3l8q$1...@news.boulder.ibm.com...

Gerald Bauer

unread,
Oct 2, 2003, 12:43:39 PM10/2/03
to
Hello Michael,

Yes I do have the Comand's property CommandType set to
CommandType.StoredProcedure.

I set the Command property CommandText to a recommended standard calling
method (according to various docmentations I am reading) of "call
StoredProcName(?,?)}" - where the ? are the parameters of the stored
procedure. The this way of coding throws an exception.

To avoid the exception I set the Command property CommandText to just the
name of the stored proc. I believe that the initial way I set up the call
should not throw an exception of SQLCODE=07001 wrong number of parameters
because it is documented that way of coding is the recommended way of
calling a stored procedure. Even IBM DB2 .NET Data Provider Help suggests
the first way of calling a stored procedure.

Cheers,

Gerald


"Michael Hoy" <hoy...@ca.ibm.com> wrote in message
news:blhium$5eig$1...@news.boulder.ibm.com...

Michael Hoy

unread,
Oct 2, 2003, 8:18:56 PM10/2/03
to
Hi Gerald,

The suggested solution is indeed to use the "CALL SP(?,?)" syntax but you
must also leave the Command property CommandType to the default value, which
is CommandType.Text. If you change the Command property CommandType and set
it to CommandType.StoredProcedure (which you did) then you MUST also set the
Command property CommandText to the name of the stored procedure.

So, just leave the CommandType to the default value and stick with the "CALL
SP(?,?)" syntax.

Hope this clarifies the issue.

-Michael

"Gerald Bauer" <bauer....@mayo.edu> wrote in message

news:blhkju$2b9u$1...@news.boulder.ibm.com...

0 new messages