• How to pass multi column conditions to a SQL query in XPages using JDBC for external Database

    By cyrus mobaraki 1 decade ago

    Hello,

    Could you please help me out as I am stock how to give more condition to the following example.

    I would like to know how do you pass more conditions to where clause of the following SQL queries.

    1.  Is it possible to pass more condition to the following line of code that is for the where clause condition to the sql query bellow

          @JdbcUpdate("postgres","ressystem.users",v, "id=?" , id )

          @JdbcDelete("postgres","ressystem.users",v, "id=?" , id )

    How can I pass more columns to the following where clause, I don't know the format of Params:Array to allow me pass more  conditions to the (@JdbcDelete, @JdbcUpdate) i.e, if you  like to add the firstName column as the second conditions.

    @JdbcUpdate("postgres","ressystem.users",v, "id=?" , id )

    2.   Is it possible to join two tables with multi conditions to the sql query on Xpage file

     <xp:this.data>
    <    xe:jdbcQuery connectionName="postgres"
       var="jdbcData1" defaultOrderBy="id"sqlTable="ressystem.users"
           clearOnRendering="true"maxBlockCount="1">
    </    xe:jdbcQuery>
    </    xp:this.data>

    3.  Would it be possible to pass a parameter to the sql file "AllUsers.sql" for the where clause of the sql , like your example AllUsers.sql

    <xp:this.data>
    <    xe:jdbcQueryconnectionName="derby1"
    var="jdbcData1"defaultOrderBy="id"sqlFile="AllUsers.sql"
    calculateCount="true">
    </    xe:jdbcQuery>
    </    xp:this.data>

    &nbsp;

Many thanks for your time and help in advanced.

&nbsp;

Cyrus

    • Answers bellow

      By Philippe Riand 1 decade ago

      1- You can pass an array by using something like
         new Arrray(value1, value2, ...)
      Or you should be able to use any Java object that is either a Java array or a List

      2- Yes and the second data source should use a parameter that does the link.
      Now, the second data source has to refresh when it is rendered, so you should use the clearOnRendering parameter

      3- Yes, your query can contain a '?' and then you pass a parameter to it.

       

      • further question to your answers

        By cyrus mobaraki 1 decade ago

        Hi phil,

        Thanks for your tip but I have some open questions, can you please help me a bit more.

        I would like to know the syntax of the parameters for value1 and value2. new Arrray(value1, value2, ...)

        Imagine we have this sql statement, what is the syntax within the @JdbcUpdate or @JdbcDbColumn., Do I have to give the column name, the value and the comparison operator likes AND and OR and so on….

         

        UPDATE ressystem.users

           SET id=?, firstname=?, lastname=?, city=?, state=?

         WHERE (id = 10 and firstName = 'John')

         OR    (city = 'Zurich' and state = 'zh')

         ;

         

        Get user input

        var id = getComponent("recordId").getValue();

        var givenFirstName = getComponent("firstname").getValue();

         

        populating the array value for the sql Set.

         

        var v = {

                  id: id,

                  firstName: givenFirstName,

                  lastName: "Blob",

                  city: "Zurich",

                  state: "zh"

                 }

         

        My ambiguity on the syntax definition is as follow:

        How you do exactly write the above where clause as a parameter to the following line of code. I have done it in many different way but none of them is correct. Here some of my trial and error version.

         

        var myCond = new Array ("id:",id, "firstame:", givenFirstName);

        var myCond2 = new Array("id = ", id , "firstname = " , givenFirstName);

        var myCond3 = new Array();

        myArray2[0] = "id = " + id;

        myArray2[1] = "  and firstname = " + "'" + givenFirstName + "'";

         

        var c = @JdbcUpdate("postgres","ressystem.users", v ,  "id=?" , id , myCon  );

        var c = @JdbcUpdate("postgres","ressystem.users", v, myCon  );

         

        I also would like to know if we can do something like this.

         

        SELECT u.id, u.firstname, u.lastname, u.city, u.state, u.state_id, s.state_id, s.state, s.label

        FROM ressystem.users u , states s

        where u.state_id = s.state_id

        and u.id = 10

        or s.state like '%zh%'

         

        @JdbcDbColumn("postgres","ressystem.users","u.firstname")

        @JdbcDbColumn("postgres","ressystem.users???"," u.firstname???, s.state??? ")

         

         

         

        2.   Sorry I don’t follow your answer can you give me an example please, I am new in Lotus notes world and I am learning as I go further, I am very grateful for your time and help.

         <xp:this.data>
        <        xe:jdbcQuery connectionName="postgres"
           var="jdbcData1" defaultOrderBy="id"sqlTable="ressystem.users"
               clearOnRendering="true"maxBlockCount="1">
        xe:jdbcQuery>
        xp:this.data>

         

        3.  I have managed to see how you can pass parameter to a sql file but I have another questions.

        Is it possible to do something like this example in the sql file or outside of the sql file. I would like to do the (and) part if the second parameter is not null or empty screen.

        SELECT id, firstname, lastname, city, state

          FROM ressystem.users

          where id = ?

          if (? != null || ? != “”)

              and firstname = ?

         

        My example of Paramter to a file, it does work.

        <xp:this.data>

        <xe:jdbcQuery connectionName="postgres"

        var="jdbcData1" defaultOrderBy="ordnumber" sqlFile="RoomResource.sql"

        calculateCount="true">

        <xe:this.sqlParameters>

        <xe:sqlParameter>                       

        <xe:this.value>#{javascript:  var locID = "";

           if (viewScope.LocName){

              if (viewScope.LocName.indexOf(".") != -1){

        locId =   (viewScope.LocName.substr(0,viewScope.LocName.indexOf(".")));

              }else{

                    locId = (viewScope.LocName);

              }    

          }else{

              locId = 1;

          }

        parseFloat(locId) || ""}]]>xe:this.value>

        xe:sqlParameter>

        xe:this.sqlParameters>

        xe:jdbcQuery>

        xp:this.data>

         

        Thanks again fro your help.

        Cyrus

         

        • Some answers

          By Philippe Riand 1 decade ago

          JDBC parameter are set in a query using ? and are replaced by the values yuo pass to the query. Generally, they are a just a value you want to use in your query, sometime a column name if the rdbms permits it but they cannot (afaik) be an operator or a whole consition (c='xxx'). Please, look at your rdbms and JDBC doc.

          @JdbcUpdate uses a JS Object for the values (name list), and an JS array for the parameters (ordered list)
          This is not an arry, but a JS object:
          var v = {
          id: id,
          firstName: givenFirstName,
          lastName: "Blob",
          city: "Zurich",
          state: "zh"
          }

          This is an array:
          var a = new Array('a','b',c)
           

          @JdbcDbColumn("postgres","ressystem.users???"," u.firstname???, s.state??? ")
          No, at it requiresone column name. For more columns, you should use @JdbcExecute and process the result set

          3. This is related to your rbdms sql capability, please refer to its documentation

           

          • thanks

            By cyrus mobaraki 1 decade ago

            Thanks for your answer, I will give it another attempt to see if I can follow the examples.