Skip to content
New issue

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

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Table created with non-nullable column even without NOT NULL in column definition #963

Closed
tsegismont opened this issue May 26, 2021 · 0 comments · Fixed by #975
Closed

Table created with non-nullable column even without NOT NULL in column definition #963

tsegismont opened this issue May 26, 2021 · 0 comments · Fixed by #975
Assignees
@tsegismont
Labels
bug module:mssql
Milestone
4.1.1

Comments

@tsegismont
Copy link
Contributor

When using the MS SQL Client for CREATE TABLE statements, the resulting columns are non-nullable even when NOT NULL is not specified on the column definition.

This does not happen when using the JDBC client on the same database.

PreparedStatement create = conn.prepareStatement("create table Basic (id int, dessimal numeric(19,2), primary key (id))");
create.executeUpdate();
PreparedStatement insert = conn.prepareStatement("INSERT INTO Basic (id, dessimal) values (3, ?)");
insert.setBigDecimal(1, new BigDecimal("3.2"));
insert.executeUpdate();

The snippet above works fine but this one fails:

connnection.preparedQuery("create table Basic2 (id int, dessimal numeric(19,2), primary key (id))")
  .execute(ctx.asyncAssertSuccess(create -> {
    connnection.preparedQuery("INSERT INTO Basic2 (id, dessimal) values (3, @p1)")
      .execute(Tuple.of(NullValue.BigDecimal), ctx.asyncAssertSuccess());
  }));

The error message is:

io.vertx.mssqlclient.MSSQLException: {number=515, state=2, severity=16, message='Cannot insert the value NULL into column 'dessimal', table 'master.dbo.Basic2'; column does not allow nulls. INSERT fails.', serverName='0ac9600ef0bb', lineNumber=1}

As a workaround, the column definition must explicitly allow NULL values:

create table Basic2 (id int, dessimal numeric(19,2) null, primary key (id))
@tsegismont tsegismont self-assigned this May 26, 2021
@tsegismont tsegismont mentioned this issue May 26, 2021
Closed
@tsegismont tsegismont added this to the 4.1.0 milestone May 26, 2021
@vietj vietj modified the milestones: 4.1.0, 4.1.1 Jun 1, 2021
This was referenced Jun 4, 2021
Merged
Closed
tsegismont added a commit to tsegismont/vertx-sql-client that referenced this issue Jun 9, 2021
@tsegismont
MS SQL Connection Options: same as JDBC
70ac98a 
Fixes eclipse-vertx#963

In particular, ODBC_ON implies ANSI_DEFAULTS which activates the following ISO behaviors:

- ANSI_NULLS
- CURSOR_CLOSE_ON_COMMIT
- ANSI_NULL_DFLT_ON
- IMPLICIT_TRANSACTIONS
- ANSI_PADDING
- QUOTED_IDENTIFIER
- ANSI_WARNINGS

Signed-off-by: Thomas Segismont <[email protected]>
@tsegismont tsegismont mentioned this issue Jun 9, 2021
Merged
Rattenkrieg pushed a commit to Rattenkrieg/vertx-sql-client that referenced this issue Nov 8, 2021
@tsegismont @Rattenkrieg
MS SQL Connection Options: same as JDBC
8749e4d 
Fixes eclipse-vertx#963

In particular, ODBC_ON implies ANSI_DEFAULTS which activates the following ISO behaviors:

- ANSI_NULLS
- CURSOR_CLOSE_ON_COMMIT
- ANSI_NULL_DFLT_ON
- IMPLICIT_TRANSACTIONS
- ANSI_PADDING
- QUOTED_IDENTIFIER
- ANSI_WARNINGS

Signed-off-by: Thomas Segismont <[email protected]>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@tsegismont tsegismont

Labels
bug module:mssql
Projects
None yet
Milestone
4.1.1
Development

Successfully merging a pull request may close this issue.

MS SQL Connection Options: same as JDBC tsegismont/vertx-sql-client
2 participants
@vietj @tsegismont