Concatenate in a select statement in C

I am doing a select statement in C code but I need to concatenate two fields from our informix database:

Current code:

        $select
                line1
        into
                $email_id
        from
                aa_rec
        where
                aa_rec.aa = $pcode  and aa_rec.id = $nslc_buf.id
                and  ( aa_rec.end_date is null
                or  aa_rec.end_date >= today
                or  aa_rec.end_date = 0);

I need line1 and line2 from aa_rec to be combined into $email_id

I don't do C code much anymore, so any help you can give to help me remember the syntax would be great!
dcrowley_01Asked:
Who is Participating?
 
Dave FordSoftware Developer / Database AdministratorCommented:
I would probably do something like this:

$select line1 || line2
   into $email_id
   from aa_rec
  where aa_rec.aa = $pcode
    and aa_rec.id = $nslc_buf.id
    and ( aa_rec.end_date is null
     or   aa_rec.end_date >= today
     or   aa_rec.end_date = 0)
;

Open in new window


HTH,
DaveSlash
0
 
SharathData EngineerCommented:
Did you try like this?
$select
                concat(line1,line2)
        into
                $email_id
        from
                aa_rec
        where
                aa_rec.aa = $pcode  and aa_rec.id = $nslc_buf.id
                and  ( aa_rec.end_date is null
                or  aa_rec.end_date >= today
                or  aa_rec.end_date = 0);

Open in new window

0
 
dcrowley_01Author Commented:
I did, it doesn't error but it is not combining the two fields...
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Dave FordSoftware Developer / Database AdministratorCommented:
If either line1 or line2 is NULL, then the concatenation will be null. You could guard against that by using COALESCE if you wanted to.

$select coalesce(line1,'') || 
        coalesce(line2, '')
   into $email_id
   from aa_rec
  where aa_rec.aa = $pcode
    and aa_rec.id = $nslc_buf.id
    and ( aa_rec.end_date is null
     or   aa_rec.end_date >= today
     or   aa_rec.end_date = 0)
;

Open in new window

0
 
Dave FordSoftware Developer / Database AdministratorCommented:
What results are you seeing specifically?
0
 
dcrowley_01Author Commented:
It is just not combining line1 and line 2.  If I change it to line2, I get the 2nd half of the email. If I put it back to line1 I get the 1st half of the email.

But when I use concat or the other method I only get line1 not line1 and line2 combined.

coalesce errors out.

So I know I am in the right part of the code. But for whatever reason concat doesn't work.  Should be simple but I don't see it.
0
 
dcrowley_01Author Commented:
This is an informix database btw.
0
 
SharathData EngineerCommented:
Can you post some sample data from the two fields? Do you have NULLs or any new line character in them?
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Informix uses the double pipe (||) as the concatenation operator.  Older releases didn't have a concatenate function, but I don't know if that applies to you.

And of course, since this is embedded in C, the Informix pre-processor is the critical component.

Try Dave's earlier suggestion to use the double pipe.  If that doesn't work, try returning both columns and concatenating them inside of the C program.

        $select
                line1, line2
        into
                $email_id, line2
        from
                aa_rec
        where
                aa_rec.aa = $pcode  and aa_rec.id = $nslc_buf.id
                and  ( aa_rec.end_date is null
                or  aa_rec.end_date >= today
                or  aa_rec.end_date = 0);

Open in new window



Kent
0
 
dcrowley_01Author Commented:
Can I get an example of strconcat in C?
0
 
SharathData EngineerCommented:
strcat(string1, string2)
0
 
sarabandeCommented:
strcat(string1, string2)
be aware that the first char buffer needs to have a size such it could take both the strings.

to make your code safe you could use a 3rd buffer like in

int len1 = (int)strlen(string1);
int len2 = (int)strlen(string2);
char * string3 = (char*)malloc(len1+1+len2+1);
*string3 = '\0';  // zero termination
strcat(string3, string1);
strcat(string3, "|");
strcat(string3, string2);

// use the string3
...
free(string3);

Open in new window


Sara
0
 
dcrowley_01Author Commented:
This is driving me nuts... I can't get it to work:

Here is the entire subroutine:

int get_email()
{
        $char       pcode[33];
        $char       email_id[65] ;
        char        *macroVal;

        (void) strcpy(pcode, "");
        macroVal = (char *) cis_feat_string("JICS_EMAIL_AA_CODE");
        strcpy(pcode,macroVal);
        (void) strcpy(email_id,"");
        strcpy(nslc_buf.email_id,email_id);


        $select
                line1 || line2
        into
                $email_id
        from
                aa_rec
        where
                aa_rec.aa = $pcode  and aa_rec.id = $nslc_buf.id
                and  ( aa_rec.end_date is null
                or  aa_rec.end_date >= today
                or  aa_rec.end_date = 0);


        if (SQLCODE == 100)
                {

                        handleMsg(MSG_WRN_MAIL,
                                "No email available for ID: '%d' ", nslc_buf.id);
                }
        else if (SQLCODE )
            {
                if (SQLCODE == -284 )
                {
                        handleMsg(MSG_WRN_MAIL,
                        "Check aa_rec - Many Email ID's for ID: %d \n", nslc_buf.id );
                } else if (SQLCODE != 100 || SQLCODE != -284 )
                {
                        handleMsg(MSG_ERR_MAIL,
                        "Select error %d at %s:%d",
                                SQLCODE, __FILE__, __LINE__);
                        return(REG_FAIL);
                }
             }
        strcpy(nslc_buf.email_id,email_id);
/*      printf(" Id: %d, email_id: %s \n",nslc_buf.id, nslc_buf.email_id);  */
        return(REG_OK);
}
0
 
dcrowley_01Author Commented:
Ok, I increased email_id from 65 to 85 and it is working now (sorta) but I need to trim the white space:

After changing this:
  $char       email_id[85] ;

I get back this:
Testers-Tst_Tst-Testerss16@tesste                                r.edu
0
 
Kent OlsenData Warehouse Architect / DBACommented:
How are line1 and line2 defined?  

You're showing 33 characters from line1, then blanks.  If line1 is longer than 33 characters, this should work:

  rtrim (line1) || line2

otherwise

  line1 || ltrim (line2)

and just to be sure, you could even write:

  rtrim (line1) || ltrim (line2)
0
 
dcrowley_01Author Commented:
Nice! That did it! (I went with the bottom solution just to be sure)...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.