pgsql function parameter order

pgsql function parameter order

Postby g1mp » Fri 16 May 2014 14:12

Dear,

I'm given a task to change the database in an existing web application from mssql to pgsql.
Everything is used with strongly typed datatable (using stored procedures/functions).

The first problem is, that the generated function parameter's are in alphabetical order (not the same as the parameter order in databas):

Code: Select all
CREATE OR REPLACE FUNCTION telep_telephely_paged_select(p_username character varying, p_varosnev character varying, p_nev character varying, p_nyilvszam integer, p_cim character varying, p_tev_id integer, p_stat_id integer, p_tipus_id integer, p_sortexpression character varying DEFAULT 'nyilvszam desc'::character varying, p_pagingstartrow integer DEFAULT 0, p_pagingrowcount integer DEFAULT 10)
  RETURNS SETOF telep_telephely_paged_select_resulttype AS
$BODY$
DECLARE

Image

If everything is left that way it does not work. I can arrange the order of the parameters by hand and everything works fine, but I have to do it every time I reconfigure the query.

Are there any way to skip this manual step?


The second problem is, I didn't find a way that pgsql would allow me to use something like the '@' in mssql for declaring a variable, so I can't differ the incoming parameter from a table column name.
Because of this I introduced 'p_' prefix for incoming parameters as seen in the code prior.

But these datatables are used in detail views and are bound to different objects (existing code that I don't really want to touch).
Because of this 'p_' prefix the insert statement variables does not match the returned table column names.
I found a way in Parameters Collection Editor to overwrite
Column name: p_columnName
ParameterName: columnName

With this the existing code works perfectly, but this is also a manual step that has to be redone every time something changes.
Normally the visual editor generates the following values:
ColumnName: (empty)
ParameterName: p_columnName


Thanks for any help.

Best regards.
g1mp
 
Posts: 3
Joined: Fri 16 May 2014 13:13

Re: pgsql function parameter order

Postby g1mp » Tue 20 May 2014 14:38

So has anyone any idea on the problem with parameter order, the other one is not that important.
Or at least can someone reproduce this behavior?
g1mp
 
Posts: 3
Joined: Fri 16 May 2014 13:13

Re: pgsql function parameter order

Postby Pinturiccio » Wed 21 May 2014 14:09

g1mp wrote:The first problem is, that the generated function parameter's are in alphabetical order (not the same as the parameter order in databas)

We could not reproduce the issue. In our environment the parameters of telep_telephely_paged_selectTableAdapter are listed in the order, specified in the function definition, not in the alphabetical order. Please provide us the following information for reproducing the issue:
1. dotConnect for PostgreSQL version;
2. PostgreSQL server version;
3. The DDL script for creating the telep_telephely_paged_select functions and database objects necessary for this function, for example, the telep_telephely_paged_select_resulttype type.

g1mp wrote:Normally the visual editor generates the following values:
ColumnName: (empty)
ParameterName: p_columnName

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1978
Joined: Wed 02 Nov 2011 09:44

Re: pgsql function parameter order

Postby g1mp » Wed 21 May 2014 16:26

1. dotConnect for PostgreSQL
7.3.146.0
2. PostgreSQL server version
"PostgreSQL 9.3.2, compiled by Visual C++ build 1600, 64-bit"
3.The DDL script
Code: Select all
CREATE TYPE public.test_resulttype AS
   (thely_id integer,
    nev character varying,
    irsz smallint,
    var_nev character varying,
    kozter_nev character varying,
    kozjell_nev character varying,
    cim_hazsz character varying,
    cim_kieg character varying,
    nyilvszam character varying,
    stat_nev character varying,
    tipus_nev character varying,
    uzem_id integer);
ALTER TYPE public.test_resulttype
  OWNER TO postgres;

CREATE OR REPLACE FUNCTION public.test(p_2 character varying, p_0 character varying, p_1 character varying, p_4 integer, p_5 character varying, p_3 integer, p_6 integer DEFAULT 0, p_7 integer DEFAULT 10)
  RETURNS SETOF test_resulttype AS
$BODY$
BEGIN
   RETURN QUERY SELECT
      1::integer,
      'nev'::varchar(200),
      2::smallint,
      'var_nev'::varchar(200),
      'kozter_nev'::varchar(200),
      'kozjell_nev'::varchar(200),
      'com_hazsz'::varchar(200),
      'cim_kieg'::varchar(200),
      'nyilvszam'::varchar(200),
      'stat_nev'::varchar(200),
      'tipus_nev'::varchar(200),
      3::integer;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION public.test(character varying, character varying, character varying, integer, character varying, integer, integer, integer)
  OWNER TO postgres;


I've added a DataSet at solution explorer.
I've used visual designer:
1. Add-->TableAdapter
2. Select Connection String
3. Use existing stored procedure
4. Select--> test(public)
5. Unselect Fill a DataTable
6. Select Retrun a DataTable
7. Finish

The return types are all ok (correct order and types)

But the GetData methode is generated incorrectly
DataSet1.Designer.cs

Code: Select all
public virtual DataSet1.testDataTable GetData(string p_0, string p_1, string p_2, global::System.Nullable<int> p_3, global::System.Nullable<int> p_4, string p_5, global::System.Nullable<int> p_6, global::System.Nullable<int> p_7) {
            this.Adapter.SelectCommand = this.CommandCollection[0];
            if ((p_0 == null)) {
                this.Adapter.SelectCommand.Parameters[0].Value = global::System.DBNull.Value;
...


As you can see, the GetData methode has the parameters in alphabetical order.

If I leave everything as is and try to call GetData:

Code: Select all
using PostGresParameterOrder.DataSet1TableAdapters;
namespace PostGresParameterOrder
{
    class Program
    {
        static void Main(string[] args)
        {
            DataSet1TableAdapters.testTableAdapter adapter = new testTableAdapter();
            var ret = adapter.GetData("", "", "", 3, 4, "", 5, 6);
        }
    }
}


I get the following exception:
Code: Select all
System.FormatException was unhandled
  HResult=-2146233033
  Message=Input string was not in a correct format.
  Source=mscorlib
  StackTrace:
       at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
       at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
       at System.String.System.IConvertible.ToInt32(IFormatProvider provider)
       at System.Convert.ToInt32(Object value)
       at Devart.Data.PostgreSql.PgSqlUtils.ParamValueToBytes(String version, Encoding encoding, PgSqlType pgSqlType, Object val, Int32 typeOid, Boolean integerDateTime)
       at Devart.Data.PostgreSql.PgSqlCommand.a(String A_0, Encoding A_1, ArrayList A_2, Boolean A_3)
       at Devart.Data.PostgreSql.PgSqlCommand.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords)
       at Devart.Common.DbCommandBase.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
       at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
       at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
       at PostGresParameterOrder.DataSet1TableAdapters.testTableAdapter.GetData(String p_0, String p_1, String p_2, Nullable`1 p_3, Nullable`1 p_4, String p_5, Nullable`1 p_6, Nullable`1 p_7) in c:\users\gimp\documents\visual studio 2010\Projects\PostGresParameterOrder\PostGresParameterOrder\DataSet1.Designer.cs:line 1345
       at PostGresParameterOrder.Program.Main(String[] args) in c:\users\gimp\documents\visual studio 2010\Projects\PostGresParameterOrder\PostGresParameterOrder\Program.cs:line 14
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:


If I use "Parameters Collection Editor" and arrange the parameters everthing works as expected.
g1mp
 
Posts: 3
Joined: Fri 16 May 2014 13:13

Re: pgsql function parameter order

Postby Pinturiccio » Thu 22 May 2014 11:24

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1978
Joined: Wed 02 Nov 2011 09:44

Re: pgsql function parameter order

Postby Pinturiccio » Wed 28 May 2014 13:10

We have fixed the bug with parameters order for functions when creating DataSet. We will post here when the corresponding build of dotConnect for PostgreSQL is available for download.
Pinturiccio
Devart Team
 
Posts: 1978
Joined: Wed 02 Nov 2011 09:44

Re: pgsql function parameter order

Postby Pinturiccio » Thu 29 May 2014 14:24

New build of dotConnect for PostgreSQL 7.3.171 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/postgresql/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=29685
Pinturiccio
Devart Team
 
Posts: 1978
Joined: Wed 02 Nov 2011 09:44

Re: pgsql function parameter order

Postby Pinturiccio » Tue 10 Jun 2014 14:49

g1mp wrote:Normally the visual editor generates the following values:
ColumnName: (empty)
ParameterName: p_columnName

Our investigation shows that we cannot affect generation of variable prefixes and their binding to columns by MSDataSetGenerator.
Pinturiccio
Devart Team
 
Posts: 1978
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL