Database interaction using ADBC (JDBC Adapter)

Home Community Forums Questions about Apama EPL (Event Processing Language) Database interaction using ADBC (JDBC Adapter)

This topic contains 10 replies, has 2 voices, and was last updated by  sgolden 1 year, 3 months ago.

Viewing 11 posts - 1 through 11 (of 11 total)
  • Author
    Posts
  • #1155

    Marimuthu
    Participant

    Hi,

    Can someone provide me the steps to connect and exchange the data with Oracle database using ADBC bundle?

    • This topic was modified 1 year, 3 months ago by  Marimuthu.
    #1160

    sgolden
    Participant

    Hi Marimuthu,
    We recommend for JDBC connections that you use the supplied and tested DataDirect drivers.
    In an Apama Designer project you can follow the steps to create an ADBC Adapter

    Please see the following documentation topics:
    Using the Apama Database Connector: Adding an ADBC adapter to an Apama project
    Using the Apama Database Connector: Configuring the Apama database connector: Configuring an ADBC adapter
    Setting up SQL database connections: Direct JDBC connection

    In studio for Oracle12c you would configure the following for your JDBC Adapter (Replacing HOST and DATABASENAME as appropriate):
    Database Type – Oracle
    Database URL/DATABASE_LOCATION – jdbc:sag:oracle://HOST:1433;databaseName=DATABASENAME
    Driver/JDBC_DRIVER_NAME – com.apama.jdbc.oracle.OracleDriver
    Driver Classpath – Install_dir/Apama/lib/eyoracle.jar

    Hope this helps,
    Regards,
    Sharon

    #1161

    Marimuthu
    Participant

    Hi Sharon,

    Thanks for your response.

    I am able to configure JDBC adapter instance.

    But I was unable to perform any DB operation even after opening the DB connection. Please find the steps which I have implemented so far,

    1. Added ADBC Bundle to project.
    2. Configured the JDBC instance by filling the details in
    *General Properties
    *Advanced Properties
    *Variables
    3. Written a code in monitor file to open database connection using DBUtil package.

    db.setAdapterInstanceName(“JDBCAdapter1”);
    db.open(type, serviceId, URL, user, password, autoCommit, readOnly, batchSize,timeOut,handleError)

    4. Written a code for reverting data from database or insert/update/delete (doSQLCmd) records in database

    select : db.doSQLQueryAck(“SELECT * FROM EMPLOYEE;”,handleResult,ackId,false);
    DML : db.doSQLCmd(dbQryStr); db.doSQLCommit();

    5. Executed coded. Connection opened successfully and SQL command also executed but desire result is not reflecting in DB for DML operation and for select operation no records are retrieved.

    Is there anything missing here?

    It would be great if you share some example with steps for select and insert operation.

    Thanks,
    Marimuthu C

    #1162

    sgolden
    Participant

    Hi Marimuthu,
    Please Replace the URL with the following
    jdbc:sag:oracle://HOST:1433;SID=DATABASENAME
    where HOST and DATABASE are replaced with your settings.

    If you want the results of the query to appear in the correlator console window you should have something like the following in your implementation of handleResults.

    It’s not obvious what autoCommit is set to on opening the database, remember you only need to explicitly commit if auto commit is set to false.

    What is the dbQryStr that you are trying to execute in db.doSQLCmd(dbQryStr);?

    Regards,
    Sharon

    • This reply was modified 1 year, 3 months ago by  sgolden.
    #1164

    Marimuthu
    Participant

    Hi Sharon,

    I did the same. But Still its not working.

    • This reply was modified 1 year, 3 months ago by  Marimuthu.
    #1166

    sgolden
    Participant

    Hi Marimuthu,
    Can you post you EPL file and also the xml file with the database settings
    This can be found at YourWorkspace\ProjectName\bundle_instance_files\Adapter_for_JDBC\JDBCAdapter1.xml

    Regards,
    Sharon

    #1167

    Marimuthu
    Participant

    Hi Sharon,

    I am unable to attach/post EPL file as well as JDBC Adapter xml file. Can you please let me know, is there any other way to share these information with you?

    #1168

    sgolden
    Participant

    Hi Marimuthu,
    If you have fileZilla or similar software for ftp access you should be able to upload the files to http://ftp.softwareag.com

    Regards,
    Sharon

    #1170

    sgolden
    Participant

    Hi Marimuthu,
    Did you have any look uploading the files? if so what exactly should I be looking for, a project or specific files?
    Also have you had a look at the ADBC samples that are available under <INSTALL_DIR>sample/adbc specifically query-data and store-data.
    Another thing to check is in the IAF adapter log file to make sure there are no errors when opening the database/running the query etc…

    Regards,
    Sharon

    #1178

    Marimuthu
    Participant

    Hi Sharon,

    I need to perform DATABASE operations like SELECT , INSERT and UPDATE using com.apama.database.DBUtil and com.apama.database.DBAcknowledge.

    Will you able to give me the steps for the same?

    Note: I was unable to share the monitor and event definition files with you.

    #1179

    sgolden
    Participant

    Hi Marimuthu,
    Without seeing the logs I won’t be able to help you figure out why your current query command isn’t working
    What issues were you having uploading to the ftp site?

    Providing the connection to the database has succeeded you should be able to execute queries as you mentioned with the following:
    db.doSQLQueryAck(queryString, yourHandleResultMethod, ackId, false)
    or if you don’t want an acknowledgement the following:
    db.doSQLQuery(QUERY_STRING, yourHandleResultMethod)

    Inserts and updates are handled in commands such as the following:
    db.doSQLCmdAck(INSERT_STRING, ackId, false);
    or
    db.doSQLCmd(INSERT_STRING);

    Regards,
    Sharon

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic.