Solved

Concatenate in a select statement in C

Posted on 2013-10-23
16
520 Views
Last Modified: 2013-10-24
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!
0
Comment
Question by:dcrowley_01
  • 7
  • 3
  • 3
  • +2
16 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 39595372
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
 
LVL 18

Accepted Solution

by:
daveslash earned 250 total points
ID: 39595425
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
 

Author Comment

by:dcrowley_01
ID: 39595440
I did, it doesn't error but it is not combining the two fields...
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 18

Expert Comment

by:daveslash
ID: 39595458
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
 
LVL 18

Expert Comment

by:daveslash
ID: 39595462
What results are you seeing specifically?
0
 

Author Comment

by:dcrowley_01
ID: 39595492
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
 

Author Comment

by:dcrowley_01
ID: 39595493
This is an informix database btw.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39595540
Can you post some sample data from the two fields? Do you have NULLs or any new line character in them?
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39595548
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
 

Author Comment

by:dcrowley_01
ID: 39595622
Can I get an example of strconcat in C?
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39595757
strcat(string1, string2)
0
 
LVL 33

Expert Comment

by:sarabande
ID: 39596432
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
 

Author Comment

by:dcrowley_01
ID: 39597893
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
 

Author Comment

by:dcrowley_01
ID: 39597925
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
 
LVL 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 250 total points
ID: 39597956
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
 

Author Comment

by:dcrowley_01
ID: 39597995
Nice! That did it! (I went with the bottom solution just to be sure)...
0

Featured Post

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Summary: This tutorial covers some basics of pointer, pointer arithmetic and function pointer. What is a pointer: A pointer is a variable which holds an address. This address might be address of another variable/address of devices/address of fu…
This is a short and sweet, but (hopefully) to the point article. There seems to be some fundamental misunderstanding about the function prototype for the "main" function in C and C++, more specifically what type this function should return. I see so…
The goal of this video is to provide viewers with basic examples to understand opening and writing to files in the C programming language.
The goal of this video is to provide viewers with basic examples to understand and use conditional statements in the C programming language.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question