data types mappings

[Vojtech Babic]

I would like to know if there is mappings summary table.

For ex.
pgSql .net
------------------
bool Boolean

thanks...

[Shalex]

PostgreSQL to .NET types mapping table:
[PostgreSQL:BigInt] --> [.NET:long]
[PostgreSQL:Bit, VarBit] --> [.NET:BitArray]
[PostgreSQL:Uuid] --> [.NET:Guid]
[PostgreSQL:Char, VarChar, Text, Cldr, Inet, MacAddr] --> [.NET:String]
[PostgreSQL:Boolean] --> [.NET:bool]
[PostgreSQL:ByteA] --> [.NET:byte[]]
[PostgreSQL:Oid] --> [.NET:int]
[PostgreSQL:Int2Vector] --> [.NET:Int16[]]
[PostgreSQL:OidVector, OidVector801, oidVector] --> [.NET:int[]]
[PostgreSQL:Date, TimeStamp, TimeStampTZ] --> [.NET:DateTime]
[PostgreSQL:Interval, Interval12] --> [.NET:TimeSpan]
[PostgreSQL:Real(MONO), Money, Double] --> [.NET:double]
[PostgreSQL:Int] --> [.NET:int]
[PostgreSQL:Time, TimeTZ] --> [.NET:TimeSpan]
[PostgreSQL:Real(not MONO)] --> [.NET:float]
[PostgreSQL:Numeric] --> [.NET:Decimal]
[PostgreSQL:SmallInt] --> [.NET:short]
[PostgreSQL:Row] --> [.NET:object]

.NET to PostgreSQL types mapping table:
[.NET:String] --> [PostgreSQL:VarChar]
[.NET:Int64, UInt32] --> [PostgreSQL:BigInt]
[.NET:Boolean] --> [PostgreSQL:Boolean]
[.NET:Double] --> [PostgreSQL:Double]
[.NET:Int32, UInt16] --> [PostgreSQL:Int]
[.NET:DateTime] --> [PostgreSQL:TimeStamp]
[.NET:Single] --> [PostgreSQL:Real]
[.NET:Decimal, UInt64] --> [PostgreSQL:Numeric]
[.NET:Int16, Byte, SByte] --> [PostgreSQL:SmallInt]
[.NET:byte[], PgSqlBlob, char[]] --> [PostgreSQL:ByteA]
[.NET:TimeSpan] --> [PostgreSQL:TimeTZ]
[.NET:PgSqlBox] --> [PostgreSQL:Box]
[.NET:PgSqlCircle] --> [PostgreSQL:Circle]
[.NET:PgSQlLSeg] --> [PostgreSQL:LSeg]
[.NET:PgSqlCursor] --> [PostgreSQL:VarChar]
[.NET:PgSqlPath] --> [PostgreSQL:Path]
[.NET:PgSqlPoint] --> [PostgreSQL:Point]
[.NET:PgSqlPolygon] --> [PostgreSQL:Polygon]
[.NET:PgSqlInterval] --> [PostgreSQL:Interval]
[.NET:PgSqlTimeStamp] --> [PostgreSQL:TimeStamp]
[.NET:PgSqlText] --> [PostgreSQL:VarChar]
[.NET:BitArray] --> [PostgreSQL:VarBit]
[.NET:Guid] --> [PostgreSQL:Uuid]
[.NET:PgSqlRow] --> [PostgreSQL:Row]
[.NET:PgSqlLargeObject] --> [PostgreSQL:LargeObject]

[Vojtech Babic]

Great
Thanks

[fredbest]

I have a Date type in Postgresql function parameter.
how do I call the function from .net ???
DateTime ???


[quote="Shalex"][b]PostgreSQL to .NET types mapping table[/b]:
[PostgreSQL:BigInt] --> [.NET:long]
[PostgreSQL:Bit, VarBit] --> [.NET:BitArray]
[PostgreSQL:Uuid] --> [.NET:Guid]
[PostgreSQL:Char, VarChar, Text, Cldr, Inet, MacAddr] --> [.NET:String]
[PostgreSQL:Boolean] --> [.NET:bool]
[PostgreSQL:ByteA] --> [.NET:byte[]]
[PostgreSQL:Oid] --> [.NET:int]
[PostgreSQL:Int2Vector] --> [.NET:Int16[]]
[PostgreSQL:OidVector, OidVector801, oidVector] --> [.NET:int[]]
[PostgreSQL:Date, TimeStamp, TimeStampTZ] --> [.NET:DateTime]
[PostgreSQL:Interval, Interval12] --> [.NET:TimeSpan]
[PostgreSQL:Real(MONO), Money, Double] --> [.NET:double]
[PostgreSQL:Int] --> [.NET:int]
[PostgreSQL:Time, TimeTZ] --> [.NET:TimeSpan]
[PostgreSQL:Real(not MONO)] --> [.NET:float]
[PostgreSQL:Numeric] --> [.NET:Decimal]
[PostgreSQL:SmallInt] --> [.NET:short]
[PostgreSQL:Row] --> [.NET:object]

[b].NET to PostgreSQL types mapping table[/b]:
[.NET:String] --> [PostgreSQL:VarChar]
[.NET:Int64, UInt32] --> [PostgreSQL:BigInt]
[.NET:Boolean] --> [PostgreSQL:Boolean]
[.NET:Double] --> [PostgreSQL:Double]
[.NET:Int32, UInt16] --> [PostgreSQL:Int]
[.NET:DateTime] --> [PostgreSQL:TimeStamp]
[.NET:Single] --> [PostgreSQL:Real]
[.NET:Decimal, UInt64] --> [PostgreSQL:Numeric]
[.NET:Int16, Byte, SByte] --> [PostgreSQL:SmallInt]
[.NET:byte[], PgSqlBlob, char[]] --> [PostgreSQL:ByteA]
[.NET:TimeSpan] --> [PostgreSQL:TimeTZ]
[.NET:PgSqlBox] --> [PostgreSQL:Box]
[.NET:PgSqlCircle] --> [PostgreSQL:Circle]
[.NET:PgSQlLSeg] --> [PostgreSQL:LSeg]
[.NET:PgSqlCursor] --> [PostgreSQL:VarChar]
[.NET:PgSqlPath] --> [PostgreSQL:Path]
[.NET:PgSqlPoint] --> [PostgreSQL:Point]
[.NET:PgSqlPolygon] --> [PostgreSQL:Polygon]
[.NET:PgSqlInterval] --> [PostgreSQL:Interval]
[.NET:PgSqlTimeStamp] --> [PostgreSQL:TimeStamp]
[.NET:PgSqlText] --> [PostgreSQL:VarChar]
[.NET:BitArray] --> [PostgreSQL:VarBit]
[.NET:Guid] --> [PostgreSQL:Uuid]
[.NET:PgSqlRow] --> [PostgreSQL:Row]
[.NET:PgSqlLargeObject] --> [PostgreSQL:LargeObject][/quote]

[AndreyR]

Yes, you should use the DateTime .NET type:

[PostgreSQL:Date, TimeStamp, TimeStampTZ] --> [.NET:DateTime]

[bozr]

Dear devart developers,

I get a type mapping problem when using smallint in a function argument.
My function is declared as follows:

Code: Select all

CREATE OR REPLACE FUNCTION sp_plt_dummy("@StatusId" smallint DEFAULT 0)
  RETURNS void AS $$
BEGIN
END;
$$ LANGUAGE 'plpgsql'


My application code looks like this:

Code: Select all

DbProviderFactory factory = DbProviderFactories.GetFactory("Devart.Data.PostgreSql");
DbConnection connection = ProviderFactory.CreateConnection();
connection.ConnectionString = "Host=localhost;Database=G3;User Id=me;Password=secret;Protocol=2;";
connection.Open();

...

DbCommand dummy = connection.CreateCommand();
dummy.CommandText = "sp_PLT_Dummy";
dummy.CommandType = CommandType.StoredProcedure;
DbParameter parameter = dummy.CreateParameter();
parameter.ParameterName = "@StatusId";
parameter.DbType = DbType.Int16;
dummy.Parameters.Add(parameter);
dummy.Prepare();

...

dummy.Parameters["@StatusId"].Value = 1;
dummy.ExecuteNonQuery();


This generates a PgSqlException with the message "function sp_plt_dummy(integer) does not exist at character 15" and stack trace:

Code: Select all

   at Devart.Data.PostgreSql.PgSqlDataReader.e(Int32 A_0)
   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.ExecuteReader()
   at Devart.Common.DbCommandBase.ExecuteNonQuery()
   at DbTest.PltTest.ProcessLot() in C:\Repositories\G3_DbTest\DbTest\Tests\PltTest.cs:line 300


It seems that DbType.Int16 cannot be mapped to a smallint function argument?
Any help is greatly appreciated.

P.S. I currently use the latest free edition (4.95.140.0) and EnterprizeDB's postgresql 8.4.4

[edit] If I change the postgresql function's parameter type to "int" it works flawlessly.

[Shalex]

Thank you for the bug report. We have reproduced it. I will notify you when the issue with the smallint function parameters is solved.

[Shalex]

We have fixed the problem. Look forward to the next build of dotConnect for PostgreSQL. I will post here when it is available for download. This issue is associated with protocol 2.0. As a workaround, you can change the "Protocol=2;" entry of your connection string to "Protocol=3;" or put "select sp_PLT_Dummy(1::smallint)" to your PgSqlCommand.CommandText.

[StanislavK]

We've released the 4.95.152 build with the fix. The new build can be downloaded from
http://www.devart.com/dotconnect/postgresql/download.html
(the trial version) or from Registered Users' Area (provided that you have an active subscription):
http://secure.devart.com/

For more information on improvements and fixes available in the 4.95.152 version, please refer to
http://www.devart.com/forums/viewtopic.php?t=18591

[bozr]

Thanks a bunch!

The smallint issue is indeed fixed now. However I run into another one
My function definition:

Code: Select all

CREATE OR REPLACE FUNCTION sp_plt_dummy2("@Timestamp" timestamp)
  RETURNS void AS $$
BEGIN
END;
$$ LANGUAGE 'plpgsql'


The C# code:

Code: Select all

DbProviderFactory factory = DbProviderFactories.GetFactory("Devart.Data.PostgreSql");
DbConnection connection = ProviderFactory.CreateConnection();
connection.ConnectionString = "Host=localhost;Database=G3;User Id=me;Password=secret;Protocol=2;Unicode=true";
connection.Open();

...

DbCommand dummy2 = connection.CreateCommand();
dummy2.CommandText = "sp_plt_dummy2";
dummy2.CommandType = CommandType.StoredProcedure;
DbParameter parameter = dummy2.CreateParameter();
parameter.ParameterName = "@Timestamp";
parameter.DbType = DbType.DateTime;
dummy2.Parameters.Add(parameter);
dummy2.Prepare();

...

dummy2.Parameters["@Timestamp"].Value = DateTime.Now;
dummy2.ExecuteNonQuery();


dotConnect complains that it cannot find the function "sp_plt_dummy2".
When I change the function parameter type of sp_plt_dummy2 to "timestamp with time zone" it works as expected.

[Shalex]

Thank you for your report. We are investigating the issue. It seems like it persists only with protocol 2.0. As a workaround please use the Protocol=3; connection string parameter.

[Shalex]

The problem is fixed. I will post here when the corresponding build is available for download.

[mvccms-jon]

Is double really the right choice for money?

http://www.postgresql.org/docs/9.1/stat ... money.html

Values of the numeric, int, and bigint data types can be cast to money. Conversion from the real and double precision data types can be done by casting to numeric first, for example:

SELECT '12.34'::float8::numeric::money;
However, this is not recommended. Floating point numbers should not be used to handle money due to the potential for rounding errors.

I know MSSQL maps money to decimal and this seems to make more sense to me.

[Shalex]

We will investigate the possibility to map Money (PostgreSQL) -> Decimal (.NET) and post here about the results.

[Shalex]

The behaviour will be changed starting from the next public build of dotConnect for PostgreSQL: the Money PostgreSQL data type will be mapped to System.Decimal. We will post here when it is available for download.