6 Replies Latest reply: Mar 19, 2014 12:15 AM by 8f80e349-a64e-4900-a69f-bdfb21db1786 RSS

    OSM DataInstance JDBC Adapter: Invalid SQL issue....

    8f80e349-a64e-4900-a69f-bdfb21db1786

      Hi,

      We are facing one issue while using the database adapter as DataInstance Behavior.


      Here are the scripts we used for the highlighted parameters:

       

      oms:datasource as XPATH: 'mslv/oms/oms1/internal/jdbc/DataSource'

      oms:sql as XQUERY:

      <instance name="OSM" xsi:type="externalInstanceType">

      <adapter>com.mslv.oms.view.rule.adapter.DatabaseAdapter</adapter>

      <parameter  name="oms:dataSource"> 'mslv/oms/oms1/internal/jdbc/DataSource'</parameter>

      <parameter name="oms:sql">

      "SELECT P.NODE_VALUE_TEXT FROM ORDERMGMT_OSMINSTALL.OM_ORDER_INSTANCE  P

      JOIN ORDERMGMT_OSMINSTALL.OM_ORDER_DATA_DICTIONARY PP

      ON P.DATA_DICTIONARY_ID=PP.DATA_DICTIONARY_ID

      where  PP.DATA_DICTIONARY_MNEMONIC='site_id' "

      </parameter>

      </instance>

       

      The following error logged in Logfile:

      <Feb 18, 2014 5:30:16 AM IST> <Error> <oms> <BEA-000000> <impl.j: failed to invoke adapter for externalInstanceType: name[DataInstance] class[com.mslv.oms.view.rule.adapter.DatabaseAdapter]

      1. com.mslv.oms.view.rule.adapter.AdapterException: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

       

              at com.mslv.oms.view.rule.adapter.DatabaseAdapter.retrieveInstance(Unknown Source)

              at oracle.communications.ordermanagement.rule.impl.j.getExternalInstance(Unknown Source)

              at oracle.communications.ordermanagement.rule.impl.j.findInstance(Unknown Source)

              at oracle.communications.ordermanagement.rule.impl.a.a(Unknown Source)

              at oracle.communications.ordermanagement.rule.impl.a.a(Unknown Source)

              Truncated. see log file for complete stacktrace

       

      Caused By: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

       

              at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)

              at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)

              at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)

              at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)

              at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)

              Truncated. see log file for complete stacktrace

       

      Can you pls suggest the remedy for this issue in the SQL? The same SQL runs well in TOAD and also shows the selected rows.

       

      Thanks in advance..

        • 1. Re: OSM DataInstance JDBC Adapter: Invalid SQL issue....
          Viktor.Jarolim-Oracle

          Hi when I tested this I entered the SQL in the design studio with single quotes.

           

          Where does your code snippet come from?

           

          When I build my cartridge, I then find something like this in my cartridgeBuild folder:

           

             <instance xsi:type="externalInstanceType"

              name="service_id_sequence">

              <adapter>com.mslv.oms.view.rule.adapter.DatabaseAdapter</adapter>

              <parameter name="oms:dataSource">&#039;mslv/oms/oms1/internal/jdbc/DataSource&#039;</parameter>

              <parameter name="oms:sql">&#039;select service_id_seq.nextval from dual&#039;</parameter>

              <cache>

               <scope>SYSTEM</scope>

               <timeout>15000</timeout>

               <maxSize>50</maxSize>

              </cache>

             </instance>

           

          But I don't know how you can map values from your order into the where clause of your stmt.

          You could probably do that by using the concat function and using xpath to designate the data from your order.

          • 2. Re: OSM DataInstance JDBC Adapter: Invalid SQL issue....
            8f80e349-a64e-4900-a69f-bdfb21db1786


            Hi Viktor,

            Thanks for your help.

            I have entered this script in datainstance properties tab in the dataprovider tab of the cartridge view(design studio). But I found only datainstance.xsd in my cartridgebuild->automation folder. Do you have any other file generated for this.

            I need to get thw list of data not from my current order, but from other order instances. In case of same order, I would have choose the xpath.

            I have changed the sql, but it also giving the error "com.mslv.oms.view.rule.adapter.AdapterException: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement".

            Here is the new SQL:

            <instance name="OSM" xsi:type="externalInstanceType">

            <adapter>com.mslv.oms.view.rule.adapter.DatabaseAdapter</adapter>

            <parameter  name="oms:dataSource">'mslv/oms/oms1/internal/jdbc/DataSource'

            </parameter>

            <parameter name="oms:sql">

            'SELECT P.NODE_VALUE_TEXT FROM ORDERMGMT_OSMINSTALL.OM_ORDER_INSTANCE  P

            JOIN ORDERMGMT_OSMINSTALL.OM_ORDER_DATA_DICTIONARY PP

            ON P.DATA_DICTIONARY_ID=PP.DATA_DICTIONARY_ID

            where  PP.DATA_DICTIONARY_MNEMONIC=?'

            </parameter>

            <parameter name="in:1">'site_id'</parameter>

            </instance>

             

            Pls suggest.

            • 3. Re: OSM DataInstance JDBC Adapter: Invalid SQL issue....
              Viktor.Jarolim-Oracle

              Now I think your syntax is correct for the SQL.

              But defining the behaviour you need to define the data provider first and then define the behaviour itself on the element in the task data.

              In fact, you need to define two behaviours.

              One is a data instance which references your data provider and the other is a calculate rule which will parse data out of the result returned by your SQL.

               

              Then, more generally, you should use the OSM web service adapter if you need data from other OSM orders, not the DB.

              And also, this statement would return a large number of rows, you need some condition describing the orders you want to retrieve from the DB in your where clause.

               

              e.g. if you need data from other orders with same value of the site element, this should be in the where clause.

               

              For a working example of calling the Osm Web Service Adapter, see order to activate cartridges which you can download from edelivery.

              It is invoked from orchestration, but invoking it from a behaviour is by and large the same.

              Hope it helps.

               

              In your cartridge build folder in the file mytaskname_view.xml you will see something like :

               

                      <viewRule xsi:type="calculateRuleType">

                          <annotation>

                              <documentation> </documentation>

                          </annotation>

                          <description>Calculation</description>

                          <expression>true()</expression>

                          <calculation>instance(&#039;service_id_sequence&#039;)/rowSet/row/nextval/text()</calculation>

                      </viewRule>

                      <viewRule xsi:type="dataInstanceRuleType">

                          <annotation>

                              <documentation> </documentation>

                          </annotation>

                          <description>service_id_sequence</description>

                          <instance xsi:type="externalInstanceType"

                              name="service_id_sequence">

                              <adapter>com.mslv.oms.view.rule.adapter.DatabaseAdapter</adapter>

                              <parameter name="oms:dataSource">&#039;mslv/oms/oms1/internal/jdbc/DataSource&#039;</parameter>

                              <parameter name="oms:sql">&#039;select service_id_seq.nextval from dual where dummy = ? &#039;</parameter>

                              <parameter name="in:1">&#039;X&#039;</parameter>

                              <cache>

                                  <scope>SYSTEM</scope>

                                  <timeout>15000</timeout>

                                  <maxSize>50</maxSize>

                              </cache>

                          </instance>

                      </viewRule>

               

              Viktor

              • 4. Re: OSM DataInstance JDBC Adapter: Invalid SQL issue....
                8f80e349-a64e-4900-a69f-bdfb21db1786

                Thanks Viktor.

                I have not tried the Webservice option due to the processing time to retrieve all the orders and then this particular field.

                 

                But I have tried both lookup as well as calculate behaviors.

                Now I am neither getting any exception in the log nor the update reflects in my Webclient task view. Have your view gets updated with the calculate behavior?

                I am using OSM SDK version [7.0.3.745].

                 

                My task_view.xml shows as following with lookup:

                 

                <viewNode element="b_end_site_id">

                    <editable>true</editable>  <minOccurs>1</minOccurs>

                    <maxOccurs>1</maxOccurs>

                   

                        <viewRule xsi:type="dataInstanceRuleType">

                            <description>DataInstance</description>

                            <instance xsi:type="externalInstanceType"

                                name="DataInstance">

                                <adapter>com.mslv.oms.view.rule.adapter.DatabaseAdapter</adapter>

                                <parameter name="oms:dataSource">&#039;mslv/oms/oms1/internal/jdbc/DataSource&#039;</parameter>

                                <parameter name="oms:sql">&#039;SELECT P.NODE_VALUE_TEXT FROM ORDERMGMT_OSMINSTALL.OM_ORDER_INSTANCE  P

                JOIN ORDERMGMT_OSMINSTALL.OM_ORDER_DATA_DICTIONARY PP

                ON P.DATA_DICTIONARY_ID=PP.DATA_DICTIONARY_ID

                where  PP.DATA_DICTIONARY_MNEMONIC=?&#039;

                </parameter>

                                <parameter name="in:1">&#039;site_id&#039;</parameter>

                                <cache>

                                    <scope>SYSTEM</scope>

                                    <timeout>15000</timeout>

                                    <maxSize>50</maxSize>

                                </cache>

                            </instance>

                        </viewRule>

                        <viewRule xsi:type="lookupRuleType">

                            <annotation>

                                <documentation>This behavior retrieves the inline XML defined for the data instance behavior and displays it  as a lookup field, which is the same as a dropdown list field,  in the Web client</documentation>

                            </annotation>

                            <description>LookUp</description>

                            <itemset>

                                <nodeset>instance(&#039;DataInstance&#039;)/rowSet/row/NODE_VALUE_TEXT</nodeset>

                                <value name="Value" openSelectionSeverity="CRITICAL" sortOrder="0" sortDirection="ascending" hidden="false" position="0">

                                    <node>text()</node>

                                </value>

                            </itemset>

                        </viewRule>

                   

                </viewNode>

                 

                With Calculate, it shows following:

                <viewNode element="a_end_site_id">

                    <editable>true</editable>  <minOccurs>1</minOccurs>

                    <maxOccurs>1</maxOccurs>

                   

                        <viewRule xsi:type="dataInstanceRuleType">

                            <description>DataInstance</description>

                            <instance xsi:type="externalInstanceType"

                                name="DataInstance">

                                <adapter>com.mslv.oms.view.rule.adapter.DatabaseAdapter</adapter>

                                <parameter name="oms:dataSource">&#039;mslv/oms/oms1/internal/jdbc/DataSource&#039;</parameter>

                                <parameter name="oms:sql">&#039;SELECT P.NODE_VALUE_TEXT FROM ORDERMGMT_OSMINSTALL.OM_ORDER_INSTANCE  P

                JOIN ORDERMGMT_OSMINSTALL.OM_ORDER_DATA_DICTIONARY PP

                ON P.DATA_DICTIONARY_ID=PP.DATA_DICTIONARY_ID

                where  PP.DATA_DICTIONARY_MNEMONIC=?&#039; </parameter>

                                <parameter name="in:1">&#039;site_id&#039;</parameter>

                                <cache>

                                    <scope>SYSTEM</scope>

                                    <timeout>15000</timeout>

                                    <maxSize>50</maxSize>

                                </cache>

                            </instance>

                        </viewRule>

                        <viewRule xsi:type="calculateRuleType">

                            <description>Calculation</description>

                            <calculation>instance(&#039;DataInstance&#039;)/rowSet/row/NODE_VALUE_TEXT/text()</calculation>

                        </viewRule>

                   

                </viewNode>

                • 5. Re: OSM DataInstance JDBC Adapter: Invalid SQL issue....
                  Viktor.Jarolim-Oracle

                  Hi try in your calculate / lookup to use node_value_text in lowercase.

                   

                   

                  I believe that when OSM transforms the result set into an xml document, it will use lowercase for the columns returned by the stmt.

                   

                   

                  See in my example "/rowSet/row/nextval/text()"

                   

                   

                   

                   

                   

                   

                  Also, your stmt will return more than one row (since it will return the value for all orders in the DB), this might possibly also lead to an error, try adding "and rownum = 1" into the where clause of your stmt to make sure this is not an issue and let me know if that helped.

                   

                   

                   

                   

                   

                   

                  As for the lookup, I think your syntax is incorrect, - but I would have to look up some old examples I have and don't have enough time to do it.

                   

                   

                  Try to make the calculate work by fixing the two issues I indicated above.

                   

                   

                   

                   

                   

                   

                  Viktor

                  • 6. Re: OSM DataInstance JDBC Adapter: Invalid SQL issue....
                    8f80e349-a64e-4900-a69f-bdfb21db1786

                    Thanks Victor.

                    I could resolve the issue with lookup..the issue was with the upper case of the column name..

                    And for lookup /rowSet/row/node_value_txt worked. Because in the value/name tab (of lookup properties) i have selected text() as node