Problem with embedded SQL in C/C++ (ECPG)

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!

embedded sql is not part of the c or c++ language. It appears that you are using a pre-processor (possibly Orical, Informix or some other database 4GL language) that converts the statements into code that a c or c++ compiler will understand. If that is the case, you will have to read the preprocessor's documentation about how to make that a variable.

Quote:

---

Originally Posted by Ancient Dragon

embedded sql is not part of the c or c++ language.

---

I realize that. I wasn't trying to say it was. As I said in my opening paragraph, I wasn't sure where to place this thread since it could easily fit in multiple forums. Since this particular embedded language is designed for C/C++, this forum seemed like a good place for it. If it doesn't fit in this forum, then I would be all for having it moved to a more appropriate place.

Quote:

---

It appears that you are using a pre-processor (possibly Orical, Informix or some other database 4GL language) that converts the statements into code that a c or c++ compiler will understand. If that is the case, you will have to read the preprocessor's documentation about how to make that a variable.

---

I'm using a library called ECPG. And yes it uses a pre-processor that converts the embedded SQL to C code.

The documentation doesn't mention how to handle variables in this instance, which is why I was asking the question to begin with. This particular library has some of the worst documentation I've ever seen. Unfortunately, I don't have a choice in the matter. I have to use this particular library.

Quote:

---

Originally Posted by NeuralClone

I realize that.

---

I thought you did, but just making sure we are both (and other readers too) on the same page.

Quote:

---

Originally Posted by NeuralClone

I'm using a library called ECPG. And yes it uses a pre-processor that converts the embedded SQL to C code.

The documentation doesn't mention how to handle variables in this instance, which is why I was asking the question to begin with.

---

You will probably have to find a board, mailing list, or discussion group specific to that library. Its very doubeful that anyone here will know anything abut it. Yes, they do have a mailing list that you will want to join.

Quote:

---

Originally Posted by Ancient Dragon

I thought you did, but just making sure we are both (and other readers too) on the same page.

---

I don't see any harm in that. :)

Quote:

---

You will probably have to find a board, mailing list, or discussion group specific to that library. Its very doubeful that anyone here will know anything abut it. Yes, they do have a mailing list that you will want to join.

---

Oddly enough, soon after you posted this, I found a solution. Someone in their IRC channel knew what was going on. Also, thank you for the link. :)

Anyway, if anyone is interested, here is the correct, working code:

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))
                {
                          this->qstatus();
                        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;
}

---

The important thing to realize is that you can't place variables directly in a string. So you have to compare them using the following:

Code:

---

WHERE someval LIKE '%' || :val || '%'

---

In standard SQL this would translate to:

Code:

---

WHERE someval LIKE %val%

---