/* 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.
DDL of table_a
CREATE TABLE table_a (
record smallint(6) default NULL,
id char(3) default NULL,
color varchar(10) default NULL
)Example:
record id color 1 001 BLACK 2 NULL PINK NULL 002 WHITE 3 NULL BLUE NULL NULL GREEN NULL 003 YELLOW 4 004 BLACK
Table that is converted to table_b
DDL of table_b
CREATE TABLE table_b (
record smallint(6) default NULL,
id char(3) default NULL,
color varchar(10) default NULL
)Example:
record id color 1 001 BLACK 2 001 PINK 2 002 WHITE 3 002 BLUE 3 002 GREEN 3 003 YELLOW 4 004 BLACK
Training available on C/C++, CGI, Unix , MySQL (or other API) in
Bangladesh and abroad.
Ask for "Visit & Train US$999" package.
*/
#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 3.2 Copyright Aftab
Jahan Subedar \n\t\tjahan@geocities.com \n\t\tsms:+447765341890\n");
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") ;
}
}
/* Change History:
3.2 added mysql_close() Aug 13 2003*/