Jsp, Tomcat, J2Ee, Linux Web Hosting, Hibernate Programming Blog

CHAPTER 23 Introducing PDO The number of available software solutions is simultaneously a blessing and a curse. While this embarrassment of riches is of great advantage to end users, allowing them to find products that meets their specific needs, it s long proven to be a nightmare for developers and system administrators, requiring that two or more distinct products transparently interoperate. Although adherence to standards such as XML is greatly contributing to interoperability efforts, we re still years away from any widespread resolution. This problem is particularly pronounced for applications requiring a database back end. While all mainstream databases adhere to the SQL standard, albeit to varying degrees, the interfaces that programmers depend upon to interact with the database can vary greatly (even if the queries are largely the same). Therefore, applications are almost invariably bound to a particular database, forcing users to also install and maintain the required database if they don t already own it, or alternatively to choose another, possibly less-capable solution that is compatible with their present environment. For instance, suppose your organization requires an application that runs exclusively on Oracle, but your organization is standardized on an open-source database. Are you prepared to invest the considerable resources required to purchase the necessary Oracle licenses, and prepared to maintain the database, all for the sake of running this particular application? To resolve such dilemmas, enterprising programmers began developing database abstraction layers, which serve to decouple the application logic from that used to communicate with the database. By passing all database-related commands through this generalized interface, it became possible for an application to use one of several database solutions, provided that the database supported the features required by the application, and that the abstraction layer offered a driver compatible with that database. A graphical depiction of this process is found in Figure 23-1.
Please visit our professional web hosting services to find out about cheap and reliable webhost service that will surely answer all your demands.

CHAPTER 22 SQLITE If your employees salaries total $16,000, you could expect the following output: The employees can purchase 40 ounces. Summary The administrative overhead required of many database servers often outweighs the advantages of added power they offer to many projects. SQLite offers an ideal remedy to this dilemma, providing a fast and capable back end at a cost of minimum maintenance. Given SQLite s commitment to standards, ideal licensing arrangements, and quality, consider saving yourself time, resources, and money by using SQLite for your future projects.
We recommend cheap and reliable webhost to host and run your web applications: Coldfusion Web Hosting services.

552 CHAPTER 22 SQLITE sqlite_create_aggregate() boolean sqlite_create_aggregate (resource dbh, string func, mixed step_func, mixed final_func [, int num_args]) The sqlite_create_aggregate()function is used to register a user-defined aggregate function, step_func. Actually it registers two functions: step_func, which is called on every row of the query target, and final_func, which is used to return the aggregate value back to the caller. Once registered, you can call final_func within the caller by the alias func. The optional num_args parameter specifies the number of parameters the aggregate function should take. Although the SQLite parser attempts to discern the number if this parameter is omitted, you should always include it for clarity s sake. Consider an example. Building on the salary conversion example from the previous section, suppose you want to calculate the total amount of gold employees could collectively purchase:
From our experience, we can recommend PHP Web Hosting services, if you need affordable webhost to host and run your web application.

CHAPTER 22 SQLITE /* Create the user-defined function. */ sqlite_create_function($sqldb,”salarytogold”, “convert_salary_to_gold”, 1); /* Query the database using the UDF. */ $query = “select salarytogold(salary) FROM employee WHERE empid=1″; $result = sqlite_query($sqldb, $query); list($salaryToGold) = sqlite_fetch_array($result); /* Display the results. */ echo “The employee can purchase: “.$salaryToGold.” ounces.”; /* End the database connection. */ sqlite_close($sqldb); ?> Assuming user Jason makes $10,000 per year, you can expect the following output: The employee can purchase 25 ounces. sqlite_udf_encode_binary() string sqlite_udf_encode_binary (string data) The sqlite_udf_encode_binary() function encodes any binary data intended for storage within an SQLite table. Use this function instead of sqlite_escape_string() when you re working with data sent to a UDF. sqlite_udf_decode_binary() string sqlite_udf_decode_binary (string data) The sqlite_udf_decode_binary() function decodes any binary data previously encoded with the sqlite_udf_encode_binary() function. Use this function when you re returning possibly binary unsafe data from a UDF. Creating Aggregate Functions When you work with database-driven applications, it s often useful to derive some value based on some collective calculation of all values found within a particular column or set of columns. Several such functions are commonly made available within a SQL server s core functionality set. A few such commonly implemented functions, known as aggregate functions, include sum(), max(), and min(). However, you might require a custom aggregate function not otherwise available within the server s default capabilities. SQLite compensates for this by offering the ability to create your own. The function used to register your custom aggregate functions, sqlite_create_aggregate(), is introduced in this section.
In case you need quality webspace to host and run your web applications, try our personal web hosting services.

550 CHAPTER 22 SQLITE Note The NULL character typically signals the end of a binary string, while 0×01 is the escape character used within binary data. Therefore, to ensure that the escape character was properly interpreted by the binary data parser, it would need to be decoded. When you re using user-defined functions, a topic discussed in the next section, you should never use this function. Instead, use the sqlite_udf_encode_binary() and sqlite_udf_decode_binary() functions. Both are introduced in the next section. Creating and Overriding SQLite Functions An intelligent programmer will take every opportunity to reuse code. Because many databasedriven applications often require the use of a core task set, there are ample opportunities to reuse code. Such tasks often seek to manipulate database data, producing some sort of outcome based on the retrieved data. As a result, it would be quite convenient if the task results could be directly returned via the SQL query, like so: sqlite>SELECT convert_salary_to_gold(salary) …> FROM employee WHERE empID=1″; PHP s SQLite library offers a means for registering and maintaining customized functions such as this. This section shows you how it s accomplished. sqlite_create_function() boolean sqlite_create_function (resource dbh, string func, mixed callback [, int num_args]) The sqlite_create_function() function enables you to register custom PHP functions as SQLite user-defined functions (UDFs). For example, this function would be used to register the convert_salary_to_gold() function discussed in the opening paragraphs of this section, like so: From our experience, we are can tell you that you can find a reliable and cheap webhost service at Java Web Hosting services.

CHAPTER 22 SQLITE This example returns: Array ( [empid] => integer [name] => varchar(25) [title] => varchar(25) ) Working with Binary Data SQLite is capable of storing binary information in a table, such as a GIF or JPEG image, a PDF document, or a Microsoft Word document. However, unless you treat this data carefully, errors in both storage and communication could arise. Several functions are available for carrying out the tasks necessary for managing this data, one of which is introduced in this section. The other two relevant functions are introduced in the next section. sqlite_escape_string() string sqlite_escape_string (string item) Some characters or character sequences have special meaning to a database, and therefore they must be treated with special care when trying to insert them into a table. For example, SQLite expects that single quotes signal the delimitation of a string. However, because this character is often used within data that you might want to include in a table column, a means is required for tricking the database server into ignoring single quotes on these occasions. This is commonly referred to as escaping these special characters, often done by prefacing the special character with some other character, a single quote (’) for example. Although you can do this manually, a function is available that will do the job for you. The sqlite_escape_string() function escapes any single quotes and other binary-unsafe characters intended for insertion in an SQLite table found in item. Let s use this function to escape an otherwise invalid query string: This returns: As they always say, this is ‘’an'’ example. If the string contains a NULL character or begins with 0×01, circumstances that have special meaning when working with binary data, sqlite_escape_string() will take the steps necessary to properly encode the information so that it can be safely stored and later retrieved.
Searching for affordable and reliable webhost to host and run your web applications? Go to our java web server services and you will be pleased.

548 CHAPTER 22 SQLITE This returns the following (this shows only one of three possible outcomes): Randomly chosen employee of the month: Ray Fox (Employee ID: 3) One point of common confusion that arises in this example regards the starting index offset of result sets. The offset always begins with 0, not 1, which is why you need to subtract 1 from the total rows returned in this example. As a result, the randomly generated row offset integer must fall within a range of 0 and one less than the total number of returned rows. Learning More About Table Schemas There is one function available for learning more about an SQLite table schema. It s introduced in this section. sqlite_fetch_column_types() array sqlite_fetch_column_types (string table, resource dbh) The function sqlite_fetch_column_types() returns an array consisting of the column types located in the table identified by table. The returned array includes both the associative and numerical hash indices. The following example outputs an array of column types located in the employee table used earlier in this chapter:
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.

CHAPTER 22 SQLITE “; if (sqlite_has_more($results)) echo “Still more rows to go!
“; else echo “No more rows!
“; } sqlite_close($sqldb); ?> This returns: Name: Jason Gilmore (Employee ID: 1) Still more rows to go! Name: Sam Spade (Employee ID: 2) Still more rows to go! Name: Ray Fox (Employee ID: 3) No more rows! sqlite_next() boolean sqlite_next (resource result) The sqlite_next() function moves the result set pointer to the next position, returning TRUE on success and FALSEif the pointer already resides at the end of the result set. sqlite_rewind() boolean sqlite_rewind (resource result) The sqlite_rewind() function moves the result set pointer back to the first row, returning FALSE if no rows exist in the result set and TRUE otherwise. sqlite_seek() boolean sqlite_seek (resource result, int row_number) The sqlite_seek() function moves the pointer to the row specified by row_number, returning TRUE if the row exists and FALSE otherwise. Consider an example in which an employee of the month will be randomly selected from a result set consisting of the entire staff:
Please visit our professional web hosting services to find out about cheap and reliable webhost service that will surely answer all your demands.

546 CHAPTER 22 SQLITE The sqlite_num_rows() function returns the number of rows located in the result_set. An example follows: “; sqlite_close($sqldb); ?> This returns: Total rows returned: 3 sqlite_changes() int sqlite_changes (resource dbh) The sqlite_changes() function returns the total number of rows affected by the most recent modification query. For instance, if an UPDATE query modified a field located in 12 rows, then executing this function following that query would return 12. Manipulating the Result Set Pointer Although SQLite is indeed a database server, in many ways it behaves much like what you experience when working with file I/O. One such way involves the ability to move the row pointer around the result set. Several functions are offered for doing just this, all of which are introduced in this section. sqlite_current() array sqlite_current (resource result [, int result_type [, bool decode_binary]]) The sqlite_current() function is identical to sqlite_fetch_array()in every way except that it does not advance the pointer to the next row of the result set. Instead, it only returns the row residing at the current pointer position. If the pointer already resides at the end of the result set, FALSE is returned. sqlite_has_more() boolean sqlite_has_more (resource result_set) The sqlite_has_more()function determines whether the end of the result_set has been reached, returning TRUE if additional rows are still available, and FALSE otherwise. An example follows:
You need excellent and relaible webhost company to host your web applications? Then pay a visit to Inexpensive Web Hosting services.

CHAPTER 22 SQLITE sqlite_field_name() string sqlite_field_name (resource result, int field_index) The sqlite_field_name() function returns the name of the field located at the index offset field_index found in the result set. For example: “; echo “Field name found at offset #1: “.sqlite_field_name($results,1).”
“; echo “Field name found at offset #2: “.sqlite_field_name($results,2).”
“; sqlite_close($sqldb); ?> This returns: Field name found at offset #0: empid Field name found at offset #1: name Field name found at offset #2: title As is the case with all numerically indexed arrays, the offset starts at 0, not 1. sqlite_num_fields() int sqlite_num_fields (resource result_set) The sqlite_num_fields() function returns the number of columns located in the result_set. For example: “; sqlite_close($sqldb); ?> This returns: Total fields returned: 3 sqlite_num_rows() int sqlite_num_rows (resource result_set)
Visit our web design programs services for an affordable and reliable webhost to suit all your needs.