CODEWITHSUNDEEP
c#sqldb2
MagB
MagB

Reputation: 2221

DB2 ERROR [07004] SQL0313N

I am a newbie in DB2 world and am using:
- DB2 Data Provider for .NET (IBM.Data.DB2.dll version 9.7.4.4)
- C# VS2010 with .NET Framework 4.0

I have problem with query that uses parameter. My code snippet: 

DB2Command cmd = new DB2Command();
cmd.CommandText = "SELECT COUNT(*) FROM CUSTOMERS t0 WHERE (t0.\"CITY\" < :p0)";
cmd.Connection = Db2Connection;
DB2Parameter param = cmd.CreateParameter();
param.DB2Type = DB2Type.VarChar;
param.ParameterName = ":p0";
param.Value = "Seattle";
var p = cmd.Parameters.Add(param);
var execResult = cmd.ExecuteScalar();

I get following error on cmd.ExecuteScalar():

The number of variables in the EXECUTE statement, the number of variables in the OPEN statement, or the number of arguments in an OPEN statement for a parameterized cursor is not equal to the number of values required. SQLSTATE=07004

Please help how to fix the problem. Thank you in advance.

Additional information:
1. I just tried to use IBM Data Studio to verify the DB2 command using query editor. It doesn't recognize the prefix "@" for parameter. So I use oracle-liked prefix ":" for it. It works. But my C# code still raises the error [07004] SQL0313N
2. If I don't use any prefix for parameter on my C# code, I get ERROR [42703] [IBM][DB2/NT64] SQL0206N \"P0\" is not valid in this context.

Upvotes: 1

Views: 7332

Answers (1)

MagB
MagB

Reputation: 2221

Finally I find out 2 ways to fix the problem.

  1. Using unnamed parameter "?" instead of parameter name ":p0". 

    DB2Command cmd = new DB2Command();  
cmd.CommandText = "SELECT COUNT(*) FROM CUSTOMERS t0 WHERE (t0.\"CITY\" < ?)";  
cmd.Connection = Db2Connection;  
DB2Parameter param = cmd.CreateParameter();  
param.DB2Type = DB2Type.VarChar;  
param.ParameterName = "param1";  
param.Value = "Seattle";  
var p = cmd.Parameters.Add(param);  
var execResult = cmd.ExecuteScalar();

  2. Activate HostVarParameters property of class DB2ConnectionStringBuilder and the original code remains unchanged (keeping using named parameters).

My 2 cents,
Mag

Upvotes: 1

Related Questions