#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    1
    Rep Power
    0

    How to move a SELECT statement into a public static


    The Specs for this assignment is as follows:

    - **Locate all embedded sql statements and implement constants for embedded sql which does not already use constants.**
    - **Replace any string concatenation within Sql statement creation with String.format().**
    - **For any String.format commands you must escape out any existing % symbols. These symbols are often used in LIKE operations.**

    How can I move this SQL query into the 'public static String MY_QUERY' so that it will not be forward referencing and the 'match' and 'useFor' variables can be resolved. What I will do with the MY_QUERY is access it from a JUnit Test and then execute the query against a source and target database to make sure that the query performs the required query on both databases for data migration.


    package artemispm.autocalc;

    import java.sql.*;
    import java.util.*;
    import a7.unittests.dao.UnitTestHelper;
    import artemispm.serverutil.*;
    import artemispm.trdo.*;
    import artemispm.parser.*;

    public abstract class TRBaseScoreCalculator implements ExpressionParserLookup {

    /*****THIS IS THE NEW QUERY THAT WILL BE ACCESSED FROM JUNIT TEST****/

    public static String MY_QUERY =

    public Connection m_con;
    protected String m_characName = "";

    public boolean isThisCharacInUse(Connection con, String characName,String useFor)
    throws SQLException, TRException {
    boolean result;
    String match = "[" + TRBaseSql.rewrapQuotes(characName) + "]";

    if(TRBaseSql.getDatabaseType(con) == TRBaseSql.DBTYPESQLSERVER) {
    match = "[[]" + TRBaseSql.rewrapQuotes(characName) + "]";
    }

    TRSystemSQL sql = new TRSystemSQL();
    TRSystem sys=sql.getSystem(con);
    result = ( sys.getScoreCalculation() != null
    && sys.getScoreCalculation().indexOf(match) >= 0 );
    if (result) return(result);
    else {

    int count;

    /*****THIS IS THE PLACE I NEED HELP AT 12/5/2012 ******/
    /*I NEED TO SOMEHOW MOVE THE BELOW STATEMENT USING THE SPECS INTO MY_QUERY**/
    /**"select count(userfieldid) from tr_userfield where calcexpression like '%" + match + "%'and usefor like '%" + useFor +"%'"***/

    count = sql.executeGetInt(con, "select count(userfieldid) from tr_userfield where calcexpression like '%" + match + "%'and usefor like '%" + useFor +"%'" , null);

    if (count > 0) return true;
    count = sql.executeGetInt(con,
    "select count(characid) from tr_charac where calcexpression like '%" + match + "%'and usefor like '%" + useFor +"%'" , null);
    return (count > 0);
    }
    }
    }
  2. #2
  3. Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Aug 2010
    Location
    Eastern Florida
    Posts
    3,699
    Rep Power
    347
    Please edit the post and wrap the code in code tags.

IMN logo majestic logo threadwatch logo seochat tools logo