Domain Name Hosting, Java Programming - Cheap Web Hosting Blog

158 Part II . SQL Essentials Common CLI Errors Using the CLI myself (and working with many people who are new to databases and the CLI), I ve seen quite a few error messages. Often the errors crop up simply because I can t seem to type well that day, but just as often they show up for other reasons that call for some simple troubleshooting. Access Denied errors The Access Deniederror has a number of variations, three of which are shown in Figure 8-32. Figure 8-32: Three variations of the Access Denied error with the CLI Here s what produced the errors shown in Figure 8-32: . In the first example I am trying to obtain access to the CLI as the user suehring but I am not specifying a password via the -pswitch. This try fails; the error message hints (not too subtly) why: Using Password: No. . In the second attempt, I add the -p switch but then press Enter instead of typing the password. Notice the similarity of result between the second and first attempts (though I didn t use the -p switch in the first one). . In the third attempt, I use the -p switch but mistype the password. Notice this time that the end of the error message is different: Using Password: Yes. Another variation of the Access Denied error is shown in Figure 8-33: I try to connect to a database on which I have no privileges or rights. As you can see, the error number is different, as is the message.
Note: In case you are looking for affordable webhost to host and run your servlet application check Vision mysql5 web hosting services

Chapter 8 . Command Line Interface (CLI) 157 scroll more than one screen and it will also tell the CLI to automatically prompt for the password on startup. This should be set in your local configuration file such as the .my.cnffile in your home directory in Linux. [mysql] pager=less password A MySQL administration configuration The following is an example of what an administrator s configuration might look like for MySQL. You would have to add a username of admin with proper privileges to the database. The configuration will connect as the user admin and prompt for a password. The default database to connect to is the mysqlgrantsdatabase that houses user rights and privileges. I ve chosen a default pager of less to specify fewer results on-screen (less to scroll through). I added the vertical option because I ve found that displaying the grant tables vertically really helps comprehension since most of the grant tables will wrap around the screen. Finally, all operations within the CLI will be appended to a logfile for tracking [mysql] user=admin password database=mysql pager=less vertical tee=mysqlaudit.log Note The tee option does not provide complete security or tracking for operations performed within a database. A client-only MySQL CLI configuration If you connect to a remote MySQL server frequently, adding a host= line to the basic configuration might help. With the host= configuration, the CLI automatically connects to the remote host without requiring you to specify the -hswitch on the command line. [mysql] host=192.168.1.75 pager=less password Your MySQL CLI configuration Your MySQL CLI configuration may look somewhat different from the ones I ve given as examples. Building and tailoring the configuration to your application and installation is one of many ways you can improve your experience with MySQL.
Note: If you are looking for high quality webhost to host and run your jsp application check Vision florida web design services

156 Part II . SQL Essentials Figure 8-31: Using batch mode to produce output with tab delimiters Cross-Some other commands and statements such as mysqldump or SELECT INTO Reference OUTFILE may be better suited to exporting data. Those commands are discussed in Chapter 10. Creating a Useful MySQL CLI Environment With all the options covered in this chapter, you may be wondering which ones you should use and whether to set the options globally (for all users of the CLI), or only for your user account, or only in your home directory. The answers are flexible, determined by what you need your database application to do. For example, if all users on the computer use the same username to connect to a particular database, you could make that usage a global setting in the MySQL configuration file. Alternatively, if you use a particular username but everyone else uses a different one, you could specify that setting in your local or personal MySQL configuration file. Tip Programmers still debate the matter of which pager command is best for displaying results. For practicality s sake, you may want to avoid the debate by specifying that setting only in a local or personal configuration file. A useful environment for the CLI can be as complicated or as simple as you d like it to be. Ideally, you could produce a configuration file personalized for your use of the MySQL CLI. With a good configuration, you could save time and increase productivity by not having to specify the same options every time you start the CLI. A basic MySQL CLI configuration The following configuration example might be a fine starting point for anyone s use of MySQL. The configuration provides a default pager of less for results that would
Note: If you are looking for reliable webhost to maintain and run your java application check Vision java hosting services

Chapter 8 . Command Line Interface (CLI) 155 Printing results vertically Just as the MySQL CLI can print results vertically when being used interactively to help improve layout, vertical output is sometimes useful in non-interactive mode as well. This is especially true for appending data to a file to be examined later. The switch to turn on the appropriate option is -E or –vertical. This format can be made permanent by adding the word vertical to your MySQL configuration file under the [mysql] section. Using batch mode to produce tab-delimited output To produce output that uses tabs as delimiters, you can use MySQL in batch mode. Turn batch mode on with the -B or –batchswitch (or add the word batch to your MySQL configuration file under the [mysql]section). In batch mode, the output from a SELECT statement would show values separated by tabs instead of by columns (useful if you re importing data into another application, because most applications recognize the tab character as a valid field separator). In Figure 8-30, I issue a simple SELECT statement in normal mode, which formats the fields as a table. Figure 8-30: A query in normal mode produces tabular-formatted output In Figure 8-31, I issue that same SELECTstatement, this time in batch mode. Notice that the output no longer has the tabular format. Instead, it shows tabs as delimiters. Using batch mode, I could redirect the output file and import it into another database application.
Note: In case you are looking for affordable webhost to host and run your servlet application check Vision ecommerce web hosting services

154 Part II . SQL Essentials In Figure 8-28, the output is redirected by using the greater-than sign (>) and the name of the file that receives the output is inventory.html. Caution In Linux, the single greater-than sign (>) causes the redirected output to overwrite whatever is in the receiving file. If you want merely to append the output instead of using it to replace what s in the file, use two greater-than signs (>>). After moving or copying the file (with the newly appended output) to a place where the Web server can get hold of it, I have a Web page consisting of a simple table that shows the products in the inventory. Figure 8-29 shows what it looks like. Figure 8-29: A simple Web page produced with the output from the -H switch Suppressing column names Every time MySQL runs a query, the output of the query includes column names at the top. If you want to exclude column names (as when you append the output to a different file), you can use an option to suppress them. To suppress column names, start the MySQL CLI with the -N or –skip-columnnames switch. To make this function permanent, add skip-column-names to your MySQL configuration file in the [mysql]section.
Note: If you are looking for high quality webhost to host and run your jsp application check Vision florida web design services

Chapter 8 . Command Line Interface (CLI) 153 Figure 8-27: The output from a HTML-formatted query that uses the CLI In Figure 8-27, the switches and the order of the command are what you would expect. Caution Whenever you add an option or a switch to the mysql command, be sure to use the correct case. For example, adding the -H option gives MySQL different instructions from those specified by the -h switch (lowercase h). Adding -h instead of -Hwould have made MySQL think you were specifying a host instead of calling for HTML output. As you can see from Figure 8-27, the output isn t pretty. The greatest usefulness of the HTML switch is redirecting output to a file, as shown in Figure 8-28. Figure 8-28: Redirecting the output from an HTML-formatted query
Note: If you are looking for cheap webhost to host and run your apache application check Vision apache web hosting services

152 Part II . SQL Essentials Using the CLI in Non-Interactive Mode The CLI need not be used interactively; sometimes running a simple query or command on the server does the job. For example, you may want to redirect the output of a statement to a file, or use a scheduler (such as cron) to schedule the running of a particular command. In such instances, the MySQL CLI can be used in a non- interactive mode that makes it operate like any basic, straightforward command. Executing a statement When you re operating in non-interactive mode, one of the most frequent and useful operations is to execute a statement. To do so, you use the -e or –execute= command-line switch. For example, to run a simple query that finds the users and hosts allowed to connect to a particular database, you can use the -eswitch as illustrated in Figure 8-26. Figure 8-26: Using the -e switch to run a simple query In Figure 8-26, I specified the -pswitch to have MySQL prompt for the password. Next comes the -e switch, followed by the statement to execute in quotes. Finally comes the name of the database to connect to (in this case, mysql, because that s where user data is stored). Printing HTML output One especially handy feature of the MySQL CLI is that it can output a query in HTML format a capability you can use to create a simple Web page from a query without much effort. The switch that produces HTML output is -H or –html. For example, suppose I want to make a Web page consisting of a simple report of the products in the ecommercedatabase example (yes, all three of them.) In Figure 8-27, I ran the command to produce the output.
Note: In case you are looking for affordable webhost to host and run your web application check Vision cheap hosting services

Chapter 8 . Command Line Interface (CLI) 151 Figure 8-24: Using the CLI in batch mode to get tab-delimited output while suppressing other information and feedback from the CLI. Figure 8-25: Errors that occur while you re using batch mode kick you back out to the command shell. Note You still have to terminate commands with a semicolon (;) while using batch mode interactively. If you want to prevent MySQL from terminating your session upon error when in batch mode, use the -f or –force switch. With the Force option set, the error message is displayed but the session continues, ignoring the error. Caution Use the force option with care; one error in the beginning of a series of SQL statements can wreak havoc on the rest of the statements and possibly the data.
Note: In case you are looking for affordable webhost to host and run your servlet application check Vision mysql5 web hosting services

150 Part II . SQL Essentials Figure 8-23: Here the Tee function has saved session data in the file. Printing output in HTML format Though using the CLI in non-interactive mode is generally the more useful approach, sometimes you may want to run a simple query and then copy and paste its output into a Web page as HTML output. To do so, use the -Hor –htmlswitch (or put the word htmlin your MySQL configuration file under the [mysql]section). Suppressing column names At times I ve found it useful to run a query without having the column names print in the output. From within the CLI, this technique can sometimes prevent some confusion by keeping the output from wrapping around on-screen. As with the switch that produces HTML output, this function may be best used in non-interactive mode though it s sometimes useful in interactive mode as well. To suppress column names, start the MySQL CLI with the -N or –skip-columnnames switch. To make this suppression permanent, add skip-column-namesto your MySQL configuration file in the [mysql] section. Using batch mode to produce tab delimited output The MySQL CLI also operates in a batch mode that suppresses traditional feedback from the CLI and presents query output in tab-delimited format (see Figure 8-24). If you enter batch mode and get an error message, the session is immediately terminated. You are sent back to the shell with an echo of the error message (as shown in Figure 8-25).
Note: If you are looking for cheap and reliable webhost to host and run your web application check Vision coldfusion web hosting services

Chapter 8 . Command Line Interface (CLI) 149 I added the following lines to the [mysql] section of the .my.cnfconfiguration file to produce the output in Figure 8-21. [mysql] host=localhost vertical pager=/usr/bin/less Note The use of –pager won t work when using the CLI in non-interactive mode. Using tee to save output The tee command takes the entire session including input and output from a command and appends it to a file that you specify. This function can be helpful when debugging (for security purposes), or simply as an easy way to get data into a file. The switch to use this function with MySQL is –tee=. Alternately the switch can be set in the MySQL configuration file with the use of tee= For example, Figure 8-22 uses the tee function to append the session onto a file called outputfile.txt. Figure 8-22: Using the tee function to append session data to the content of a file Figure 8-23 shows session data that has been saved to the outputfile.txt file. Note If the tee option is set in a MySQL configuration file, it can be turned off by using the –no-tee switch on the command line.
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision web and email hosting services