Autocommit option using sql:connect to Oracle 11.2.0.3 with ojdbc6.jar
Friday, 12 August 2016 · 4 messages · last reply 15 Aug 2016
C
chrisis12 Aug 2016 14:51 #
I'm trying to set the autocommit option off and I can't seem to get it to work.
I'm passing the option to
sql:connect as a map
map { 'autocommit': false() }
I also tried map { 'autoCommit': false() } as the Oracle JDBC docs <http://docs.oracle.com/cd/E11882_01/appdev.112/e13995/constant-values.html#oracle_jdbc_OracleConnection_CONNECTION_PROPERTY_AUTOCOMMIT>seem to suggest.
Am I missing something obvious?
Regards,
Chris SweeneyCG
Christian Grün12 Aug 2016 15:19 #
Quoted text · chrisis · 6 lines
> I'm trying to set the autocommit option off and I can't seem to get it to > work. > I'm passing the option to sql:connect as a map > map { 'autocommit': false() } > I also tried map { 'autoCommit': false() } as the Oracle JDBC docs seem to > suggest.
What’s happening? Do you get any feedback?
Thanks
Christian
CG
Christian Grün14 Aug 2016 22:52 #
Hi Chris (cc to the list),
Quoted text · Christian Grün · 2 lines
> I've also attached the basexdebug.txt but it just shows the requests so > probably not useful.
Sounds ok so far.
I have slightly revised our SQL code (although it shouldn’t make a big difference), and I have uploaded a new snapshot [1]. Could you give it a try?
In general, our SQL wrapper is pretty simple [1,2]. Maybe you could check if you get autocommit=false running with a plain Java JDBC code snippet?
Hope this helps,
Christian
[1] http://files.basex.org/releases/latest/
[1] https://github.com/BaseXdb/basex/blob/9a700bf1f663b18eb578acb1a8aa8bbb4d4dd707/basex-core/src/main/java/org/basex/query/func/sql/SqlConnect.java#L56-L59 [2] https://github.com/BaseXdb/basex/blob/9a700bf1f663b18eb578acb1a8aa8bbb4d4dd707/basex-core/src/main/java/org/basex/query/func/sql/SqlFn.java
Quoted text · Christian Grün · 50 lines
> > Thanks, > > chris > > On 12 August 2016 at 14:46, Christian Grün <christian.gruen at gmail.com> > wrote: >> >> Could you try the -d command-line flag (or the command SET DEBUG true >> in the GUI) and check if you get some helpful feedback on stderr? >> >> >> On Fri, Aug 12, 2016 at 3:44 PM, chrisis <chrisisanon at gmail.com> wrote: >> > The connection is opened successfully.Not getting any feedback. >> > I'm doing a lot of inserts in a loop and it's committing after every >> > insert. >> > My issue is with the overhead of all the extra commits >> > >> > >> > >> > >> > >> > >> > Thanks, >> > >> > chris >> > >> > >> > On 12 August 2016 at 14:19, Christian Grün <christian.gruen at gmail.com> >> > wrote: >> >> >> >> > I'm trying to set the autocommit option off and I can't seem to get >> >> > it >> >> > to >> >> > work. >> >> > I'm passing the option to sql:connect as a map >> >> > map { 'autocommit': false() } >> >> > I also tried map { 'autoCommit': false() } as the Oracle JDBC docs >> >> > seem >> >> > to >> >> > suggest. >> >> >> >> What’s happening? Do you get any feedback? >> >> >> >> Thanks >> >> Christian >> > >> > > >
LV
Lizzi, Vincent15 Aug 2016 18:06 #
This is only tangentially related, but I have used the SQL module to connect to Oracle databases so I want to mention this in case it helps someone. The Oracle driver ojdbc7.jar seems to have trouble with complex SQL SELECT statements, and gives misleading error messages. When encountering problems with SELECT statements, the solution I’ve found is to add a wrapping select statement. For example:
SELECT * FROM ( …original query… )
The same occurs using ojdbc7 from Java, so this problem is related to the Oracle driver and is not specific to BaseX.
Vincent
Quoted text · basex-talk-bounces at mailman.uni-konstanz.de · 69 lines
From: basex-talk-bounces at mailman.uni-konstanz.de [mailto:basex-talk-bounces at mailman.uni-konstanz.de] On Behalf Of Christian Grün Sent: Sunday, August 14, 2016 4:53 PM To: chrisis <chrisisanon at gmail.com>; BaseX <basex-talk at mailman.uni-konstanz.de> Subject: Re: [basex-talk] Autocommit option using sql:connect to Oracle 11.2.0.3 with ojdbc6.jar Hi Chris (cc to the list), > I've also attached the basexdebug.txt but it just shows the requests so > probably not useful. Sounds ok so far. I have slightly revised our SQL code (although it shouldn’t make a big difference), and I have uploaded a new snapshot [1]. Could you give it a try? In general, our SQL wrapper is pretty simple [1,2]. Maybe you could check if you get autocommit=false running with a plain Java JDBC code snippet? Hope this helps, Christian [1] http://files.basex.org/releases/latest/<http://files.basex.org/releases/latest/> [1] https://github.com/BaseXdb/basex/blob/9a700bf1f663b18eb578acb1a8aa8bbb4d4dd707/basex-core/src/main/java/org/basex/query/func/sql/SqlConnect.java#L56-L59<https://github.com/BaseXdb/basex/blob/9a700bf1f663b18eb578acb1a8aa8bbb4d4dd707/basex-core/src/main/java/org/basex/query/func/sql/SqlConnect.java#L56-L59> [2] https://github.com/BaseXdb/basex/blob/9a700bf1f663b18eb578acb1a8aa8bbb4d4dd707/basex-core/src/main/java/org/basex/query/func/sql/SqlFn.java<https://github.com/BaseXdb/basex/blob/9a700bf1f663b18eb578acb1a8aa8bbb4d4dd707/basex-core/src/main/java/org/basex/query/func/sql/SqlFn.java> > > Thanks, > > chris > > On 12 August 2016 at 14:46, Christian Grün <christian.gruen at gmail.com<mailto:christian.gruen at gmail.com>> > wrote: >> >> Could you try the -d command-line flag (or the command SET DEBUG true >> in the GUI) and check if you get some helpful feedback on stderr? >> >> >> On Fri, Aug 12, 2016 at 3:44 PM, chrisis <chrisisanon at gmail.com<mailto:chrisisanon at gmail.com>> wrote: >> > The connection is opened successfully.Not getting any feedback. >> > I'm doing a lot of inserts in a loop and it's committing after every >> > insert. >> > My issue is with the overhead of all the extra commits >> > >> > >> > >> > >> > >> > >> > Thanks, >> > >> > chris >> > >> > >> > On 12 August 2016 at 14:19, Christian Grün <christian.gruen at gmail.com<mailto:christian.gruen at gmail.com>> >> > wrote: >> >> >> >> > I'm trying to set the autocommit option off and I can't seem to get >> >> > it >> >> > to >> >> > work. >> >> > I'm passing the option to sql:connect as a map >> >> > map { 'autocommit': false() } >> >> > I also tried map { 'autoCommit': false() } as the Oracle JDBC docs >> >> > seem >> >> > to >> >> > suggest. >> >> >> >> What’s happening? Do you get any feedback? >> >> >> >> Thanks >> >> Christian >> > >> > > >
Written for BaseX 8.5. BaseX may have changed since: please check the current documentation.
⚡Generated with XQuery