Support for GreenPlum

Support for GreenPlum

Postby craigfis » Fri 25 Jan 2013 22:28

I'm trying to use dotConnect for PostgreSQL to connect to EMC's GreenPlum implementation, which has some limitations.
For example it doesn't support the "RETURNING value" clause on an INSERT statement, which is commonly used to return the new primary key value on a automatically sequentially numbered record.

Calling the .AddObject method on an entity that has such a primary key defined gives me an error: "The RETURNING clause of the INSERT statement is not supported in this version of Greenplum Database."
Presumably dotConnect could generate SQL that didn't rely on the RETURNING clause to get .AddObjeect to work here.
craigfis
 
Posts: 2
Joined: Fri 25 Jan 2013 22:17

Re: Support for GreenPlum

Postby Shalex » Mon 28 Jan 2013 08:34

Please specify the ORM framework (e.g.: LinqConnect/Entity Framework/NHibernate) you are working with.
Shalex
Devart Team
 
Posts: 7781
Joined: Thu 14 Aug 2008 12:44

Re: Support for GreenPlum

Postby craigfis » Mon 28 Jan 2013 14:55

I'm using Entity Framework.
craigfis
 
Posts: 2
Joined: Fri 25 Jan 2013 22:17

Re: Support for GreenPlum

Postby Shalex » Fri 01 Feb 2013 08:34

Assuming that you are working with Entity Developer (*.edml), please try the following workaround:
1. Create a table in your database:
Code: Select all
CREATE TABLE autoinc_table
(
  id serial NOT NULL,
  mydata character varying,
  CONSTRAINT autoinc_table_pkey PRIMARY KEY (id)
);

2. Drag&drop the table on the model designer.
3. Right click on the Stored Procedures node in Store part of the model > Add > New Command Text:
Stored Procedure Name=insert_sp
SQL Script=insert into autoinc_table(mydata) values (:p0); select max(id) from autoinc_table;
Parameter's data type:
p0=varchar, direction=In
4. Right click on the class in CSDL > Stored Procedure Mapping, select Insert in the Commands list, and set:
a) Storage Procedure=insert_sp(p0 IN varchar)
b) Result Column Bindings:
Column=max, Property=Id:Int32
5. Execute the following code and make sure that id is returned from database:
Code: Select all
Devart.Data.PostgreSql.Entity.PgSqlEntityProviderServices.UnpreparedCommandExecution = true;
            using (PostgreEntities context = new PostgreEntities()) {
                AutoincTable t = new AutoincTable() { Mydata = "a" };
                context.AutoincTables.AddObject(t);
                context.SaveChanges();
                //check t.id
            }

The reason of using unprepared execution is described at
http://www.devart.com/dotconnect/postgresql/docs/?Devart.Data.PostgreSql~Devart.Data.PostgreSql.PgSqlCommand~UnpreparedExecute.html.
Shalex
Devart Team
 
Posts: 7781
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL