Implementing a MySQL Client

Lets say a situation comes where you have to access a MySQL Server running in some part of the world and for some reason the MySQL Client/Connector is not available for your platform (i am talking of embedded, just in case you are wondering) or though the source is available, trying to get it to compile cleanly for your platform is just too much of a f****** headache. Well, a much simpler way exists. Just implement the MySQL Client/Server protocol using the article below.

http://dev.mysql.com/doc/internals/en/client-server-protocol.html

If i knew such a protocol document was available which gave details on how the MySQL Client and Server communicate with each other, i could have saved so much of my time and effort. Hope if anyone else out there needs a MySQL Client/Connector to communicate with the MySQL Server and if support is not available for his platform, this will be of help.

N.B. I hope it’s implicit that you need a TCP/IP connection and need to use socket programming.

A SQL Tutorial

Not going to say much in this post. RDBMS is more or less a ubiquitous technology which everyone uses without ever realizing. With more and more embedded systems starting to run operating systems, a database is one of the most component that the OS can provide support for (though technically i guess, you could compile SQLite for your bare bone platform). Most developers at some point or another will have to get their hands dirty using SQL to access the RDBMS. So, here is a very good tutorial by Mr. Philip Greenspun which will teach you how to use SQL in general.

http://philip.greenspun.com/sql/

Have a good time.

Sending HTTP Request on Linux


#include <stdio.h>
#include <sys/socket.h>
#include <arpa/inet.h>
#include <stdlib.h>
#include <netdb.h>
#include <string.h>
int create_tcp_socket();
char *get_ip(char *host);
char *build_get_query(char *host, char *page);
void usage();

#define HOST "coding.debuntu.org"
#define PAGE "/"
#define PORT 80
#define USERAGENT "HTMLPOST 1.0"

int main(int argc, char **argv)
{
struct sockaddr_in *remote;
int sock;
int tmpres;
char *ip;
char *get;
char buf[4096];
char *host;
char *page;

if(argc == 1){
usage();
exit(2);
}
host = argv[1];
if(argc > 2){
page = argv[2];
}else{
page = PAGE;
}
sock = create_tcp_socket();
ip = get_ip(host);
fprintf(stderr, "IP is %s\n", ip);
remote = (struct sockaddr_in *)malloc(sizeof(struct sockaddr_in *));
remote->sin_family = AF_INET;
tmpres = inet_pton(AF_INET, ip, (void *)(&(remote->sin_addr.s_addr)));
if( tmpres < 0)
{
perror("Can't set remote->sin_addr.s_addr");
exit(1);
}else if(tmpres == 0)
{
fprintf(stderr, "%s is not a valid IP address\n", ip);
exit(1);
}
remote->sin_port = htons(PORT);

if(connect(sock, (struct sockaddr *)remote, sizeof(struct sockaddr)) < 0){
perror("Could not connect");
exit(1);
}
get = build_get_query(host, page);
fprintf(stderr, "Query is:\n<<START>>\n%s<<END>>\n", get);

//Send the query to the server
int sent = 0;
while(sent < strlen(get))
{
tmpres = send(sock, get+sent, strlen(get)-sent, 0);
if(tmpres == -1){
perror("Can't send query");
exit(1);
}
sent += tmpres;
}
//now it is time to receive the page
memset(buf, 0, sizeof(buf));
int htmlstart = 0;
char * htmlcontent;
while((tmpres = recv(sock, buf, 4096, 0)) > 0){
if(htmlstart == 0)
{
/* Under certain conditions this will not work.
* If the \r\n\r\n part is splitted into two messages
* it will fail to detect the beginning of HTML content
*/
htmlcontent = strstr(buf, "\r\n\r\n");
if(htmlcontent != NULL){
htmlstart = 1;
htmlcontent += 4;
}
}else{
htmlcontent = buf;
}
if(htmlstart){
fprintf(stdout, htmlcontent);
}

memset(buf, 0, tmpres);
}
if(tmpres < 0)
{
perror("Error receiving data");
}
free(get);
free(remote);
free(ip);
close(sock);
return 0;
}

void usage()
{
fprintf(stderr, "USAGE: htmlget host [page]\n\
\thost: the website hostname. ex: coding.debuntu.org\n\
\tpage: the page to retrieve. ex: index.html, default: /\n");
}

int create_tcp_socket()
{
int sock;
if((sock = socket(AF_INET, SOCK_STREAM, IPPROTO_TCP)) < 0){
perror("Can't create TCP socket");
exit(1);
}
return sock;
}

char *get_ip(char *host)
{
struct hostent *hent;
int iplen = 15; //XXX.XXX.XXX.XXX
char *ip = (char *)malloc(iplen+1);
memset(ip, 0, iplen+1);
if((hent = gethostbyname(host)) == NULL)
{
herror("Can't get IP");
exit(1);
}
if(inet_ntop(AF_INET, (void *)hent->h_addr_list[0], ip, iplen) == NULL)
{
perror("Can't resolve host");
exit(1);
}
return ip;
}

char *build_get_query(char *host, char *page)
{
char *query;
char *getpage = page;
char *tpl = "POST /%s HTTP/1.0\r\nHost: %s\r\nUser-Agent: %s\r\n\r\n";
if(getpage[0] == '/'){
getpage = getpage + 1;
fprintf(stderr,"Removing leading \"/\", converting %s to %s\n", page, getpage);
}
// -5 is to consider the %s %s %s in tpl and the ending \0
query = (char *)malloc(strlen(host)+strlen(getpage)+strlen(USERAGENT)+strlen(tpl)-5);
sprintf(query, tpl, getpage, host, USERAGENT);
return query;
}