Page 1 of 1

Problem creating function

Posted: Sun 08 Jan 2006 17:05
by Rich
Hi,

I am trying to create a function using c# via PostgreSQLDirect .NET 2.40.

I can successfully create a tables, sequences etc but evey time I try and create a function I am gettin an exception in the Corelabs code. Where am I going wrong, or have I stumbed upon an issue?

The code that tries to create the function:

first I create a stringBuilder object:

sqlSb = new StringBuilder("CREATE OR REPLACE FUNCTION \"spAddInvoice\"(\"pStrInvoiceNumber\" \r\n");
sqlSb.Append(" \"varchar\", \"pLngCompanyId\" int4, \"pLngInvoiceNo\" int4, \"pStrThread\" \r\n");
sqlSb.Append(" \"varchar\", \"pStrNotes\" \"varchar\", \"pStartTime\" \"timestamp\", \r\n");
sqlSb.Append(" \"pEndTime\" \"timestamp\", \"pErrorNumber\" int4) \r\n");
sqlSb.Append(" RETURNS void AS \r\n");
sqlSb.Append("$BODY$ \r\n");
sqlSb.Append("INSERT INTO \"tblInvoices\" ( \r\n");
sqlSb.Append(" \"strInvoiceNumber\", \r\n");
sqlSb.Append(" \"lngCompanyId\", \r\n");
sqlSb.Append(" \"lngInvoiceNo\", \r\n");
sqlSb.Append(" \"strThread\", \r\n");
sqlSb.Append(" \"strNotes\", \r\n");
sqlSb.Append(" \"startTime\", \r\n");
sqlSb.Append(" \"endTime\", \r\n");
sqlSb.Append(" \"errorNumber\") \r\n");
sqlSb.Append(" VALUES ($1,$2,$3,$4,$5,$6,$7,$8) \r\n");
sqlSb.Append("$BODY$ \r\n");
sqlSb.Append(" LANGUAGE 'sql' VOLATILE; \r\n");

then call a function to run the statement:

if (executeInstallStatement(sqlSb, cmd, results) == false)
successful = false;

The function is as follows:

private static bool executeInstallStatement(StringBuilder sqlSb, PgSqlCommand cmd, StringBuilder results)
{
string sqlTxt = sqlSb.ToString()
bool successful = true;
try
{
cmd.CommandText = sqlTxt;
results.Append("EXECUTING COMMAND: \r\n\r\n" + sqlTxt + "\r\n");
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
results.Append("COMMAND FAILED: \r\n\r\n" + e.Message + "\r\n\r\n");
results.Append("DETAILS: \r\n\r\n" + e.ToString() + "\r\n\r\n");
successful = false;
}

if (successful == true)
results.Append("COMMAND EXECUTED SUCCESSFULLY\r\n\r\n");
return successful;
}

The connection and command objects have been created/opened earlier in the code and work fine for all the other create statements.

I have tried taking the final sql text and running it in pgAdmin III query window and that runs fine as well, but through code I get:

System.NullReferenceException was unhandled
Message="Object reference not set to an instance of an object."
Source="CoreLab.PostgreSql"
StackTrace:
at CoreLab.PostgreSql.a.f(Boolean A_0)
at CoreLab.PostgreSql.a.ab()

I have tried stripping out whitespace, removing the carriage return and line feeds but nothing seems to make any difference.

Thanks,

Rich

Posted: Tue 10 Jan 2006 09:59
by SecureGen
I tried your code in simple console application:
class Class1 {

[STAThread]
static void Main(string[] args) {

PgSqlConnection conn = new PgSqlConnection(connStr);
PgSqlCommand cmd = conn.CreateCommand();
try {
conn.Open();
StringBuilder sqlSb = new StringBuilder("CREATE OR REPLACE FUNCTION \"spAddInvoice\"(\"pStrInvoiceNumber\" \r\n");
sqlSb.Append(" \"varchar\", \"pLngCompanyId\" int4, \"pLngInvoiceNo\" int4, \"pStrThread\" \r\n");
sqlSb.Append(" \"varchar\", \"pStrNotes\" \"varchar\", \"pStartTime\" \"timestamp\", \r\n");
sqlSb.Append(" \"pEndTime\" \"timestamp\", \"pErrorNumber\" int4) \r\n");
sqlSb.Append(" RETURNS void AS \r\n");
sqlSb.Append("$BODY$ \r\n");
sqlSb.Append("INSERT INTO \"tblInvoices\" ( \r\n");
sqlSb.Append(" \"strInvoiceNumber\", \r\n");
sqlSb.Append(" \"lngCompanyId\", \r\n");
sqlSb.Append(" \"lngInvoiceNo\", \r\n");
sqlSb.Append(" \"strThread\", \r\n");
sqlSb.Append(" \"strNotes\", \r\n");
sqlSb.Append(" \"startTime\", \r\n");
sqlSb.Append(" \"endTime\", \r\n");
sqlSb.Append(" \"errorNumber\") \r\n");
sqlSb.Append(" VALUES ($1,$2,$3,$4,$5,$6,$7,$8) \r\n");
sqlSb.Append("$BODY$ \r\n");
sqlSb.Append(" LANGUAGE 'sql' VOLATILE; \r\n");

cmd.CommandText = sqlSb.ToString();
cmd.ExecuteNonQuery();
}
catch(Exception ex) {
Console.WriteLine("Error:" + ex.Message);
}
}
It reports me a valid exception "Parameter '1' is missing". Command object interprets $1 as a parameter placeholder and expects parameter value. Maybe in your case you already had some parameters on command from previous execution and it produced another exception in result. So please try to use alternative syntax for parameters in your stored procedure.