How can you use parameterized statements with DB2 Text Search?

Question

I've tried this:

select * from ourschema.mytable 
    where contains(mysearchablefield, @searchTerms) = 1;

Where @searchTerms was set to "search terms"

Unfortunately, it only produced an error: ERROR [42610] [IBM][DB2/NT] SQL0418N A statement contains a use of a parameter marker that is not valid. SQLSTATE=42610

Is there a way to use parameterized queries for text search with DB2? If not, is there a document which describes the syntax in detail for manual (ugh) escaping of the search terms (quotes, etc)?

Answer 1

Instead of @field you need to use "?". Everything is basically the same.

Okay, here is a live code sample.

            sqlStmt = "SELECT COMPLAINT_NUMBER, VIOLATION_NUMBER, COMMON_ADDRESS_KEY, " +
            "DEPT_CODE, DEPT_CODE_DESC, DIVISION_CODE, DIVISION_CODE_DESC, " +
            "EMPLOYEE_NAME, COMPLAINT_CODE, COMPLAINT_CODE_DESC, COMPLAINT_DATE, " +
            "COMMON_ADDRESS_OWNER, RESOLUTION_CODE, 1 AS SORTORDER " +
            "FROM QMFILES/NVMASTP " +
            "WHERE VCLOSEDATE = 0 AND " +
            "DEPT_CODE LIKE @DEPT_CODE1 AND " +
            "DIVISION_CODE LIKE @DIVISION_CODE1 AND " +
            "COMPLAINT_DATE BETWEEN @FROM_COMPLAINT_DATE1 AND @TO_COMPLAINT_DATE1 " +
            statusQry +
            "UNION " +
            "SELECT COMPLAINT_NUMBER, VIOLATION_NUMBER, COMMON_ADDRESS_KEY, " +
            "DEPT_CODE, DEPT_CODE_DESC, DIVISION_CODE, DIVISION_CODE_DESC, " +
            "EMPLOYEE_NAME, COMPLAINT_CODE, COMPLAINT_CODE_DESC, COMPLAINT_DATE, " +
            "COMMON_ADDRESS_OWNER, RESOLUTION_CODE, 2 AS SORTORDER " +
            "FROM QMFILES/NVMASTP " +
            "WHERE VCLOSEDATE <> 0 AND " +
            "DEPT_CODE LIKE @DEPT_CODE2 AND " +
            "DIVISION_CODE LIKE @DIVISION_CODE2 AND " +
            "COMPLAINT_DATE BETWEEN @FROM_COMPLAINT_DATE2 AND @TO_COMPLAINT_DATE2 " +
            statusQry +
            "ORDER BY DEPT_CODE, DIVISION_CODE, COMPLAINT_CODE, SORTORDER";

        iDB2Command cmd = new iDB2Command(sqlStmt, conn);
        conn.Open();
        cmd.DeriveParameters();
        conn.Close();
        cmd.Parameters["@DEPT_CODE1"].Value = dept;
        cmd.Parameters["@DIVISION_CODE1"].Value = serviceArea;
        cmd.Parameters["@DEPT_CODE2"].Value = dept;
        cmd.Parameters["@DIVISION_CODE2"].Value = serviceArea;
        cmd.Parameters["@FROM_COMPLAINT_DATE1"].Value = Convert.ToDecimal(fromDateString);
        cmd.Parameters["@TO_COMPLAINT_DATE1"].Value = Convert.ToDecimal(toDateString);
        cmd.Parameters["@FROM_COMPLAINT_DATE2"].Value = Convert.ToDecimal(fromDateString);
        cmd.Parameters["@TO_COMPLAINT_DATE2"].Value = Convert.ToDecimal(toDateString);

I hope this helps you out more.