How to use Oracle connector?

Q&A
1

I followed the database connector wizard to run a SELECT on my local Oracle XP 10g database but all I get are 'invalid SQL batch' messages.

The system suggests you just enter an SQL statement.

If anyone has some instructions for running an SQL statement from an Oracle connector and returning the result to a Bonita variable, that would b e great.

(I did post a similar query to the legacy forum last week but haven't had any reply so thought I would try the new questions and answers section.

 

Thanks in advance,

Michael

2

Hi Michael,

I know that feeling. I've been through this problem in the last three weeks.

First of all, what version of Bonita you are using?

I realized that even the most simple select need a groovy complement to run and to work with the form. And you'll need the proper connectors. I can help you with the few things I've learnt, just keep updating this post if you can.

3

I'm using the latest 6.0.4 community version with the Oracle 10g jdbc connector which uses the ojdbc6.jar.

I guess you can't just enter a SELECT and have to write something in groovy.

Just to test out the conenctivity, I tried a simple SELECT returning a single field, although that would be returned as a resultset.

The next screen of the wizard allows you to assign the result to a variable. I set up a string variable and couldn't work out how to get the first element of the resultset into it.

But since the SELECT gave the 'invalid SQL batch command' (or something similar), I never got to the next step.

Perhaps you could share what you had learnt?

4

Hi Michael,

Yes, I'll share with you what I've learnt. I'm finishing a server with the new version of bonita (6.0.4) and I'll need to set some selects in my database.

You could start using this script to test your conncetion. It works, for sure, but it's kinda groovy stuff and is better used when you set it as a value from a variable in your form. I'm sharing a picture to try helping you:

The script I used:

import groovy.sql.Sql

String result  = ''

Sql sql = BonitaSql.newInstance("jdbc:oracle:thin:@MYIPADRESS:1521:INSTANCE", "USER", "PASSWORD",new oracle.jdbc.driver.OracleDriver())

sql.eachRow 'SELECT FIELD FROM (SELECT FIELD FROM TABLE ORDER BY FIELD DESC) WHERE ROWNUM = 1', { result += it.FIELD }

sql.close()

result

Sorry for my image, it's in portuguese, but you'll realize the fields through the position of them.

The bold variables are the ones you should use your own.

You can use a simple select, of course.

Hope you can make it work for your own test.

PS: I'm using ojdbc14.jar