1

I have a problem with a database right now. There is a BLOB field in the database. The BLOB is an XML. In this BLOB I have to update the XML.

However, I have no idea how to do this. Unfortunately, databases are not my strong point.

My method so far looks like this:

public void UpdateCharacterData(long characterID, long oid, String name, String xmlData) {
    String sql = "SELECT data FROM objstore WHERE obj_id=? AND namespace_int=? AND type=? AND name=?";
    //String sql = "UPDATE data FROM objstore WHERE obj_id=? AND namespace_int=? AND type=? AND name=?";
    try (PreparedStatement ps = queries.prepare(sql)) {
        ps.setLong(1, characterID);
        ps.setInt(2, 3);
        ps.setString(3, "Player");
        ps.setString(4, name);
        try (ResultSet rs = queries.executeSelect(ps)) {
            if (rs != null) {
                while (rs.next()) {
                    String data = rs.getString("data");
                    Log.debug(data);
                }
            }
        }           
    } catch (SQLException ex) {
        ex.printStackTrace();
        Log.dumpStack("SQLException: " + ex);
    }
}

So I would have to read out the BLOB completely and convert it to XML. Make my changes and then write them back. At least that's my guess.

I hope someone can help me further.

Thank you!

Don
  • 11
  • 2
  • Are you sure it's `BLOB` and not `TLOB`? What kind of DB is that? – PM 77-1 Aug 05 '22 at 17:19
  • Yes, it is a BLOB. The database belongs to a game engine. Unfortunately, I cannot change the BLOB either, as it is permanently integrated. – Don Aug 05 '22 at 17:24
  • With this code I can read the entire content of the BLOB: ```String data = rs.getString("data");``` Then I get the XML structure. – Don Aug 05 '22 at 17:31
  • Have a look at Java's [Large Objects](https://docs.oracle.com/javase/tutorial/jdbc/basics/sqlxml.html), though your approach would still remain the same. – PM 77-1 Aug 05 '22 at 17:34
  • With the Large object I can now get everything into a SQLXML. But how can I change the individual values here? I can't find a suitable method in my Value. ```SQLXML xml = rs.getSQLXML("data");``` – Don Aug 05 '22 at 17:48
  • Isn't "Accessing SQLXML Object Data" section describes just that? You goal is to pass it to an existing parser, and then deal with as with any other XML Document object. See https://stackoverflow.com/questions/31693670/how-to-update-xml-files-in-java or search for similar. – PM 77-1 Aug 05 '22 at 18:23
  • Which dbms are you using? – jarlh Aug 05 '22 at 18:28

1 Answers1

0

It doesn't really work that way. I have now read out the data with it:

sqlxml = rs.getSQLXML("data");

After the try/catch it continues:

Document xmlDoc = (Document)XMLConverter.ConvertStringToDocument(sqlxml);
if(xmlDoc == null) {
    Log.error("xmlDoc is null.");
    return;
}

This would be the converter:

public static Document ConvertStringToDocument(SQLXML sqlxml) {
    try {  
        InputStream binaryStream = sqlxml.getBinaryStream();
        DocumentBuilder parser = DocumentBuilderFactory.newInstance().newDocumentBuilder();
        Document doc = (Document)parser.parse(binaryStream);
        return doc;
    } catch (Exception ex) {
        Log.error(ex.toString());
    }
    return null;
}

However, I always get null. No exception.

Don
  • 11
  • 2
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 09 '22 at 15:45
  • I have been able to solve the problem in the meantime. Many thI have been able to solve the problem in the meantime. SQXML did not work for me. Unfortunately, I could not find out why. I have now found a way to convert it to XML. Thanks a lot for the help! :)anks for the help! :) – Don Aug 10 '22 at 07:56