Hi Otis,

I've got a problem inserting a row with a identity field in postgresql. I'm using VS 2005, .Net 2.0, LLBLGen Pro version 2.0.0.0, build version 2.0.0.60814 for LLBLGen Pro and 2.0.0.0 for the LLBLGen postgresql driver (all latest ones).

I've got this simple table defined in postgresql :

CREATE TABLE "fidesSchema"."ReportKey" ( "ReportID" int4 NOT NULL DEFAULT nextval('"fidesSchema"."ReportKey_ReportID_seq"'::regclass), CONSTRAINT "RaportKeyPK" PRIMARY KEY ("ReportID") ) WITHOUT OIDS;

Since the field ReportID is autoincremented an the designer didn't seem to notice that, I used the "Is identiy/ Sequence field" checkbox in the designer and selected the fidesSchema.ReportKey_ReportID_seq in the sequence combobox. So far, so good. I now try to insert a new row into the table using the adapter templates :

ReportkeyEntity reportKey = new ReportkeyEntity(); reportKey.IsDirty = true; base.SaveEntity(reportKey, true);

I used the IsDirty flag, since there's no field changed to force LLBLGen to produce the query.

Running that gives me an ORMQueryExecutionException (ERROR: 3F000: schema "fidesSchema" does not exist).

The query generated by LLBLGen is : INSERT INTO "fidesSchema"."ReportKey" ("ReportID") VALUES (nextval('fidesSchema.ReportKey_ReportID_seq'))

where it should be : INSERT INTO "fidesSchema"."ReportKey" ("ReportID") VALUES (nextval('"fidesSchema"."ReportKey_ReportID_seq"'))

note the added quotes.

Unless there's another way to specify the sequence by adding those quotes manually, it looks like a bug to me.

Is there a workaround ?

Franck

grabbing sequence information from postgresql is a challenge. The routine they added to get sequence names is very new and also buggy, so I had to roll my own to support 7.x servers.

You specify the sequence as: "ReportID" int4 NOT NULL DEFAULT nextval('"fidesSchema"."ReportKey_ReportID_seq"'::regclass), CONSTRAINT "RaportKeyPK" PRIMARY KEY ("ReportID")

but if you specify it as: "ReportID" int4 NOT NULL DEFAULT nextval('ReportKey_ReportID_seq'::regclass), CONSTRAINT "RaportKeyPK" PRIMARY KEY ("ReportID")

at least according to the pgAdmin III code autogenned by it. What I find a little odd is the double quotes INSIDE a single-quote string. Either use double quotes OR use single quotes but not both, at least that's how I understand how DB's interpret strings, but perhaps this is a postgresql specific quirck...

Hi Otis,

It looks like if you use you second syntax it's working in pgAdmin III indeed.

The trouble is the you have to create the sequence in the public schema and not (in that case) fidesSchema. If you do that, the designer doesn't allow you to set the right sequence. The choices displayed are only the sequences from the schema linked to the table (I create only in fidesSchema, so I get only the sequences from fidesSchema).

Another way of creating the sql, would be : insert into "fidesSchema"."ReportKey" ("ReportID") VALUES (default);

But this doesn't seem to be supported ni llblgen either (I didn't expect it anyway).

Now, for the double quotes inside the single quote string, I guess it's simply to refer the sequence : it's in the schema fidesSchema and the sequence name is ReportKey_ReportID_seq. That's the standard way of naming of postgresql when you've got schema (that isn't the 'public' default one).

I guess it wouldn't be hard to rename "fidesSchema.ReportKey_ReportID_seq" to "fidesSchema"."ReportKey_ReportID_seq" in the generated sql. The only problem is : where ? can I change it ?

Franck

I actually tried to change my schema to the default one and I still have the problem. the generated query is now : INSERT INTO "public"."ReportKey" ("Reportid") VALUES (nextval('public.ReportKey_ReportID_seq'))

instead of : INSERT INTO "public"."ReportKey" ("Reportid") VALUES (nextval('"public"."ReportKey_ReportID_seq"'))

so the public schema is still mentionned (I though it wouldn't since it's not needed) and it doesn't look like I can create autoincrement fields at all (I mean appart from have to select the max add one and use that value in the reportid field of the entity, which is a bit ugly).

Any chance it can get fixed ?

Franck

Something else ot notice is that the designer is able to figure out the field is a sequence field if the schema is the default one, but not in another schema, which is a bit strange ...

I need info to reproduce this, as I'm a bit lost where you created which sequence and in which schema and in which schema you created the table.

Sequences work fine, if I create a table with an autonumber field, so the sequence is created as well. The driver loads all sequences it can find and enlists them in the designer. What I think goes wrong is that it doesn't take into account case sensitive names in that last situation, as it doesn't store quotes in the sequence name, nor does it surround the name with quotes.

So please provide the info requested so I can setup a repro case here and check where I have to fix this: driver or DQE, or if I don't have to fix anything. I never had a problem with sequences in my tests on postgresql, though I do know that retrieving them is a huge pain because postgresql stores its meta-data in the most utterly retarded way you can possibly imagine (even more silly than mysql)

Franck wrote:

Something else ot notice is that the designer is able to figure out the field is a sequence field if the schema is the default one, but not in another schema, which is a bit strange ...

This is because of the way postgresql lets you determine if a field has a sequence defined. That's pretty cumbersome, and the routine added to postgresql in 8.0 I think, doesn't work properly.

The requirement of you to use double quotes is caused by the fact you use a case sensitive name and you stored the name with double quotes. When the name is created without double quotes, the name is case insensitive, and you don't need the double quotes.

Ok, so here is an example. The SQL code in postgresql is the following :

CREATE SCHEMA "testSchema" AUTHORIZATION fides;

CREATE SEQUENCE "testSchema"."testTable_testField_seq" INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1; ALTER TABLE "testSchema"."testTable_testField_seq" OWNER TO fides;

CREATE TABLE "testSchema"."testTable" ( "testField" int4 NOT NULL DEFAULT nextval('"testSchema"."testTable_testField_seq"'::regclass), CONSTRAINT "testPK" PRIMARY KEY ("testField") ) WITHOUT OIDS; ALTER TABLE "testSchema"."testTable" OWNER TO fides;

I then add this table to the list of entities of an LLBLGen project. Set the testField to a sequence field and select the testSchema.testTable_testField_seq value in the sequence combo box.

After generating I create a new console project in VS2005 referecing the LLBLGen code and having this main program :

using System; using System.Collections.Generic; using System.Text; using TestLLBLGenDatabase.EntityClasses; using TestLLBLGenDatabase.DatabaseSpecific;

namespace TestLLBLGen { class Program { static void Main(string[] args) { Console.WriteLine("Trying to create a new row."); TestTableEntity table = new TestTableEntity(); table.IsDirty = true; DataAccessAdapter adapter = new DataAccessAdapter(); if (adapter.SaveEntity(table)) { Console.WriteLine("New row created"); } else { Console.WriteLine("New row not created"); } Console.ReadLine(); } } }

And it fails with a exception.

Now after having read your reply, I tried to do the same without using case insensitive tables and I works fine; the row gets inserted. So it's really just a problem of quotes not placed where they show be.

Franck

Thanks Franck for the info. I'll try to reproduce it and fix the issue where appropriate (driver and/or DQE for postgresql)

No problem, let me know if you update something,

Cheers.

I can reproduce the issue. Looking into it now.

(edit) In the driver I use a regex to match the default sequence of a table with the sequences already seen: @"nextval('" + "\"?" + @"((?<schemaName>[\w]+).)?(?<sequenceName>[\w]+)" + "\"?'"

It doesn't take into account quotes around the '.', only at both sides of the sequence name. So that has to be added first in the driver.

(edit) Ok that fixes it. The table is now by default set to the correct sequence.

On to the quotes in the query!

(edit). The DQE didn't insert these. I now add the quotes to the sequence name parts: schema and sequence name.

I also saw the DQE had the wrong schema name overwrite settings read code, so I've fixed that as well (and documented it, it wasn't documented that postgresql also supported schema name overwriting).

Fixed in next build.

Ok, thanks. I'll test it when it's out

It works now. Thanks Otis.

Hi Otis,

today I got the same kind of error with the latest build 11012012 3.5.0.0 SD.LLBLGen.Pro.ORMSupportClasses.NET20.

The code generated is the following insert statement:

    Query: INSERT INTO "CustomerMgmt"."Customer" ("Id", "FirstName", "Name", "Street", "Street2", "Zipcode", "City", "Email", "Mobile", "IsRegistered", "ContactPerson", "Password", "Telephone") VALUES (nextval('CustomerMgmt.SEQ_CUSTOMER_ID'), :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, :p12, :p13)

An exception was caught during the execution of an action query: FEHLER: 3F000: Schema »customermgmt« does not exists. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

The Sequence is defined as REATE SEQUENCE "CustomerMgmt"."SEQ_CUSTOMER_ID" INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1; ALTER TABLE "CustomerMgmt"."SEQ_CUSTOMER_ID" OWNER TO postgres;

Reproducing this error is possible within pgadmin: select nextval('CustomerMgmt.SEQ_CUSTOMER_ID') -> Schema customermgmt does not exists select nextval('"CustomerMgmt"."SEQ_CUSTOMER_ID"') -> Correct value 1

The special handling with Schema and sequence in case of Upper/Lowercase schema names is documented at http://www.postgresql.org/docs/8.1/static/functions-sequence.html:

The sequence name can be schema-qualified if necessary:

nextval('myschema.foo') operates on myschema.foo nextval('"myschema".foo') same as above nextval('foo')

Hope you can reproduce and find a fix.

Thank you, Frank

Hi Frank,

Just to be sure, could you please post the code that triggers this? I'm not sure if that is the same of the code posted in your first posts. Are you using schema/catalog name overwrites?

Hi Daelmo,

thanks for your answer. Just to clearify, I'm a different Frank encountering the same kind of error, therefore I just reopened the thread. Hope, that alright.

Here is the code used to get the error: (The tables are empty, used llblgen model first approach). I'm not using catalog/name override, but the tables are grouped using Groupnames. Within postgres, the generated tables are separated into different schemas within the same db instance reflecting the groupnames.

           string connString = @"Server=localhost;Port=5432;Database=postgres;Userid=[User];Password=[password];Protocol=3; SSL=true;SslMode=Require; Pooling=true;MinPoolSize=1;MaxPoolSize=20;ConnectionLifeTime=15;";
            DataAccessAdapter adapter = new DataAccessAdapter(connString);
             adapter.StartTransaction(System.Data.IsolationLevel.ReadCommitted, "TR");
            try
            {
                CustomerEntity customer = new CustomerEntity(1);
                OrderEntity order = new OrderEntity(1);
                adapter.FetchEntity(customer);
                adapter.FetchEntity(order);

                // alter the entities
                customer.City = "Testcity";
                customer.ContactPerson = "User";
                customer.Email = "user@test.de";
                customer.FirstName = "UserFN";
                customer.IsRegistered = true;
                customer.Mobile = "+4923456789";
                customer.Name = "Username";
                customer.Password = "password";
                customer.Street = "Streetname 1";
                customer.Street2 = "Streetname2";
                customer.Telephone = "+49789789";
                customer.Zipcode = "12345";

                customer.Order.Add(order);
                order.OrderName = "Testorder";

                adapter.SaveEntity(customer);
                adapter.SaveEntity(order);

                // done
                adapter.Commit();
            }
            catch
            {
                adapter.Rollback();
                throw;
            }
            finally
            {
                adapter.Dispose();
            }

I do recall we fixed this some time ago, as I do recall having a bit of a problem fixing it. We'll look into why it still occurs in v3.5

FrankE: we always want people to start a new thread, so the original thread's info isn't dragged into the new problem's description. Please next time open a new thread, always.

v3.5 indeed doesn't wrap sequence and schema name within '"'. Looking into why this is.

(edit) fixed.

See attached DQE dll. It didn't override the proper CreateValidAlias() method, and in some situations it didn't wrap proper "" around the schema name. It now does.

Hi Otis,

thank you for your fast response and solution, works like a charm now.

And next time, I'll open a new thread

Frank