Stored Procedure invokation

Stored Procedure invokation

Postby drysg » Mon 12 Mar 2012 20:36

Yes, I am a newbie, but I have looked through the forums, samples, etc, and I think this should be easy to answer:

How do I invoke the stored procedure shown below. I used the E/F wizard and it found my tables, and the stored procedure and I can see the SP in the ModelBrowser (and all the properties seem fine).

Since it seems to use the E/F mappings, I was hoping I could use some sort of Linq syntax, but even not all I want to do is get the table into an array.

I am stuck after the line:

Code: Select all
GPLCatalog context = new GPLCatalog();


(yes, you can laugh at this newbie, but if you are serious about support, then maybe you can take this as a data point, and provide a starter example!)


Code: Select all
CREATE OR REPLACE FUNCTION portal.getBoston(param INT) RETURNS TABLE(idx bigint,
                                                                     product VARCHAR,
                                                                     size bigint,
                                                                     DATE TIMESTAMP,
                                                                 type VARCHAR,
                                                                 classification VARCHAR,
                                                                 distributor VARCHAR,
                                                                 egpl_date TIMESTAMP,
                                                                 classification_int INT,
                                                                 handling VARCHAR,
                                                                 creator VARCHAR,
                                                                 datum VARCHAR,
                                                                 elevation_ft INT,
                                                                 description VARCHAR,
                                                                 do_location VARCHAR,
                                                                 bbox VARCHAR,
                                                                 uniq_id VARCHAR ) AS $$
    SELECT
        cat.idx,
        cat.product,
        cat.size_bytes,
        cat.date,
        cat.type,
        cat.classification,
        cat.distributor,
        cat.egpl_date,
        cat.classification_int,
        cat.handling,
        cat.creator,
        cat.datum,
        cat.elevation_ft,
        cat.description,
        cat.do_location,
        cat.bbox,
        CAST(cat.uniq_id AS VARCHAR)
    FROM
        portal.catalog AS cat
    WHERE
        ST_Intersects(ST_GeomFromText('POLYGON((-70 42,-71 42,-71 41,-70 41,-70 42))', 4326),
        cat.location) $$ LANGUAGE SQL;
drysg
 
Posts: 28
Joined: Fri 09 Mar 2012 15:10

Entity Developer

Postby drysg » Mon 12 Mar 2012 21:56

When I open up the EntityDeveloper I see my tables, but when I try to drag the stored procedure to the design surface I get:

Cannot create method for a storage function 'getboston' that can be composed. Only stored procedures may be mapped.


However, the properties of the SP show Composable as TRUE, and return type record.
drysg
 
Posts: 28
Joined: Fri 09 Mar 2012 15:10

Postby Shalex » Thu 15 Mar 2012 12:32

Please drag&drop your getBoston function from Entity Developer's Database Explorer to the SSDL part of your model in Model Explorer. Then set the Concealed Function to True for your function in Store part in Model Explorer. Drag&drop getBoston to Conceptual part.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Sent email

Postby drysg » Thu 15 Mar 2012 21:00

But that is the problem that I am having. I.e. I cannot drag and drop the function onto the SSDL from the explorer. I can move it and drag it, but then when I drop it I get that message about

Cannot create method for a storage function 'getboston' that can be composed. Only stored procedures may be mapped.


I sent you a screen shot of what this looks like on the "drop".
drysg
 
Posts: 28
Joined: Fri 09 Mar 2012 15:10

Another thing I tried

Postby drysg » Thu 15 Mar 2012 21:08

What I did now was just go straight to the model explorer, find the SP, and then change its property to concealed: True.

Then I was able to drag it to the surface.

I first get this message:

In order to obtain metadata of procedure result set it is necessary to execute the procedure, which may cause database damage.
Procedure name: portal.getboston
Do you wish to obtain metadata of procedure result set?

Since it is a GET, I was not worried, so I said yes.

But then I get the following error:

Failed to obtain metadata of procedure result set.
Unknown parameter direction.

But in the Model Explorer, it showing all the parameters, and it clearly marks all as either IN or OUT.
drysg
 
Posts: 28
Joined: Fri 09 Mar 2012 15:10

Postby Shalex » Wed 21 Mar 2012 13:28

drysg wrote:Failed to obtain metadata of procedure result set.

We have answered you by e-mail (RECORD cannot be decomposed into subtypes, use REFCURSOR).
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Postby drysg » Wed 21 Mar 2012 13:55

I did not receive a message about how to use REF CURSOR types in C#.


Yes, I understand that I need a stored procedure that returns REF CURSOR, and I have implemented the one you sent me.

Yes, I am now use DevArt LinqConnect to populate the model. (no longer using Devart Entity Model) as you suggested.

Yes, I see the stored procedure in the ModelExplorer (and it understands the input type. But it says the return type is NULL


1. I am waiting to hear from you about the last item in your email. Since I need to find who to call and this procedure and do something with the rows returned by the REF CURSOR

GPLContext.GPLDataContext context = new GPLContext.GPLDataContext();

context.SearchBox("POLYGON((-70 42,-71 42,-71 41,-70 41,-70 42))"); // This statement gets an error


2. I see that the return type that LinqConnect provided for SearchBox is NULL. What should the return type be?




Here is the stored procedure you sent me, as I implemented it:


CREATE OR REPLACE FUNCTION portal.search_box(searchbox text)
RETURNS refcursor AS
$BODY$
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR SELECT
cat.idx,
cat.product,
cat.size_bytes,
cat.date,
cat.type,
cat.classification,
cat.distributor,
cat.egpl_date,
cat.classification_int,
cat.handling,
cat.creator,
cat.datum,
cat.elevation_ft,
cat.description,
cat.do_location,
cat.bbox,
CAST(cat.uniq_id AS VARCHAR)
FROM
portal.catalog AS cat
WHERE
st_geomfromtext(searchBox, 4326) && cat.location; RETURN ref; END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION portal.search_box(text)
OWNER TO postgres;
drysg
 
Posts: 28
Joined: Fri 09 Mar 2012 15:10

Postby MariiaI » Fri 23 Mar 2012 12:42

1) Try updating the version of your dotConnect for PostgreSQL to the latest 5.80.325. For example, the method generated with this build for the stored procedure, that you've specified, returns ISingleResult instead of void.

2) If it doesn't help, please specify the message and stack trace of the inner exception from the one you are getting. The exception you posted is a common one and is used to wrap arbitrary server errors.
Also,if possible, send us the model you are working with.

3) To get the results from the returned collection, you could use ISingleResult Interface.
Try the following code:
Code: Select all
ISingleResult result = context.SearchBox("POLYGON((-70 42,-71 42,-71 41,-70 41,-70 42))");
var list = result.ToList();


For more information about ISingleResult Interface, see:
http://msdn.microsoft.com/en-us/library/bb534556.aspx
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Postby drysg » Fri 23 Mar 2012 14:45

Hello Mariia,

I think my messages are crossing paths, I apologize.

I upgraded to: 5.80.325 but the issue remains.

I got in the email yesterday a solution from Aleksandr, which used the Entity Developer. That was excellent advice, and I proceed to implement what he said. Here is there results as they stand:

Very clean, and very simple.

I really like your API, But I am still not able to use it.

When open the EntityDeveloper on the EDML model, then set portal.search_box to (CONCEALED FUNCTION := True), and then drag it I get the pop-up about adding the metadata my change the database. (Since this is a read only function I allow it to continue). But then I get the following error (as a popup on the design surface of the Entity Developer).


Failed to obtain metadata of procedure result set.
function st_geomfromtext(text, integer) does not exist


I am including both the stored procedure as well as my C# method (It is a WCF method that gets an argument of the form ?searchbox=72,40,70,42 which I use to build the OGC polygon

Code: Select all
CREATE OR REPLACE FUNCTION portal.search_box(searchbox text)
  RETURNS refcursor AS
$BODY$
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR SELECT
cat.idx,
cat.product,
cat.size_bytes,
cat.date,
cat.type,
cat.classification,
cat.distributor,
cat.egpl_date,
cat.classification_int,
cat.handling,
cat.creator,
cat.datum,
cat.elevation_ft,
cat.description,
cat.do_location,
cat.bbox,
CAST(cat.uniq_id AS VARCHAR)
FROM
portal.catalog AS cat
WHERE
st_geomfromtext(searchBox, 4326) && cat.location; RETURN ref; END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION portal.search_box(text)
  OWNER TO postgres;




Code: Select all
        public CatalogResults SearchBox(string requestBox)
        {
            string poly = Tools.Box2Poly(requestBox);                           // convert googlemaps bbox to OGC polygon
            GPLModel.GPLEntities context = new GPLModel.GPLEntities();
            context.Connection.Open();
            context.Connection.BeginTransaction();
            List matches = context.SearchBox(poly).ToList();
            //Devart: I thought I need a .EndTransaction, but I can't find one in the API
            context.Connection.Close();

            List resultList = new List();
            foreach (SearchBoxResult item in matches)
            {
                CatalogEntry i = new CatalogEntry
                {
                    idx = item.Idx,
                    product = item.Product,
                    size = (long)item.SizeBytes,
                    date = item.Date.ToString(),
                    type = item.Type,
                    classification = item.Classification,
                    distributor = item.Distributor,
                    egplDate = item.EgplDate.ToString(),
                    classificationVal = (int)item.ClassificationInt,
                    handling = item.Handling,
                    creator = item.Creator,
                    datum = item.Datum,
                    elevation = (int)item.ElevationFt,
                    description = item.Description,
                    dirLocation = item.DoLocation,
                    bbox = item.Bbox,
                    uniqID = item.UniqId
                };
                resultList.Add(i);
            }

            CatalogEntry[] results = resultList.ToArray();
            CatalogResults response = new CatalogResults();
            response.count = results.Length;
            response.results = results;
            return response;
        }


Code: Select all
    [DataContract]
    public class CatalogEntry
    {
        Int64 _idx;
        String _product;
        Int64 _size;
        String _date;
        String _type;
        String _classification;
        String _distributor;
        String _egplDate;
        Int32 _classificationVal;
        String _handling;
        String _creator;
        String _datum;
        Int32 _elevation;
        String _description;
        String _dirLocation;
        String _bbox;
        String _uniqID;

        [DataMember]
        public Int64 idx
        {
            get { return _idx; }
            set { _idx = value; }
        }


Etc. etc.


Just some backup documentation.

I managed to get a straight ADO connection working for purposes of my query. It is much less elegant, clear, or maintainable as compared to your Linq interface. Which is why I am interested in your product. But this might help in the figuring out what needs to be done:
Code: Select all
        public CatalogResults SearchBoxADO(string requestBox)
        {
            string poly = Tools.Box2Poly(requestBox);
            String sql = String.Format("SELECT * FROM portal.catalog cat WHERE st_geomfromtext('{0}'::text, 4326) && cat.location;", poly);
            Devart.Common.DbConnectionStringBuilder csb = new Devart.Common.DbConnectionStringBuilder();
            csb["Host"] = "localhost";
            csb["Port"] = 5432;
            csb["User Id"] = "postgres";
            csb["Password"] = "yechezkal";
            csb["Database"] = "GPL";
            csb["Max Pool Size"] = 150;
            csb["Connection Timeout"] = 30;
            PgSqlConnection conn = new PgSqlConnection(csb.ConnectionString);
            conn.Open();
            PgSqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            PgSqlDataReader reader = cmd.ExecuteReader();
            List resultList = new List();
            try
            {
                while (reader.Read())
                {
                    CatalogEntry it = new CatalogEntry
                        {
                            uniqID = reader.GetString(0),
                            size = reader.GetInt64(1),
                            date = reader.GetDateTime(2).ToString(),
                            type = reader.GetString(3),
                            classification = reader.GetString(4),
                            distributor = reader.GetString(5),
                            egplDate = reader.GetDateTime(6).ToString(),
                            classificationVal = reader.GetInt32(7),
                            handling = reader.GetString(8),
                            creator = reader.GetString(9),
                            description = reader.GetString(10),
                            datum = reader.GetString(11),
                            dirLocation = reader.GetString(12),
                            elevation = reader.GetInt32(13),
                            // location = reader.GetValue(14),
                            idx = reader.GetInt64(15),
                            bbox = reader.GetString(16),
                            product = reader.GetString(17)
                        };
                    resultList.Add(it);
                }
            }
            finally
            {
                reader.Close();
                conn.Close();
            };

            CatalogEntry[] results = resultList.ToArray();
            CatalogResults response = new CatalogResults();
            response.count = results.Length;
            response.results = results;
            return response;
        }
drysg
 
Posts: 28
Joined: Fri 09 Mar 2012 15:10


Return to dotConnect for PostgreSQL