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)
Date: Thu, 28 Aug 2008 09:37:09 -0700
From: Craig L Russell <Craig.Russell@Sun.COM>
Subject: Re: Saving XML into a database
In-reply-to: <19204059.post@talk.nabble.com>
Sender: Craig.Russell@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <3F22B710-2BF9-47D1-909A-7C3182F47E7C@SUN.com>
MIME-version: 1.0
Content-type: multipart/signed; protocol="application/pkcs7-signature";
 micalg=sha1; boundary=Apple-Mail-10--1031422286
References: <BAY113-W227AFEE2ED61E6D53F31B8E56A0@phx.gbl>
 <48B330E5.2000807@elfyard.com> <19204059.post@talk.nabble.com>


--Apple-Mail-10--1031422286
Content-Type: text/plain;
	charset=WINDOWS-1252;
	format=flowed;
	delsp=yes
Content-Transfer-Encoding: quoted-printable

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 =20
InputStream that will deliver the ascii version of your xml document =20
from memory. Not knowing what interface your xml dom objects support, =20=

you might be able to write the dom as a stream to a =20
ByteArrayOutputStream, take the byte[] result and create a =20
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 =20=

> saved
> into a database... If I adopt your approach, I will need to save my =20=

> DOM
> objects to a file before reading them in...
>
> I am looking for a way to insert in memory XML DOM objects into the =20=

> 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 =20=

>>> XML
>>> column.
>>>
>>> My sample code looks like this ....
>>>
>>>        String expression =3D "INSERT INTO XML_DATA (DOCUMENT ) " +
>>>                "VALUES (XMLPARSE( DOCUMENT CAST (? AS CLOB) PRESERVE
>>> WHITESPACE))";
>>>
>>>        try
>>>        {
>>>            PreparedStatement preparedStatement =3D
>>> connection.prepareStatement(expression);
>>>            // update statement and execute
>>> //            Element ele =3D getHomeViewElement();
>>>            logger.debug("xmlDocument =3D " +
>>> 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 =20=

>>> in
>>> an exception ....
>>> java.sql.SQLException: Invalid XML DOCUMENT: Content is not =20
>>> 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) =20
>> throws
>> 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();
>>    }
>>
>> -- table
>>
>> create table file_versions(
>>        id int NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY =20
>> (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=97learn how to burn a DVD =20=

>>> with
>>> Windows=AE. Make your smash hit
>>> <http://clk.atdmt.com/MRT/go/108588797/direct/01/>
>>
>>
>>
>
> --=20
> 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!


--Apple-Mail-10--1031422286
Content-Disposition: attachment;
	filename=smime.p7s
Content-Type: application/pkcs7-signature;
	name=smime.p7s
Content-Transfer-Encoding: base64

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGUDCCAwkw
ggJyoAMCAQICECvOQSuIjHMvOZRC95BRg/wwDQYJKoZIhvcNAQEFBQAwYjELMAkGA1UEBhMCWkEx
JTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQ
ZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA3MTIxMDE1MjM1MVoXDTA4MTIwOTE1MjM1
MVowbDEQMA4GA1UEBBMHUnVzc2VsbDEUMBIGA1UEKhMLQ3JhaWcgTGFpcmQxHDAaBgNVBAMTE0Ny
YWlnIExhaXJkIFJ1c3NlbGwxJDAiBgkqhkiG9w0BCQEWFUNyYWlnLlJ1c3NlbGxAU3VuLkNPTTCC
ASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAKzqGlLUastboCRqc0iBoTz2ODcqpMpEyPUo
nYtluchkSIoWzOW63AuoTczRt9sKfhwoK5mope+62B6Li06WJabm2UHqKAaNSuMHLsmyqvOdwbSt
enY7/HxOSCMqVoyVBTRJc2M8feCSVgi7ptGq9cM+Maa64R1/p9zqaQNucceU/1uper90bWplsjAT
rHgicgr9XJIQb6uYjhjlgxxnY/aispnCvLxMX+CiA2FWeeJTI7AiFlLwibTXYF4v12ToByvXtTiJ
knuND8qpwhK3Wp0tL4ae8mZ0nlKjCuNnqh99ZyEyTFHZBfVx8WSWRXkY4qxCG/IDQUo7WUaefOQT
1mECAwEAAaMyMDAwIAYDVR0RBBkwF4EVQ3JhaWcuUnVzc2VsbEBTdW4uQ09NMAwGA1UdEwEB/wQC
MAAwDQYJKoZIhvcNAQEFBQADgYEAEqfFNFoch0QPVKWJ4maAZl3MJD10yMeWt5xb+WNSkhYKHD8I
42E8tpdE3kmc5wp2cZrz9JqJF/KCQ/gI4pmDk1qpTs5pvXzFNiD5Lu5eLza4iyxSlTHUXcCnyNC6
4m0qC8p4m/51NEql5hyacj/+vdlEe5dygpyNGUCiyA/SdAswggM/MIICqKADAgECAgENMA0GCSqG
SIb3DQEBBQUAMIHRMQswCQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBlMRIwEAYDVQQH
EwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYDVQQLEx9DZXJ0aWZp
Y2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUgUGVyc29uYWwgRnJlZW1h
aWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRoYXd0ZS5jb20wHhcNMDMw
NzE3MDAwMDAwWhcNMTMwNzE2MjM1OTU5WjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3Rl
IENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWls
IElzc3VpbmcgQ0EwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAMSmPFVzVftOucqZWh5owHUE
cJ3f6f+jHuy9zfVb8hp2vX8MOmHyv1HOAdTlUAow1wJjWiyJFXCO3cnwK4Vaqj9xVsuvPAsH5/Ef
kTYkKhPPK9Xzgnc9A74r/rsYPge/QIACZNenprufZdHFKlSFD0gEf6e20TxhBEAeZBlyYLf7AgMB
AAGjgZQwgZEwEgYDVR0TAQH/BAgwBgEB/wIBADBDBgNVHR8EPDA6MDigNqA0hjJodHRwOi8vY3Js
LnRoYXd0ZS5jb20vVGhhd3RlUGVyc29uYWxGcmVlbWFpbENBLmNybDALBgNVHQ8EBAMCAQYwKQYD
VR0RBCIwIKQeMBwxGjAYBgNVBAMTEVByaXZhdGVMYWJlbDItMTM4MA0GCSqGSIb3DQEBBQUAA4GB
AEiM0VCD6gsuzA2jZqxnD3+vrL7CF6FDlpSdf0whuPg2H6otnzYvwPQcUCCTcDz9reFhYsPZOhl+
hLGZGwDFGguCdJ4lUJRix9sncVcljd2pnDmOjCBPZV+V2vf3h9bGCE6u9uo05RAaWzVNd+NWIXiC
3CEZNd4ksdMdRv9dX2VPMYIDEDCCAwwCAQEwdjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhh
d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVt
YWlsIElzc3VpbmcgQ0ECECvOQSuIjHMvOZRC95BRg/wwCQYFKw4DAhoFAKCCAW8wGAYJKoZIhvcN
AQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMDgwODI4MTYzNzEwWjAjBgkqhkiG9w0B
CQQxFgQUWdc+WfBB+KlS7iHE7dmghAqG+4AwgYUGCSsGAQQBgjcQBDF4MHYwYjELMAkGA1UEBhMC
WkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0
ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhArzkEriIxzLzmUQveQUYP8MIGHBgsqhkiG
9w0BCRACCzF4oHYwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQ
dHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhAr
zkEriIxzLzmUQveQUYP8MA0GCSqGSIb3DQEBAQUABIIBAHuN2rX8HCDZ4oa7kKhleFkgBZpwnWYK
by7i7ZABJDLxD8nDfU/D5rLmSsOUh6/NkSB2hs3QwQ6pDsvselu8tdXRqfO9xZxUVBDN0syWQnwz
jxcHN/WSE3l/amnQ2netDkPeZMqydJcwadH6hOE/U7y77sOeVran8ffmVKSJjiFg54tibeAEHAK+
q2d4gUv+984ZOTT9S5xC6M8HyLhI6SCHJBTwfQu56CPyIoJ/w/ZDE0xqBna4bhPZQAXbQCY7gafe
5RqxgyoX1jw2iww/3ks7AAdALTAKcCQefx7TEozVhprA9/95FOyrxa+gLWjnaz3rRe0sGWBDQa04
CffPXRsAAAAAAAA=

--Apple-Mail-10--1031422286--