/* Copyright (c) 2003 Aftab Jahan Subedar
mysql_last_value() Version 3.2 ------------------------------
Replaces NULL column(s) with value from last available column value.
Scenario --------
Table to be operated on.
table_a
-------
record id color 1 001 BLACK
2 NULL PINK
NULL 002 WHITE
3 NULL BLUE
NULL NULL GREEN
NULL 003 YELLOW
4 004 BALCK Table that is converted to.
table_b
-------
record id color 1 001 BLACK
2 001 PINK
2 002 WHITE
3 002 BLUE
3 002 GREEN
3 003 YELLOW
4 004 BALCKThis is free for public. Commercial uses require license from
Aftab Jahan Subedar
Software Engineer
Subedar Technologies
Subedar Baag
Bibir Bagicha #1
81/1-A North Jatrbari
Dhaka 1204
Bangladesh
sms://+447765341890
sms://+880171859159
http://www.ceobangladesh.com
http://www.DhakaStockExchangeGame.com
http://www.geocities.com/jahan.geo -- source code found here
+880171859159 sms +447765341890 [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED]
Has Problem?
Feel free to report.License Fee: USD 25 or equivalent for Lifetime . Bank information:
i. Aftab Jahan Subedar
Sort Code: 800283
Account No. 07271988
Bank Of Scotland
Newington Branch
51 South Clerk Street
Edinburgh EH8 9PP
UK ii. Aftab Jahan Subedar
Savings Account No. 794-2-4403321-4
[Sort Code 794]
Standard Chartered Bank
32 36 Jalan 52/4
Petaling Jaya
Selangor
Malaysia iii.Aftab Jahan Subedar
Savings Account No. 18 1757 393 01
[Sort Code 18]
Standard Chartered Bank
53 Kawran Bazar, G.P.O Box #3668
Dhaka 1215
Bangladeshcompile instruction:
cc -o mysql_last_value mysql_last_value.c -I/usr/local/include/mysql -L/usr/local/lib/mysql -lmysqlclient
usage:
./mysql_last_value -u user -h host -d last_value_test -f table_from -t table_to -r "replace_field1 replace_fieldn" -v -p secretpassword
Parameters:
-d database
-f from which table to copy from
-t to which table to be copied to
-r replace field names separated with space. upto
20 fields.
-v display verbose
-v -v display insert statement ;(example:
./mysql_last_value -d last_value_test -f table_a -t table_b -r "record id" -v
./mysql_last_value -d last_value_test -f table_a -t table_b -r "record" -v
Training available on C/C++, CGI, Unix , MySQL (or other API) in Bangladesh and abroad.
*/
#include <stdio.h> #include <fcntl.h> #include <stdlib.h> #include <unistd.h> #include <string.h> #include <ctype.h> #include <mysql.h>
/* for freeing easily*/
char *host = NULL; char *user = NULL; char *passwd = NULL; char *database = NULL; char *sql_insert_to = NULL; char *sql_insert_from = NULL; int verbose = 1; int use_supplied = 0; char *last_value=NULL; char *replace_field_name=NULL; char *criterion=NULL; char *insert_statement=NULL; char *replace_value[20]; unsigned int *puiQuotes=NULL; char *pcQuery=NULL; MYSQL mysql; MYSQL_RES *pResult=NULL; unsigned int *puiIndexOfReplaceField=NULL; unsigned int uiNumOfReplaceField=0; unsigned long *pulFieldLengths; unsigned int uiReplaceIndex=0;
void usage(void);
void free_all(void);
char *strupr(char *str);
/*int strcmpp(const char *p1, const char *p2);*/
void append_insert(unsigned long length,char *value);
unsigned int get_replace_index(unsigned int uiCurrentIndex);
void replace_field(MYSQL_ROW pTuple,unsigned int uiFieldIndex,unsigned int uiReplaceIndex);
void copy_field(MYSQL_ROW pTuple,unsigned int uiFieldIndex);
void append_insert(unsigned long length,char *value)
{ unsigned long old_length=0;
char *p;
old_length=strlen(insert_statement);
/*printf("\nRaw after strlen:%s",insert_statement);*//*if ((p = (char *)realloc(insert_statement,old_length+length+1)) == NULL) */
if ((p = (char *)malloc(old_length+length+1)) == NULL)
{
printf("\n Error requesting memory");
free_all();
exit(1);
}
strcpy(p,insert_statement);
free(insert_statement);
insert_statement=p;/*printf("\nB4 null:%s",insert_statement);*/
insert_statement[old_length]='\0';
/*printf("\nB4:%s",insert_statement);*/
insert_statement= strcat(insert_statement, value);
/*printf("\nAfter:%s\n\n",insert_statement);*/}
char *strupr(char *str)
{
char *s; for(s = str; *s; s++)
*s = toupper(*s);
return str;
}
void free_all() { unsigned int i; if(host) free(host); if(user) free(user); if(passwd) free(passwd); if(database) free(database); if(sql_insert_to) free(sql_insert_to); if(sql_insert_from) free(sql_insert_from); if(last_value) free(last_value); if(replace_field_name) free(replace_field_name); if(criterion) free(criterion); if(insert_statement) free(insert_statement); for(i=0;i<uiNumOfReplaceField;i++) if(replace_value[i]) free(replace_value[i]); if(puiQuotes) free(puiQuotes); if(pcQuery) free(pcQuery); if(pResult) mysql_free_result(pResult); mysql_close(&mysql); if(puiIndexOfReplaceField) free(puiIndexOfReplaceField);
}
void usage(void) {
printf("\n\n\tmysql_last_value %s Copyright Aftab Jahan Subedar [EMAIL PROTECTED] \n\t\tsms:+447765341890\n",VERSION);
printf("\n\nusage:\n\t\tmysql_last_value [-h host] [-u user] [-p password] \n");
printf("\t\t-d database -f from_table -t to_table -r replace_field [-v]\n");
}
int main(int argc, char **argv)
{
extern char *optarg;
MYSQL_FIELD *pField;
unsigned int uiNumOfFields,uiCount,uiIterator;
my_ulonglong mulNumOfRows;
MYSQL_ROW pTuple;
char *save;int i;
/*optind = 0;*/
while ((i = getopt(argc, argv, "h:u:p:d:f:t:r:vl:c:")) != -1)
{ switch(i)
{
case 'h':
host =(char *)strdup(optarg);
break;
case 'u':
user = (char *)strdup(optarg);
break;
case 'p':
passwd =(char *) strdup(optarg);
break;
case 'd':
database =(char *) strdup(optarg);
break;
case 'f':
sql_insert_from = (char *)strdup(optarg);
break;
case 't':
sql_insert_to = (char *)strdup(optarg);
break;
case 'v':
verbose++;
break; case 'l':
last_value = (char *)strdup(optarg);
use_supplied++;
break;
case 'r':
replace_field_name=(char *)strdup(optarg);
replace_field_name=strupr(replace_field_name); break;
case 'c':
criterion = (char *)strdup(optarg);
break;
case '?':
usage();
free_all();
exit(1);
break;
default:
break;
}
} if (argc ==1)
{
usage();
printf("\n\tHint:Supply some arguments!!!%d\n",argc);
free_all();
exit(1);
}
if (verbose ==2)
{
printf("\nChecking passed values");
}if ((database == NULL) || (sql_insert_from == NULL) ||(sql_insert_to == NULL) ||(replace_field_name==NULL))
{
usage();
printf("\n\tHint:Database, From Table, INTO Table and Replace Field must be provided *must* be provided!\n");
free_all();
exit(1);
}
if (verbose ==2)
{
printf("\nChecking user values");
}
if ((use_supplied > 1) && (last_value== NULL))
{
if(verbose==2)
printf("\nSince you did not provide last_value, I will use the previous record value. {:-p");
use_supplied=0;
}
if (verbose ==2)
{
printf("\nPreparing to initate MySQL connection");
} if(mysql_init(&mysql)==NULL)
{
printf("\nFailed to initate MySQL connection");
free_all();
exit(1);
}
if (verbose ==2)
{
printf("\t .....Done MySQL connection");
}
if (verbose ==2)
{
printf("\nLogging into MySQL server");
} if (!mysql_real_connect(&mysql,host,user,passwd,database,0,NULL,0))
{
printf("\nFailed to connect to MySQL server: Error: %s\n",
mysql_error(&mysql));
free_all();
exit(1); }
if (verbose ==2)
{
printf("\t .....Logged into MySQL server");
} if (verbose ==2)
{
printf("\nAllocating some memory from OS");
}if(criterion==NULL)
uiCount= 14/* select * from+space*/+strlen(sql_insert_from) +1/*null*/;
else
uiCount= 14/*select * from+space*/+strlen(sql_insert_from)+1/*space*/+6/*where+space*/+strlen(criterion)+1/*null*/;
if ((pcQuery = (char *)malloc(uiCount)) == NULL)
{
printf("\n Error requesting memory");
free_all();
exit(1);
}
if (verbose ==2)
{
printf("\n\t .....Received memory from OS");
}
if (verbose ==2)
{
printf("\n Opening Table to be inserted from..");
}
if(criterion==NULL)
sprintf(pcQuery,"SELECT * FROM %s",sql_insert_from);
else
sprintf(pcQuery,"SELECT * FROM %s WHERE %s",sql_insert_from,criterion); if (verbose ==2)
{
printf("\nDoing Query %s",pcQuery);
} if (mysql_query(&mysql,pcQuery))
{
printf("\nFailed to open table. Error: %s\n",mysql_error(&mysql));
free_all();
exit(1); }
else /* query succeeded, process any data returned by it*/
{
if (verbose ==2)
{
printf("\nQuery succeeded.\nDoing initial calls");
}pResult = mysql_store_result(&mysql);
if (pResult) /* there are rows*/
{
uiNumOfFields = mysql_num_fields(pResult);
mulNumOfRows = mysql_affected_rows(&mysql);
if (verbose ==2)
{
printf("\nIntial Calls Received: \n\t\tNoOfFields:\t%d\n\t\tNumOfRows:\t%lu",uiNumOfFields,mulNumOfRows);
}
}
else /* mysql_store_result() returned nothing; should it have?*/
{
printf("\nFailed to open table. Error: %s\n",mysql_error(&mysql));
free_all();
exit(1);
}
}
if(mulNumOfRows <=0)
{
printf("\nEmpty Source Table. ");
free_all();
exit(1);
}
if (verbose ==2)
{
printf("\n\t .....Table opened");
} if (verbose ==2)
{
printf("\nCounting the required length for insert statement");
}if ((puiQuotes = (unsigned int*) malloc(sizeof(unsigned int) *uiNumOfFields )) == NULL)/* a 'or space and a null and both side*/
{
printf("\n Error requesting memory");
free_all();
exit(1);
}
uiCount=0L;/* insert length */
strupr(replace_field_name);
if ((puiIndexOfReplaceField =(unsigned int *) malloc(sizeof(unsigned int) * 20)) == NULL)/*warning:support of 20 fields only*/
{
printf("\nError requesting memory for IndexOfReplaceField");
free_all();
exit(1);
}
uiNumOfReplaceField=0;
uiCount=0;
while ((pField = mysql_fetch_field(pResult)))
{
strupr(pField->name);/*its ok,its not const*/
save=replace_field_name;
if(strstr(save,pField->name)!=NULL)
{
puiIndexOfReplaceField[uiNumOfReplaceField]=uiCount ;/*building replace index*/
uiNumOfReplaceField++;
if ((replace_value[uiNumOfReplaceField] = (char *)malloc(pField->length+1l)) == NULL)
{
printf("\n Error requesting memory");
free_all();
exit(1);
}
}
switch(pField->type)
{
/*case FIELD_TYPE_CHAR :*/
case FIELD_TYPE_TINY :
case FIELD_TYPE_SHORT :
case FIELD_TYPE_LONG :
case FIELD_TYPE_INT24 :
case FIELD_TYPE_LONGLONG :
case FIELD_TYPE_FLOAT :
case FIELD_TYPE_DOUBLE :
case FIELD_TYPE_DECIMAL :
puiQuotes[uiCount]=0;
break;
default:
puiQuotes[uiCount]=1;
break;
}
uiCount++;}
if(uiNumOfReplaceField==0 || uiNumOfReplaceField>20)
{
printf("\nError: Replace Field Name not found in the table.");
free_all();
exit(1);
}/*malloc according to uiCount for insert_statement + insert and other commands*/
uiCount=12/*INSERT INTO */+strlen(sql_insert_to)+1/*space*/;
uiCount+=8;/*VALUES()*/
uiCount+=uiNumOfFields *2;/*single quotes+space ' '*/
uiCount+=uiNumOfFields -1 ;/*comma ,*/
uiCount+=10;/*null terminator+secured buffer*/
if (verbose ==2)
{
printf("\nAllocating some memory from OS");
}
if (verbose ==2) { printf("\nDoing the big loop\n"); }
if (mulNumOfRows>0L)
{ /*sprintf(insert_statement,"INSERT INTO %s VALUES(*/
/*strcat(insert_statement,sql_insert_to);
strcat(insert_statement," VALUES(");*/ insert_statement=NULL;
while ((pTuple = mysql_fetch_row(pResult)) != NULL)
{
if(insert_statement!=NULL)
free(insert_statement);
if ((insert_statement =(char *) malloc(uiCount)) == NULL)
{
printf("\nError requesting memory for Insert Buffer");
free_all();
exit(1);
} pulFieldLengths=mysql_fetch_lengths(pResult);
sprintf(insert_statement,"INSERT INTO %s VALUES(",sql_insert_to); if (verbose ==2 )
{
printf("\nInitialized fetch done.");
}/*initiation*/
uiReplaceIndex=get_replace_index(0);
if(uiReplaceIndex>0) /*if this is the replace field*/
replace_field(pTuple,0,uiReplaceIndex-1);
else
copy_field(pTuple,0);
for (uiIterator = 1; uiIterator < uiNumOfFields; uiIterator++)
{
insert_statement=strcat(insert_statement, ","); /*Comma n has been malloced above*/
uiReplaceIndex=get_replace_index(uiIterator);
if(uiReplaceIndex>0) /*if this is the replace
field*/
replace_field(pTuple,uiIterator,uiReplaceIndex-1);
else
copy_field(pTuple,uiIterator);}/* for*/
insert_statement=strcat(insert_statement,")");/*values
close*/
if (verbose ==3)
{
printf("\n%s\n",insert_statement);
} if (verbose ==2)
{
printf("\nInserting\n");
} if (mysql_query(&mysql,insert_statement))
{
printf("\nFailed to replace fields. Error:
%s\n",mysql_error(&mysql));
free_all();
exit(1);}
}/*while*/
}/*if has rows */
printf("Done\n");
free_all();
exit(0);
} unsigned int get_replace_index(unsigned int uiCurrentIndex)
{
unsigned int i;
for(i=0;i<uiNumOfReplaceField;i++)
{
if(puiIndexOfReplaceField[i]==uiCurrentIndex) /*if this is the replace field*/
{
/*printf("\n%u",puiIndexOfReplaceField[i]);*/
return ++i;
}
}
return 0;
}
void replace_field(MYSQL_ROW pTuple,unsigned int uiFieldIndex,unsigned int uiReplaceIndex)
{
char temp[2];
temp[1]='\0';
temp[0]=puiQuotes[uiFieldIndex]?'\'':' ';
if (pTuple[uiFieldIndex] != 0) /*if filed has value*/
{
/*free n malloc*/
if(replace_value[uiReplaceIndex]!=NULL)
free(replace_value[uiReplaceIndex]);
if ((replace_value[uiReplaceIndex] = (char *)malloc(pulFieldLengths[uiFieldIndex]+1l)) == NULL)
{
printf("\n Error requesting memory");
free_all();
exit(1);
}
strcpy(replace_value[uiReplaceIndex],pTuple[uiFieldIndex]);
insert_statement=strcat(insert_statement,temp); /*Quotes Open*/
append_insert(pulFieldLengths[uiFieldIndex],pTuple[uiFieldIndex]) ;
insert_statement=strcat(insert_statement, temp);/*Quotes Close*/}
else /*else field has no value*/
{
if(replace_value[uiReplaceIndex]!=NULL)/*if replace value has value*/
{
insert_statement=strcat(insert_statement, temp); /*Quotes Open*/
append_insert(strlen(replace_value[uiReplaceIndex]),replace_value[uiReplaceIndex]) ;
insert_statement=strcat(insert_statement, temp);/*Quotes Close*/
}
else/*else replace valeu has
no value*/
{
append_insert(4,"NULL") ; }
}}
void copy_field(MYSQL_ROW pTuple,unsigned int uiFieldIndex)
{
char temp[2];
temp[1]='\0';
temp[0]=puiQuotes[uiFieldIndex]?'\'':'
'; if (pTuple[uiFieldIndex] != 0) /*if
filed has value*/
{insert_statement=strcat(insert_statement,temp); /*Quotes Open*/
append_insert(pulFieldLengths[uiFieldIndex],pTuple[uiFieldIndex]) ;
insert_statement=strcat(insert_statement, temp);/*Quotes Close*/
}
else /*else filed has no value*/
{
append_insert(4,"NULL") ;}
}
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
