Calling SQL from a Program

If you want a downloadable copy of the program without the explanation embedded in it, follow this link, it the programs are exactly the same. A reminder of how to compile and run all this appears at the end.

This example program shows all that is required to use SQL functions in a standard C program.

#include 
#include 
#include "/usr/local/include/mysql/mysql.h" 

void main(void)
{ MYSQL *sql;
  MYSQL_RES *r;
  MYSQL_ROW row;

The MYSQL data type is used to store a handle on a connection to the database server. A handle is made when you successfully connect, and must be provided as a parameter to every subsequent operation. It stores details of your connection, plus error messages and result codes. MYSQL_RES is a kind of cursor for walking through the results of a query row-by-row, and MYSQL_ROW is capable of storing one whole row. It is really just an array of strings, one for each field or column. Don't forget to replace the "xxxxxxxxxx" with a real password. Or even better, make the user type the password in, then it won't appear in the program source.

  int num_fields, i, j, notok;
  char *mypassword="xxxxxxxxxx";

  sql=mysql_real_connect(NULL, NULL, NULL, mypassword, 0, NULL, 0);
  if (sql==NULL)
  { printf("Failed to connect to server\n");
    exit(1); }

Before anything can be done, you must connect to the SQL server. mysql_real_connect does that. It returns a handle on your connection that must be provided as a parameter in all other calls. The first parameter should always be NULL. The second is a string, giving the internet address of the SQL server, NULL means use the computer that this program is running on (the "local host"). The third is your username (what you logged in as is the default). The fourth is your password (your SQL database password, not your unix login password). The next is the port number that the server is running on (zero means use the normal default). The next lets you specify a weird way to connect, and the final parameter lets you make small changes to the way answers are reported to you. The connect function returns 0 (which C understands as false) if it is successful.

  notok=mysql_select_db(sql,"class");
  if (notok)
  { printf("mysql_select_db failed: %s\n", mysql_error(sql));
    exit(1); }
  else 
    printf("selected database ok\n");

mysql_error returns a string describing and error that may have happened during the last operation. A similar function mysql_info returns a string describing the results of successful operations.

Once a connection is made, you must tell it which database to use. You have access to two databases, one with your username (which you can modify and mutilate as much as you like), and one called "class" which you can only read. mysql_select_db lets you change which database is being used.

Normally you should not just exit if there is an error. Close your connection (shown at the end of this example) and tidy up any messes your program has made first.

Next, we ask the server what tables are available in the current database. This works in exactly the same way as general queries, but is a little simpler to see, because it returns the result (a list of table names) as a list of rows each with just one field. The function mysql_list_tables asks the question "What tables have I got?":

  r=mysql_list_tables(sql, NULL);
  if (r==NULL)
  { printf("mysql_list_tables failed: %s\n", mysql_error(sql));
    exit(1); }

Immediately after any successful query, the function mysql_num_fields tells you how manu fields each row of the result has (i.e. how many columns in the result table). In this case, the answer must be 1, becuase we just asked for a list of table names. It does not tell you how many rows there are. However, there is a function called mysql_num_rows, used in exactly the same way, which does tell you how many rows to expect if you need to know.

  num_fields=mysql_num_fields(r);
  if (num_fields!=1)
  { printf("Something funny here!\n");
    exit(1); }

The next thing to do is to retrieve each row in turn, and print it. The function mysql_fetch_row retrieves the next row from a cursor. A row is just an array of strings (remember arrays are indexed starting from 0), so the little loop below whould print out all the table names.

  while (1)
  { row=mysql_fetch_row(r);
    if (row==NULL) break;
    printf("Table: %s\n", row[0]); }

A cursor occupies dynamic memory. If you don't realease it when you are finished with it, you will gradually strangle the system until it all falls down in a big ugly heap. mysql_free_result should always be used after every successful use of mysql_list_tables or any other function that returns a cursor.

  mysql_free_result(r);

Now we'll perform a more general query. Every main database operation is considered a query, even insert and update operations. Put the query in a string (no semicolon at the end) and give it to mysql_query. The result is again 0 for success.

  notok=mysql_query(sql, "select * from people where state='UT'");
  if (notok)
  { printf("mysql_query failed: %s\n", mysql_error(sql));
    exit(1); }
  else 
    printf("query accepted: %s\n", mysql_info(sql));

Unlike the list-tables special function, mysql_query does not return a cursor for inspecting the results until you explicitly ask for one. mysql_store_result, which is not a very suggestive name, asks for a cursor for the last query's results. It is used exactly in the same way as the previous one was. First we ask it how many fields each row will have, then we enter a loop fetching each row from the cursor, with an inner loop printing each field from the row.

  r=mysql_store_result(sql);
  if (r==NULL)
  { printf("mysql_store_result failed: %s\n", mysql_error(sql));
    exit(1); }
  printf("Utah Residents:\n");
  j=0;
  num_fields=mysql_num_fields(r);
  while (1)
  { row=mysql_fetch_row(r);
    if (row==NULL) break;
    j+=1;
    printf(" %2d: _", j);
    for (i=0; i<num_fields; i+=1)
    { printf("%s_", row[i]); }
    printf("\n"); }
  printf("total of %d results\n",j);
  mysql_free_result(r);

Don't forget the mysql_free_result again, to release the memory used by the cursor.

When you have no more queries to send, close the connection to the database with mysql_close and that's it.

  mysql_close(sql);
  printf("All done\n"); }

Compiling and running

The command to compile this kind of program must tell the compiler which special libraries to load, and where to look for them. You've got to get the command exactly right. Capitalisation is significant; don't add spaces where the don't belong. There are no digits in this command; anything that looks like a 1 (one) is really an l (little 'L'). Assuming that your program is in a file called db,c, this is the command to compile it:

    cc db.c -L/usr/local/lib/mysql -lm -lmysqlclient -o db

If it compiles successfully, just the command db will run it, and you should see something exactly like this:

selected database ok
Table: airports
Table: flights
Table: people
Table: reservations
query accepted: (null)
Utah Residents:
  1: _10055_F_Ms_Usffav_Kuk_16533 E 32nd Av_Sidemefville_UT_60034_319-674-3074_
  2: _10299_M_Mr_Spimkey_Auree_8030 Pas Pl_Lapefes_UT_60281_402-381-6923_
  3: _10415_F_Ms_Slech_Duellc_9444 E Ufu Av_Sidemefville_UT_60034_309-678-3018_
  4: _10435_F_Ms_Breyckly_Agleoch_6838 Agi Ln_Sidemefville_UT_60034_309-678-7656_
  5: _10454_M_Mr_Jehey_Kamd_3167 S Depos Rd_Picinaloria_UT_59540_907-458-3434_
  6: _10455_F_Mrs_Inad_Kamd_3167 S Depos Rd_Picinaloria_UT_59540_907-458-3434_
  7: _10658_M_Mr_Hiam_Quoezou_6554 NW Mago Blvd_Lapefes_UT_60281_402-381-4329_
  8: _10814_F_Ms_Iachu_Tresloomp_13495 NW 89th Blvd_Lapefes_UT_60281_402-383-2783_
  9: _11069_M_Mr_Dreesh_Frek_7857 NW Dega Blvd_Lapefes_UT_60281_402-381-0509_
 10: _11072_M_Mr_Shoadd_Ochliusnna_8480 E Erunu Av_Sidemefville_UT_60034_319-678-7032_
 11: _11073_F_Ms_Plieckweo_Ochliusnna_8480 E Erunu Av_Sidemefville_UT_60034_319-678-7032_
 12: _11074_M_Mr_Ub_Ochliusnna_8480 E Erunu Av_Sidemefville_UT_60034_319-678-7032_
 13: _11163_M_Mr_Skaymm_Pek_1986 Ocuf Ct_Obibapasville_UT_60528_513-898-5450_
 14: _11188_F_Miss_Queobbloi_Scang_2950 NW 133rd Blvd_Lapefes_UT_60281_412-385-8703_
 15: _11233_F_Miss_Braze_Ploydd_7364 E 142nd Av_Sidemefville_UT_60034_309-678-5212_
 16: _11508_F_Mrs_Mabo_Pod_14927 W 81st Ct_Iropol_UT_59787_218-961-4505_
 17: _11534_M_Mr_Kaupp_Fep_7421 NE Besegem Pl_Obibapasville_UT_60528_503-894-3412_
 18: _11535_F_Ms_Erai_Fep_7421 NE Besegem Pl_Obibapasville_UT_60528_907-450-1278_
 19: _11536_F_Ms_Ayme_Fep_7421 NE Besegem Pl_Obibapasville_UT_60528_907-450-1278_
 20: _11586_F_Ms_Keow_Floyfis_728 E Iti Av_Sidemefville_UT_60034_319-674-7096_
 21: _11630_F_Mrs_Jimoi_Rauxoamg_8966 Laf St_Picinaloria_UT_59540_907-458-9898_
 22: _11670_F_Mrs_Aiyillc_Ayldee_3305 Let St_Picinaloria_UT_59540_917-452-7274_
 23: _11914_M_Mr_Bwepoi_Uye_3674 E Ufi Av_Sidemefville_UT_60034_319-672-5638_
total of 23 results
All done