0

Lets say I have one column with datatype of VARCHAR(500) in db2 zOS table and I wanted to insert data into that column using prepared statement. But that data/string has null character in between. e.g: "hello\0test\0example!". My question is does setString(index, str) will change those null characters with space or any other character ? or will get it inserted as it is ?

I tried inserting data but not sure. I found change in hex values of original string and inserted string.

Pooja Shelar
  • 3
  • 2

1 Answers1

0

'\0' in Java is considered null. Answering your question, your string will be inserted as-is. It will have null inside. It will then be dependent on your DBMS in your case 'db2' that how it will handle null inside string.

I have tested it using MySQL . I am getting the same data which is inserted. Additionally I have verified it using char instead of String and by printing their ascii values which is just an additional check.

I have used this code for printing every character value:

private static void printStringAndAscii(String str)
    {
        System.out.println("Incoming String:" + str);

        System.out.println("Char with ASCII Values:");

        for (char c : str.toCharArray())
        {
            System.out.println(((int) c) + "-" + c);
        }
    }

To test it with database I have first printed your given string and save in database, then extracted it and got it printed (deletion is for my own testing). I have tested following code with MySQL utf8 default colation :

    Class.forName("com.mysql.jdbc.Driver").newInstance();

    String originalString = "hello\0test\0example!";

    try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/corporate", "user", "pwd"))
    {
        printStringAndAscii(originalString);

        //Removing previously inserted entry
        PreparedStatement prepareStatement = connection.prepareStatement("DELETE FROM tip WHERE tip_id=?");
        prepareStatement.setInt(1, 1);

        int result = prepareStatement.executeUpdate();
        prepareStatement.close();

        System.out.println("Delete Result:" + result);

        //Inserting new entry
        prepareStatement = connection.prepareStatement("INSERT INTO tip(tip_id,tip_text) VALUES(?,?)");
        prepareStatement.setString(1, "1");
        prepareStatement.setString(2, originalString);

        result = prepareStatement.executeUpdate();

        System.out.println("Insert Result:" + result);
        prepareStatement.close();

        Statement statement = connection.createStatement();

        //Loading inserted entry from database
        ResultSet resultSet = statement.executeQuery("SELECT * FROM tip WHERE tip_id=1");

        if (resultSet.next())
        {
            //printing the fetched string from database
            String string = resultSet.getString(2);
            printStringAndAscii(string);
        }

        resultSet.close();
        statement.close();
        connection.close();
    }
    catch (Throwable e)
    {
        e.printStackTrace();
    }

Interestingly, I have found out that System.out.println() prints until it finds '\0' (like c), however prepared statement inserts complete string with length.

saadeez
  • 111
  • 4