data types mappings

data types mappings

Postby Vojtech Babic » Fri 25 Sep 2009 12:19

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

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

thanks...
Vojtech Babic
 
Posts: 2
Joined: Fri 25 Sep 2009 12:12

Postby Shalex » Mon 28 Sep 2009 16:20

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]
Shalex
Devart Team
 
Posts: 5569
Joined: Thu 14 Aug 2008 12:44

Postby Vojtech Babic » Wed 30 Sep 2009 11:22

Great :D
Thanks
Vojtech Babic
 
Posts: 2
Joined: Fri 25 Sep 2009 12:12

Postby fredbest » Wed 21 Apr 2010 01:45

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]
fredbest
 
Posts: 4
Joined: Thu 15 Apr 2010 02:19

Postby AndreyR » Wed 21 Apr 2010 12:37

Yes, you should use the DateTime .NET type:
[PostgreSQL:Date, TimeStamp, TimeStampTZ] --> [.NET:DateTime]
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby bozr » Mon 21 Jun 2010 13:05

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.
bozr
 
Posts: 17
Joined: Mon 21 Jun 2010 12:39

Postby Shalex » Tue 22 Jun 2010 13:56

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
Devart Team
 
Posts: 5569
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Wed 23 Jun 2010 15:34

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.
Shalex
Devart Team
 
Posts: 5569
Joined: Thu 14 Aug 2008 12:44

Postby StanislavK » Thu 29 Jul 2010 13:17

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
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby bozr » Thu 29 Jul 2010 14:58

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.
bozr
 
Posts: 17
Joined: Mon 21 Jun 2010 12:39

Postby Shalex » Tue 03 Aug 2010 11:25

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
Devart Team
 
Posts: 5569
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Wed 04 Aug 2010 14:08

The problem is fixed. I will post here when the corresponding build is available for download.
Shalex
Devart Team
 
Posts: 5569
Joined: Thu 14 Aug 2008 12:44

Postby mvccms-jon » Sat 14 Apr 2012 15:26

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.
mvccms-jon
 
Posts: 3
Joined: Sat 14 Apr 2012 15:21

Postby Shalex » Wed 18 Apr 2012 11:37

We will investigate the possibility to map Money (PostgreSQL) -> Decimal (.NET) and post here about the results.
Shalex
Devart Team
 
Posts: 5569
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Tue 24 Apr 2012 09:40

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.
Shalex
Devart Team
 
Posts: 5569
Joined: Thu 14 Aug 2008 12:44

Next

Return to dotConnect for PostgreSQL