/* 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 BALCK
This 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 Bangladesh
compile 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]