Can't save objects to a PostgreSQL Database

BIZ TMS Aurelius
1

Hello,


I'm trying to save my first object to a postgresql DB and I'm getting an error.
(I'm using firedac)
Here is my entity class : 


  [Entity]
  [Automapping]
  TDBState = class
  Private
    FId : Integer;
    FName : String;
    FShortName : String;
    FIsProductive : Boolean;
    FIsUsed : Boolean;
    FIsTerminative : Boolean;
    FDisplayOrder : Integer;
    FIntColor : Integer;


    [Transient]
    FColor : TColor;


    [Association]
    FParentState : TDBState;
  Published
    Property id : Integer read FId write Fid;
    property intColor: Integer read FIntColor write FIntColor;
    Property name : String read FName write FName;
    Property shortName : String read FShortName write FShortName;
    Property isProductive : Boolean read FIsProductive write FIsProductive;
    Property isUsed : Boolean read FIsUsed write FIsUsed;
    Property isTerminative : Boolean read FIsTerminative write FIsTerminative;
    Property displayOrder : Integer read FDisplayOrder write FDisplayOrder;
    Property parent : TDBState read FParentState write FParentState;
  end;

The whole process of creating the table is ok apparently.

Here the code i'm using for saving an object : 


  objMan := TObjectManager.Create(StatesDB.ORMDBConnection);


  tmpDBState1 := TDBState.Create;
  tmpDBState1.name := 'Production';
  tmpDBState1.shortName := 'Prod';
  tmpDBState1.isProductive := TRUE;
  tmpDBState1.isUsed := TRUE;
  tmpDBState1.isTerminative := FALSE;
  tmpDBState1.displayOrder := 1;


  objMan.Save(tmpDBState1);

I'm getting the following err in PostgreSQL logs : 



2014-09-22 11:25:57 CEST LOG:  execute 45STM: DECLARE "44STM" CURSOR WITH HOLD FOR SELECT NEXTVAL('SEQ_DBSTATE');
	
2014-09-22 11:25:57 CEST LOG:  execute 46STM: FETCH FORWARD 50 FROM "44STM"
2014-09-22 11:25:57 CEST LOG:  execute 46STM: FETCH FORWARD 50 FROM "44STM"
2014-09-22 11:25:57 CEST LOG:  statement: CLOSE "44STM"
2014-09-22 11:25:57 CEST ERROR:  syntax error at or near ";" at character 54
2014-09-22 11:25:57 CEST STATEMENT:  SELECT COUNT(*) FROM (
	SELECT NEXTVAL('SEQ_DBSTATE');
	
	) A
2

Just an additional piece of information since it appears to come from the sequence increment mechanism :

The "current value" of the sequence DOES get incremented. So if i keep running my code the "current value" goes 11, 12, 13 etc..
3

I don't know what's going on. Some tool is wrapping the sql generated by Aureilus with a "Select count(*) From". This is not generated by Aurelius. All PostgreSQL tests passed here, and what you are trying to achieve is a very basic operation

4

Shouldn't Aurelius use "Serial" type for the purpose of numerical auto-incremented fields ? 

see http://www.postgresql.org/docs/9.1/static/datatype-numeric.html (8.1.4)

But of course that's just avoiding the problem i'm facing. 
Maybe the issue is in the .BuildDatabase() step. I don't see that the ID defined as using seq_dbstate. Does it mean Aurelius "remember" the sequence it should use for this precise field?

Could it be FireDAC's fault?
5

PostgreSQL supports both serial and sequences, and Aurelius choice is using sequences for several reasons. Actually, by the way, Serial field in PostgreSQL is not a real field. It's an alias, internally it creates an integer field, an internal sequence and an internal trigger, so it actually uses sequences.


Yes, it associates the sequence with the table. Might be a problem with firedac, but only if you are using some different settings in TFDConnection, because we run all our tests here with firedac as well and we don't see any issue.

What can be done is that we remove the final ";" from the statements, this could make it work for you. But the root of the problem should be found anyway, some tool should detect a trailing ";" and remove it.
6

I have found the issue.

It is indeed FireDAC's "fault", and mine too :). I was using a FireDAC param "RecordCountMode" at the value "cmTotal". See doc here : http://docwiki.embarcadero.com/Libraries/XE5/en/FireDAC.Stan.Option.TFDFetchOptions.RecordCountMode

the problem is that this param make FD use a statement like following to get the count of the executed query (so you can have the correct .recordCount property value)


SELECT COUNT(*) FROM (<original SQL command text>)

We have to deal with this incompatibility.
Can you do something about it in Aurelius's FireDAC driver to avoid this?
I set this option deliberately and i'd like not to change it so i don't have to go through the whole process of testing my whole legacy app against the 4 DBMS it support.
7

Yes, it's possible. I think it's just a matter of removing the trailing ";" from SQL statements. Please send me a direct e-mail (wagner at tms) so I can send you the patch.

8

Did you get my email? I sent you through my email address (m.baumgartner). 

9

Yes, answered.