Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
Message-ID: <48B330E5.2000807@elfyard.com>
Date: Tue, 26 Aug 2008 01:23:33 +0300
From: Valentin Cozma <vcozma@elfyard.com>
User-Agent: Thunderbird 2.0.0.16 (X11/20080724)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: Saving XML into a database
References: <BAY113-W227AFEE2ED61E6D53F31B8E56A0@phx.gbl>
In-Reply-To: <BAY113-W227AFEE2ED61E6D53F31B8E56A0@phx.gbl>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

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/>