Hi,
I'm having trouble writing dial-up user billing software in C using MySQL C
API.
Program suppose to send query to mysql server and retrieve records and
process those.
It sends query for each class_id for every day and get records from
ACCOUNTING table.
While retrieving records it adds user in linked list if user doesn't exist.
There will be total not more than 1000 users
per one day with duration, number of session, time_stamp etc. After adding
user to linked list it calculates
the charges for duration that he connected to Internet. In order to
calculate charge it sends various queries to
various tables to get data. Program is working fine for one day. But when I
try to calculate from beginning of the
month, after 2 days program crashes saying:
"Out of memory (Needed 8164 bytes)
select charge,from_min,to_min,class_id,id from customers.intervals where
class_id='19' order by id query failed!"
when sending trying to send query to get data for calculation.
I think there are some problem with memory management, but when I just run
program without calculating charges
it works just fine.
Can somebody recommend me better way of handling in this situation? Is this
good choice to use linked list in this case?
Is there any other good resource for writing mysql client in C on the web?
Please let me know if somebody knows and solved this problem before.
Please give me some recommendation in this regard.
I'm using FreeBSD 4.6-STABLE, with 512 MB RAM and 50GB HDD with 6GB of free
space.
MySQL version is :3.23.38
thanks in advance,
Ganbold
Below is my code snippets:
-------------------------------------------------------------------------------------------------------------
typedef struct user_b{
int contract_id;
int class_id;
char *username;
int numSession;
long time_stamp;
long duration;
double pre_balance;
double due_balance;
double total;
double tax;
double payment;
int discount;
int is_payed; /* 1 for true, 0 for false */
int location_id;
struct user_b *next;
} USER;
------------------------------------------------------------------------------------------------
Main code
------------------------------------------------------------------------------------------------
USER *customer_list;
customer_list = NULL;
for(i=day;i>0;i--){
deleteTableEntry(fp,i,"billing_test");
if(customer_list!=NULL){
customer_list = destroyUserList(customer_list); /*
destroy user list */
}
customer_list = prepareUser(fp,customer_list,i,daytime,nighttime);
writeBillingTable(fp,customer_list);
writePaymentTable(fp,customer_list);
}
if(customer_list!=NULL){
customer_list = destroyUserList(customer_list); /* destroy
user list */
}
Functions
------------------------------------------------------------------------------------------------
/*---------------------------------------------------------------------------------------------------------------------*/
USER *installUser(FILE *logfile, USER *pslist,USER **curr, char *userid,
int class_id, int contract_id, long duration, long time_stamp, int day)
/*
Parameter: USER **curr - pointer to pointer to current user
*/
{
USER *p;
USER *prev; /* pointer to previous element in list */
double amount;
if((p = searchUser(pslist,&prev,userid))==NULL){
/* if user is not in list */
/* allocate memory */
p = (USER *) malloc(sizeof(USER));
/* if unsuccessfully allocated */
if(p ==NULL || (p->username = my_alloc(userid)) ==NULL){
fprintf(stderr,"Out of memory while allocating!\n");
exit(4);
}
p->class_id = class_id;
p->contract_id = contract_id;
p->duration = duration;
p->time_stamp = time_stamp;
p->numSession = 1;
/* add user to our linked list in appropriate place */
if(prev == NULL){ /* in the beginning or to null list */
p->next = pslist;
pslist = p;
}else{ /* in the middle or at end */
p->next = prev->next;
prev->next = p;
}
}else{
p->duration += duration;
p->numSession++;
}
/* calculate charges */
amount = applyIntervals(logfile, day, class_id, time_stamp,
duration,p->duration,&p->total);
amount = applyTimeFrame(logfile, day, class_id, time_stamp,
duration,p->duration,&p->total);
amount = applyClassInfo(logfile, day, class_id, time_stamp,
duration,p->duration,&p->total);
/* current is now points to existing user or to new user */
*curr = p;
/* return pointer to our list */
return pslist;
} /* end installUser */
/*---------------------------------------------------------------------------------------------------------------------*/
USER *prepareUser(FILE *logfile, USER *customer_list, int day, char
*day_time, char *night_time)
{
MYSQL_RES *res_set;
MYSQL_ROW row;
char query[BUF];
USER *curr; /* pointer to current elem */
char *morning;
char *midnight;
int session,contract_id,class_id;
long time_stamp;
long duration;
int i,numClasses,c_id[20];
midnight = getSpecDate(logfile,day,day_time);
morning = getSpecDate(logfile,day,night_time);
snprintf(query,BUF,"select id from class where charge > 0 order by id");
printf("query: %s\n",query);
process_query(logfile,query);
fprintf(logfile, "SELECT succeeded!\n");
if((res_set = mysql_store_result(conn))==NULL){
fprintf(stderr, "store result failed!\n");
fprintf(logfile, "store result failed!\n");
exit(4);
}
i = 0;
while((row = mysql_fetch_row(res_set)) != NULL){
/* get class_ids */
c_id[i] = atoi(row[0]);
i++;
}
numClasses = i;
for(i=0; i < numClasses; i++){
snprintf(query,BUF,"select
USERNAME,CLASS_ID,CONTRACTID,TIME_STAMP,ACCTSESSIONTIME from
radius.ACCOUNTING where from_unixtime(TIME_STAMP-ACCTSESSIONTIME,'%s-%s-%s
%s:%s:%s')>'%s' and from_unixtime(TIME_STAMP-ACCTSESSIONTIME,'%s-%s-%s
%s:%s:%s')<='%s' and class_id='%d' order by
USERNAME","%Y","%m","%d","%H","%i","%S",morning,"%Y","%m","%d","%H","%i","%S",midnight,c_id[i]);
printf("query: %s\n",query);
process_query(logfile,query);
fprintf(logfile, "SELECT succeeded!\n");
if((res_set = mysql_store_result(conn))==NULL){
fprintf(stderr, "store result failed!\n");
fprintf(logfile, "store result failed!\n");
exit(4);
}
while((row = mysql_fetch_row(res_set)) != NULL){
/* search user from list and if not exists add it */
/* it adds username, class_id, numSession and time_stamp */
if(row[2]!=NULL && row[1]!=NULL){
class_id = atoi(row[1]);
contract_id = atoi(row[2]);
time_stamp = atol(row[3]);
duration = atol(row[4]);
if(contract_id!=0 && class_id!=0){
customer_list =
installUser(logfile,customer_list,&curr,row[0],class_id,contract_id,duration,time_stamp,day);
}
}
}
}
free(midnight);
free(morning);
/* return pointer to our list */
return customer_list;
}
/*---------------------------------------------------------------------------------------------------------------------*/
double intervalCharge(long duration, int from_min, int to_min, double charge)
{
double total = 0.0;
if(from_min <= duration && to_min >= duration){
/* if duration is in between intervals then we have to calculate */
total = charge * (duration - from_min)/60;
}else if(from_min <= duration && to_min < duration){
/* if duration is in beyond interval then we have to calculate */
total = charge * (to_min - from_min)/60;
}
return total;
}
/*---------------------------------------------------------------------------------------------------------------------*/
double applyIntervals(FILE *logfile, int day, int class_id, long
time_stamp, long duration, long totalDuration, double *total)
{
MYSQL_RES *res_set;
MYSQL_ROW row;
char query[BUF];
double charge, total_h = 0.0;
int from_min;
int to_min;
double total_per_connection = 0.0;
double total_per_connection_h = 0.0;
long c_time_stamp;
int m_class_id,id[20],i=0, numId, interval_id;
int difference,start_time,tmp, status = 0;
char *m_date;
/* we have to look changes in history table */
m_date = getDate(logfile,day);
snprintf(query,BUF,"select
time_stamp,charge,from_min,to_min,class_id,interval_id from
customers.intervals_history where from_unixtime(time_stamp,'%s-%s-%s')='%s'
and time_stamp<='%ld' and class_id='%d' order by time_stamp
desc","%Y","%m","%d",m_date,time_stamp-duration,class_id);
process_query(logfile,query);
fprintf(logfile, "SELECT succeeded!\n");
if((res_set = mysql_store_result(conn))==NULL){
fprintf(stderr, "store result failed!\n");
fprintf(logfile, "store result failed!\n");
exit(4);
}
if(mysql_num_rows(res_set)>0){
i = 0;
while((row = mysql_fetch_row(res_set)) != NULL){
c_time_stamp = atol(row[0]);
if(row[1]!=NULL){
charge = atof(row[1]);
if(charge==0.0)
continue;
}
from_min = atoi(row[2]) * 60;
to_min = atoi(row[3]) * 60;
m_class_id = atoi(row[4]);
id[i] = atoi(row[5]);
/* if start time is in between intervals then we have to
calculate */
total_per_connection_h += intervalCharge(duration, from_min,
to_min, charge);
total_h += total_per_connection_h;
i++;
}
}
numId = i; /* store number of changed interval's id */
/* we have to look at the main intervals table */
snprintf(query,BUF,"select charge,from_min,to_min,class_id,id from
customers.intervals where class_id='%d' order by id",class_id);
process_query(logfile,query);
fprintf(logfile, "SELECT succeeded!\n");
if((res_set = mysql_store_result(conn))==NULL){
fprintf(stderr, "store result failed!\n");
fprintf(logfile, "store result failed!\n");
exit(4);
}
if(mysql_num_rows(res_set)>0){
while((row = mysql_fetch_row(res_set)) != NULL){
if(row[0]!=NULL){
charge = atof(row[0]);
if(charge==0.0)
continue;
}
from_min = atoi(row[1]) * 60;
to_min = atoi(row[2]) * 60;
m_class_id = atoi(row[3]);
interval_id = atoi(row[4]);
for(i=0; i < numId; i++){
if(id[i]==interval_id){
status = 1;
}
}
if(status != 1){
/* if current interval is not changed */
/* if start time is in between intervals then we have to
calculate */
total_per_connection += intervalCharge(duration, from_min,
to_min, charge);
*total += total_per_connection;
}
}
}
/* add interval changes and not changed interval charges */
*total += total_h;
total_per_connection += total_per_connection_h;
free(m_date);
return total_per_connection;
}
/*---------------------------------------------------------------------------------------------------------------------*/
double applyTimeFrame(FILE *logfile, int day, int class_id, long
time_stamp, long duration, long totalDuration, double *total)
{
MYSQL_RES *res_set;
MYSQL_ROW row;
char query[BUF];
double charge;
int resolution;
int min_duration;
int timeleft,reminder;
double total_per_connection = 0.0;
double total_per_connection_h = 0.0, total_h = 0.0;
long c_time_stamp;
int begin_hour,end_hour;
double seconds_per_penny;
double x_seconds_per_penny;
int m_class_id,hour_id,i=0,numId,status = 0;
char *m_date;
m_date = getDate(logfile,day);
/* first we have to look in main time_frame table */
snprintf(query,BUF,"select
charge,min_duration,resolution,begin_hour,end_hour,from_day,to_day,class_id,id
from customers.time_frame where class_id='%d' order by id",class_id);
process_query(logfile,query);
fprintf(logfile, "SELECT succeeded!\n");
if((res_set = mysql_store_result(conn))==NULL){
fprintf(stderr, "store result failed!\n");
fprintf(logfile, "store result failed!\n");
exit(4);
}
if(mysql_num_rows(res_set)>0){
while((row = mysql_fetch_row(res_set)) != NULL){
if(row[0]!=NULL){
charge = atof(row[0]);
if(charge==0.0)
continue;
}
min_duration = atoi(row[1]);
resolution = atoi(row[2]); /* we should count resolution */
begin_hour = atoi(row[3]);
end_hour = atoi(row[4]);
m_class_id = atoi(row[7]);
hour_id = atoi(row[8]);
if(strcmp(getDayOfWeek(time_stamp-duration),row[6])>=0 &&
strcmp(getDayOfWeek(time_stamp),row[7])<=0){
if(getHour(time_stamp-duration) >= begin_hour &&
getHour(time_stamp) <= end_hour){
/* if user connected in between time frame charge is
calculated regardless of duration */
total_per_connection = charge;
*total = total_per_connection;
status = 1;
}
}
if(status ==1) /* if time frame is already calculated we have
to exit from loop */
break;
}
}
/* we have to look changes in history table */
snprintf(query,BUF,"select
time_stamp,charge,min_duration,resolution,begin_hour,end_hour,from_day,to_day,class_id,hour_id
from customers.time_frame_history where
from_unixtime(time_stamp,'%s-%s-%s')='%s' and time_stamp<='%ld' and
class_id='%d' order by time_stamp
desc","%Y","%m","%d",m_date,time_stamp-duration,class_id);
process_query(logfile,query);
fprintf(logfile, "SELECT succeeded!\n");
if((res_set = mysql_store_result(conn))==NULL){
fprintf(stderr, "store result failed!\n");
fprintf(logfile, "store result failed!\n");
exit(4);
}
if(mysql_num_rows(res_set)>0){
while((row = mysql_fetch_row(res_set)) != NULL){
c_time_stamp = atol(row[0]);
if(row[1]!=NULL){
charge = atof(row[1]);
if(charge==0.0)
continue;
}
min_duration = atoi(row[2]);
resolution = atoi(row[3]); /* we should count resolution */
begin_hour = atoi(row[4]);
end_hour = atoi(row[5]);
m_class_id = atoi(row[8]);
hour_id = atoi(row[9]);
if(strcmp(getDayOfWeek(time_stamp-duration),row[6])>=0 &&
strcmp(getDayOfWeek(time_stamp),row[7])<=0){
if(getHour(time_stamp-duration) >= begin_hour &&
getHour(time_stamp) <= end_hour){
/* if user connected in between time frame charge is
calculated regardless of duration and returned immediatedly*/
total_per_connection = charge;
*total = total_per_connection;
return total_per_connection;
}
}
}
}
free(m_date);
return total_per_connection;
}
/*---------------------------------------------------------------------------------------------------------------------*/
double classCharge(int timeleft, double charge, double x_charge, char
*prepaid, long totalDuration, long duration, double *total)
{
double total_per_connection = 0.0;
int difference,start_time,tmp;
double seconds_per_penny;
double x_seconds_per_penny;
if(timeleft==0){
timeleft = getNumDays() * 24;
}
seconds_per_penny = (timeleft * 36)/charge;
x_seconds_per_penny = 36/x_charge;
/* if not prepaid but it has extra charge */
if(strcmp(prepaid,"NO")==0 && timeleft!=0 && x_charge!=0.0){
if(totalDuration > (timeleft * 3600)){
/* calculate extra charge */
*total = charge + ((totalDuration - (timeleft * 3600)) /
x_seconds_per_penny) / 100;
difference = totalDuration - (timeleft * 3600); /* difference
between timeleft and totalDuration */
start_time = totalDuration - duration;
tmp = (timeleft * 3600) - start_time;
/* if user connected before timeleft and continued over timeleft
calculate each part using regular charge and extra charge */
if(difference >= duration)
total_per_connection = (duration / x_seconds_per_penny) / 100;
else
total_per_connection = (tmp / seconds_per_penny) / 100 +
((totalDuration - (timeleft * 3600)) / x_seconds_per_penny) / 100;
}else if(totalDuration < (timeleft * 3600)){
*total = (totalDuration / seconds_per_penny) / 100;
total_per_connection = (duration / seconds_per_penny) / 100;
}else if(totalDuration == (timeleft * 3600)){
*total = charge;
total_per_connection = (duration / seconds_per_penny) / 100;
}
/* if prepaid */
}else if(strcmp(prepaid,"YES")==0){
if(totalDuration > (timeleft * 3600)){
*total = charge + ((totalDuration - (timeleft * 3600)) /
seconds_per_penny) / 100;
}else if(totalDuration < (timeleft * 3600)){
*total = (totalDuration / seconds_per_penny) / 100;
}else if(totalDuration == (timeleft * 3600)){
*total = charge;
}
total_per_connection = (duration / seconds_per_penny) / 100;
/* if not prepaid and it doesn't have extra charge */
}else{
*total = (totalDuration / seconds_per_penny) / 100;
total_per_connection = (duration / seconds_per_penny) / 100;
}
return total_per_connection;
}
/*---------------------------------------------------------------------------------------------------------------------*/
double applyClassInfo(FILE *logfile, int day, int class_id, long
time_stamp, long duration, long totalDuration, double *total)
{
MYSQL_RES *res_set;
MYSQL_ROW row;
char query[BUF];
double charge;
double x_charge;
int resolution;
int min_duration;
int timeleft,reminder;
int m_class_id;
double total_per_connection = 0.0;
long c_time_stamp;
char *m_date;
m_date = getDate(logfile,day);
/* we have to look main class table */
snprintf(query,BUF,"select
charge,x_charge,min_duration,resolution,special_days,timeleft,prepaid,id
from customers.class where id='%d'",class_id);
process_query(logfile,query);
fprintf(logfile, "SELECT succeeded!\n");
if((res_set = mysql_store_result(conn))==NULL){
fprintf(stderr, "store result failed!\n");
fprintf(logfile, "store result failed!\n");
exit(4);
}
if(mysql_num_rows(res_set)>0){
while((row = mysql_fetch_row(res_set)) != NULL){
if(row[0]!=NULL){
charge = atof(row[0]);
if(charge==0.0)
continue;
}
x_charge = atof(row[1]);
min_duration = atoi(row[2]);
resolution = atoi(row[3]); /* we should count resolution */
timeleft = atoi(row[5]);
m_class_id = atoi(row[7]);
total_per_connection = classCharge(timeleft, charge, x_charge,
row[6], totalDuration, duration, total);
}
}
/* we have to look changes in history table */
snprintf(query,BUF,"select
time_stamp,charge,x_charge,min_duration,resolution,special_days,timeleft,prepaid,class_id
from customers.class_history where
from_unixtime(time_stamp,'%s-%s-%s')='%s' and time_stamp<='%ld' and
class_id='%d' order by time_stamp desc limit
1","%Y","%m","%d",m_date,time_stamp-duration,class_id);
process_query(logfile,query);
fprintf(logfile, "SELECT succeeded!\n");
if((res_set = mysql_store_result(conn))==NULL){
fprintf(stderr, "store result failed!\n");
fprintf(logfile, "store result failed!\n");
exit(4);
}
if(mysql_num_rows(res_set)>0){
while((row = mysql_fetch_row(res_set)) != NULL){
c_time_stamp = atol(row[0]);
charge = atof(row[1]);
x_charge = atof(row[2]);
min_duration = atoi(row[3]);
resolution = atoi(row[4]); /* we should count resolution */
timeleft = atoi(row[6]);
m_class_id = atoi(row[8]);
total_per_connection = classCharge(timeleft, charge, x_charge,
row[7], totalDuration, duration, total);
}
}
free(m_date);
return total_per_connection;
}
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
- Re: Help! problem using MySQL C API Ganbold
- Re: Help! problem using MySQL C API Shyamal Banerjee
