Search code examples
javajdbcprepared-statementnetezza

preparedStatement.setString(1,"null"), is being interpreted as null instead of a string (after preparedStatement.addBatch())

PreparedStatement setString is taking a "null" (as if String a = "null") and after .addBatch its being transformed into a normal null (as if String a = null ).

I dont know how to be able to bypass this misinterpretation since a lot of lines are being executed at once after they have been all added as batch ... (the sql statement is an INSERT INTO ...) (the VarChar cannot be null but String a = "null" can be accepted by the table if its sent without batch ... the whole program stops due to the error msg from the server)

Error Message : Error while writing data into database... org.netezza.error.NzSQLException: ERROR: Column 17 : Field cannot contain null values

Code : 

preparedStatement.setString(17, x); //currently x was recieved as "null"
preparedStatement.addBatch() //after other parameters were filled then this statement
preparedStatement.executeBatch(); //after the logs this statement is executed.

Hopefully theres a quick fix for that

I used this bypass but thought there could be a better one ... (since i cannot change the databases default value or manipulate the data ... )

if(helper.toLowerCase().equals("null"))
    helper = (helper.equals("null") ? "null ":"NULL ");

preparedStatement.setString(17, helper);
Solution

  • Keep in mind that I never used Netezza and I am not able to try anything. This is a collection of information found in the official documentation of IBM Netezza.

    If I am reading this right, there is an explicit conversion of text value "null" to recognize the absence of a value... for external table. The reason is simple, an external table is a flat file so it probably store everything as TEXT without separator, but with delimited size file.

    Handle the absence of a value

    In SQL, a record must include a value if a column is declared as not null. When a record contains no value for a column, the column is considered to be null. The system provides an explicit and implicit method for conveying nullness.
    - The explicit method includes a specific token in the field instead of a value. By default, this token is the word “null” (not case-sensitive). You can use the nullValue option to change this token to any other 1 - 4 character alphabetic token. You can precede or follow an occurrence of the explicit null token in a non-string field with adjacent spaces. For the system to recognize an explicit null token in a string field, the token cannot have preceding or trailing adjacent spaces. The explicit null token method makes it impossible to express a string that consists of exactly the text of the null token.

    Then, we can see the same idea in The NullValue option

    Specifies the string to use for the null value, with a maximum 4-byte UTF-8 string. The default is ‘NULL’.

    You could replace this option for this table with any other String but I believe this would just move the problem...

    So my solution, set the NullValue for your table to " " (a space) and in your project trim every value, you would never be able to have a " " since it would be trimmed as "".