Abstract:
A method for retrieving data from a database and providing the data to a client is provided. In one embodiment, a server receives a request for data from a client. The request comprises a set of parameters, which in one embodiment comprises three parameters: an SQL SELECT statement, an indication as the number of records per page, and an indication as to the page number of the one page to return. Database server paging software on the server analyzes the set of parameters to determine and create an appropriate page of data. The server then transmits the page of data to the client. Thus, only a single request by the client is necessary and the client is not required to sift through a large key set as in the prior art.

Description:
BACKGROUND OF THE INVENTION  
       [0001]     1. Technical Field  
         [0002]     The present invention relates generally to computer software and, more particularly, to databases, and even more particularly to retrieving and providing data from databases.  
         [0003]     2. Description of Related Art  
         [0004]     When an end user has the ability to select a set of records from a database that match certain criteria, the number of records that meet the criteria could be very large. Sending this large set of records to the user&#39;s client machine is problematic, because it could take too much time, and the user&#39;s machine might not have enough memory to contain the entire set. The solution is to send one page of records from the entire set, and give the user the ability to select any page at random from the entire set.  
         [0005]     One common prior art solution to this problem is the stored key method. In this method the primary keys of ALL the selected records are sent to the client. The client displays the selected page by finding the correct set of keys, and re-queries the server with the page of keys to get the rest of the fields for those records. However, the key set returned by the stored key method could be very large. Furthermore, the stored key method requires two requests to the server per page. Therefore, it would be desirable to have a method, system, and computer program product for providing database application programs with the ability to decompose a set of records into a number of pages with an arbitrary number of records per page, and return any one of those pages to a requesting client based on a single request from the client and eliminating or reducing other undesirable aspects of the stored key method.  
       SUMMARY OF THE INVENTION  
       [0006]     The present invention provides a method for retrieving data from a database and providing the data to a client. In one embodiment, a server receives a request for data from a client. The request comprises a set of parameters, which in one embodiment comprises three parameters: an SQL SELECT statement, an indication as the number of records per page, and an indication as to the page number of the one page to return. Database server paging software on the server analyzes the set of parameters to determine and create an appropriate page of data. The server then transmits the page of data to the client. Thus, only a single request by the client is necessary and the client is not required to sift through a large key set as in the prior art.  
     
    
     BRIEF DESCRIPTION OF THE DRAWINGS  
       [0007]     The novel features believed characteristic of the invention are set forth in the appended claims. The invention itself, however, as well as a preferred mode of use, further objectives and advantages thereof, will best be understood by reference to the following detailed description of an illustrative embodiment when read in conjunction with the accompanying drawings, wherein:  
         [0008]      FIG. 1  depicts a pictorial representation of a distributed data processing system in which the present invention may be implemented;  
         [0009]      FIG. 2  depicts a block diagram of a data processing system which may be implemented as a server is depicted in accordance with the present invention;  
         [0010]      FIG. 3  depicts a block diagram of a data processing system in which the present invention may be implemented;  
     
    
     DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENT  
       [0011]     With reference now to the figures, and in particular with reference to  FIG. 1 , a pictorial representation of a distributed data processing system is depicted in which the present invention may be implemented.  
         [0012]     Distributed data processing system  100  is a network of computers in which the present invention may be implemented. Distributed data processing system  100  contains network  102 , which is the medium used to provide communications links between various devices and computers connected within distributed data processing system  100 . Network  102  may include permanent connections, such as wire or fiber optic cables, or temporary connections made through telephone connections.  
         [0013]     In the depicted example, server  104  is connected to network  102 , along with storage unit  106 . In addition, clients  108 ,  110  and  112  are also connected to network  102 . These clients,  108 ,  110  and  112 , may be, for example, personal computers or network computers. For purposes of this application, a network computer is any computer coupled to a network that receives a program or other application from another computer coupled to the network. In the depicted example, server  104  provides data from database  106  to clients  108 - 112 . Clients  108 ,  110  and  112  are clients to server  104 . Distributed data processing system  100  may include additional servers, clients, and other devices not shown.  
         [0014]     The database server paging system which provides data from database  106  exists, in one embodiment, as one stored procedure on server  104 , which in this embodiment is a Structured Query Language (SQL) server. An SQL server is a relational Database Management System (DBMS) server, i.e., a server that controls the organization, storage, retrieval, security and integrity of data in a database. The SQL server accepts requests from applications and instructs the operating system to transfer the appropriate data.  
         [0015]     SQL is a language used to interrogate and process data in a relational database. Originally developed by IBM for its mainframes, all database systems designed for client/server environments currently support SQL. SQL commands can be used to interactively work with a database or can be embedded within a programming language to interface to a database. Programming extensions to SQL have turned it into a full-blown database programming language, and all major database management systems (DBMSs) currently support the language.  
         [0016]     The client application on any one of clients  108 - 112  sends parameters to the database server paging system on server  104 . The database server paging system then generates a dynamic SQL statement to implement the solution and return the requested page. Although depicted as a separate component, database  106  may be implemented as a component within server  104 . The database server paging system of the present invention is descried in more detail below.  
         [0017]     In the depicted example, distributed data processing system  100  is the Internet, with network  102  representing a worldwide collection of networks and gateways that use the TCP/IP suite of protocols to communicate with one another. At the heart of the Internet is a backbone of high-speed data communication lines between major nodes or host computers consisting of thousands of commercial, government, education, and other computer systems that route data and messages. Of course, distributed data processing system  100  also may be implemented as a number of different types of networks such as, for example, an intranet, a local area network (LAN), or a wide area network (WAN). Appropriate security measures, many of which are well known in the art, may also be implemented to protect the privacy and integrity of data retrieved from database  106 , such as, for example, encrypting data before transmission and utilization of a virtual private network (VPN).  
         [0018]      FIG. 1  is intended as an example and not as an architectural limitation for the processes of the present invention.  
         [0019]     Referring to  FIG. 2 , a block diagram of a data processing system which may be implemented as a server, such as any one of servers  104 - 106  in  FIG. 1 , is depicted in accordance with the present invention. Data processing system  200  may be a symmetric multiprocessor (SMP) system including a plurality of processors  202  and  204  connected to system bus  206 . Alternatively, a single processor system may be employed. Also connected to system bus  206  is memory controller/cache  208 , which provides an interface to local memory  209 . I/O bus bridge  210  is connected to system bus  206  and provides an interface to I/O bus  212 . Memory controller/cache  208  and I/O bus bridge  210  may be integrated as depicted.  
         [0020]     Peripheral component interconnect (PCI) bus bridge  214  connected to I/O bus  212  provides an interface to PCI local bus  216 . A number of modems  218 - 220  may be connected to PCI bus  216 . Typical PCI bus implementations will support four PCI expansion slots or add-in connectors. Communications links to network computers  108 - 112  in  FIG. 1  may be provided through modem  218  and network adapter  220  connected to PCI local bus  216  through add-in boards.  
         [0021]     Additional PCI bus bridges  222  and  224  provide interfaces for additional PCI buses  226  and  228 , from which additional modems or network adapters may be supported. In this manner, server  200  allows connections to multiple network computers. A memory mapped graphics adapter  230  and hard disk  232  may also be connected to I/O bus  212  as depicted, either directly or indirectly.  
         [0022]     Those of ordinary skill in the art will appreciate that the hardware depicted in  FIG. 2  may vary. For example, other peripheral devices, such as optical disk drives and the like, also may be used in addition to or in place of the hardware depicted. The depicted example is not meant to imply architectural limitations with respect to the present invention.  
         [0023]     Data processing system  200  may be implemented as, for example, an AlphaServer GS1280 running a UNIX® operating system. AlphaServer GS1280 is a product of Hewlett-Packard Company of Palo Alto, Calif. “AlphaServer” is a trademark of Hewlett-Packard Company. “UNIX” is a registered trademark of The Open Group in the United States and other countries  
         [0024]     With reference now to  FIG. 3 , a block diagram of a data processing system in which the present invention may be implemented is illustrated. Data processing system  300  is an example of a client computer. Data processing system  300  employs a peripheral component interconnect (PCI) local bus architecture. Although the depicted example employs a PCI bus, other bus architectures, such as Micro Channel and ISA, may be used. Processor  302  and main memory  304  are connected to PCI local bus  306  through PCI bridge  308 . PCI bridge  308  may also include an integrated memory controller and cache memory for processor  302 . Additional connections to PCI local bus  306  may be made through direct component interconnection or through add-in boards. In the depicted example, local area network (LAN) adapter  310 , SCSI host bus adapter  312 , and expansion bus interface  314  are connected to PCI local bus  306  by direct component connection. In contrast, audio adapter  316 , graphics adapter  318 , and audio/video adapter (A/V)  319  are connected to PCI local bus  306  by add-in boards inserted into expansion slots. Expansion bus interface  314  provides a connection for a keyboard and mouse adapter  320 , modem  322 , and additional memory  324 . In the depicted example, SCSI host bus adapter  312  provides a connection for hard disk drive  326 , tape drive  328 , CD-ROM drive  330 , and digital video disc read only memory drive (DVD-ROM)  332 . Typical PCI local bus implementations will support three or four PCI expansion slots or add-in connectors.  
         [0025]     An operating system runs on processor  302  and is used to coordinate and provide control of various components within data processing system  300  in  FIG. 3 . The operating system may be a commercially available operating system, such as Windows XP, which is available from Microsoft Corporation of Redmond, Wash. “Windows XP” is a trademark of Microsoft Corporation. An object oriented programming system, such as Java, may run in conjunction with the operating system, providing calls to the operating system from Java programs or applications executing on data processing system  300 . Instructions for the operating system, the object-oriented operating system, and applications or programs are located on a storage device, such as hard disk drive  326 , and may be loaded into main memory  304  for execution by processor  302 .  
         [0026]     Those of ordinary skill in the art will appreciate that the hardware in  FIG. 3  may vary depending on the implementation. For example, other peripheral devices, such as optical disk drives and the like, may be used in addition to or in place of the hardware depicted in  FIG. 3 . The depicted example is not meant to imply architectural limitations with respect to the present invention. For example, the processes of the present invention may be applied to multiprocessor data processing systems.  
         [0027]     With reference now to  FIG. 4 , a diagram illustrating an exemplary process flow and program function is depicted in accordance with one embodiment of the present invention. In this embodiment, a client application  402 , which may be implemented on, for example, data processing system  300  in  FIG. 3 , calls a stored procedure on SQL server  404 , which may be implemented as, for example, server  200  depicted in  FIG. 2 , and supplies SQL server  404  with the following parameters: a SQL SELECT statement, the number of records per page, and the page number of the one page to return (step  408 ). An example of a call to the SQL server is GetPage(query, 15, 201)  406 , where “query” would be the SQL SELECT statement, “15” the number of records per page, and “201” the page number of the one page to return to client application  402 . The database server paging system on SQL server  404  then creates a cursor based on the supplied query parameter (step  410 ). The database server paging system then creates cursor variables to hold the values from each record fetched by the cursor (step  412 ).  
         [0028]     Next, the database server paging system creates a table variable to accumulate the records fetched by the cursor (step  414 ). The cursor then moves to the first record of the requested page (step  416 ). The cursor then fetches one record into the set of cursor variables (Step  418 ). The values in the cursor variables are inserted into the table variable (step  420 ). When enough records are inserted into the table variable to fill one page, the contents of the table variable are returned to the client (step  422 ).  
         [0029]     The procedure outlined in  FIG. 4  is presented merely as an example of a database server paging system and not as an architectural limitation to the present invention. For example, in other embodiments, in step  414 , a temporary table could be used instead of a table variable. This would require the database server paging system to delete the temporary table after it sends the page. However, the creation and deletion of a temporary table is a bit more time consuming than the creation and deletion of a table variable. Other variations and modifications will be apparent to those of ordinary skill in the art.  
         [0030]     Thus, the present invention provides that, with a single request to the SQL server, the client application  402  is able to obtain data from a database related to the query of the client and the data is provided in a manageable form of a single page rather than an unmanageable amount of data as in the prior art. Therefore, if the client may peruse the data in the page transferred from the SQL server to determine if that data satisfies the goals of the request and, if not, another page may be obtained from the SQL server. Thus, data transfer from the SQL server to the client is also minimized.  
         [0031]     It is important to note that while the present invention has been described in the context of a fully functioning data processing system, those of ordinary skill in the art will appreciate that the processes of the present invention are capable of being distributed in the form of a computer readable medium of instructions and a variety of forms and that the present invention applies equally regardless of the particular type of signal bearing media actually used to carry out the distribution. Examples of computer readable media include recordable-type media such a floppy disc, a hard disk drive, a RAM, and CD-ROMs and transmission-type media such as digital and analog communications links.  
         [0032]     The description of the present invention has been presented for purposes of illustration and description, but is not intended to be exhaustive or limited to the invention in the form disclosed. Many modifications and variations will be apparent to those of ordinary skill in the art. The embodiment was chosen and described in order to best explain the principles of the invention, the practical application, and to enable others of ordinary skill in the art to understand the invention for various embodiments with various modifications as are suited to the particular use contemplated.