Page 1 of 1
How do I call a Stored Proc from code with dotConnect for PostgreSQL?
Posted: Mon 13 Mar 2017 20:30
by OutOfTouch6947
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?
Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?
Posted: Wed 15 Mar 2017 21:04
by OutOfTouch6947
I still have no idea how to do this, I have tried a few things and it isn't working.
Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?
Posted: Thu 16 Mar 2017 09:46
by Shalex
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/postg ... sLINQ.html
Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?
Posted: Thu 16 Mar 2017 14:38
by OutOfTouch6947
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/postg ... sLINQ.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/postg ... ursor.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 ;
Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?
Posted: Thu 16 Mar 2017 18:52
by OutOfTouch6947
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;
Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?
Posted: Thu 16 Mar 2017 20:25
by OutOfTouch6947
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();
}
Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?
Posted: Fri 17 Mar 2017 12:14
by Pinturiccio
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?
Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?
Posted: Fri 17 Mar 2017 15:04
by OutOfTouch6947
@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
Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?
Posted: Mon 20 Mar 2017 21:17
by OutOfTouch6947
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.
Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?
Posted: Tue 21 Mar 2017 15:32
by Pinturiccio
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?
Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?
Posted: Tue 21 Mar 2017 16:25
by OutOfTouch6947
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.
Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?
Posted: Tue 21 Mar 2017 17:11
by OutOfTouch6947
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.