data types mappings
-
- Posts: 2
- Joined: Fri 25 Sep 2009 12:12
data types mappings
I would like to know if there is mappings summary table.
For ex.
pgSql .net
------------------
bool Boolean
thanks...
For ex.
pgSql .net
------------------
bool Boolean
thanks...
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]
[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]
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]
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]
Dear devart developers,
I get a type mapping problem when using smallint in a function argument.
My function is declared as follows:
My application code looks like this:
This generates a PgSqlException with the message "function sp_plt_dummy(integer) does not exist at character 15" and stack trace:
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.
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'
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();
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
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.
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.
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
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
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
Thanks a bunch!
The smallint issue is indeed fixed now. However I run into another one
My function definition:
The C# code:
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.
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'
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();
When I change the function parameter type of sp_plt_dummy2 to "timestamp with time zone" it works as expected.
-
- Posts: 3
- Joined: Sat 14 Apr 2012 15:21
- Contact:
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.
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.