Question: MSSQL, retrieving data from existing table?

[Bondifrench]

I am trying to retrieve data from an existing table in MSSQL, the table name is Domain.GICS_Industry
it has 4 columns:
id (primary key, integer, not null)
code (integer, null)
Name (nvarchar(255), null)
IndustryGroupId (foreignkey, integer, null)

I tried to retrieve the data like this:

var gicsIndustry = sequelize.define('gicsIndustry', {
    Id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        allowNull: false
    },
    code: Sequelize.INTEGER,
    Name: Sequelize.STRING,
    IndustryGroupId: {
        type: Sequelize.INTEGER
}
}, {
    tableName: 'Domain.GICS_Industry',
    freezeTableName: true,
    timestamps: false
});

sequelize
    .sync()
    .then(function() {
        return gicsIndustry.findAll()
            .then(function(content) {
                for (var i = 0; i < content.length; i++) {
                    console.log(content[0])
                };
            })
    })
    .catch(function(error) {
        console.log('Unable to connect to the database: ', error);
    })

I get the following message:

Executing (default): IF OBJECT_ID('[Domain.GICS_Industry]', 'U') IS NULL CREATE TABLE "Domain.GICS_Industry" ("Id" INTEGER NOT NULL , "code" INTEGER NULL, "Name" NVARCHAR(255) NULL, "IndustryGroupId" INTEGER NULL, PRIMARY KEY ("Id"));
Executing (default): EXEC sys.sp_helpindex @objname = N'[Domain.GICS_Industry]';
Executing (default): SELECT "Id", "code", "Name", "IndustryGroupId" FROM "Domain.GICS_Industry" AS "gicsIndustry";
Problem trying to retrieve data:  { [SequelizeDatabaseError: Invalid column name 'IndustryGroupId'.]
  name: 'SequelizeDatabaseError',
  message: 'Invalid column name \'IndustryGroupId\'.',
  parent:
   { [RequestError: Invalid column name 'IndustryGroupId'.]
     name: 'RequestError',
     message: 'Invalid column name \'IndustryGroupId\'.',
     code: 'EREQUEST',
     sql: 'SELECT "Id", "code", "Name", "IndustryGroupId" FROM "Domain.GICS_Industry" AS "gicsIndustry";' },
  original:
   { [RequestError: Invalid column name 'IndustryGroupId'.]
     name: 'RequestError',
     message: 'Invalid column name \'IndustryGroupId\'.',
     code: 'EREQUEST',
     sql: 'SELECT "Id", "code", "Name", "IndustryGroupId" FROM "Domain.GICS_Industry" AS "gicsIndustry";' },
  sql: 'SELECT "Id", "code", "Name", "IndustryGroupId" FROM "Domain.GICS_Industry" AS "gicsIndustry";' }

Am I obliged to define the other table that the foreign key refers to?

Actually I tried, modifying the definition like this:

    IndustryGroupId: {
        type: Sequelize.INTEGER,
        references: 'Domain.GICS_IndustryGroup',
        referencesKey: 'id'
                }

I got more or less the same error:

Executing (default): IF OBJECT_ID('[Domain.GICS_Industry]', 'U') IS NULL CREATE TABLE "Domain.GICS_Industry" ("Id" INTEGER NOT NULL , "code" INTEGER NULL, "Name" NVARCHAR(255) NULL, "IndustryGroupId" INTEGER NULL, PRIMARY KEY ("Id"), FOREIGN KEY ("IndustryGroupId") REFERENCES "Domain.GICS_IndustryGroup" ("id"));
Executing (default): EXEC sys.sp_helpindex @objname = N'[Domain.GICS_Industry]';
Executing (default): SELECT "Id", "code", "Name", "IndustryGroupId" FROM "Domain.GICS_Industry" AS "gicsIndustry";
Problem trying to retrieve data:  { [SequelizeDatabaseError: Invalid column name 'IndustryGroupId'.]
  name: 'SequelizeDatabaseError',
  message: 'Invalid column name \'IndustryGroupId\'.',
  parent:
   { [RequestError: Invalid column name 'IndustryGroupId'.]
     name: 'RequestError',
     message: 'Invalid column name \'IndustryGroupId\'.',
     code: 'EREQUEST',
     sql: 'SELECT "Id", "code", "Name", "IndustryGroupId" FROM "Domain.GICS_Industry" AS "gicsIndustry";' },
  original:
   { [RequestError: Invalid column name 'IndustryGroupId'.]
     name: 'RequestError',
     message: 'Invalid column name \'IndustryGroupId\'.',
     code: 'EREQUEST',
     sql: 'SELECT "Id", "code", "Name", "IndustryGroupId" FROM "Domain.GICS_Industry" AS "gicsIndustry";' },
  sql: 'SELECT "Id", "code", "Name", "IndustryGroupId" FROM "Domain.GICS_Industry" AS "gicsIndustry";' }

What am I doing wrong?
I did verify that I could connect to MSSQL beforehand, I am using "sequelize": "^2.0.0-rc7" with tedious
Thanks
@mbroadst @mickhansen

[mickhansen]

tableName: 'Domain.GICS_Industry' does the tablename literally have a . or is it in a schema?
If it's a schema you should use schema: 'Domain' instead.

[Bondifrench]

@mickhansen
I changed the model definition to this:

var gicsIndustry = sequelize.define('gicsIndustry', {
    Id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        allowNull: false
    },
    code: Sequelize.INTEGER,
    Name: Sequelize.STRING,
    IndustryGroupId: {
        type: Sequelize.INTEGER
    }
}, {
    schema: 'Domain',
    tableName: 'GICS_Industry',
    freezeTableName: true,
    timestamps: false
});

sequelize
    .sync()
    .then(function() {
        return gicsIndustry.findAll()
            .then(function(content) {
                for (var i = 0; i < content.length; i++) {
                    console.log(content[i])
                };
            })
    })
    .catch(function(error) {
        console.log('Problem trying to retrieve data: ', error);
    })

I still get the same error message:

Executing (default): IF OBJECT_ID('[Domain.GICS_Industry]', 'U') IS NULL CREATE TABLE "Domain.GICS_Industry" ("Id" INTEGER NOT NULL , "code" INTEGER NULL, "Name" NVARCHAR(255) NULL, "IndustryGroupId" INTEGER NULL, PRIMARY KEY ("Id"));
Executing (default): EXEC sys.sp_helpindex @objname = N'[Domain.GICS_Industry]';
Executing (default): SELECT "Id", "code", "Name", "IndustryGroupId" FROM "Domain.GICS_Industry" AS "gicsIndustry";
Problem trying to retrieve data:  { [SequelizeDatabaseError: Invalid column name 'IndustryGroupId'.]
  name: 'SequelizeDatabaseError',
  message: 'Invalid column name \'IndustryGroupId\'.',
  parent:
   { [RequestError: Invalid column name 'IndustryGroupId'.]
     name: 'RequestError',
     message: 'Invalid column name \'IndustryGroupId\'.',
     code: 'EREQUEST',
     sql: 'SELECT "Id", "code", "Name", "IndustryGroupId" FROM "Domain.GICS_Industry" AS "gicsIndustry";' },
  original:
   { [RequestError: Invalid column name 'IndustryGroupId'.]
     name: 'RequestError',
     message: 'Invalid column name \'IndustryGroupId\'.',
     code: 'EREQUEST',
     sql: 'SELECT "Id", "code", "Name", "IndustryGroupId" FROM "Domain.GICS_Industry" AS "gicsIndustry";' },
  sql: 'SELECT "Id", "code", "Name", "IndustryGroupId" FROM "Domain.GICS_Industry" AS "gicsIndustry";' }

[mickhansen]

#2918 yeah there appears to be an issue with schemas, will have to wait for @mbroadst to take a look, he's the MSSQL wiz.

[mbroadst]

@Bondifrench yep, give me a day or two to sort it out, definitely left out proper schema support during the initial dialect support, though it shouldn't be too difficult.

[mbroadst]

@Bondifrench hi, I think I've added proper schema support in my latest commit. You can try it out to see if it solves your problem, the code is here: https://github.com/mbroadst/sequelize/tree/schema-support, or it may be merged into master relatively soon

[Bondifrench]

@mbroadst
Hi, I have reinstalled sequelize (so version 2.0.0 rc7) and tried again with the following code:

var gicsIndustry = sequelize.define('gicsIndustry', {
    Id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        allowNull: false
    },
    code: Sequelize.INTEGER,
    Name: Sequelize.STRING,
    IndustryGroupId: {
        type: Sequelize.INTEGER,
        field: 'IndustryGroupId',
            referencesKey: 'id'
    }
}, {
    schema: 'Domain',
    tableName: 'GICS_Industry',
    freezeTableName: true,
    timestamps: false
});
sequelize
    .sync()
    .then(function() {
        return gicsIndustry.findAll()
            .then(function(content) {
                for (var i = 0; i < content.length; i++) {
                    console.log(content[i])
                };
            })
    })
    .catch(function(error) {
        console.log('Problem trying to retrieve data: ', error);
    })

and I still get the following error:

Executing (default): IF OBJECT_ID('[Domain.GICS_Industry]', 'U') IS NULL CREATE TABLE "Domain.GICS_Industry" ("Id" INTEGER NOT NULL , "code" INTEGER NULL, "Name" NVARCHAR(255) NULL, "IndustryGroupId" INTEGER NULL, PRIMARY KEY ("Id"));
Executing (default): EXEC sys.sp_helpindex @objname = N'[Domain.GICS_Industry]';
Executing (default): SELECT "Id", "code", "Name", "IndustryGroupId" FROM "Domain.GICS_Industry" AS "gicsIndustry";
Problem trying to retrieve data:  { [SequelizeDatabaseError: Invalid column name 'IndustryGroupId'.]
  name: 'SequelizeDatabaseError',
  message: 'Invalid column name \'IndustryGroupId\'.',
  parent:
   { [RequestError: Invalid column name 'IndustryGroupId'.]
     name: 'RequestError',
     message: 'Invalid column name \'IndustryGroupId\'.',
     code: 'EREQUEST',
     sql: 'SELECT "Id", "code", "Name", "IndustryGroupId" FROM "Domain.GICS_Industry" AS "gicsIndustry";' },
  original:
   { [RequestError: Invalid column name 'IndustryGroupId'.]
     name: 'RequestError',
     message: 'Invalid column name \'IndustryGroupId\'.',
     code: 'EREQUEST',
     sql: 'SELECT "Id", "code", "Name", "IndustryGroupId" FROM "Domain.GICS_Industry" AS "gicsIndustry";' },
  sql: 'SELECT "Id", "code", "Name", "IndustryGroupId" FROM "Domain.GICS_Industry" AS "gicsIndustry";' }

As I mentionned before it's a legacy table, ie an already existing table, not created initially with Sequelize, does it matter?

[mbroadst]

@Bondifrench you can't use rc7 this hasn't been introduced to a tagged release candidate yet, instead you should change your package.json's dependencies to point to master, like so:

"dependencies": {
    "sequelize": "git://github.com/sequelize/sequelize#master",
   ... other dependencies ...
}

[mbroadst]

@Bondifrench it shouldn't matter that its an existing table, but it does seem that you're not getting one of the column names right. Could you possibly use microsoft sql server management studio to run this query for me 'sp_help"Domain.GICS_Industry"' so we can see the actual column names compared to the ones that sequelize is generating?

[Bondifrench]

Sorry was busy with other things, your fix to Sequelize did enable me to run the query correctly, thanks for that.
The columns names generated by the query 'sp_help"Domain.GICS_Industry"' were exactly the same as the ones I defined in my model, ie Id, code, Name and IndustryGroupId.

Thanks a lot Matt for your work on the MS SQL part, and thanks to the Sequelize team, awesome job.