proper way to instantiate command parameter mapped to Date on server

[tr888]

I am trying to troubleshoot possible causes for empty DataReader (field-names but zero rows) when executing a function that returns TABLE, reported here:

http://forums.devart.com/viewtopic.php?f=3&t=25754

I know the function is working because it works with npgsql provider.

One possibility is that the Date value is being passed to the server incorrectly. If the function on the server wants a Date (not a Timestamp) what is the proper way to create the command parameter and assign its value using Devart dotConnect? Are there any localization issues involved?

Do I simply assign a string?

PgSqlCommand.Parameters["mydate"].PgSqlValue = "2013-01-28";

Or cast the string to a custom DevArt type not recognized in .NET CLR?

//hypothetically:
PgSqlCommand.Parameters["mydate"].PgSqlValue = (PgSqlDataTypes.Date) "2013-01-28";

Or assign a DateTime value?

PgSqlCommand.Parameters["mydate"].PgSqlValue = DateTime.Parse("2013-01-28 00:00:00");

Thanks

[Pinturiccio]

If you don't specify the PgSqlType property for a parameter, dotConnect for PostgreSQL tries to set this property itself. PgSqlType is defined on the basis of the Value property value and its data type. As 3 fields from the PgSqlType correspond to the System.DateTime data type: PgSqlType.Date, PgSqlType.TimeStamp, PgSqlType.TimeStampTZ, one of them, which is PgSqlType.TimeStamp, is selected automatically. To avoid conflicts, specify the PgSqlType property explicitly.

As we don't know the structure of your function, we've created a small example and we'll show you on its basis how to use dotConnect for PostgreSQL:
DDL\DML scripts:

Code: Select all

CREATE TABLE dater
(
  id serial NOT NULL,
  val date,
  CONSTRAINT dater_pkey PRIMARY KEY (id)
);

INSERT INTO dater(val) VALUES ('2012-12-21');
INSERT INTO dater(val) VALUES ('2013-01-13');
INSERT INTO dater(val) VALUES ('2005-01-01');

CREATE OR REPLACE FUNCTION foo(a date)
RETURNS TABLE(b int, c date) AS $$
BEGIN
  RETURN QUERY SELECT d.id, d.val FROM dater d where val>a;
  
END;
$$ LANGUAGE plpgsql;

C# code:

Code: Select all

PgSqlConnection conn = new PgSqlConnection("host=db;port=5440;uid=postgres;pwd=postgres;unicode=true;");
conn.Open();
PgSqlCommand comm = new PgSqlCommand("foo", conn);
comm.CommandType = CommandType.StoredProcedure;

PgSqlParameter param = new PgSqlParameter();
param.ParameterName = "val";
param.PgSqlType = PgSqlType.Date;
param.Value = DateTime.Parse("2008-12-21 00:00:00");
comm.Parameters.Add(param);
            
PgSqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
	for (int i = 0; i < reader.FieldCount; i++)
                    Console.Write(reader.GetValue(i) + "\t");
        Console.WriteLine();
}
conn.Close();

If this example doesn't help, please send us your test sample with the corresponding DDL/DML scripts.