From derby-user-return-9695-apmail-db-derby-user-archive=db.apache.org@db.apache.org Thu Aug 28 16:21:42 2008 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 649 invoked from network); 28 Aug 2008 16:21:42 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 28 Aug 2008 16:21:42 -0000 Received: (qmail 58123 invoked by uid 500); 28 Aug 2008 16:21:38 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 58107 invoked by uid 500); 28 Aug 2008 16:21:38 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 58096 invoked by uid 99); 28 Aug 2008 16:21:38 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 28 Aug 2008 09:21:38 -0700 X-ASF-Spam-Status: No, hits=3.7 required=10.0 tests=DNS_FROM_OPENWHOIS,FORGED_HOTMAIL_RCVD2,SPF_HELO_PASS,SPF_PASS,WHOIS_MYPRIVREG X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of lists@nabble.com designates 216.139.236.158 as permitted sender) Received: from [216.139.236.158] (HELO kuber.nabble.com) (216.139.236.158) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 28 Aug 2008 16:20:40 +0000 Received: from isper.nabble.com ([192.168.236.156]) by kuber.nabble.com with esmtp (Exim 4.63) (envelope-from ) id 1KYkFB-0003gL-GN for derby-user@db.apache.org; Thu, 28 Aug 2008 09:21:09 -0700 Message-ID: <19204059.post@talk.nabble.com> Date: Thu, 28 Aug 2008 09:21:09 -0700 (PDT) From: Iwud H8u To: derby-user@db.apache.org Subject: Re: Saving XML into a database In-Reply-To: <48B330E5.2000807@elfyard.com> MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Nabble-From: iwudh8u@hotmail.com References: <48B330E5.2000807@elfyard.com> X-Virus-Checked: Checked by ClamAV on apache.org 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 databas= e because my code generates these xml objects programatically. Any ideas on how to do this? Valentin Cozma wrote: >=20 > jay _ wrote: >> I've been struggling to find some decent examples of how to save XML=20 >> into a Derby database. >> >> I've managed to figure out that XML needs to be serialised into a=20 >> different datatype before it can saved in Derby. I am using a CLOB.=20 >> However, I am not quite sure how exactly to get the data into the XML=20 >> column. >> >> My sample code looks like this .... >> >> String expression =3D "INSERT INTO XML_DATA (DOCUMENT ) " + >> "VALUES (XMLPARSE( DOCUMENT CAST (? AS CLOB) PRESERVE=20 >> WHITESPACE))"; >> >> try >> { >> PreparedStatement preparedStatement =3D=20 >> connection.prepareStatement(expression); >> // update statement and execute >> // Element ele =3D getHomeViewElement(); >> logger.debug("xmlDocument =3D " +=20 >> xmlDocument.getDocument().toString()); >> preparedStatement.setString(1, xmlDocument.toString());=20 >> >> preparedStatement.executeUpdate(); >> >> } catch (SQLException e) { >> logger.warn(e); >> } >> >> Using either the Element or the Document toString() methods result in=20 >> an exception .... >> java.sql.SQLException: Invalid XML DOCUMENT: Content is not allowed in= =20 >> prolog. >=20 >=20 >=20 >=20 >=20 > I am inserting xml as blob ( binary ) . works like a charm . example=20 > follows . >=20 > -- write > public void addFileVersion(int idFile, String autor, String=20 > 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(); > } >=20 > -- read >=20 > public void saveFileVersionToDisk(int idFile, File location) throws= =20 > SQLException, IOException { > InputStream result =3D null; > getFileVersion.setInt(1, idFile); > ResultSet rs =3D getFileVersion.executeQuery(); > if(rs.next()) { > result =3D rs.getBinaryStream("bytes"); > } > if(result !=3D null) { > OutputStream of =3D new FileOutputStream(location); > FileUtils.copyFiles(result, of); > } > rs.close(); > } >=20 > -- table >=20 > create table file_versions( > id int NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START= =20 > WITH 1, INCREMENT BY 1), > id_file int not null constraint FK_FILE_VERSIONS__files=20 > references files(id) on delete cascade, > autor varchar(100) NOT NULL, > comentarii varchar(1000) NOT NULL, > bytes blob not null); >=20 >=20 >=20 >> >> Can someone suggest what I am doing wrong here? >> >> Cheers, >> Jay >> >> ------------------------------------------------------------------------ >> Be the filmmaker you always wanted to be=E2=80=94learn how to burn a DVD= with=20 >> Windows=C2=AE. Make your smash hit=20 >> >=20 >=20 >=20 --=20 View this message in context: http://www.nabble.com/Saving-XML-into-a-datab= ase-tp19149566p19204059.html Sent from the Apache Derby Users mailing list archive at Nabble.com.