Solved

Concatenate in a select statement in C

Posted on 2013-10-23
16
523 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 3
  • +2
16 Comments
 
LVL 41

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:
Dave Ford 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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
LVL 18

Expert Comment

by:Dave Ford
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:Dave Ford
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 41

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 41

Expert Comment

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

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem to save 10 180
convert char array to number in c 5 93
How to delete files in a folder according to older file first using file operation in C 77 113
asp.net web app 3 59
November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Preface I don't like visual development tools that are supposed to write a program for me. Even if it is Xcode and I can use Interface Builder. Yes, it is a perfect tool and has helped me a lot, mainly, in the beginning, when my programs were small…
The goal of this video is to provide viewers with basic examples to understand and use pointers in the C programming language.
Video by: Grant
The goal of this video is to provide viewers with basic examples to understand and use nested-loops in the C programming language.

751 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