MSSQL: Set boolean column to False tries to insert NULL
grbspltt opened this issue · 3 comments
Issue Description
Trying to update an entity with a boolean property, set the value to false will create a DB error, Cannot insert the value NULL into column 'licActive', table 'vendor'; column does not allow nulls. Update fails.
Expected Behavior
If updating the property to false, SQL script should set the value to 0.
Actual Behavior
Vendor Schema
@Entity({name: 'vendors'})
class VendorEntity extends DefaultColumns implements IVendorEntity {
@Column({type: 'nvarchar', length: 150, nullable: false})
@IsString()
@MaxLength(150)
name: string;
@Column({type: 'bit', nullable: false, default: 0})
licActive: boolean;
@Column({type: 'smalldatetime', nullable: true})
@IsDateString()
@IsOptional()
licFrom: Date | null;
@Column({type: 'smalldatetime', nullable: true})
@IsDateString()
@IsOptional()
licLastRefresh: Date | null;
@Column({type: 'bit', nullable: false, default: 0})
licNoVerified: boolean;
@Column({type: 'smalldatetime', nullable: true})
@IsDateString()
@IsOptional()
licTo: Date | null;
}
Update Code
async clearCSLB(id: number): Promise<any> {
const now = new Date();
const cslbUpdater: Partial<VendorEntity> = {
licNoVerified: false,
licActive: false,
licFrom: null,
licTo: null,
licLastRefresh: now
};
return getRepository(VendorEntity)
.createQueryBuilder()
.update()
.set(cslbUpdater)
.where('id = :id', {id})
.execute();
}
Steps to Reproduce
- Create entity
- Update Entity with false in the boolean column
My Environment
Dependency | Version |
---|---|
Operating System | Windows 10 |
Node.js version | v12.18.3 |
Typescript version | v4.0.2 |
TypeORM version | v0.2.32 |
Additional Context
Error Output
query: 'UPDATE "vendors" SET "licTo" = @0, "licFrom" = @1, "licActive" = @2, "licNoVerified" = @3, "licLastRefresh" = @4, "version" = "version" + 1, "updated" = CURRENT_TIMESTAMP WHERE "id" = @5',
parameters: [
MssqlParameter {
value: undefined,
type: 'smalldatetime',
params: []
},
MssqlParameter {
value: undefined,
type: 'smalldatetime',
params: []
},
MssqlParameter { value: undefined, type: 'bit', params: [] },
MssqlParameter { value: true, type: 'bit', params: [] },
MssqlParameter {
value: 2021-04-20T18:34:31.000Z,
type: 'smalldatetime',
params: []
},
10
]
Relevant Database Driver(s)
-
sqlserver
Are you willing to resolve this issue by submitting a Pull Request?
- Yes, I have the time, and I know how to start.
- Yes, I have the time, but I don't know how to start. I would need guidance.
- No, I don't have the time, although I believe I could do it if I had the time...
- No, I don't have the time and I wouldn't even know how to start.
I can't reproduce this issue.
The error output does not match your clearCSLB function.
The snippet you provided here specifies licFrom and licTo values as NULLs, so those can't be undefined parameters.
As for the boolean errors: I could only reproduce them if I set them to undefined in my clearCSLB function.
Is there another function that also updates vendors?
Additional data:
Dependency | Version |
---|---|
Operating System | Windows 10 x64 |
Node.js version | v12.16.3 |
Typescript version | v4.0.2 |
TypeORM version | v0.2.32 |
@egedib thank you so much for looking into this. The relevant code is
{
licNoVerified: false,
licActive: false,...
Those parameters are undefined instead of false. I have also tried to set to 0 instead of false but it gets the same error.
Can you compare my test repository with your solution? If you can, please run it and check if the same error is still there.