pgsql function parameter order

[g1mp]

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 

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]

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?

[Pinturiccio]

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.

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.

[g1mp]

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.

[Pinturiccio]

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.

[Pinturiccio]

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]

New build of dotConnect for PostgreSQL 7.3.171 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/postgr ... nload.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]

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.