Hello,
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:
Code:
SELECT somevalue
FROM mydb
WHERE UPPER(somevalue) LIKE UPPER('%the value%')
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.
Code:
SELECT somevalue
FROM mydb
WHERE UPPER(somevalue) LIKE UPPER('%:val%')
If this code is incorrect, how can I perform an equivalent query?
The full code I'm using for this query is as follows:
Code:
#include <iostream>
#include <sstream>
#include <cstdlib>
#include "db.h"
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();
VARCHAR b[100];
VARCHAR d[100];
EXEC SQL END DECLARE SECTION;
EXEC SQL BEGIN;
EXEC SQL DECLARE findRoom CURSOR FOR SELECT building, directions
FROM Rooms
WHERE UPPER(building) LIKE UPPER('%:s%') OR UPPER(directions) LIKE UPPER('%:s%')
ORDER BY building ASC, room ASC;
EXEC SQL OPEN findRoom;
while (true)
{
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[2], sqlca.sqlwarn[0]);
break;
}
else
{
bd.resize(0);
dir.resize(0);
bd.assign(b.arr, b.len);
dir.assign(d.arr, d.len);
cout << bd << SEPARATOR_STRING << dir << endl;
}
}
EXEC SQL CLOSE findRoom;
EXEC SQL COMMIT;
}
Thanks in advance!