MSSQL: insert query generation bug #125

Closed
H0 opened this Issue Apr 10, 2012 · 3 comments

Comments

Projects
None yet
2 participants
@H0

H0 commented Apr 10, 2012

Beans from this table:

CREATE TABLE QTEST (ID int IDENTITY(1,1) NOT NULL,  C1 int NULL);
QQtest q= QQtest.qtest;
SQLInsertClause sic = new SQLInsertClause(conn, new SQLServerTemplates(), q);
Qtest qt= new Qtest();
qt.setC1(1);
sic.populate(qt,DefaultMapper.WITH_NULL_BINDINGS).addBatch();
sic.execute());

Results in:

java.lang.IllegalArgumentException: com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'WHERE'.
    at com.mysema.query.sql.dml.AbstractSQLClause.setParameters(AbstractSQLClause.java:68)
    at com.mysema.query.sql.dml.SQLInsertClause.prepareStatementAndSetParameters(SQLInsertClause.java:267)
    at com.mysema.query.sql.dml.SQLInsertClause.createStatement(SQLInsertClause.java:232)
    at com.mysema.query.sql.dml.SQLInsertClause.execute(SQLInsertClause.java:305)
    at ShowBug.indirect(ShowBug.java:67)
    at ShowBug.main(ShowBug.java:78)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'WHERE'.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
    at com.microsoft.sqlserver.jdbc.SQLServerParameterMetaData.<init>(SQLServerParameterMetaData.java:426)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getParameterMetaData(SQLServerPreparedStatement.java:1532)
    at com.mysema.query.sql.types.NullType.setValue(NullType.java:43)
    at com.mysema.query.sql.types.NullType.setValue(NullType.java:25)
    at com.mysema.query.sql.Configuration.set(Configuration.java:97)
    at com.mysema.query.sql.dml.AbstractSQLClause.setParameters(AbstractSQLClause.java:66)
    ... 5 more

JDBC code used:

PreparedStatement pst= conn.prepareStatement("INSERT INTO QTEST(ID, C1) values (?,?)");
int type = pst.getParameterMetaData().getParameterType(1);

Fix: adding space between "QTEST" and "(ID, C1)": "INSERT INTO QTEST (ID, C1) values (?,?)"

...and there another problem arises:

RelationalPathBase generated from MSSQL DB created with mentioned above SQL-code
differs with RelationalPathBase generated from H2 with same code by following line:

public final com.mysema.query.sql.PrimaryKey<Qtest> constraint4 = createPrimaryKey(id); 

absence of which leads to generation of logically-invalid PreparedStatement:

INSERT INTO QTEST (ID, C1) values (?,?)

instead of H2's

INSERT INTO QTEST (C1) values (?)

I think there should be another way to tell BeanMapper.createMap(RelationalPath<?>, Object)
not to map particular fields insted of creating PK/manually adding magic line to generated source.
Again(#74), maintaining of field mask may be a somewhat cleaner aproach to generation of prepared statements...

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Apr 11, 2012

Member

Fixed the mssql issues.

Member

timowest commented Apr 11, 2012

Fixed the mssql issues.

timowest added a commit that referenced this issue Apr 11, 2012

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Apr 11, 2012

Member

It appears MSSQL doesn't expose the primary keys correctly, feel free to create a ticket for the field mask approach.

Member

timowest commented Apr 11, 2012

It appears MSSQL doesn't expose the primary keys correctly, feel free to create a ticket for the field mask approach.

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Apr 20, 2012

Member

Released in 2.5.0

Member

timowest commented Apr 20, 2012

Released in 2.5.0

@timowest timowest closed this Apr 20, 2012

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