4 Replies Latest reply: Jan 26, 2012 10:19 PM by 701304 RSS

    Getting error while invoking relational physical DS for sybase stored proc

    701304
      *com.bea.dsp.das.exception.DASException: com.bea.dsp.wrappers.rdb.exceptions.RDBWrapperException: {bea-err}RDBW0004: Error executing SQL query: [BEA][Sybase JDBC Driver][Sybase]SELECT INTO command not allowed within multi-statement transaction+.*

      I have created a physical DS function using "relational" in the wizard and connecting to sybase ASE stored procedure that is in unchained mode. I had configured the JDBC pool using BEA sybase non-XA 12.x driver.

      When i run the physical DS (or logical DS), i get the above error. The sybase srever(and stored proc) is hosted by an external system and right now i do not have access/contacts to change anything.

      After hours spent googling, it appears that the fix may possibly be setting "set chained off" before calling the stored proc. To achieve that, i tried the below two methods but nothing seems to work:

      - In the JDBC URL, set property via ?chained=off (also tried false instead of "off")
      - In Admin console/JDBC connection pool configuration/initSQL tried "SQL SET CHAINED OFF" and "SET CHAINED OFF"

      My ODSI version is 10gR3
      Oracle Workshop for WebLogic
      Version: 10.3
      Build id: 1137967

      I tried using java physical DS and get the same error. I wrote a test stored proc in my local PC sybase ASE and it runs fine. But i connected as "sa".

      I would like to exhaust all possible options from my side before making the long process of getting something changed in the external sybase DB like setting the proc mode to "anymode".

      Please help.

      Thank you.
        • 1. Re: Getting error while invoking relational physical DS for sybase stored proc
          Mikereiche-Oracle
          I wrote a test stored proc in my local PC sybase ASE and it runs fine.
          Did the test stored proc use "SELECT INTO"?

          Was the test stored proc tagged the same as the one that fails (chained/unchained?)

          Can you get jdbc code that successfully calls the stored proc using the datasource?

          BTW - if you used "SET CHAINED OFF" as initSql, unless you have a table named SET, the sql will fail and the datasource will not be created. So that casts some doubt on whether the other setting "SQL SET CHAINED OFF" was applied.

          Also - the connections are reset to default when they are returned to the connection pool, so I think "SQL SET CHAINED OFF" should be used as the "Test Table Name" and Test On Reserve should be checked.

          Edited by: mikereiche on Jan 26, 2012 3:36 PM
          • 2. Re: Getting error while invoking relational physical DS for sybase stored proc
            701304
            mikereiche wrote:


            Did the test stored proc use "SELECT INTO"?
            Yes. But it did not do much(code below)
            >
            Was the test stored proc tagged the same as the one that fails (chained/unchained?)
            Yes. "sp_procxmode" confirms that the test stored proc is in "unchained" transaction mode.
            >
            Can you get jdbc code that successfully calls the stored proc using the datasource?
            Yes, my colleague was trying the jdbc code and i was trying in the workshop with relational. She has left for the day so will post tomorrow. Please note that this is only for the test stored proc. The java jdbc also gets the same error for the "real" stored proc.
            >
            BTW - if you used "SET CHAINED OFF" as initSql, unless you have a table named SET, the sql will fail and the datasource will not be created. So that casts some doubt on whether the other setting "SQL SET CHAINED OFF" was applied.

            Also - the connections are reset to default when they are returned to the connection pool, so I think "SQL SET CHAINED OFF" should be used as the "Test Table Name" and Test On Reserve should be checked.
            Tried it now, but it does not work. I'm getting the same error.

            test stored proc:

            create procedure dbo.getCustomerMulti @cid_inp varchar(40)
            as
            begin
            select dbo.Customer.FirstName, dbo.Customer.cid, dbo.Customer.LastName, dbo.Customer.DateCreated, dbo.Customer.id into #temp from dbo.Customer
            select dbo.Customer.FirstName, dbo.Customer.cid, dbo.Customer.LastName, dbo.Customer.DateCreated, dbo.Customer.id from dbo.Customer where cid LIKE @cid_inp + '%'
            end


            Thank you.
            • 3. Re: Getting error while invoking relational physical DS for sybase stored proc
              Mikereiche-Oracle
              The java jdbc also gets the same error for the "real" stored proc.
              Is that using a jndi data source, or jdbc connection (using username/password/driver/url )?

              If it fails using the jndi data source, try a jdbc connection and then we can figure out how to make the datasource give equivalent connections. If it fails using the jdbc connection - you'll have to have the owners of the stored proc show you how to call it.


              You could also try setting SERVER_INITIATED_TRANSACTIONS=false on the jdbc url.

              Edited by: mikereiche on Jan 26, 2012 4:29 PM
              • 4. Re: Getting error while invoking relational physical DS for sybase stored proc
                701304
                >

                >
                Is that using a jndi data source, or jdbc connection (using username/password/driver/url )?
                She tried both.
                >
                >
                You could also try setting SERVER_INITIATED_TRANSACTIONS=false on the jdbc url.
                In jConnect 6 and versions below that, it did not accept the parameter. I insstalled jConnect 7 but getting the same error with this parameter set in jdbc url.

                I'll try to get in touch with the store proc developer. Thanks for your help. I'll post the answer if i figure it out.
                >
                Edited by: mikereiche on Jan 26, 2012 4:29 PM