Migrating from Oracle to Azure SQL caveat — prepared statement set string causes implicit conversion

Problem

SELECT id, column_1 from mySchema.myTable Where stringId = @PO
String selectByStringId = "SELECT id, column_1 cd from mySchema.myTable WHERE stringId = ?";
String id = "abcde"
Connection connection = getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(selectByStringId);
preparedStatement.setString(1, id);
ResultSet resultSet = preparedStatement.executeQuery();

Reason

When an operator combines expressions of different data types, the data type with the lower precedence is first converted to the data type with the higher precedence

Data type precedence (Transact-SQL) — SQL Server | Microsoft Docs

Solution

References

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store