Please login or register to access secure site features.

Note: By continuing to use DevConnect Program Services you agree to our latest Registered Member Terms.

Sign in using DevConnect ID

Forgot password?

Trouble logging in?

Submit a ticket for Registration Support.

I have an SSO ID

?
sign in

Don't have a DevConnect or SSO ID ?

Create a DevConnect account or join the program.

register now
^
Forum Index » Avaya Orchestration Designer » Manually editing the Database DBOP file   XML
 
Author Message
EricWainz3



Joined: 03/01/2014 13:26:38
Messages: 6
Offline

I updated the <SQLQuery> in the DBOP file so that it only returns back the TOP(5) records and I have to sort descending. This is a MSSQL Server connection.

I see the file edited but when the application executes it runs the default SQLQuery built from the Database Operation File wizard.

Any ideas?
[Email]
NeilGoldsmith



Joined: 06/11/2013 09:01:26
Messages: 902
Offline

You shouldn't edit that file directly. You can edit the Java code and change the SQL query

The class constructor after the //End tag, you can modify the setSQLQuery call

For example:
//}}END:CLASS:CONSTRUCTOR

setSQLQuery("SELECT Customer.Cust_ID, Customer.First_Name, Customer.Last_Name FROM dbo.Customer WHERE ( Customer.Cust_ID = ? )");
EricWainz3



Joined: 03/01/2014 13:26:38
Messages: 6
Offline

When I right click on the DBOP does not have an option to Edit Java under Orchestration Designer.
[Email]
EricWainz3



Joined: 03/01/2014 13:26:38
Messages: 6
Offline

I found it.
That worked.
Thanks.
[Email]
GayathriManickam



Joined: 20/03/2014 07:00:52
Messages: 14
Offline

Inside select dbop file is it possible to add an INSERT query after the //END CLASS CONSTRUCTOR line? Only select seems to work inside the select DBOP file. Is this the case or am I missing something? :?
WilsonYu



Joined: 06/11/2013 14:29:24
Messages: 3858
Offline

If it's a totally different query, I would suggest you start with the dbop wizard because you need to setup the parameters too.
GayathriManickam



Joined: 20/03/2014 07:00:52
Messages: 14
Offline

Thanks.
Inside the INSERT DBOP file after //END CLASS CONSTRUCTOR, i have written manual insert query with different table name.. it's not working.
If possible please post me sample code.
WilsonYu



Joined: 06/11/2013 14:29:24
Messages: 3858
Offline

Can you show me your change and the error? If you just changed the table name, I don't know why it wouldn't work. Essentially, the system just executes the query. You have to make sure the table definition is the same as the original one.
GayathriManickam



Joined: 20/03/2014 07:00:52
Messages: 14
Offline

Thanks.
I am using different table it contains different column name. It shows bind variable does not exist error.

setSQLQuery("INSERT INTO USE.FEEDBACKRESULT ( CLID, RESULT, UCID, STARTDATE, ENDDATE ) VALUES ( ?, ?, ?, ?, ? )");
addParameter(new com.avaya.sce.runtime.connectivity.db.DbQueryParam("CLID",0,12, "InsertDB:CLID"));

//}}END:CLASS:CONSTRUCTOR

setSQLQuery("INSERT INTO USE.FEEDBACKIVR ( QUESTIONNO) VALUES ( ? )");
addParameter(new com.avaya.sce.runtime.connectivity.db.DbQueryParam("QUESTIONNO",0,12, "Ins:ques"));
WilsonYu



Joined: 06/11/2013 14:29:24
Messages: 3858
Offline

You need change to a new set of parameters by doing something like this:

//}}END:CLASS:CONSTRUCTOR

List parameters = new ArrayList();
parameters.add(new com.avaya.sce.runtime.connectivity.db.DbQueryParam("QUESTIONNO",0,12, "Ins:ques"));
setParameters(parameters);
GayathriManickam



Joined: 20/03/2014 07:00:52
Messages: 14
Offline

Thank you so much Wilsonyu . your answer is really helpful to solve my issue.
It's working.

GayathriManickam



Joined: 20/03/2014 07:00:52
Messages: 14
Offline

In database manually editing data source is possible?
RossYakulis



Joined: 06/11/2013 08:46:50
Messages: 2652
Offline

try looking at "setConnectionProperties" a method in the base class DBQuery. The properties if set are passed to the database open (Database.open(connProperties))
GayathriManickam



Joined: 20/03/2014 07:00:52
Messages: 14
Offline

Hi,

Is it possible to execute database queries without dragging and dropping the Database Operation node in Orchestration Designer?

I have created a separate JAVA file to do this (without using the DBOP node), and am able to set and get the connection parameters using the ConnectionProperties class. But I am unable to execute the query and see the effect in the table. I also get no errors. Any help will be greatly appreciated. Thanks in advance.
RossYakulis



Joined: 06/11/2013 08:46:50
Messages: 2652
Offline

You can always write java code using the jdbc api and then store the results into OD variables yourself. I am not sure why you would want to do that.
 
 
Go to: