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

Question

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'

now I believe this is a bug in mysql provider of the subsonic core class.. :( — Aivan Monceller

Aivan Monceller Aivan Monceller · Accepted Answer · 2010-12-07T16:01:56

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))

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

1 Answers