How do I call a Stored Proc from code with dotConnect for PostgreSQL?

How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Postby OutOfTouch6947 » Mon 13 Mar 2017 20:30

I don't see any examples in the documentation on how to call a stored proc from code using dotConnect for PostgreSQL that returns a result set. Can you please point me at one or provide me with one, thank you.

I also would like to know if using TransactionScope will work, isntead of creating an explicit transaction on the connection?
OutOfTouch6947
 
Posts: 79
Joined: Tue 02 Jun 2015 18:22

Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Postby OutOfTouch6947 » Wed 15 Mar 2017 21:04

I still have no idea how to do this, I have tried a few things and it isn't working.
OutOfTouch6947
 
Posts: 79
Joined: Tue 02 Jun 2015 18:22

Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Postby Shalex » Thu 16 Mar 2017 09:46

1. Please tell us your current approach: plain ADO.NET classes (PgSqlCommand, etc), LinqConnect, or Entity Framework.

2. Specify the issue you have encountered: the exact text of the error (and its stack trace), a test code snippet. If possible, send us a small test project with the corresponding DDL/DML script for reproducing.

JIC: https://www.devart.com/dotconnect/postgresql/docs/?TransactionsLINQ.html
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Postby OutOfTouch6947 » Thu 16 Mar 2017 14:38

Shalex wrote:1. Please tell us your current approach: plain ADO.NET classes (PgSqlCommand, etc), LinqConnect, or Entity Framework.

2. Specify the issue you have encountered: the exact text of the error (and its stack trace), a test code snippet. If possible, send us a small test project with the corresponding DDL/DML script for reproducing.

JIC: https://www.devart.com/dotconnect/postgresql/docs/?TransactionsLINQ.html


ADO.Net PgSqlCommand, I have no code snippets because I am not sure what I need to do to get a result set from a stored proc with postgresql since functions use cursors, I looked in the documentation and I could not find any examples, maybe it is there and I missed it, I just need an example.

Here is a shortened and dumbed down version of my proc:

Code: Select all
CREATE OR REPLACE FUNCTION "GetCheckRecords"() RETURNS refcursor AS $$
    DECLARE
      ref refcursor;                                                     -- Declare a cursor variable
    BEGIN
      OPEN ref FOR
         SELECT
            "PaymentDetailId",
            "CheckNumber"
                     
         FROM 
               "PaymentDetail"
            ;   -- Open a cursor
      RETURN ref;   -- Return the cursor to the caller
    END;
    $$ LANGUAGE plpgsql;


This is the closest thing I could find in DevArt documentation:
https://www.devart.com/dotconnect/postgresql/docs/Devart.Data.PostgreSql~Devart.Data.PostgreSql.PgSqlCursor.html
But in the proc in that example the ref cursor is an out parameter, I am not good enough with PostgreSQL to understand the difference between that proc and mine in how they return the cursor.

Here is some example C# code trying to use that proc:
Code: Select all
PgSqlDataTable  pgdt = new PgSqlDataTable();
           
            using (PgSqlConnection connection = new PgSqlConnection(_connectionString))
            {
                PgSqlTransaction tran = connection.BeginTransaction();

                PgSqlCommand cmd = new PgSqlCommand("GetCheckRecords", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };
               
                pgdt .Load(cmd.ExecuteReader());
            }

            return pgdt ;
OutOfTouch6947
 
Posts: 79
Joined: Tue 02 Jun 2015 18:22

Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Postby OutOfTouch6947 » Thu 16 Mar 2017 18:52

Okay here us my updated proc where I add in the ability to name the cursor since the name changes.

Code: Select all
CREATE OR REPLACE FUNCTION "GetJawsCheckByPhoneExportRecords"(INOUT ref refcursor) RETURNS refcursor AS $BODY$
   
   BEGIN
      OPEN ref FOR
         SELECT
            "PaymentDetailId",
            "CheckNumber"
                     
         FROM
               "PaymentDetail"
            ;   -- Open a cursor
      --RETURN ref;   -- Return the cursor to the caller
    END;
    $$ LANGUAGE plpgsql;
Last edited by OutOfTouch6947 on Thu 16 Mar 2017 22:24, edited 1 time in total.
OutOfTouch6947
 
Posts: 79
Joined: Tue 02 Jun 2015 18:22

Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Postby OutOfTouch6947 » Thu 16 Mar 2017 20:25

Here is my updated client ado.net code, I keep getting null error on trying to start the transaction because I am inside a transactionscope so I commented out that part and now my cursor is always null.

Code: Select all

            PgSqlDataTable checkByPhoneRecords = new PgSqlDataTable();
            PgSqlConnection connection = new PgSqlConnection(_connectionString);
            PgSqlDataReader dataReader;
            PgSqlCommand cmd = new PgSqlCommand("GetCheckRecords", connection)
            {
                CommandType = CommandType.StoredProcedure,
                ParameterCheck = true
            };

            cmd.Parameters.Add("ref","cbp");
                   
           //PgSqlTransaction tran = connection.BeginTransaction();
            connection.Open();
            try
            {
                PgSqlCursor cursor = cmd.Parameters["ref"].PgSqlValue as PgSqlCursor;
                checkByPhoneRecords .Load(cursor.GetDataReader());
               
                return checkByPhoneRecords ;
            }
            finally
            {
                //tran.Commit();
                connection.Close();
            }

OutOfTouch6947
 
Posts: 79
Joined: Tue 02 Jun 2015 18:22

Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Postby Pinturiccio » Fri 17 Mar 2017 12:14

OutOfTouch6947 wrote:But in the proc in that example the ref cursor is an out parameter, I am not good enough with PostgreSQL to understand the difference between that proc and mine in how they return the cursor.

To get data from a cursor in a return parameter, you can use the ExecuteReader method of your command. Created reader will contain data from cursor. You can use the following code with your GetCheckRecords function:
Code: Select all
DataTable dt = new DataTable();
using (TransactionScope transScope = new TransactionScope())
{
    PgSqlConnection conn = new PgSqlConnection("your connection string");
    conn.Open();

    PgSqlCommand comm = new PgSqlCommand("\"GetCheckRecords\"", conn);
    comm.CommandType = System.Data.CommandType.StoredProcedure;
    comm.ParameterCheck = true;

    var reader = comm.ExecuteReader();
               
    dt.Load(reader);
}


Does this code work for you?
Pinturiccio
Devart Team
 
Posts: 1982
Joined: Wed 02 Nov 2011 09:44

Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Postby OutOfTouch6947 » Fri 17 Mar 2017 15:04

@Pinturiccio Yes that works, thanks.
Actually I get an error when calling scope.complete.

Exception thrown: 'System.Transactions.TransactionAbortedException' in System.Transactions.dll
System.Transactions.TransactionAbortedException: The transaction has aborted. ---> Devart.Data.PostgreSql.PgSqlException: prepared transactions are disabled
OutOfTouch6947
 
Posts: 79
Joined: Tue 02 Jun 2015 18:22

Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Postby OutOfTouch6947 » Mon 20 Mar 2017 21:17

I already set max_prepared_transactions = 100 which is what the default connection pool size is and restarted the postgresql instance.

I still get the same error.
OutOfTouch6947
 
Posts: 79
Joined: Tue 02 Jun 2015 18:22

Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Postby Pinturiccio » Tue 21 Mar 2017 15:32

Please make sure that max_prepared_transactions is set to 100 in the connection you are using. For this add the following code after conn.Open(); in our example:
Code: Select all
PgSqlCommand cmd = new PgSqlCommand("show max_prepared_transactions", conn);
Console.WriteLine(cmd.ExecuteScalar());

Which value is returned in your case?
Pinturiccio
Devart Team
 
Posts: 1982
Joined: Wed 02 Nov 2011 09:44

Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Postby OutOfTouch6947 » Tue 21 Mar 2017 16:25

Pinturiccio wrote:Please make sure that max_prepared_transactions is set to 100 in the connection you are using. For this add the following code after conn.Open(); in our example:
Code: Select all
PgSqlCommand cmd = new PgSqlCommand("show max_prepared_transactions", conn);
Console.WriteLine(cmd.ExecuteScalar());

Which value is returned in your case?

It is showing 0, I don't understand why.
OutOfTouch6947
 
Posts: 79
Joined: Tue 02 Jun 2015 18:22

Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Postby OutOfTouch6947 » Tue 21 Mar 2017 17:11

OutOfTouch6947 wrote:
Pinturiccio wrote:Please make sure that max_prepared_transactions is set to 100 in the connection you are using. For this add the following code after conn.Open(); in our example:
Code: Select all
PgSqlCommand cmd = new PgSqlCommand("show max_prepared_transactions", conn);
Console.WriteLine(cmd.ExecuteScalar());

Which value is returned in your case?

It is showing 0, I don't understand why.

I just double checked the conifg and the line was commented out still, I now get 100 back from that query and code runs with no errors.
OutOfTouch6947
 
Posts: 79
Joined: Tue 02 Jun 2015 18:22


Return to dotConnect for PostgreSQL