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;
}