Page 1 of 2
data types mappings
Posted: Fri 25 Sep 2009 12:19
by Vojtech Babic
I would like to know if there is mappings summary table.
For ex.
pgSql .net
------------------
bool Boolean
thanks...
Posted: Mon 28 Sep 2009 16:20
by 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]
Posted: Wed 30 Sep 2009 11:22
by Vojtech Babic
Great
Thanks
Posted: Wed 21 Apr 2010 01:45
by 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]
Posted: Wed 21 Apr 2010 12:37
by AndreyR
Yes, you should use the DateTime .NET type:
[PostgreSQL:Date, TimeStamp, TimeStampTZ] --> [.NET:DateTime]
Posted: Mon 21 Jun 2010 13:05
by 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.
Posted: Tue 22 Jun 2010 13:56
by 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.
Posted: Wed 23 Jun 2010 15:34
by 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.
Posted: Thu 29 Jul 2010 13:17
by StanislavK
We've released the 4.95.152 build with the fix. The new build can be downloaded from
http://www.devart.com/dotconnect/postgr ... nload.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
Posted: Thu 29 Jul 2010 14:58
by 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.
Posted: Tue 03 Aug 2010 11:25
by 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.
Posted: Wed 04 Aug 2010 14:08
by Shalex
The problem is fixed. I will post here when the corresponding build is available for download.
Posted: Sat 14 Apr 2012 15:26
by 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.
Posted: Wed 18 Apr 2012 11:37
by Shalex
We will investigate the possibility to map Money (PostgreSQL) -> Decimal (.NET) and post here about the results.
Posted: Tue 24 Apr 2012 09:40
by 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.