mysqlactiverecordsubsonic3subsonic-active-recordlastinsertid

Subsonic 3.0.0.4 active record templates for mysql not returning last inserted id


Steps to reproduce error:

CREATE TABLE person ( person_id int(11) NOT NULL AUTO_INCREMENT, firstname varchar(20) DEFAULT NULL, lastname varchar(20) DEFAULT NULL, age int(11) DEFAULT '0', PRIMARY KEY (person_id) ) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=latin1

INSERT INTO person(firstname,lastname,age) VALUES ('myname',NULL,NULL) ;SELECT LAST_INSERT_ID() as newid

    Person personObject = new Person();
    personObject.Firstname= "myname";
    personObject.Add();
    Response.Write(personObject.PersonId);

output is"0'


Solution

  • This not a bug but rather an error that is likely to happen if you change the naming convention of columns and tables -> ToPascalCase. By default a column/table name is copied the way it is defined on the MySQL database. Which makes this ugly

    Original

    person personObject = new person()
    personObject .person_id = 1;
    personObject .first_name = "hello";
    personObject .Save();
    

    What I like to happen

    Person personObject = new Person()
    personObject.PersonId = 1;
    personObject.FirstName = "hello";
    personObject.Save();
    

    There will be two(~3, can't remember now) major issues to the SubSonic core after changing the default template to generate the following names using SubSonic's inflector class.

    reflection for properties that are required for UPDATES/INSERTS must be changed example:

    //from
    item.GetType().GetProperty(tbl.PrimaryKey.Name);
    //to
    item.GetType().GetProperty(Inflector.ToPascalCase(tbl.PrimaryKey.Name));
    

    column names should be returned to its original property when doing inserts example:

    //from
    GetColumn(key)
    //to
    GetColumn(Inflector.AddUnderscores(key))