java - 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);
解决方案
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.
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 ""
.
推荐阅读
- python-3.x - 无法导入电视节目
- aws-codebuild - CodeBuild 无法运行 CDK 合成器
- python - 根据其他列的两个条件创建新列
- conda - 如何解决 conda 环境中的 PyLint“导入”缺陷?
- css - 如何修复悬停冲突并在 ReactJS 自动完成中输入事件?
- amazon-web-services - 如何获取带有元数据的 S3 对象版本?
- html - 如何反转这个 SCSS 动画?
- python-3.x - 如何从 Dockerfile 运行环境初始化 shell 脚本
- swift - 直接将 Firebase Firestore 与移动客户端一起使用是否足够安全?如何避免恶意请求和超出限制的过多请求?
- docker - 如何在docker上正确设置alsa dummy?