Summary
Activity
Forums
Tracker
Lists
Tasks
Docs
Surveys
News
SCM
Files

[#1010630] Missing comma’s between parameters when Preparing a call to a function.

View Trackers | Bugs | Download .csv | Monitor

Date:
2009-05-13 22:47		 Priority:
3
State:
Open
Submitted by:
Frank Szot (szotski) 		Assigned to:
Nobody (None)
Npgsql Version:
None		 Category:
Group:
Resolution:
None
Summary:
Missing comma’s between parameters when Preparing a call to a function.

Detailed description
I'm new to postgreSql and npgsql and while I was doing some inital coding I found the following bug in npgsql ver 2.0.5.

When executing the following sample method, the dbCmp.Prepare() throws an exception with a message of “type \"varchar$2\" does not exist".

private void ExecuteFunction(String conString)
{
NpgsqlConnection dbCon = new NpgsqlConnection(conString);
dbCon.Open();
NpgsqlCommand dbCmd = dbCon.CreateCommand();
dbCmd.CommandText = "TestFunction";
dbCmd.CommandType = CommandType.StoredProcedure;
(dbCmd.Parameters.Add("@Var1",
NpgsqlTypes.NpgsqlDbType.Varchar)).Value = "Var 1 value";
(dbCmd.Parameters.Add("@Var2",
NpgsqlTypes.NpgsqlDbType.Varchar)).Value = "Var 2 value";
dbCmd.Prepare();
dbCmd.ExecuteNonQuery();
dbCon.Close();
}

Debugging into the NpgsqlCommand:: GetParseCommandText() the command text string being returned is:
select * from TestFunction($1::varchar$2::varchar)
When it should be:
select * from TestFunction($1::varchar,$2::varchar)

The fix I implemented was to track when to add a comma as such:

if (parameters.Count > 0)
{
// The ReplaceParameterValue below, also checks if the parameter is present.
String parameterName;
Int32 i;
Boolean isFirstInputParameter = true;

for (i = 0; i < parameters.Count; i++)
{
if ((parameters[i].Direction == ParameterDirection.Input) ||
(parameters[i].Direction == ParameterDirection.InputOutput))
{
string parameterSize = "";
if (parameters[i].TypeInfo.UseSize && (parameters[i].Size > 0))
{
parameterSize += string.Format("({0})", parameters[i].Size);
}
if (!addProcedureParenthesis)
{
//result = result.Replace(":" + parameterName, parameters[i].Value.ToString());
parameterName = parameters[i].CleanName;
//textCommand = textCommand.Replace(':' + parameterName, "$" + (i+1));
// Just add typecast if needed.
if (parameters[i].UseCast)
parseCommand = ReplaceParameterValue(parseCommand, parameterName, string.Format("${0}::{1}{2}", (i + 1), parameters[i].TypeInfo.CastName, parameterSize));
else
parseCommand = ReplaceParameterValue(parseCommand, parameterName, string.Format("${0}{1}", (i + 1), parameterSize));
}
else
{
if (parameters[i].UseCast)
parseCommand += string.Format("{0}${1}::{2}{3}", isFirstInputParameter ? string.Empty : ", ", (i + 1), parameters[i].TypeInfo.CastName, parameterSize);
else
parseCommand += string.Format("{0}${1}{2}", isFirstInputParameter ? string.Empty : ", ", (i + 1), parameterSize);
}

isFirstInputParameter = false;
}
}
}

I do relize I could add the parameters to the command text as a work around but I want to be able to run the same command text on Sql Server.

Thanks

Szotski

Followup

No Followups Have Been Posted

Attached Files:

Attachments:
NpgsqlCommand.cs

Changes:

Field Old Value Date By
File Added402: NpgsqlCommand.cs2009-05-13 22:47szotski
Powered By FusionForge