Maximo Open Forum

Expand all | Collapse all

Rest API's and Database views

  • 1.  Rest API's and Database views

    Posted 14 days ago
    Is it possible to retrieve data from a database view using a REST API?
    #Customizations

    ------------------------------
    Brian Swanson
    Herman Miller
    ------------------------------


  • 2.  RE: Rest API's and Database views

    Posted 14 days ago
    Edited by Steven Shull 13 days ago
    Yes, and how will depend on whether or not Maximo is aware of the view (IE is it configured as an object inside of Maximo). If it's an object inside of Maximo you should create an object structure to process it. If Maximo is not aware of the view, but the Maximo account has access to the database view, you can write an Automation Script and invoke that automation script from the REST API. Section 14 of the REST API document covers calling a script from the REST API pretty well. Overview (ibm.com)

    If you've never written an automation script to query the database, using DBShortcut is probably the easiest approach. If you call the script from the REST API, you'll have an implicit variable of request which you can call request.getUserInfo() to get their userInfo which is necessary to get their connection key. We typically use python and wrap this with a try except finally to ensure that the database connection is closed when we're done. I've written a really basic shell of the code to execute a database query, but you'll at least need to build the responseBody implicit variable to return back to your code. 

    from psdi.mbo import DBShortcut,SqlFormat

    try:
        dbShortcut=DBShortcut()
        dbShortcut.connect(request.getUserInfo().getConnectionKey())
        sqfQuery=SqlFormat("SELECT * FROM asset WHERE siteid=:1 and status='OPERATING'")
        sqfQuery.setObject(1,"ASSET","SITEID","BEDFORD")
        resultSet=dbShortcut.executeQuery(sqfQuery.format())

        while resultSet.next():
            resultSet.getString("ASSETNUM")
    except:
        # put your error handling here, whether that's emailing or what. Be careful that it won't generate another exception though
    finally:
        dbShortcut.close()


    Edited to try and fix formatting of the code. Using the code snippet is removing all the line breaks for me.

    ------------------------------
    Steven Shull
    Projetech
    ------------------------------