Solved

Concatenate in a select statement in C

Posted on 2013-10-23
16
509 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 45

Expert Comment

by:Kdo
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 32

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:Kdo
Kdo 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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 and use pointers in the C programming language.
The goal of this video is to provide viewers with basic examples to understand how to create, access, and change arrays in the C programming language.

707 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now