Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

Inserts using adapter mssql, generated SQL having id = NULL on INSERTS even though id column is autoincrement and pk. SQL rejected by SQL-Server. #576

Closed
tidonamarco opened this Issue Jan 24, 2013 · 7 comments

Comments

Projects
None yet
2 participants

Hi!

Propel generated and sent this SQL to the SQL-Server for a new row:

INSERT INTO [TEST].[dbo].[brands]([brand_id], [name]) VALUES (NULL ,"test")
Returned error: DEFAULT or NULL are not allowed as explicit identity values.

instead of this one:
INSERT INTO [TEST].[dbo].brands VALUES ("test")

We are using propelorm 1.6.2. and mssql adapter. It comes down to the runtime: util/BasePeer.php

Table in the schema.xml:

<table name="brands" phpName="Brand">
    <column name="brand_id" phpName="BrandId" type="INTEGER" size="11" primaryKey="true" autoIncrement="true" required="true"/>
    <column name="name" phpName="Name" type="VARCHAR" size="60" required="true"/>
</table>

This is some debug output from

BrandTableMap Object
(
    [columns:protected] => Array
        (
            [BRAND_ID] => ColumnMap Object
                (
                    [type:protected] => INTEGER
                    [size:protected] => 11
                    [pk:protected] => 1
                    [notNull:protected] => 1
                    [defaultValue:protected] => 
                    [relatedTableName:protected] => 
                    [relatedColumnName:protected] => 
                    [table:protected] => BrandTableMap Object
 *RECURSION*
                    [columnName:protected] => BRAND_ID
                    [phpName:protected] => BrandId
                    [validators:protected] => Array
                        (
                        )
                    [valueSet:protected] => Array
                        (
                        )
                    [isPkString:protected] => 
                )
            [NAME] => ColumnMap Object
                (
                    [type:protected] => VARCHAR
                    [size:protected] => 60
                    [pk:protected] => 
                    [notNull:protected] => 1
                    [defaultValue:protected] => 
                    [relatedTableName:protected] => 
                    [relatedColumnName:protected] => 
                    [table:protected] => BrandTableMap Object
 *RECURSION*
                    [columnName:protected] => NAME
                    [phpName:protected] => Name
                    [validators:protected] => Array
                        (
                        )
                    [valueSet:protected] => Array
                        (
                        )
                    [isPkString:protected] => 
                )
        )
    [columnsByPhpName:protected] => Array
        (
            [BrandId] => ColumnMap Object
                (
                    [type:protected] => INTEGER
                    [size:protected] => 11
                    [pk:protected] => 1
                    [notNull:protected] => 1
                    [defaultValue:protected] => 
                    [relatedTableName:protected] => 
                    [relatedColumnName:protected] => 
                    [table:protected] => BrandTableMap Object
 *RECURSION*
                    [columnName:protected] => BRAND_ID
                    [phpName:protected] => BrandId
                    [validators:protected] => Array
                        (
                        )
                    [valueSet:protected] => Array
                        (
                        )
                    [isPkString:protected] => 
                )
            [Name] => ColumnMap Object
                (
                    [type:protected] => VARCHAR
                    [size:protected] => 60
                    [pk:protected] => 
                    [notNull:protected] => 1
                    [defaultValue:protected] => 
                    [relatedTableName:protected] => 
                    [relatedColumnName:protected] => 
                    [table:protected] => BrandTableMap Object
 *RECURSION*
                    [columnName:protected] => NAME
                    [phpName:protected] => Name
                    [validators:protected] => Array
                        (
                        )
                    [valueSet:protected] => Array
                        (
                        )
                    [isPkString:protected] => 
                )
        )
    [dbMap:protected] => DatabaseMap Object
        (
            [name:protected] => models
            [tables:protected] => Array
                (
                    [brands] => BrandTableMap Object
 *RECURSION*
                )
            [tablesByPhpName:protected] => Array
                (
                    [Brand] => BrandTableMap Object
 *RECURSION*
                )
        )
    [tableName:protected] => brands
    [phpName:protected] => Brand
    [classname:protected] => Brand
    [package:protected] => models
    [useIdGenerator:protected] => 1
    [isSingleTableInheritance:protected] => 
    [primaryKeys:protected] => Array
        (
            [BRAND_ID] => ColumnMap Object
                (
                    [type:protected] => INTEGER
                    [size:protected] => 11
                    [pk:protected] => 1
                    [notNull:protected] => 1
                    [defaultValue:protected] => 
                    [relatedTableName:protected] => 
                    [relatedColumnName:protected] => 
                    [table:protected] => BrandTableMap Object
 *RECURSION*
                    [columnName:protected] => BRAND_ID
                    [phpName:protected] => BrandId
                    [validators:protected] => Array
                        (
                        )
                    [valueSet:protected] => Array
                        (
                        )
                    [isPkString:protected] => 
                )
        )
    [foreignKeys:protected] => Array
        (
        )
    [relations:protected] => Array
        (
        )
    [relationsBuilt:protected] => 
    [pkInfo:protected] => 
    [prefix:private] => 
)

Params:

array(2) {
[0]=>
array(3) {
["column"]=>
string(8) "BRAND_ID"
["table"]=>
string(6) "brands"
["value"]=>
NULL
}
[1]=>
array(3) {
["column"]=>
string(4) "NAME"
["table"]=>
string(6) "brands"
["value"]=>
string(3) "MMM"
}
}

Returned and rejected SQL for a simple INSERT: INSERT INTO brands (BRAND_ID,NAME) VALUES (:p1,:p2)

Is it a bug or misconfiguration? Thanks!

A downgrade of the generator to 1.5.4 helped for the moment.

Contributor

brunnels commented Feb 5, 2013

I've been using mssql extensively for a while now and helped to contribute a lot of the mssql updates for propel 1.5 and 1.6. Are you using windows or linux? php version?

Hi Brunnels! PHP is 5.2.6-1+lenny16 on Debian Linux. We are using FreeTDS (version 8.0 set in freetds.conf) connecting to a MS SQL Server Enterprise Edition 9.0. The SQL Server is managed by the client.

Contributor

brunnels commented Feb 6, 2013

I have several tables that have a single primary key not named "id" but none are auto-increment. I didn't see any propel tests that test this scenario either. To test could you rename the brand_id column to id and change your schema to something like this to see if it works?

<table name="brands" phpName="Brand">
    <column name="id" phpName="BrandId" type="INTEGER" size="11" primaryKey="true" autoIncrement="true" required="true"/>
    <column name="name" phpName="Name" type="VARCHAR" size="60" required="true"/>
</table>

Hi Benjamin! Thanks for your research!! The only environment running mssql we have currently available, is the staging area of our customer which is heavily in use these days by our customer due to upcoming releases. I can not test this right now...
Is there really a convention/need in propel for the auto-increment id-fields to be named just 'id'??

Contributor

brunnels commented Feb 11, 2013

I'm not sure of the requirement of auto-increment fields to be id. I don't remember off the top of my head but could do some research. I see you closed the issue. Did you find a resolution?

On a side note I plan to add support for the Microsoft MSSQL Native ODBC driver for Linux to propel soon. Right now there is an open bug with PHP that requires it to be patched and manually built to use the driver https://bugs.php.net/bug.php?id=61777 . I was hoping the patch would make it into PHP before I started the work but it's been open for a long time now. Maybe if I send the php guys a pull request it would happen sooner?

The 'solution' for us was to downgrade the generator for the moment to 1.5.4. ODBC sounds like a good alternative!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment