Topic
2 replies Latest Post - ‏2012-01-02T15:14:48Z by Tim_L
Tim_L
Tim_L
4 Posts
ACCEPTED ANSWER

Pinned topic Using a Variable Builder in a SQL Call Builder

‏2011-11-04T19:29:09Z |
Here is the situation:

I have 2 imported pages, FindForm and ListForm, each of which have a data page. I have a SQL Call builder that builds the schema for my FindForm, placing one field on the page. I put a value into the field, click a find button which invokes a LJO method that is passed the data from the find form, processes it, and returns a where clause to a variable.

Like this:
whereClause += inputName + " " + operator + " '" + inputValue + "'";
waa.getVariables().setString("whereClause", whereClause);

I immediately do a System.out of the ${Variables/whereClause} and it displays the correct value. The SQL to generate my ListForm is: select * from my_table ${Variables/whereClause}

I have a Return button on my ListForm that opens the FindForm once again. The ListForm displays properly the first time I run it, but if I return to the FindForm, enter a different value, the System.out displays the new value just fine, however the select that builds the ListForm uses the original value of the variable.

For example:
FindForm - Field: supplier_number, Value: 11111
Click find, and whereClause returned from LJO = "where supplier_number = '11111'" and the SQL to generate the ListForm looks like this: select * from my_table where supplier_number = '11111'
Click return button.
FindForm - Field: supplier_number, Value: 99999
Click find, and whereClause returned from LJO = "where supplier_number = '99999'" and the SQL to generate the ListForm looks like this: select * from my_table where supplier_number = '11111'

Its like the model is using a cached version of the whereClause variable! I have spent hours trying to get past this and am stumped. I have an example model I can provide, however it will need to be modified to use your own tables and data sources.

Thanks in advance for any hope offered!
Updated on 2012-01-02T15:14:48Z at 2012-01-02T15:14:48Z by Tim_L
  • SystemAdmin
    SystemAdmin
    532 Posts
    ACCEPTED ANSWER

    Re: Using a Variable Builder in a SQL Call Builder

    ‏2011-12-18T08:31:14Z  in response to Tim_L
    Hi .... i think you are asking about Custom SQL statements..

    do this steps ..

    1 - create variable builder and type your sql code without the where clause then put {?} or any String in the last of your Query.
    and make it request scope.
    ex :
    SELET * FROM MYTABLE
    {?}

    2 - then in the method builder you well call the arguments for each argument check if the argument is null or not.
    ex:
    String whereClause = "WHERE";
    if(!(arg1.equals("") && !(arg1 !== null))
    {
    whereClause += " AND ...."
    }
    .
    .
    // code complement .....

    3 - then in the last block of this method get the Orginial String SQL variable and replace the special String with whereClause.
    ex :
    //complement of the code above :
    //
    String currentSql = webAppAccess.getVariables().getString("orginalSqlhere");

    webAppAccess.getVariables().setString("orginalSqlhere", currentSql.replace("{?}", whereClause));

    4 - Testing the New SQL :
    String newSql = webAppAccess.getVariables().getString("orginalSqlhere");
    System.out.println("========== The New SQL HERE ==========="+newSql)

    Don't be afraid from loosing .... at least you have the honor of trying.
  • Tim_L
    Tim_L
    4 Posts
    ACCEPTED ANSWER

    Re: Using a Variable Builder in a SQL Call Builder

    ‏2012-01-02T15:14:48Z  in response to Tim_L
    When I submitted a PMR, the result was to explicitly set the query result to NULL prior to running the next query. It seems to work well.

    Added the following line to an Action List builder:

    Assignment!Variables/GetListStatement=${Java/null}