___ ___ __ __ _____ ___ _ __
| T T| T T/ ___/ / \ | T / ]
| _ _ || | ( \_ Y Y| | / /
| \_/ || ~ |\__ T| Q || l___ / /
| | |l___, |/ \ || || T/ \_
| | || !\ |l || |\ |
l___j___jl____/ \___j \__,_jl_____j \____j
%-----------------------------------------------------------------%
| Do you like C language programming ? |
| With a simply mathematical method, row and coloum count so easy |
| to print out database fields ! |
| >> Modified at 26 October 2013 |
%-----------------------------------------------------------------%
Mysql query using C programming, here are the output sample :
wardi@darkstar:~/programming/c$ ./myquery
query> show tables;
+------------------+
| Tables_in_smsd |
+------------------+
| daemons |
| debug_log |
| gammu |
| guided_sms |
| inbox |
| inbox_view |
| outbox |
| outbox_multipart |
| pbk |
| pbk_groups |
| phones |
| sentitems |
| sentitems_view |
+------------------+
13 rows returned
query> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| smsd |
| test |
+--------------------+
7 rows returned
query> select * from daemons;
+-------+------+
| Start | Info |
+-------+------+
+-------+------+
0 rows returned
query> select * from phones;
+----+---------------------+---------------------+---------------------+------+---------+-----------------+--------------------------------------------------------+---------+--------+------+----------+
| ID | UpdatedInDB | InsertIntoDB | TimeOut | Send | Receive | IMEI | Client | Battery | Signal | Sent | Received |
+----+---------------------+---------------------+---------------------+------+---------+-----------------+--------------------------------------------------------+---------+--------+------+----------+
| | 2012-06-12 13:58:12 | 2012-06-12 09:44:08 | 2012-06-12 13:58:22 | yes | yes | 354056300814201 | Gammu 1.31.0, Windows Server 2007, GCC 4.6, MinGW 3.11 | 0 | 60 | 0 | 0 |
+----+---------------------+---------------------+---------------------+------+---------+-----------------+--------------------------------------------------------+---------+--------+------+----------+
1 rows returned
Here the full source code :
/*
* myquery.c
* Interactively mysql server query
* I wrote it for fun, refreshing C programming
* You must match you self this code
* about host, username, password, database name,
* and mysql socket
*
* Compile this code with GCC standar and mysql installed,
* % cc myquery.c -o myquery -I /usr/include/mysql/ -lmysqlclient -std=c99
*
* Author: Suwardi,
* Mail: war49@swevel.com
*/
#include <stdio.h>
#include <mysql.h>
#include <stdlib.h>
#include <string.h>
MYSQL *conn;
MYSQL *do_connect (const char *, const char *, const char *, const char *,
unsigned int, const char *, unsigned int);
static void do_query (MYSQL *, const char *);
static void print_error (MYSQL *, const char *);
static void print_dashes (MYSQL_RES *);
static void process_result (MYSQL *, MYSQL_RES *);
int main (void)
{
const char *host = "localhost";
const char *user = "wardi";
const char *pw = "wardiabc123";
const char *db = "abc";
const char *socket="/var/lib/mysql/mysql.sock";
if (do_connect (host, user, pw, db, 0, socket, 0) == NULL) {
fprintf (stderr, "connect fail\n");
exit (255);
}
while (1) {
char buf[1024];
fprintf (stderr, "query> ");
if (fgets (buf, sizeof (buf), stdin) == NULL)
break;
do_query (conn, buf);
}
return (0);
}
MYSQL *do_connect (const char *host, const char *user, const char *pw,
const char *db, unsigned int port, const char *socket, unsigned int flags)
{
if ((conn = mysql_init (NULL)) == NULL) {
print_error (conn, "mysql_init()");
return NULL;
}
if ((mysql_real_connect (conn, host, user, pw, db, 0, socket, 0)) == NULL) {
print_error (conn, "mysql_real_connect() fail");
return NULL;
}
return conn;
}
static void do_query (MYSQL *conn, const char *query)
{
MYSQL_RES *result;
unsigned int field_count;
if (mysql_query (conn, query) != 0) {
print_error (conn, "process_query() fail");
return;
}
result = mysql_store_result (conn);
if (result == NULL) {
if (mysql_field_count (conn) > 0)
print_error (conn, "problem processing result set");
else
printf ("%lu rows affected\n", (unsigned long) mysql_affected_rows (conn));
}else{
process_result (conn, result);
mysql_free_result (result);
}
}
static void print_error (MYSQL *conn, const char *mesg)
{
fprintf (stderr, "%s -> %u (%s)\n", mesg, mysql_errno (conn), mysql_error (conn));
}
static void print_dashes (MYSQL_RES *result)
{
MYSQL_FIELD *field;
unsigned int i, j;
mysql_field_seek (result, 0);
fputc ('+', stdout);
for (i = 0; i < mysql_num_fields (result); i++) {
field = mysql_fetch_field (result);
for (j = 0; j < field->max_length + 2; j++)
fputc ('-', stdout);
fputc ('+', stdout);
}
fputc ('\n', stdout);
}
static void process_result (MYSQL *conn, MYSQL_RES *result)
{
MYSQL_FIELD *field;
MYSQL_ROW row;
unsigned int i, col_len;
/* column display width */
mysql_field_seek (result, 0);
for (i = 0; i < mysql_num_fields (result); i++) {
field = mysql_fetch_field (result);
col_len = strlen (field->name);
if (col_len < field->max_length)
col_len = field->max_length;
if (col_len < 4 && !IS_NOT_NULL (field->flags))
col_len = 4;
field->max_length = col_len;
}
print_dashes (result);
fputc ('|', stdout);
mysql_field_seek (result, 0);
for (i = 0; i < mysql_num_fields (result); i++) {
field = mysql_fetch_field (result);
printf (" %-*s |", field->max_length, field->name);
}
fputc ('\n', stdout);
print_dashes (result);
while ((row = mysql_fetch_row (result)) != NULL) {
mysql_field_seek (result, 0);
fputc ('|', stdout);
for (i = 0; i < mysql_num_fields (result); i++) {
field = mysql_fetch_field (result);
if (row[i] == NULL)
printf (" %-*s |", field->max_length, "NULL");
else if (IS_NUM (field->type))
printf (" %-*s |", field->max_length, row[i]);
else
printf (" %-*s |", field->max_length, row[i]);
}
fputc ('\n', stdout);
}
print_dashes (result);
printf ("%lu rows returned\n",
(unsigned long) mysql_num_rows (result));
}
