Client 4An Interactive Query Program
Client 4—An Interactive Query Program
Let's put together much of what we've developed so far and use it to write a simple interactive client,
client4
. This program lets you enter queries, executes them using our general purpose query handler
process_query()
, and displays the results using the
process_result_set()
display formatter developed in the
client4 will be similar in some ways to mysql , although of course not with as many features. There are several restrictions on what client4 will allow as input:
It turns out that
client4
is almost completely trivial to write (about a
To construct client4 , begin by copying the client skeleton client3.c to client4.c . Then add to that the code for the process_query() , process_result_set() , and print_dashes() functions. Finally, in client4.c , look for the line in main() that says this: /* ... issue queries and process results here ... */ Replace that line with the following while loop:
while (1)
{
char buf[10000];
fprintf (stderr, "query> "); /* print prompt */
if (fgets (buf, sizeof (buf), stdin) == NULL) /* read query */
break;
if (strcmp (buf, "quit\n") == 0 strcmp (buf, "\q\n") == 0)
break;
process_query (conn, buf); /* execute query */
}
Compile client4.c to produce client4.o , link client4.o with the client library to produce client4 , and you're done. You have an interactive MySQL client program that can execute any query and display the results. The following example shows how the program works, both for SELECT and non- SELECT queries, as well as for statements that are erroneous: % ./client4 query> USE sampdb 0 rows affected query> SELECT DATABASE(), USER() +------------+-------------------+ DATABASE() USER() +------------+-------------------+ sampdb sampadm@localhost +------------+-------------------+ 1 rows returned query> SELECT COUNT(*) FROM president +----------+ COUNT(*) +----------+ 42 +----------+ 1 rows returned query> SELECT last_name, first_name FROM president ORDER BY last_name LIMIT 3 +-----------+-------------+ last_name first_name +-----------+-------------+ Adams John Adams John Quincy Arthur Chester A. +-----------+-------------+ 3 rows returned query> CREATE TABLE t (i INT) 0 rows affected query> SELECT j FROM t Could not execute query Error 1054 (Unknown column 'j' in 'field list') query> USE mysql Could not execute query Error 1044 (Access denied for user: 'sampadm@localhost' to database 'mysql') |
Writing Clients That Include SSL SupportMySQL 4 includes SSL support, which you can use in your own programs to access the server over secure connections. To show how this is done, this section describes the process of modifying client4 to produce a similar client named sslclient that outwardly is much the same but allows encrypted connections to be established. For sslclient to work properly, MySQL must have been built with SSL support, and the server must be started with the proper options that identify its certificate and key files. You'll also need certificate and key files on the client end. For more information, see the "Setting Up Secure Connections" section in Chapter 12, "Security." In addition, you should use MySQL 4.0.5 or later. The SSL and option-handling routines for earlier 4.0.x releases will not behave quite as described here. The sampdb distribution contains a source file, sslclient.c , from which the client program sslclient can be built. The following procedure describes how sslclient.c is created, beginning with client4.c :
If you follow the preceding procedure, the usual
load_defaults()
and
handle_options()
routines will take care of parsing the SSL-related options and setting their values for you automatically. The only other thing you need to do is pass SSL option information to the client library before connecting to the server if the options
/* initialize connection handler */
conn = mysql_init (NULL);
if (conn == NULL)
{
print_error (NULL, "mysql_init() failed (probably out of memory)");
exit (1);
}
#ifdef HAVE_OPENSSL
/* pass SSL information to client library */
if (opt_use_ssl)
mysql_ssl_set (conn, opt_ssl_key, opt_ssl_cert, opt_ssl_ca,
opt_ssl_capath, opt_ssl_cipher);
#endif
/* connect to server */
if (mysql_real_connect (conn, opt_host_name, opt_user_name, opt_password,
opt_db_name, opt_port_num, opt_socket_name, opt_flags) == NULL)
{
print_error (conn, "mysql_real_connect() failed");
mysql_close (conn);
exit (1);
}
Note that you don't test mysql_ssl_set() to see if it returns an error. Any problems with the information you supply to that function will result in an error when you call mysql_real_connect() . Produce sslclient by compiling sslclient.c and then run it. Assuming that the mysql_real_connect() call succeeds, you can proceed to issue queries. If you invoke sslclient with the appropriate SSL options, communication with the server should occur over an encrypted connection. To determine whether or not that is so, issue the following query: SHOW STATUS LIKE 'Ssl_cipher'
The value of
Ssl_cipher
will be non-blank if an encryption cipher is in use. (To make this easier, the version of
sslclient
included in the
sampdb
distribution actually issues the query for you and
|
Similar products
Similar products
- Visual Studio Tools for Office(c) Using C# with Excel, Word, Outlook, and InfoPath
Word object model - Visual Studio Tools for Office(c) Using C# with Excel, Word, Outlook, and InfoPath
Excel xml form visual studio - 101 Microsoft Visual Basic. NET Applications
Books on vb.net toolbox controls - Mastering Delphi 7
Select from * where id and name and delphi 7