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

From: basex-talk-bounces@mailman.uni-konstanz.de [mailto:basex-talk-bounces@mailman.uni-konstanz.de] On Behalf Of Christian Grün
Sent: Sunday, August 14, 2016 4:53 PM
To: chrisis <chrisisanon@gmail.com>; BaseX <basex-talk@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/
[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



>
> Thanks,
>
> chris
>
> On 12 August 2016 at 14:46, Christian Grün <christian.gruen@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@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@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
>> >
>> >
>
>