write data to oracle database

Version 1
    This document was generated from CDN thread

    Created by: Yao Hongjiao on 21-06-2013 01:37:16 AM
    hi everyone!
    when i add uccx script application. I want to write data to my own oracle database. 
    First, i configure the Database subsystem in Cisco Unified CCX Administration,when i click 'test Connection', showing sucess.
    Second, i create the script.
    When i run the script application, it runs sucessfully.But then i find my own oracle database has no data inserted. Who can tell me why?and what's the problem with my script application?
    Thank you very much!

    Subject: RE: write data to oracle database
    Replied by: Binny Mathew on 21-06-2013 02:14:43 AM
    Hi Yao,

    What is the return code from the Db Write Step [Hope you are using this method to write the data]? is it a Success or Failure? You can either put some logs of your own and check out or debug the script and check the same. This should throw some light as to what is going wrong. 

    Personally if i had to do something like the above i would write the piece of code in Java [In a "Do"  Block or in  a seprate Java file] so this will give you a better control on DB related queries and only a inclusion of a "OJDBC jar" file should suffice and no need of creating DSN.

    Having said that the steps you are using shoud also work. 

    Rgds/Binny

    Subject: RE: write data to oracle database
    Replied by: Yao Hongjiao on 23-06-2013 08:44:29 PM
    Binny Mathew:
    Hi Yao,

    What is the return code from the Db Write Step [Hope you are using this method to write the data]? is it a Success or Failure? You can either put some logs of your own and check out or debug the script and check the same. This should throw some light as to what is going wrong. 

    Personally if i had to do something like the above i would write the piece of code in Java [In a "Do"  Block or in  a seprate Java file] so this will give you a better control on DB related queries and only a inclusion of a "OJDBC jar" file should suffice and no need of creating DSN.

    Having said that the steps you are using shoud also work. 

    Rgds/Binny
    Hi!
    I use the Db Write Step to write data, and it returns none.
    If I would write data using "Do" Block, how can i do this? Can you descript in detail? Thank you very much!

    Best wishes!

    Subject: RE: write data to oracle database
    Replied by: Binny Mathew on 24-06-2013 12:42:13 AM

    Yao,


    Download Classes12.jar and include that as part of Custom Classpath and that shoud be fine. [You would require a restart of the services of Engine and Administratio].

    Include the below snippet in the Do Block. I have written this code with the package structure so that it would be easy for you.

    The code might not be in line as i just copy pasted the code from my eclipse editor. You can first put this in an eclipse editor and change the DB details and Query and check if all is working fine and record are getting inserted.

    Also try inclulding Log4j and put some logs so that the debuging process becomes easier. 

    {

    int returnCode= 0;String returnMessage = "SUCCESS";java.sql.Connection connection = null;java.sql.Statement stmt = null;java.sql.ResultSet rs = null;try{Class.forName("oracle.jdbc.driver.OracleDriver");try { connection = java.sql.DriverManager.getConnection(//"Driver :@Host Name: Port/SID","Username","Password""jdbc:oracle:thin:@172.16.6.106:1521/AVAYAORADB","citi","citi123");stmt = connection.createStatement();//Have your insert statement here.rs = stmt.executeQuery("INSERT INTO TBL_COUNTRY_MASTER(COUNTRY_CODE, COUNTRY_NAME) VALUES ('TR', 'Russia')");} catch (java.sql.SQLException e) {//Put Some log statementsreturnCode = 1;returnMessage = e.getMessage();}}catch(Exception e){//Put Some log statementsreturnCode = 1;returnMessage = e.getMessage();}

    }

    Let me know if this resolves your issue or if you need any help.

    Rgds/Binny