Re: Comparing strings in SQL

gt6853d_at_prism.gatech.edu (George A. Zellner) writes:

>Okay. I know just enough about Oracle and SQL to be dangerous.
 

>I believe I'm running Oracle Version 7.0.12.1.0, on a Sun Sparc 10+,
>if that helps.
 

>My question: How can you best compare a string stored in a char array
>with a string in an Oracle table? I thought you could do something like
>the following:
 

>strcpy (request->USER_ID.arr, "hello");
>request->USER_ID.len = strlen("hello");
 

>SELECT USER_ID FROM PERSON WHERE PERSON.SSN# = :SSN;
 
>if ((strncmp(USER_ID.arr, request->USER_ID.arr))==0)
> x=1;
>return x;
>.
>.
>.
 

>My problem is that I can never get a string that I pull from an ORACLE table
>to match one that I pass in as a VARCHAR. I think I have everything defined
>correctly, and I have no problem comparing integers, but strings are giving me
>a hard time.
 

>Any ideas?

Yes:
You must select the value into a host variable:

   SELECT USER_ID INTO :USER_ID FROM PERSON ... strncmp requires 3 arguments, the third being the 'n' in strNcmp. try:

   if ((strncmp(USER_ID.arr, request->USER_ID.arr, USER_ID.len))==0)

better still is to convert the returned varchar into a C string, so you don't have to think about it any more:

   USER_ID.arr[USER_ID.len] = '\0';
   if ((strcmp(USER_ID.arr, request->USER_ID.arr)==0)

If your example is not simply illustrative, it can be better done as:

   strcpy(USER_ID.arr,"hello");
   USER_ID.len = strlen(USER_ID.arr);
   EXEC SQL
   SELECT NVL(MAX(1),0) INTO :x
   FROM PERSON
   WHERE USER_ID = :USER_ID
   AND SS# = :SSN;
   return(x);

>thanks
 

>--Andy Zellner
> gt6853d_at_prism.gatech.edu

>--
>Andy Zellner
>Internet: gt6853d_at_prism.gatech.edu
>I'd rather be Caving...maybe I am!
Received on Thu Feb 16 1995 - 15:41:06 CET