db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Iwud H8u <iwud...@hotmail.com>
Subject Re: Saving XML into a database
Date Thu, 28 Aug 2008 20:09:08 GMT

Hi All,

Thanks for all the responses... :) I have to confess I wasn't thinking
properly about this little problem. I had managed to serialise XML from a
file by looking at the derby manual like someone pointed out earlier.

Going back to my original post, I was going about serialising JDOM objects
the wrong way. To serialise a JDOM object, I should have used the
XMLOutputter and not the toString() method. 

Reading through all these suggestions and kicking myself in the butt for
being sloppy, here is how I have implemented this now ... I am posting it
here for someone who might be in my shoes ..

        String expression = "INSERT INTO INVOICE_DATA (DOCUMENT) " +
                "VALUES (XMLPARSE( DOCUMENT CAST (? AS CLOB) PRESERVE
WHITESPACE))";

            PreparedStatement preparedStatement =
connection.prepareStatement(expression);

            XMLOutputter outputter = new
XMLOutputter(Format.getCompactFormat());
            StringWriter stringWriter = new StringWriter();
            outputter.output(ele , stringWriter);    // use the outputter to
create a string representation
            logger.debug("stringWriter.getBuffer().toString() = " +
stringWriter.getBuffer().toString());

            // update statement and execute
            preparedStatement.setString(1,
stringWriter.getBuffer().toString());


Craig L Russell wrote:
> 
> Hi Jay,
> 
> I haven't tried this but it might be worth trying.
> 
> Use the setAsciiStream(column, InputStream) method of PreparedStatement.
> 
> What your code has to do is to figure out how to implement an  
> InputStream that will deliver the ascii version of your xml document  
> from memory. Not knowing what interface your xml dom objects support,  
> you might be able to write the dom as a stream to a  
> ByteArrayOutputStream, take the byte[] result and create a  
> ByteArrayInputStream and pass that to setAsciiStream.
> 
> Craig
> 
> On Aug 28, 2008, at 9:21 AM, Iwud H8u wrote:
> 
>>
>> Hi Valentin,
>>
>> Thanks for suggesting this... I should have been more specific with my
>> initial question. I have in-memory XML DOM objects which need to be  
>> saved
>> into a database... If I adopt your approach, I will need to save my  
>> DOM
>> objects to a file before reading them in...
>>
>> I am looking for a way to insert in memory XML DOM objects into the  
>> database
>> because my code generates these xml objects programatically.
>>
>> Any ideas on how to do this?
>>
>>
>> Valentin Cozma wrote:
>>>
>>> jay _ wrote:
>>>> I've been struggling to find some decent examples of how to save XML
>>>> into a Derby database.
>>>>
>>>> I've managed to figure out that XML needs to be serialised into a
>>>> different datatype before it can saved in Derby. I am using a CLOB.
>>>> However, I am not quite sure how exactly to get the data into the  
>>>> XML
>>>> column.
>>>>
>>>> My sample code looks like this ....
>>>>
>>>>        String expression = "INSERT INTO XML_DATA (DOCUMENT ) " +
>>>>                "VALUES (XMLPARSE( DOCUMENT CAST (? AS CLOB) PRESERVE
>>>> WHITESPACE))";
>>>>
>>>>        try
>>>>        {
>>>>            PreparedStatement preparedStatement =
>>>> connection.prepareStatement(expression);
>>>>            // update statement and execute
>>>> //            Element ele = getHomeViewElement();
>>>>            logger.debug("xmlDocument = " +
>>>> xmlDocument.getDocument().toString());
>>>>            preparedStatement.setString(1, xmlDocument.toString());
>>>>
>>>>            preparedStatement.executeUpdate();
>>>>
>>>>        } catch (SQLException e) {
>>>>            logger.warn(e);
>>>>        }
>>>>
>>>> Using either the Element or the Document toString() methods result  
>>>> in
>>>> an exception ....
>>>> java.sql.SQLException: Invalid XML DOCUMENT: Content is not  
>>>> allowed in
>>>> prolog.
>>>
>>>
>>>
>>>
>>>
>>> I am inserting xml as blob ( binary ) . works like a charm . example
>>> follows .
>>>
>>> -- write
>>>    public void addFileVersion(int idFile, String autor, String
>>> comentarii, InputStream stream, int fileSize) throws SQLException {
>>>        addVersionToFile.setInt(1, idFile);
>>>        addVersionToFile.setString(2, autor);
>>>        addVersionToFile.setString(3, comentarii);
>>>        addVersionToFile.setBinaryStream(4, stream, fileSize);
>>>        addVersionToFile.execute();
>>> }
>>>
>>> -- read
>>>
>>>    public void saveFileVersionToDisk(int idFile, File location)  
>>> throws
>>> SQLException, IOException {
>>>        InputStream result = null;
>>>        getFileVersion.setInt(1, idFile);
>>>        ResultSet rs = getFileVersion.executeQuery();
>>>        if(rs.next()) {
>>>            result = rs.getBinaryStream("bytes");
>>>        }
>>>        if(result != null) {
>>>            OutputStream of = new FileOutputStream(location);
>>>            FileUtils.copyFiles(result, of);
>>>        }
>>>        rs.close();
>>>    }
>>>
>>> -- table
>>>
>>> create table file_versions(
>>>        id int NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY  
>>> (START
>>> WITH 1, INCREMENT BY 1),
>>>        id_file int not null constraint FK_FILE_VERSIONS__files
>>> references files(id) on delete cascade,
>>>        autor varchar(100) NOT NULL,
>>>        comentarii varchar(1000) NOT NULL,
>>>        bytes blob not null);
>>>
>>>
>>>
>>>>
>>>> Can someone suggest what I am doing wrong here?
>>>>
>>>> Cheers,
>>>> Jay
>>>>
>>>> ------------------------------------------------------------------------
>>>> Be the filmmaker you always wanted to be—learn how to burn a DVD  
>>>> with
>>>> Windows®. Make your smash hit
>>>> <http://clk.atdmt.com/MRT/go/108588797/direct/01/>
>>>
>>>
>>>
>>
>> -- 
>> View this message in context:
>> http://www.nabble.com/Saving-XML-into-a-database-tp19149566p19204059.html
>> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>>
> 
> Craig L Russell
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 408 276-5638 mailto:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
> 
> 
>  
> 

-- 
View this message in context: http://www.nabble.com/Saving-XML-into-a-database-tp19149566p19208215.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message