7 Replies Latest reply: Feb 2, 2012 3:12 PM by 701304 RSS

    Passing null as input parameter for relational physical DS

    701304
      I'm creating a adapter(physical and logical) to talk to a Sybase Stored procedure. A few input integer arguments to the stored procedure are always null

      From Stored procedures - default NULL , I use xs:int(()) to pass in nulls. But for the 2nd argument($id),which has args with valid input following it, the stored procedure does not return any results. Executing the same stored proc with same inputs via Java JDBC or Sybase central gives the right results.

      But if i give a value for the 2nd argument ($id), the stored proc returns valid multi-row results indicating that the plumbing(my rowset schema..) works.

      In the below code, I have included the 2nd argument(id_inp) as an input to Logical just to be able to test using "setNull" check box. I also tried just passing "xs:int(())" without getting it as input into the logical.

      Physical:
      declare function f1:test_get($cid as xs:string, *$id as xs:int?*, $dt as xs:string, $tm as xs:string, $in1 as xs:int?, $in2 as xs:int?, $in3 as xs:int?, $in4 as xs:int?) as schema-element(t1:test_get) external;

      Logical:

      declare function tns:test_get($cid_inp as xs:string, $dt_inp as xs:string, $tm_inp as xs:string, *$id as xs:int?*) as element(ns1:OutputSchema)*{
      for $test_ret_get in f1:test_get($cid_inp, *$id*, $dt_inp, $tm_inp, xs:int(()), xs:int(()), xs:int(()), xs:int(()))
      return
      .....

      The sybase stored proc is declared as below: [ id, in1.in2,in3,in4 do not need to be passed in]
      create proc test_get
      @cid          varchar(32)=null,
      @id          int=null,
      @dt               varchar(8)=null,
      @tm               varchar(8)=null,
      @in1               int=null,
      @in2          int=null,
      @in3          int=null,
      @in4           int=0
      as

      Can i pass in null to an input argument followed by valid inputs to arguments following it?

      Is there any other way to send null?

      Thank you.
        • 1. Re: Passing null as input parameter for relational physical DS
          701304
          mikereiche wrote:
          First - it is impossible to optimize stored procedures, as sorting, joining and filtering cannot be "pushed" inside the stored procedure. For this reason, I encourage you to avoid using stored procedures in your solution.
          Unfortunately, this is an external Sybase DB stored proc that we need to call to get data.
          >
          The issue is that SQL syntax for querying for a null value ( ... where COL_A is null ) is not consistent with querying for a non-null value (... where COL_A = ? ), and for ODSI to support querying for null values, it would need to delay generating the sql until the value of the arguments was known, and also there would be the possibility of having 2^number_of_args different sql statements. So it does not execute queries where any of the arguments are null. (look at the audit for your query, there won't be any executions of the sql).
          The stored procedure seems to correctly handle the null input parameters since I get the correct results with Java JDBC.
          See the thread, Re: Search Functionality , the post by Jeff Hoffman at "Mar 8, 2010 12:24 PM"

          Jeff shows how to solve this problem when it occurs in a sql query (SELECT) - and this is useful for doing query-by-form - which, by the looks of it, is what you are after. To implement it for a stored query, you would need an extra "IsNull" arg for each arg that could be null,

          myProcedure( @lastName varchar(8), @lastNameIsNull int, @firstName varchar(8) @firstNameIsNull int)

          select from CUSTOMER where (LASTNAME = @lastName or @lastNameIsNull=1) and (FIRSTNAME = @firstName or @firstNameisNull=1)

          And you would call that from ODSI as below (using Jeff's ensureNotNull function).


          myProcedure( tns:ensureNotNull($lastName), fn:empty($lastName) , tns:ensureNotNull($firsttName), fn:empty($firstName) )

          You may note that when lastName is null, a bogus value of "" (empty-string) will be passed for the value of @lastName. This does not matter since LASTNAME = @lastName will have no effect in the query since @lastNameIsNull=1 is true.
          I'm probably missing something but it is different in my case that i want to pass in null and the stored proc already has the necessary check for null. It looks up a different table when this arg is null and gets the values needed for execution.

          I turned on the Audit for query parameters and it goes out as null for the 2nd argument. Its probably what i'm not getting from your explanation but I don't understand why it would behave differently compared to Java JDBC when it looks like the input parameters are the same. This also uses the JDBC DataSource created in Weblogic console.

          Audit log:

          rows: 0
          parameters:
          10000
          null
          12/06/11
          15:48:00
          null
          null
          null
          0
          • 2. Re: Passing null as input parameter for relational physical DS
            mikereiche
            I deleted my other posts as they only apply to SQL select statements. Sorry.

            Nulls can be passed to stored procedures without any issues. If you look at the audit when you specified id=999, it will look like this. There will be three null arguments followed by a non-null argument.

            rows: 1
            parameters:
            10000
            999
            12/06/11
            15:48:00
            null
            null
            null
            0

            So the only thing I can think of is that the stored procedure is actually returning 0 rows when id=null. Could you post the physical data services for the stored procedure and the java/jdbc code you're using to call it?
            • 3. Re: Passing null as input parameter for relational physical DS
              701304
              The problem seems to be with jdbc driver(jconn3.jar). It works fine with jConnect 7(jconn4.jar) and latest jTds drivers.

              I'm not using java but relational physical data service.
              • 4. Re: Passing null as input parameter for relational physical DS
                mikereiche
                If you must use the jconn3.jar driver, try adding IGNORE_DONE_IN_PROC=true to the jdbc url. Google on IGNORE_DONE_IN_PROC to see why I suggest that.
                • 5. Re: Passing null as input parameter for relational physical DS
                  701304
                  I tried IGNORE_DONE_IN_PROC=true and then also added SERVER_INITIATED_TRANSACTIONS=false but still does not help.

                  I thought i tried all the available drivers in ODSI but will try once more with this parameter set.
                  • 6. Re: Passing null as input parameter for relational physical DS
                    mikereiche
                    Why don't you use the jconn4.jar driver since it works?
                    • 7. Re: Passing null as input parameter for relational physical DS
                      701304
                      My mistake. It works fine with jconn3.jar without the IGNORE_DONE_ON_PROC. The problem is only with BEA Sybase drivers[weblogic.jdbc.sybase.SybaseDriver].