Problem creating function
Posted: Sun 08 Jan 2006 17:05
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
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