dcrowley_01
asked on
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I did, it doesn't error but it is not combining the two fields...
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)
;
What results are you seeing specifically?
ASKER
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.
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.
ASKER
This is an informix database btw.
Can you post some sample data from the two fields? Do you have NULLs or any new line character in them?
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.
Kent
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);
Kent
ASKER
Can I get an example of strconcat in C?
strcat(string1, string2)
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);
Sara
ASKER
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_EMAI L_AA_CODE" );
strcpy(pcode,macroVal);
(void) strcpy(email_id,"");
strcpy(nslc_buf.email_id,e mail_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,e mail_id);
/* printf(" Id: %d, email_id: %s \n",nslc_buf.id, nslc_buf.email_id); */
return(REG_OK);
}
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_EMAI
strcpy(pcode,macroVal);
(void) strcpy(email_id,"");
strcpy(nslc_buf.email_id,e
$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,e
/* printf(" Id: %d, email_id: %s \n",nslc_buf.id, nslc_buf.email_id); */
return(REG_OK);
}
ASKER
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
After changing this:
$char email_id[85] ;
I get back this:
Testers-Tst_Tst-Testerss16
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nice! That did it! (I went with the bottom solution just to be sure)...
Open in new window