stevedave
asked on
Embedded SQL in C code, problem with LIKE Statement
All,
I have a database that is suppose to be a mock of a database a library would use. I am now writing a c program that would be the interface for the librarians and users of the library. One of the menu options I have is an option to search for a book by its title. For instance, one of the books in the library is "The Humane Interface". If the user searched for "Humane", they should get that as a result. If the user searched for "Hum", they should get that result.
My table that holds the titles of my books is called BOOK. Here is my select statement.
SELECT TITLE
FROM BOOK
WHERE TITLE LIKE '%:title%';
:title is the value I get from the user. It seems to be searching for ":title" instead of what :title is a variable for. I also tried to use strcat like so:
strcpy(temp, "'%");
strcat(temp, title);
strcat(temp, "%'");
SELECT TITLE
FROM BOOK
WHERE TITLE LIKE :temp;
The SELECT statement works because if I hardcode "Humane" into the select, it returns the book. I just need to know how to pass the variable into the SELECT statement. I am not sure if there is a problem with newlines at the end of the string or not.
The database I am interacting with is an Oracle9i and I am using sqlplus.
Hope this is enough information.
Thanks
I have a database that is suppose to be a mock of a database a library would use. I am now writing a c program that would be the interface for the librarians and users of the library. One of the menu options I have is an option to search for a book by its title. For instance, one of the books in the library is "The Humane Interface". If the user searched for "Humane", they should get that as a result. If the user searched for "Hum", they should get that result.
My table that holds the titles of my books is called BOOK. Here is my select statement.
SELECT TITLE
FROM BOOK
WHERE TITLE LIKE '%:title%';
:title is the value I get from the user. It seems to be searching for ":title" instead of what :title is a variable for. I also tried to use strcat like so:
strcpy(temp, "'%");
strcat(temp, title);
strcat(temp, "%'");
SELECT TITLE
FROM BOOK
WHERE TITLE LIKE :temp;
The SELECT statement works because if I hardcode "Humane" into the select, it returns the book. I just need to know how to pass the variable into the SELECT statement. I am not sure if there is a problem with newlines at the end of the string or not.
The database I am interacting with is an Oracle9i and I am using sqlplus.
Hope this is enough information.
Thanks
ASKER
I tried it in all the ways that I could possibly think of and none worked. Thanks for the idea though.
ASKER
I found the solution to my problem. When using strcat, the only thing wrong was that I did not need the single quotes. Here is what I should have had:
strcpy(temp, "%");
strcat(temp, title);
strcat(temp, "%");
SELECT TITLE
FROM BOOK
WHERE TITLE LIKE :temp;
Just the % is put around it with no single quote.
Nate
strcpy(temp, "%");
strcat(temp, title);
strcat(temp, "%");
SELECT TITLE
FROM BOOK
WHERE TITLE LIKE :temp;
Just the % is put around it with no single quote.
Nate
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT TITLE
FROM BOOK
WHERE TITLE LIKE (temp);