BaseX-Talk

basex-talk@mailman.uni-konstanz.de

6 participants
5250 discussions

Oracle SQL set up for extracting XML Clobs
by Bednar, Scott E 26 Mar '14

26 Mar '14

Just sharing some information I've learned over the past week about using BaseX with Oracle using the SQL Module... in particular, to extract XML out of CLOB fields in Oracle you need to jump through a couple hoops: To get things set up, the following jars need to be on the classpath (I just put them into the BaseX lib directory which gets picked up if you start BaseX using one of the .bat scripts): - ojdbc6.jar - xdb6.jar - xmlparserv2.jar With this last jar, xmlparserv2.jar, there are a few things to watch for. First, you need a later version of the jar. Earlier versions are smaller (the jar for 11g is about 1350KB, the jar you have should probably be at least that big), and the filename isn't different for different versions. Be sure of what you have... you can find it in Oracle distributions here: {ORACLE_HOME}/oracle/produce/{VERSION_NUMBER}/lib/xmlparserv2.jar The jar can also be found under /lib in the Oracle client distributions. Next, you will need to reconfigure the out-of-the-box xmlparserv2.jar to use some standard java components rather than Oracle's: Change the entries in the files in the META-INF/services as follows: - for javax.xml.parsers.DocumentBuilderFactory replace oracle.xml.jaxp.JXDocumentBuilderFactory with com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderFactoryImpl - for javax.xml.parsers.SAXParserFactory replace oracle.xml.jaxp.JXSAXParserFactory with com.sun.org.apache.xerces.internal.jaxp.SAXParserFactoryImpl - for javax.xml.transform.TransformerFactory replace oracle.xml.jaxp.JXSAXTransformerFactory with com.sun.org.apache.xalan.internal.xsltc.trax.TransformerFactoryImpl The answer on this thread has complete instructions on reconfiguring the jar: http://stackoverflow.com/questions/11853967/errors-with-xmlparserv2-jar-and… Finally, you will need to add the following property setting to the startup script you are using (basex.bat, basexhttp.bat, basexgui.bat): -Doracle.jdbc.getObjectReturnsXMLType="false" This tells Oracle to return the java standard java.sql.SQLXML type that BaseX is expecting rather than the proprietary oracle.xdb.XMLType. See: http://docs.oracle.com/cd/E18283_01/java.112/e16548/jdbcvers.htm#BABGHBCC Here's how I added it to the script: set PROPS=-Doracle.jdbc.getObjectReturnsXMLType="false" java -cp "%CP%" "%PROPS%" %VM% org.basex.BaseX %* Once you have done all of that, you should be able to run an XQuery like this to get XML out of Oracle: sql:init("oracle.jdbc.OracleDriver"), let $conn := sql:connect("jdbc:oracle:thin:@myoracledb.org:1523:mydbsid", "username","password") return sql:execute($conn, "SELECT xmltype(MyXMLClob) from MyTable") If you get escaped XML in you results, you still need to reconfigure your xmlparserrv2.jar as shown above. As a side note: the above configuration is also necessary to make the basexhttp.bat script successfully start the server. If you use the un-configured xmlparserrv2.jar it will not start. Hope this helps! Cheers, Scott Bednar -----Original Message----- From: Bednar, Scott E Sent: Sunday, March 23, 2014 5:43 PM To: 'basex-talk(a)mailman.uni-konstanz.de' Subject: XML clob w/ SQL module I'm trying to extract XML stored in a CLOB from an oracle database using the sql module for BaseX. When I select the clob value, I get this sort of thing: <sql:column name="MSG">oracle.sql.CLOB@49458ef3</sql:column> I've tried casting it to XMLType (similar message) and to char (buffer size too small for the XML I'm dealing with). Is there a way of doing this short of creating a specialized Java module for it? Thanks, Scott

1 0

Re: [basex-talk] whitespaces in import
by Stefan Sechelmann 26 Mar '14

26 Mar '14

Thank you Dirk and also Leo for the quick response. I can work with that but I have to agree with Graydon that this was unexpected using the default settings. Best Stefan Stefan Sechelmann DFG-Forschungszentrum Matheon Mathematik für Schlüsseltechnologien Technische Universität Berlin Sekretariat MA 8-3 Tel. 030/314 29 486 Straße des 17. Juni 136 Fax 030/314 79 282 10623 Berlin sechel(a)math.tu-berlin.de http://www.math.tu-berlin.de/~sechel Am 26.03.2014 um 17:59 schrieb Dirk Kirsten <dk(a)basex.org>: > Hello Stefan, > > On 26/03/14 17:51, Stefan Sechelmann wrote: > Is there some kind of whitespace normalization >> going on during import? > > Yes, it is. > > Can I set options that influence this behavior or is this a bug? > > Yes, you can. Set the CHOP option to false (see > https://docs.basex.org/wiki/Options#CHOP for details) or start BaseX > with the -w flag (which sets CHOP to false). > > Hope that helps. > > Cheers, > Dirk > > -- > Dirk Kirsten, BaseX GmbH, http://basex.org > |-- Firmensitz: Blarerstrasse 56, 78462 Konstanz > |-- Registergericht Freiburg, HRB: 708285, Geschäftsführer: > | Dr. Christian Grün, Dr. Alexander Holupirek, Michael Seiferle > `-- Phone: 0049 7531 28 28 676, Fax: 0049 7531 20 05 22

1 0

whitespaces in import
by Stefan Sechelmann 26 Mar '14

26 Mar '14

Hi BaseX Team, this may be a bug: I have an xml file: <?xml version="1.0" encoding="UTF-8"?> <Test>foo <Inner/></Test> note the whitespace between the text foo and the node <Inner/>. Importing this into basex and running a query yields <Test>foo<Inner/></Test> without whitespace. Is there some kind of whitespace normalization going on during import? Can I set options that influence this behavior or is this a bug? Best regards Stefan Stefan Sechelmann DFG-Forschungszentrum Matheon Mathematik für Schlüsseltechnologien Technische Universität Berlin Sekretariat MA 8-3 Tel. 030/314 29 486 Straße des 17. Juni 136 Fax 030/314 79 282 10623 Berlin sechel(a)math.tu-berlin.de http://www.math.tu-berlin.de/~sechel

2 1

possible bug - using inline functions in unit tests
by Jan Techter 25 Mar '14

25 Mar '14

Dear basex team, running the following unit test involving a simple anonymous function declare %unit:test function local:test-inline-function() { let $f := function($x) {$x+1} return unit:assert-equals( $f(5), 6) }; raises a java exception [exec] Improper use? Potential bug? Your feedback is welcome: [exec] Contact: basex-talk(a)mailman.uni-konstanz.de [exec] Version: BaseX 7.8 [exec] Java: Oracle Corporation, 1.7.0_51 [exec] OS: Linux, i386 [exec] Stack Trace: [exec] java.lang.ClassCastException: org.basex.query.value.type.AtomType cannot be cast to org.basex.query.value.type.FuncType [exec] at org.basex.query.func.InlineFunc.item(InlineFunc.java:261) [exec] at org.basex.query.func.InlineFunc.value(InlineFunc.java:280) [exec] at org.basex.query.QueryContext.value(QueryContext.java:367) [exec] at org.basex.query.gflwor.Let$LetEval.next(Let.java:230) [exec] at org.basex.query.gflwor.GFLWOR$2.next(GFLWOR.java:73) [exec] at org.basex.query.iter.Iter.value(Iter.java:64) [exec] at org.basex.query.expr.ParseExpr.value(ParseExpr.java:71) [exec] at org.basex.query.QueryContext.value(QueryContext.java:367) [exec] at org.basex.query.func.StaticFunc.invValue(StaticFunc.java:203) [exec] at org.basex.query.func.FuncCall.invoke(FuncCall.java:96) [exec] at org.basex.query.func.FuncCall.value(FuncCall.java:138) [exec] at org.basex.query.func.StaticFunc.invokeValue(StaticFunc.java:215) [exec] at org.basex.query.util.unit.Unit.eval(Unit.java:210) [exec] at org.basex.query.util.unit.Unit.test(Unit.java:127) [exec] at org.basex.query.util.unit.Unit.test(Unit.java:51) [exec] at org.basex.query.func.FNUnit.test(FNUnit.java:100) [exec] at org.basex.query.func.FNUnit.item(FNUnit.java:40) [exec] at org.basex.query.expr.ParseExpr.iter(ParseExpr.java:46) [exec] at org.basex.query.MainModule.iter(MainModule.java:96) [exec] at org.basex.query.QueryContext.iter(QueryContext.java:310) [exec] at org.basex.query.QueryProcessor.iter(QueryProcessor.java:81) [exec] at org.basex.core.cmd.AQuery.query(AQuery.java:89) [exec] at org.basex.core.cmd.XQuery.run(XQuery.java:22) [exec] at org.basex.core.Command.run(Command.java:329) [exec] at org.basex.core.Command.execute(Command.java:94) [exec] at org.basex.server.LocalSession.execute(LocalSession.java:121) [exec] at org.basex.server.Session.execute(Session.java:37) [exec] at org.basex.core.Main.execute(Main.java:146) [exec] at org.basex.BaseX.<init>(BaseX.java:119) [exec] at org.basex.BaseX.main(BaseX.java:38) [exec] updating to basex 7.8.1 didnt make any difference. Is this a bug? Best regards. Jan

4 9

XML clob w/ SQL module
by Bednar, Scott E 25 Mar '14

25 Mar '14

I'm trying to extract XML stored in a CLOB from an oracle database using the sql module for BaseX. When I select the clob value, I get this sort of thing: <sql:column name="MSG">oracle.sql.CLOB@49458ef3</sql:column> I've tried casting it to XMLType (similar message) and to char (buffer size too small for the XML I'm dealing with). Is there a way of doing this short of creating a specialized Java module for it? Thanks, Scott

2 3

Re: [basex-talk] multi-threaded XQueries
by Christian Grün 24 Mar '14

24 Mar '14

Hi Ying, > e.g. the dataset I use is DBLP, one of the example query is > > for $x in db:open('dblp_2013')/dblp/article[child::pages and child::title] > let $y := count($x/author) > return concat($y, "/t", db:node-id($x)) Yes, I agree there is not much that can be done to speed up this query. > It is true that these queries are competing for the same resource. > So you mean that each query causes a lot of disk I/O? Exactly. All DBLP articles need to be parsed by this query, and > 1 million result strings will be generated, resulting in a query time of appr. 0.005 ms per result. Hope this helps, Christian > However all these queries are read-only. It there any room to improve this? > -- > Regards > > Shanshan > > School of Computing > National University of Singapore

2 3

BaseX: Upcoming Features
by Christian Grün 24 Mar '14

24 Mar '14

Dear BaseX aficionados, An upcoming version of our software (7.9 or 8.0) will make it much easier to perform updates and return items at the same time [1]. It will also contain an xquery:update function and will bring xquery:eval and xquery:evaluate together again [2] Another exciting and sophisticated feature we are working on is query precompilation [3], which will particularly speed up large XQuery applications and allow for more request-specific optimizations. Next, we are working on a Database Administration interface based on RESTXQ, and we will also drop out the confusing client/server dialog from the GUI. Feature 1 and 2 are already available via a feature branch [4]. If you are working with the git sources, we are excited to get your feedback! Christian [1] https://github.com/BaseXdb/basex/issues/873 [2] https://github.com/BaseXdb/basex/issues/878 [3] https://github.com/BaseXdb/basex/issues/874 [4] https://github.com/BaseXdb/basex/tree/issue873

3 6

multi-threaded XQueries
by Ying Shanshan 23 Mar '14

23 Mar '14

Hi All, I created one database of 1GB in size, and have a batch of *read-only*queries to be performed. Each query is quite simple, so there is not much room to improve. All the queries are executed through *XQJ api.* If these queries are sequentially executed, it costs 5~8 seconds per query. To speed it up, I tried to executed it in a multi-thread way. But turns out to be much slower than the sequential one. This is what I wrote for each thread: public void run() { try { XQConnection conn = ds.getConnection(); XQSequence rs = conn.prepareExpression(input) .executeQuery(); //process the query result conn.close(); } } catch (Throwable e) { e.printStackTrace(); } } Anyone has the idea on how to improve the batch of queries. Thanks, -- Regards Shanshan School of Computing National University of Singapore

2 1

Confusing error using GROUP BY
by Charles Duffy 23 Mar '14

23 Mar '14

Howdy -- I've run into a somewhat confusing error message using GROUP BY in BaseX 7.8.1, while trying to answer a question on StackOverflow ( http://stackoverflow.com/questions/22585865/grouping-with-counts-in-xquery). Against the database at http://www.ibiblio.org/xml/examples/shakespeare/hamlet.xml, I'm running the following query: for $line in //SPEECH/LINE let $speaker := $line/../SPEAKER/text()[1] group by $speaker return <speaker name="{$speaker}" count="{count($line)}"/> ...and receiving the error: [XPTY0004] Single item expected, (db:open-pre("hamlet",748), db:open-pre("hamlet",750)) found. I've gone over the GROUP BY docs several times, and not seeing my error in the above. Could someone enlighten?

2 1

Re: [basex-talk] pearl pigments
by tony-zhao＠vip.188.com 22 Mar '14

22 Mar '14

1 0

Jump to page:

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

BaseX-Talk