I have several postgres functions, some return single values (string, ints) others return tables and others custom postgres types (like arrays).
I can use this.srv.Connection.NewCommand(“sample”,CommandType.StoredProcedure).ExecuteScalar() but not see the equivalent of use the ones defined in the schema. Conceptually, from the connection look more easy to figure (ExecuteScalar for simple, ExecuteReader for tables).
Hello.
The answer based on discussion: http://connect.remobjects.com/discussion/742/fail-to-add-command-of-postgres-function
2. How to retrieve data from the command defined at step 1
Here comes the second issue.
You will notice that Schema Command created has only one parameter.
This happens because NPGSQL.NET (PostgreSQL ADO.NET driver) itself doesn’t provide info about fuction RESULT or OUT parameters. So you need to manually define second parameter in Schema Command named ‘result’ with DataType datWideString and ParamType daptOutput.
Next step is to retrieve the Command call result from the client application:public String RandomString(Int32 length)
{
RemObjects.DataAbstract.Server.DataParameter[] results;
this.remoteCommand1.Execute("public.random_string",
new RemObjects.DataAbstract.Server.DataParameter[] { new RemObjects.DataAbstract.Server.DataParameter("length", length) },
out results);
return (String)results[0].Value;
}
where remoteCommand1 is defined asRemObjects.DataAbstract.RemoteCommand this.remoteCommand1 = new RemObjects.DataAbstract.RemoteCommand(this.components);
this.remoteCommand1.ExecuteCall.MethodName = “ExecuteCommandEx”;
this.remoteCommand1.ExecuteCall.Parameters.Clear();
this.remoteCommand1.ExecuteCall.Parameters.Add(“Result”, “Integer”, RemObjects.SDK.ParameterDirection.Result);
this.remoteCommand1.ExecuteCall.Parameters.Add(“aCommandName”, “Utf8String”, RemObjects.SDK.ParameterDirection.In);
this.remoteCommand1.ExecuteCall.Parameters.Add(“aInputParameters”, “DataParameterArray”, RemObjects.SDK.ParameterDirection.In);
this.remoteCommand1.ExecuteCall.Parameters.Add(“aOutputParameters”, “DataParameterArray”, RemObjects.SDK.ParameterDirection.Out);
this.remoteCommand1.ExecuteCall.OutgoingCommandNameParameter = “aCommandName”;
this.remoteCommand1.ExecuteCall.OutgoingParametersParameter = “aInputParameters”;
this.remoteCommand1.ExecuteCall.IncomingAffectedRowsParameter = “Result”;
this.remoteCommand1.ExecuteCall.IncomingParametersParameter = “aOutputParameters”;
this.remoteCommand1.RemoteService = this.remoteService;
Hope this helps.
Wow! that is a lot of boilerplate… I implement a lot of functions…
I report the bug to the NPGSQL.NET team at http://pgfoundry.org/tracker/?func=browse&group_id=1000140&atid=590.
I miss how in delphi is possible to chose between several drivers for each DB. I found a commercial one for .net (http://www.devart.com/dotconnect/postgresql) but no idea if will be better or not (also, requiere support for DA…).
I think I will exploit the fact that in postgres I can call all the functions with SELECT OR SELECT * FROM for ease of development…
Hello.
Devart drivers can be used with Data Abstract. To add new Devart PostgreSql database driver to the connection wizard of Schema Modeler, please do the next steps:
Change the Version in the XML code if you have not the latest Devart PostgreSql driver.
Start Schema Modeler.
Hope this helps.
I download the last version of devart, and connect sucesfully. Still the commands not create the result param,is like NPGSQL.net
Hello.
Can you send us simple function that shows the problem to investigate it in details?
Also please note that if you change .daConfig file only for Schema Modeler then your application will still get data using the default driver.
Thanks in advance.
I just simple add the command in the Schema modeler and inspect the parameter, still not call them from my app…
I use postgres 9 from the enterpriseDb website.
You can use this:
This one not have the return value
CREATE OR REPLACE FUNCTION random_string(length integer)
RETURNS TEXT
AS $$
DECLARE
chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
result text := '';
i integer := 0;
BEGIN
IF length < 0 THEN
raise exception 'Given length cannot be less than 0';
END IF;
FOR i IN 1..length LOOP
result := result || chars[1+random()*(array_length(chars, 1)-1)];
END LOOP;
RETURN result;
END;
$$
LANGUAGE plpgsql;
-- I try with the most simplistic function I have:
CREATE OR REPLACE FUNCTION public.db_version()
RETURNS INTEGER
AS $$
BEGIN
RETURN 1;
END;
$$
LANGUAGE plpgsql;
This one not show the input parameters, or the custom table return
-- Django compatible login.
CREATE OR REPLACE FUNCTION login_user (user_name text, pass text, client_version text)
RETURNS TABLE(isOk boolean, company_name TEXT, company_id INTEGER, server_version INTEGER)
AS $$
DECLARE
isOk bool=false;
company_name TEXT;
schema TEXT;
company_id INTEGER;
user_id INTEGER;
BEGIN
IF EXISTS (SELECT 1 FROM public.auth_user WHERE username=user_name) THEN
SELECT INTO isOk,company_name,company_id, schema, user_id
encode(libs.digest(split_part(password, '$', 2) || pass, 'sha1'), 'hex') =
split_part(password, '$', 3),
Company.name,
Company.id,
Company.username,
public.auth_user.id
FROM public.auth_user INNER JOIN public.Company ON public.auth_user.IdCompany = public.Company.Id
WHERE public.auth_user.username=user_name;
IF isOk THEN
UPDATE public.auth_user
SET
last_login= NOW()
WHERE user_id=user_id;
PERFORM public.set_schema(schema);
PERFORM public.login_device(user_id,client_version);
RETURN QUERY SELECT isOk, company_name, company_id, public.db_version();
END IF;
END IF;
IF not isOk THEN
RETURN QUERY SELECT False, null::TEXT, null::INTEGER, public.db_version();
END IF;
END;
$$
LANGUAGE 'plpgsql';
This is one with a special datatype , show the input but not the return
-- Soporte KEY => Value
CREATE EXTENSION hstore
SCHEMA libs;
CREATE OR REPLACE FUNCTION public.text_to_record(text_data TEXT)
RETURNS SETOF hstore
AS $$
DECLARE
line RECORD;
BEGIN
FOR line in SELECT regexp_split_to_table(text_data,E'\n') AS keys LOOP
RETURN NEXT line.keys::hstore;
END LOOP;
END
$$
LANGUAGE plpgsql;
Hello.
Thank you for the test functions. I have reproduced the problem. For the first, second and third functions there are two workarounds:
CREATE OR REPLACE FUNCTION random_string(
IN length integer,
OUT result TEXT)
AS $$
DECLARE
chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
i integer := 0;
BEGIN
result = '';
IF length < 0 THEN
raise exception 'Given length cannot be less than 0';
END IF;
FOR i IN 1..length LOOP
result := result || chars[1+random()*(array_length(chars, 1)-1)];
END LOOP;
END;
$$
LANGUAGE plpgsql;
In this case output parameter will be shown correctly in Schema Modeler.
Unfortunately these workarounds don’t work for the 4th and 5th functions due to specific features of PostgreSQL. In this case you need to change these functions to return plain types.
Hope this helps.
Ok, is working but only with the devart provider, not npgsql, but still fine for me.
I can use the login function with a regular SELECT * FROM login… so no problem. In postgres the functions can be called with SELECT and look like tables. Is possible to use this as dataset on RO?
Hello.
I can use the login function with a regular SELECT * FROM login… so no problem. In postgres the functions can be called with SELECT and look like tables. Is possible to use this as dataset on RO?
If you want to do this with login_user function described in previous message then it isn’t possible as Schema Modeler can’t recoginize result type TABLE correctly due to specific feature of PostgreSQL. But if you need to return plain type as dataset then please do the next steps:
Open Schema Modeler. Define dataset and command for custom function. Save the changes.
Open RODL file and create new operation with the name, f.e. GetSPData, that will return Binary result.
Add the next implementation of GetSPData method
public Binary GetSPData()
{
this.CheckConnection();
Binary result = new Binary();
using (IDbCommand lCommand = this.Connection.NewCommand("<command_name>", CommandType.StoredProcedure, String.Empty))
{
try
{
dataStreamer.InitializeStreamer(result, StreamerInitialization.Write);
SchemaDataTable tableSchema;
string tableName = "table_name";
tableSchema = ServiceSchema.DataTables[tableName];
using (IDataReader lReader = lCommand.ExecuteReader())
{
int maxRecords = -1;
ServiceDataStreamer.WriteDataReader(lReader, tableSchema, maxRecords, true);
}
}
finally
{
ServiceDataStreamer.FinalizeStreamer();
}
}
return result;
}
Build and run server side. Open client DataModule.cs file and choose option “Create Strongly Typed Dataset…” of RemoteDataAdapter to recreate .xsd file.
Change property DataRequestCall.MethodName to “GetSPData” value.
To get result of the command use RemoteDataAdapter Fill method as usually:
fDataModule.DataAdapter.Fill(newDataset1);
This solution works for NPGSQL.NET and Devart drivers.
Hope this helps.