SqlFieldsQuery With Variable Number of Arguments

classic Classic list List threaded Threaded
4 messages Options
narges saleh narges saleh
Reply | Threaded
Open this post in threaded view
|

SqlFieldsQuery With Variable Number of Arguments

Hi All,

How do I set the arguments for the SqlFieldsQuery if I have a variable number of arguments?

E.g.,
String sql = <SQL>; // can have variable number of arguments, i.e., "?".
SqlFieldsQuery q =   new SqlFieldsQuery(sql);
q.setArgs(...);

I am sorry if this is a trivial question but I don't seem to be able to get it right. 

thanks

akorensh akorensh
Reply | Threaded
Open this post in threaded view
|

Re: SqlFieldsQuery With Variable Number of Arguments

Hi,
  Take a look at this example:
https://github.com/apache/ignite/blob/master/examples/src/main/java/org/apache/ignite/examples/sql/SqlQueriesExample.java
   
Use this as a template:

        String sql = "select * from Person where salary > ? and salary <=
?";

        // Execute queries for salary ranges.
        print("People with salaries between 0 and 1000 (queried with SQL
query): ",
            cache.query(new SqlFieldsQuery(sql).setArgs(0, 1000)).getAll());

        print("People with salaries between 1000 and 2000 (queried with SQL
query): ",
            cache.query(new SqlFieldsQuery(sql).setArgs(1000,
2000)).getAll());

Thanks, Alex



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
narges saleh narges saleh
Reply | Threaded
Open this post in threaded view
|

Re: SqlFieldsQuery With Variable Number of Arguments

Thanks Alex. 
I am not sure this answers my question.
My question: How do I dynamically set the arguments in setArgs of SqlFieldsQuery.

For example:
sql1 = "select a,b,c from table x where age >?"
sql2 = "select a,b,c from table x where age >? and salary < ?"
sql3 = "select a,b,c,d from table x  where dept = ? and age > ? and name = ? and city = ?"

As you see each sql has a different number of arguments. I want to be able to pass an array/collection to setArgs, and have it instantiate all the arguments in the SQL to the values that I am passing to setArgs.
For example, 
SqlFieldsQuery(sql3).setArgs("dev",30,"john", "NY"');
SqlFieldsQuery(sql1).setArgs(30);

I understand that setArgs has the following format.
public SqlFieldsQuery setArgs(Object... args)

thanks



On Mon, Jan 11, 2021 at 2:14 PM akorensh <[hidden email]> wrote:
Hi,
  Take a look at this example:
https://github.com/apache/ignite/blob/master/examples/src/main/java/org/apache/ignite/examples/sql/SqlQueriesExample.java

Use this as a template:

        String sql = "select * from Person where salary > ? and salary <=
?";

        // Execute queries for salary ranges.
        print("People with salaries between 0 and 1000 (queried with SQL
query): ",
            cache.query(new SqlFieldsQuery(sql).setArgs(0, 1000)).getAll());

        print("People with salaries between 1000 and 2000 (queried with SQL
query): ",
            cache.query(new SqlFieldsQuery(sql).setArgs(1000,
2000)).getAll());

Thanks, Alex



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
akorensh akorensh
Reply | Threaded
Open this post in threaded view
|

Re: SqlFieldsQuery With Variable Number of Arguments

Nargesh,
  If you need to use an array then the following works:

          SqlFieldsQuery query = new SqlFieldsQuery(
                "select concat(firstName, ' ', lastName) from Person where
firstName = ? and lastName = ? and salary >= ?");

        Object[] args = new Object[] { "John", "Doe", 1000};
        query.setArgs(args);

        // Execute query to get names of all employees.
        QueryCursor<List&lt;?>> cursor = cache.query(query);


     In your case, define an array for each distinct use case , fill it
w/arguments as appropriate, instantiate
   a SqlFieldsQuery object and query the cache.

   
Thanks, Alex



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/