I wasn't quite sure where to put this since it applies to 3 different languages. My apologies if this is in the wrong place.
Anyway, I've been having a serious problem with embedded SQL in C/C++ using ECPG. I need to perform a SELECT and FETCH using a cursor. Everything has been working fine except for one major thing: string pattern matching. In my WHERE clause I need a statement like this:
This query works perfectly. However, I need "the value" to be a variable. If I try using a variable in place of "the value" it never returns a result.
WHERE UPPER(somevalue) LIKE UPPER('%the value%')
If this code is incorrect, how can I perform an equivalent query?
WHERE UPPER(somevalue) LIKE UPPER('%:val%')
The full code I'm using for this query is as follows:
Thanks in advance!
EXEC SQL INCLUDE sqlca;
// ... more code here ...
void CDatabase::findRoom(string search)
string bd, dir;
EXEC SQL BEGIN DECLARE SECTION;
const char * s = search.c_str();
EXEC SQL END DECLARE SECTION;
EXEC SQL BEGIN;
EXEC SQL DECLARE findRoom CURSOR FOR SELECT building, directions
WHERE UPPER(building) LIKE UPPER('%:s%') OR UPPER(directions) LIKE UPPER('%:s%')
ORDER BY building ASC, room ASC;
EXEC SQL OPEN findRoom;
EXEC SQL FETCH NEXT FROM findRoom INTO :b, :d;
if ((sqlca.sqlcode <= 0) || (sqlca.sqlcode == 100))
printf("error code %d, message %s, rows %d, warning %c\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrd, sqlca.sqlwarn);
cout << bd << SEPARATOR_STRING << dir << endl;
EXEC SQL CLOSE findRoom;
EXEC SQL COMMIT;