Using the latest version of LLBLGen 3.5 - 3.5.12.123 (just downloaded it 2014-05-01) Runtime 3.5.14.113 Adapter - .NET 4.0 PostgreSQL 9.2.8

I don't have complete code due to NDA, but I think I have everything relevant posted.

I have a Postgresql Function with the following Signature:

CREATE OR REPLACE FUNCTION ad_stats(enddate date)
  RETURNS SETOF refcursor AS

I added this function in the designer, generated, compiled and when I run the code, the call to the function tries to execute this query:

select * from "ops_test"."ops"."ad_stats(:enddate)"((E'2014-05-01')::date)

And it Produces this error:

ERROR: function ops_test.ops.ad_stats(:enddate)(date) does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You might need to add explicit type casts. Character: 15

I copy and pasted the query from the exception above and got it working by removing the initial parameterName like this:

select * from "ops_test"."ops"."ad_stats"((E'2014-05-01')::date)

I'm not a Postgres guru, and I'm not familiar with a usage of functionName(_parameterName)(_parameterValue).

Something isn't right. I hope you can help.

This is my first time attempting to call a function in Postgresql. We've been using llblgen on this project for over 2 years. Production code is at 3.1 and I just upgraded my development environment tonight to 3.5 because I suddenly was getting the problem with the double,double quotes around the schema name and the forum threads that I found suggested the upgrade to 3.5. After upgrading, I made sure that all of my project references were pointing to the new 3.5 dlls.

I think this is due to a bug in npgsql. A workaround/fix was introduced in latest v4.1 (Ref: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=22606 ), but I'm not sure whether it was back-ported to v3.x.

We will look into it.

We'll backport the template fix to v3.5, which will be available later today (I'll attach it here to this thread).

See attached file. Unpack the archive in

<LLBLGen Pro v3.5 installation folder>\Frameworks\LLBLGen Pro\Templates

as administrator. You'll overwrite some files and folders. It will add 2 new files and render one obsolete (in the shared folder, just leave it there).

After that, regenerate your code, and you should now see the elements in the procedure name be wrapped properly with "" quotes.

Please report back any issues you run into (try it out on a test system first).

I downloaded the template and it made the call. I get a new error now:

ERROR: 34000: cursor "<unnamed portal 1>" does not exist

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Npgsql.NpgsqlException: ERROR: 34000: cursor "<unnamed portal 1>" does not exist

Source Error:

Line 39: using(StoredProcedureCall call = CreateAdStatsCall(dataAccessProvider, endDate)) Line 40: { Line 41: DataTable toReturn = call.FillDataTable(); Line 42: return toReturn; Line 43: }

Source File: C:\Source\opsv1\TRE.Data.LLBL\DatabaseSpecific\RetrievalProcedures.cs Line: 41

Stack Trace:

[NpgsqlException (0x80004005): ERROR: 34000: cursor "<unnamed portal 1>" does not exist] Npgsql.<ProcessBackendResponses_Ver_3>d__a.MoveNext() +3015 Npgsql.ForwardsOnlyDataReader.GetNextResponseObject() +773 Npgsql.ForwardsOnlyDataReader.GetNextRowDescription() +88 Npgsql.ForwardsOnlyDataReader.NextResult() +68 Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean synchOnReadError) +157 Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb) +351 Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb) +715 Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb) +391 Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +9 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +15 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +325 System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) +487 System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) +293 SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CallRetrievalStoredProcedure(String storedProcedureToCall, DbParameter[] parameters, DataTable tableToFill) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:165 SD.LLBLGen.Pro.ORMSupportClasses.StoredProcedureCall.FillDataTable() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\StoredProcedureCall.cs:135 TRE.Data.LLBL.DatabaseSpecific.RetrievalProcedures.AdStats(DateTime endDate, IDataAccessCore dataAccessProvider) in C:\Source\opsv1\TRE.Data.LLBL\DatabaseSpecific\RetrievalProcedures.cs:41 TRE.Data.Reports.ad.AdStatsDAO.GetAdStats(DateTime endDate) in C:\Source\opsv1\TRE.Data\Reports\ad\AdStatsDAO.cs:26 rpt_AdStats.btnRun_Click(Object sender, EventArgs e) in c:\Source\opsv1\TRE.OPS.Web.UI\rpt\AdStats.aspx.cs:38 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +155 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3804

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.34009

I simplified my function and reproduced the error with this:

CREATE OR REPLACE FUNCTION Ad_Stats(endDate date) RETURNS SETOF refcursor AS 
$BODY$
DECLARE ref1 refcursor;
BEGIN
    OPEN ref1 FOR
    VALUES (1, 'one'), (2, 'two'), (3, 'three');

    RETURN NEXT ref1;

    RETURN;

END;
$BODY$
LANGUAGE plpgsql;

I could be coding the function incorrectly. This is my first time doing this, but I followed documentation at npgsql site: http://npgsql.projects.pgfoundry.org/docs/manual/UserManual.html There are no bookmarks, but you can search for refcursor. I could be doing this wrong because the example uses multiple resultsets where I only need to return one. I'm not sure if this is a problem with my sql or llblgen.

Mmmm . Did you re-generate the code after replacing the template? Please post the generated SQL. I will try to reproduce it...

fetch all from "<unnamed portal 1>";;

Hope that helps. Did the syntax of my function look right? When I call it from pgadmin, I get "<unnamed portal 1>" in the result pane, but I assume that's just a handle on the cursor.

yes that's the cursor. I still think it's related to the problem in npgsql. Do you use the latest npgsql? The latest build of npgsql doesn't have this problem the older ones did. Also please check the RetrievalProcedures.cs class generated with the new templates (you did regenerate the code, right?) which now should have "" around the function name elements. (so "\"name\""... )

Yes, I did regenerate. I have the latest templates (with the "). It's calling the function. I downloaded npgsql 2.1.3, installed in the gac, updated all references to the new version and removed npgsl 2.0.11 from the gac to make sure it isn't being referenced. The application is running. It is retrieving entities, but the same error still occurs.

I found another example for the function:

CREATE OR REPLACE FUNCTION ad_stats(enddate date)
  RETURNS refcursor AS
$BODY$
DECLARE ref1 refcursor;
BEGIN
    OPEN ref1 FOR
    VALUES (1, 'one'), (2, 'two'), (3, 'three');

    RETURN ref1;

END;
$BODY$
  LANGUAGE plpgsql;

It's a bit more simplified, but I'm still getting the same results (I refreshed and regenerated).

I apologize for just getting around to testing this, I was so focused on getting LLBLGen working.

I used Npgsql directly using the following code and it worked perfectly.

                        DateTime endDate = DateTime.Now;
            DataSet myDS = new DataSet();
            NpgsqlConnection conn = new NpgsqlConnection("Database=myDb;Server=localhost;Port=5432;User Id=myUserName;Password=mypassword;SSL=True;Encoding=ASCII;Protocol=3;");
            conn.Open();

            NpgsqlTransaction t = conn.BeginTransaction();
            NpgsqlCommand command = new NpgsqlCommand("Ad_Stats", conn);
            command.CommandType = CommandType.StoredProcedure;

            // Now add the parameter to the parameter collection of the command specifying its type.
            command.Parameters.Add(new NpgsqlParameter("endDate", NpgsqlDbType.Date));

            // Now, add a value to it and later execute the command as usual.
            command.Parameters[0].Value = endDate;

            NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);
            da.Fill(myDS);

            t.Commit();
            conn.Close();

So if the problem is Npgsql, what can I do to make it work with LLBLGen??

I was trying to reproduce your issue. The new template does resolve the "quotes" problem, as you can now call other SPs.

The real problem is related to 'REF CURSOR' in conjunction with transactions. It seems that the cursor is already closed when the cursor is returned, and the solution is to start a transaction. For instance, if you comment the transaction part in your Npgsql example:

//NpgsqlTransaction t = conn.BeginTransaction();
...
//t.Commit();

... you will obtain the same exception.

We will investigate this further. For the moment, use this workaround:

using (var adapter = new DataAccessAdapter())
{
    adapter.StartTransaction(IsolationLevel.Serializable, "X");
    var results = ActionProcedures.AdStats(DateTime.Now, adapter);
    adapter.Commit();
}

The following article briefly explains this behavior: http://www.sqlines.com/postgresql/npgsql_cs_result_sets

Makes sense. (the example given also starts a transaction).

So to solve this, please start a transaction as David illustrated. It's a requirement of the ado.net provider it seems, so we can't do anything else than follow what they dictate.

Ah! I should have tried that. I assumed that any fetches like that would be contained within a transaction. Thanks for the help.

Hi, i Have similar issue with postgreSQL database.

I'm using latest 4.2 final LLBLGen Pro DB: Postgres 9.3.5 SelfServicing .Net 4.5.1

I have an function for id generation in postgres db:

CREATE OR REPLACE FUNCTION new_id(OUT result bigint) AS $$
DECLARE
    our_epoch bigint :=1265897800000;
    seq_id bigint;
    now_millis bigint;
    shard_id int := 1;
    
BEGIN  

    SELECT nextval('global_id_sequence') % 1024 INTO seq_id;

    SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
    result := (now_millis - our_epoch) << 23;
    result := result | (shard_id << 10);
    result := result | (seq_id);

END;
$$ LANGUAGE PLPGSQL;

Then i've generated code using Npgsql-2.2.0.0 provider. ActionProcedures.cs contains next code:

public delegate int NewIdCallBack(ref System.Int64 result,  ITransaction transactionToUse);

        public static int NewId(ref System.Int64 result)
        {
            return NewId(ref result, null);
        }

        public static int NewId(ref System.Int64 result,  ITransaction transactionToUse)
        {
            using(StoredProcedureCall call = CreateNewIdCall(new DataAccessCoreImpl(new CommonDaoBase(), transactionToUse), result))
            {
                int toReturn = call.Call();
                result = call.GetParameterValue<System.Int64>(0);
                return toReturn;
            }
        }

        private static StoredProcedureCall CreateNewIdCall(IDataAccessCore dataAccessProvider, System.Int64 result)
        {
            return new StoredProcedureCall(dataAccessProvider, "\"db\".\"schema\".\"new_id\"(:result)", "NewId")
                            .AddParameter("result", "Bigint", 0, ParameterDirection.Output, true, 0, 0, result);
        }

But in runtime i catch following exception:

Npgsql.NpgsqlException:
syntax error at or near ":"
Severity: ERROR
Code: 42601
   at Npgsql.NpgsqlState.<ProcessBackendResponses>d__0.MoveNext()
   at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject(Boolean cleanup)
   at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription()
   at Npgsql.ForwardsOnlyDataReader.NextResultInternal()
   at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean preparedStatement, NpgsqlRowDescription rowDescription)
   at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.CallActionStoredProcedure(String storedProcedureToCall, DbParameter[] parameters, ITransaction transaction)
   at SD.LLBLGen.Pro.ORMSupportClasses.SelfServicingSpecific.DataAccessCoreImpl.CallActionStoredProcedure(String storedProcedureToCall, DbParameter[] parameters)
   at SD.LLBLGen.Pro.ORMSupportClasses.StoredProcedureCall.Call()
   at .NewId(Int64& result, ITransaction transactionToUse) at ..\StoredProcedureCallerClasses\ActionProcedures.cs
   at .NewId(Int64& result) at ..\StoredProcedureCallerClasses\ActionProcedures.cs

And if I look to the Postgresql server's log i can see: http://clip2net.com/s/j8Dh2R

But if i change signature of LLBLGen generated caller class like this:

 return new StoredProcedureCall(dataAccessProvider, "\"db\".\"schema\".\"new_id\"()", "NewId")
                            .AddParameter("result", "Bigint", 0, ParameterDirection.Output, true, 0, 0, result);

It is working!!!

From db logs we can see that db provider trying to passing db command with output parameter definition "SELECT new_id(:result)", but it must be cleared out. But by who? by LLBLgen selfservicing template or by NpgSQL?

This is normal behaviour or it is me doing something wrong?

I'm using latest 4.2 final LLBLGen Pro

Please provide the exact LLBLGen Pro runtime library build (version) number.

Also could you please do us a favor, and for the sake of testing, try to call a SP with an input parameter and see if it works.

Please don't post in old threads next time

About the problem, it's a bit odd, as the parameter has to be specified in the header, at least that was always the case. It might be npgsql nowadays changed that behavior, not sure. I'll see what is causing the problem.

Walaa wrote:

I'm using latest 4.2 final LLBLGen Pro

Please provide the exact LLBLGen Pro runtime library build (version) number.

Also could you please do us a favor, and for the sake of testing, try to call a SP with an input parameter and see if it works.

Runtime version: 4.2.14.1017

If i change parameter direction in generated code Npgsql generating exception:

Npgsql.NpgsqlException:
function sys.new_id(bigint) does not exist
Severity: ERROR
Code: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
   at Npgsql.NpgsqlState.<ProcessBackendResponses>d__0.MoveNext()
   at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject(Boolean cleanup)
   at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription()
   at Npgsql.ForwardsOnlyDataReader.NextResultInternal()
   at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean preparedStatement, NpgsqlRowDescription rowDescription)
   at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.CallActionStoredProcedure(String storedProcedureToCall, DbParameter[] parameters, ITransaction transaction)
   at SD.LLBLGen.Pro.ORMSupportClasses.SelfServicingSpecific.DataAccessCoreImpl.CallActionStoredProcedure(String storedProcedureToCall, DbParameter[] parameters)
   at SD.LLBLGen.Pro.ORMSupportClasses.StoredProcedureCall.Call()
   at core.entity.StoredProcedureCallerClasses.ActionProcedures.NewId(Int64& result, ITransaction transactionToUse) at ..\StoredProcedureCallerClasses\ActionProcedures.cs
   at core.entity.StoredProcedureCallerClasses.ActionProcedures.NewId(Int64& result) at ..\StoredProcedureCallerClasses\ActionProcedures.cs

BUT! In PostgreSQL i see in log line like this: http://clip2net.com/s/j8M5PN

So as we can see in db query the input and InputOutput parameters are parsing by Npgsql and parameter name are changed to parameter value, 0 in my case!

When i debug npgsql's source in eun time i saw that function parameters are parsed when they in and inout direction, then npgsql searches ' aramname' in function call and change it to value of parameter. So trouble is that output parameters are not parsed and parameter definition stays in result function call like in my case: 'Select new_id(:result)' instead of call without parameter at all.

and postgre throwing exception that syntax near ':' is incorrect

Otis wrote:

Please don't post in old threads next time

About the problem, it's a bit odd, as the parameter has to be specified in the header, at least that was always the case. It might be npgsql nowadays changed that behavior, not sure. I'll see what is causing the problem.

Sorry for posting in old thread i will post in separate thread next time. About my problem - i simply want to investigate in wich side error - in llblgen generated code or Npgsql's provider code. So if it in npgsql side - i will report bug to npgsql dev team forum.

We have an output parameter using proc in our tests. I'll see what the problem is with the npgsql lib I have currently installed on the test box (2.1.3)

that is: we have an output proc in our test DB, not in our tests. Using it in a test using 2.1.3 fails, but with a different error: ERROR: 42601: syntax error at or near ")" with the call:

return new StoredProcedureCall(dataAccessProvider, "\"Northwind\".\"public\".\"pr_getcustomeramount\"(:pcountry, :pcustomercount)", "Getcustomeramount")
                .AddParameter("pcountry", "Varchar", 10485760, ParameterDirection.Input, true, 0, 0, pcountry)
                .AddParameter("pcustomercount", "Integer", 0, ParameterDirection.Output, true, 0, 0, pcustomercount);

Changing the direction to InputOutput of course fails, as that requires the parameter to be present in the signature of the proc as input parameter, which it isn't (it's an OUT parameter).

Proc calls haven't worked for a long long time in npgsql (everything before 2.0.13 failed), I'll see whether there's been a change in versions following 2.1.3

(edit) there's an issue, which was apparently fixed in 2.2.0: https://github.com/npgsql/npgsql/issues/296

But as you're using 2.2.0 and you still get this issue, it might be they missed a spot. I'll try with the latest version (2.2.1)

(edit) with 2.2.1, I now get the ':' issue as well. I'll report it in the 296 issue at github with the proc I have.

(edit) noted in the 296 issue at github. Not sure whether they'll see it fast enough. If it gets ignored I'll notify Francisco. I have to read up on how to replace the parameter in the actual call on postgres to be able to fix it myself in npgsql (as that is usually the fastest route to get things fixed).

Otis wrote:

We have an output parameter using proc in our tests. I'll see what the problem is with the npgsql lib I have currently installed on the test box (2.1.3)

that is: we have an output proc in our test DB, not in our tests. Using it in a test using 2.1.3 fails, but with a different error: ERROR: 42601: syntax error at or near ")" with the call:

return new StoredProcedureCall(dataAccessProvider, "\"Northwind\".\"public\".\"pr_getcustomeramount\"(:pcountry, :pcustomercount)", "Getcustomeramount")
                .AddParameter("pcountry", "Varchar", 10485760, ParameterDirection.Input, true, 0, 0, pcountry)
                .AddParameter("pcustomercount", "Integer", 0, ParameterDirection.Output, true, 0, 0, pcustomercount);

Changing the direction to InputOutput of course fails, as that requires the parameter to be present in the signature of the proc as input parameter, which it isn't (it's an OUT parameter).

Proc calls haven't worked for a long long time in npgsql (everything before 2.0.13 failed), I'll see whether there's been a change in versions following 2.1.3

(edit) there's an issue, which was apparently fixed in 2.2.0: https://github.com/npgsql/npgsql/issues/296

But as you're using 2.2.0 and you still get this issue, it might be they missed a spot. I'll try with the latest version (2.2.1)

(edit) with 2.2.1, I now get the ':' issue as well. I'll report it in the 296 issue at github with the proc I have.

(edit) noted in the 296 issue at github. Not sure whether they'll see it fast enough. If it gets ignored I'll notify Francisco. I have to read up on how to replace the parameter in the actual call on postgres to be able to fix it myself in npgsql (as that is usually the fastest route to get things fixed).

Otis, thank you very much! I'll be watching on this thread and issue 296 thread on github!

Otis wrote:

We have an output parameter using proc in our tests. I'll see what the problem is with the npgsql lib I have currently installed on the test box (2.1.3)

that is: we have an output proc in our test DB, not in our tests. Using it in a test using 2.1.3 fails, but with a different error: ERROR: 42601: syntax error at or near ")" with the call:

return new StoredProcedureCall(dataAccessProvider, "\"Northwind\".\"public\".\"pr_getcustomeramount\"(:pcountry, :pcustomercount)", "Getcustomeramount")
                .AddParameter("pcountry", "Varchar", 10485760, ParameterDirection.Input, true, 0, 0, pcountry)
                .AddParameter("pcustomercount", "Integer", 0, ParameterDirection.Output, true, 0, 0, pcustomercount);

Changing the direction to InputOutput of course fails, as that requires the parameter to be present in the signature of the proc as input parameter, which it isn't (it's an OUT parameter).

Proc calls haven't worked for a long long time in npgsql (everything before 2.0.13 failed), I'll see whether there's been a change in versions following 2.1.3

(edit) there's an issue, which was apparently fixed in 2.2.0: https://github.com/npgsql/npgsql/issues/296

But as you're using 2.2.0 and you still get this issue, it might be they missed a spot. I'll try with the latest version (2.2.1)

(edit) with 2.2.1, I now get the ':' issue as well. I'll report it in the 296 issue at github with the proc I have.

(edit) noted in the 296 issue at github. Not sure whether they'll see it fast enough. If it gets ignored I'll notify Francisco. I have to read up on how to replace the parameter in the actual call on postgres to be able to fix it myself in npgsql (as that is usually the fastest route to get things fixed).

So, there is answer from Francisco on github - the point is that when parameter is Output direction there is no need to add it in procedure signature at all.

Removing all parameters from the signature fixes it. We had to add them for old versions, but these are too old anyway, so I've adjusted a small template for postgresql and have attached it.

Copy the attached template over the existing one in the folder: <installation folder>\Frameworks\LLBLGen Pro\Templates\PostgreSqlSpecific\C#

as Administrator.

then regenerate your code, it then should work OK

Otis wrote:

Removing all parameters from the signature fixes it. We had to add them for old versions, but these are too old anyway, so I've adjusted a small template for postgresql and have attached it.

Copy the attached template over the existing one in the folder: <installation folder>\Frameworks\LLBLGen Pro\Templates\PostgreSqlSpecific\C#

as Administrator.

then regenerate your code, it then should work OK

Thanks Otis! Now it's generated correctly! Thanks for quick support!