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

  1. #1
    Registered User
    Join Date
    Jul 2005
    Posts
    3

    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!

  2. #2
    Registered User
    Join Date
    Aug 2005
    Posts
    1,267
    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.

  3. #3
    Registered User
    Join Date
    Jul 2005
    Posts
    3
    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.

    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.

  4. #4
    Registered User
    Join Date
    Aug 2005
    Posts
    1,267
    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.

  5. #5
    Registered User
    Join Date
    Jul 2005
    Posts
    3
    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.

    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%

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Embedded SQL
    By sarac in forum C Programming
    Replies: 1
    Last Post: 05-04-2006, 09:09 AM
  2. Replies: 1
    Last Post: 03-21-2006, 07:52 AM
  3. Bin packing problem....
    By 81N4RY_DR460N in forum C++ Programming
    Replies: 0
    Last Post: 08-01-2005, 05:20 AM
  4. Replies: 5
    Last Post: 12-03-2003, 05:47 PM
  5. problem with output
    By Garfield in forum C Programming
    Replies: 2
    Last Post: 11-18-2001, 08:34 PM