Developing Database Applications
DevelopingDatabase ApplicationsJBuilder 2005
Borland Software Corporation100 Enterprise WayScotts Valley, California 95066-3249www.borland.comRefer to the file deploy.html located in the redist directory of your JBuilder product for a complete listof files that you can distribute in accordance with the JBuilder License Statement and LimitedWarranty.Borland Software Corporation may have patents and/or pending patent applications covering subjectmatter in this document. Please refer to the product CD or the About dialog box for the list ofapplicable patents. The furnishing of this document does not give you any license to these patents.COPYRIGHT 1997–2004 Borland Software Corporation. All rights reserved. All Borland brand andproduct names are trademarks or registered trademarks of Borland Software Corporation in theUnited States and other countries. All other marks are the property of their respective owners.For third-party conditions and disclaimers, see the Release Notes on your JBuilder product CD.Printed in the U.S.A.JB2005database 10E13R08040405060708-9 8 7 6 5 4 3 2 1PDF
ContentsChapter 1Chapter 4IntroductionChapter summaries . . . . . . . . . . . .Database tutorials. . . . . . . . . . . . .Database samples . . . . . . . . . . . .Related documentation . . . . . . . . . .Documentation conventions . . . . . . .Developer support and resources. . . . .Contacting Borland Developer SupportOnline resources. . . . . . . . . . . .World Wide Web . . . . . . . . . . . .Borland newsgroups . . . . . . . . . .Usenet newsgroups . . . . . . . . . .Reporting bugs . . . . . . . . . . . .1Connecting to a database233467778888Connecting to databases . . . . . . . . . . . .Adding a Database component to yourapplication . . . . . . . . . . . . . . . . .Setting Database connection properties . . .Setting up JDataStore . . . . . . . . . . . . . .Setting up InterBase and InterClient. . . . . . .Using InterBase and InterClient with JBuilderTips on using sample InterBase tables . . . .Adding a JDBC driver to JBuilder . . . . . . . .Creating the .library and .config files . . . . .Adding the JDBC driver to projects. . . . . .Connecting to a database using InterClientJDBC drivers . . . . . . . . . . . . . . . . . .Using the Database component in yourapplication . . . . . . . . . . . . . . . . . . .Prompting for user name and password . . . . .Pooling JDBC connections . . . . . . . . . . .Optimizing performance of JConnectionPool.Logging output . . . . . . . . . . . . . . . .Pooling example . . . . . . . . . . . . . . .Troubleshooting JDataStore and InterBaseconnections . . . . . . . . . . . . . . . . . .Common connection error messages . . . .Chapter 2Understanding JBuilder databaseapplications11Database application architecture . . . . . . . .DataExpress components . . . . . . . . . . . .Key features and benefits . . . . . . . . . . .Overview of the DataExpress components . .DataExpress for EJB components . . . . . . . .InternetBeans Express . . . . . . . . . . . . . .XML database components . . . . . . . . . . . .dbSwing . . . . . . . . . . . . . . . . . . . . . .Data modules and the Data Modeler . . . . . . .Database Pilot . . . . . . . . . . . . . . . . . .JDBC Monitor . . . . . . . . . . . . . . . . . . .JDataStore and JBuilder . . . . . . . . . . . . .When to use JDataStore versus JDBC driversAdditional advantages of a JDataStore . . . .Using the JDataStore Explorer . . . . . . . .JDataStore explorer operations . . . . . .InterBase and JBuilder . . . . . . . . . . . . . . 11. 12. 13. 14. 17. 17. 17. 17. 18. 18. 19. 19. 19. 20. 20. 21. 21Adding columns to a TableDataSet in the editorImporting formatted data from a text file . . . .Retrieving data from a JDBC data source . . .Exporting data . . . . . . . . . . . . . . . . .Exporting data from a QueryDataSet to atext file . . . . . . . . . . . . . . . . . . .Saving changes from a TableDataSet to aSQL table. . . . . . . . . . . . . . . . . .Saving changes loaded from aTextDataFile to a JDBC data source . . . . 23. 24. 24. 25.282931313232333334. . 35.373838404040. . 43. . 43Retrieving data from a data sourceQuerying a database . . . . . . . . . . . . .Setting properties in the query dialog box .The Query page . . . . . . . . . . . . .The Parameters page . . . . . . . . . .Place SQL text in resource bundle . . .Querying a database: Hints & tips . . . . .Enhancing data set performance . . . .Persisting query metadata. . . . . . . .Opening and closing data sets . . . . .Ensuring that a query is updatable . . .Using parameterized queries to obtain datafrom your database. . . . . . . . . . . . . .Parameterizing a query. . . . . . . . . . .Creating the application . . . . . . . . .Adding a Parameter Row . . . . . . . .Adding a QueryDataSet . . . . . . . . .Add the UI components . . . . . . . . .Parameterized queries: Hints & tips . . . .Using parameters . . . . . . . . . . . .Re-executing the parameterized querywith new parameters. . . . . . . . . .Parameterized queries in master-detailrelationships . . . . . . . . . . . . . .23.Chapter 5Chapter 3Importing and exporting datafrom a text file27. . 28. . . 25. . . 25. . . 26i45.46474748495151525252.5353535454555757. . . 59. . . 59
Chapter 6Using stored proceduresCoding stored procedures to handle dataresolution . . . . . . . . . . . . . . . . . . . .Saving changes with a ProcedureResolver . .Example: Using InterBase storedprocedures with return parameters . . . . . .Resolving data from multiple tables . . . . . . . .Considerations for the type of linkagebetween tables in the query . . . . . . . . .Table and column references (aliases) in aquery string . . . . . . . . . . . . . . . . . .Controlling the setting of the column propertiesWhat if a table is not updatable? . . . . . . . .How can the user specify that a tableshould never be updated? . . . . . . . . . .Using DataSets with RMI (streamable data sets) .Example: Using streamable data sets . . . . .Using streamable DataSet methods . . . . . .Customizing the default resolver logic. . . . . . .Understanding default resolving . . . . . . . .Adding a QueryResolver component . . . .Intercepting resolver events. . . . . . . . .Using resolver events . . . . . . . . . . . .Writing a custom data resolver . . . . . . . . .Handling resolver errors . . . . . . . . . .Resolving master-detail relationships. . . .61Stored procedures: hints & tips . . . . . . . . . .Escape sequences, SQL statements, andserver-specific procedure calls . . . . . . .Using vendor-specific stored procedures. . . . .Using JDataStore stored procedures anduser-defined functions . . . . . . . . . . . .Using InterBase stored procedures . . . . . .Using parameters with Oracle PL/SQL storedprocedures . . . . . . . . . . . . . . . . . .Using Sybase stored procedures . . . . . . .Sample application with database-serverspecific stored procedures . . . . . . . . . .Writing a custom data provider . . . . . . . . . .Obtaining metadata . . . . . . . . . . . . . .Invoking initData . . . . . . . . . . . . . .Obtaining actual data . . . . . . . . . . . . .Tips on designing a custom data provider .Understanding the provideData() methodin master-detail data sets . . . . . . . . . . 62. . 62. . 63. . 63. . 63. . 64. . 65.656566666767. . 67Chapter 7Working with columns69Understanding Column properties and metadataNon-metadata Column properties . . . . . . .Viewing column information in the columndesigner . . . . . . . . . . . . . . . . . . .Generate RowIterator Class button . . . . . .Using the column designer to persistmetadata . . . . . . . . . . . . . . . . . . .Making metadata dynamic using thecolumn designer . . . . . . . . . . . . . . .Viewing column information in theDatabase Pilot . . . . . . . . . . . . . . . .Optimizing a query . . . . . . . . . . . . . . . .Setting column properties . . . . . . . . . . .Setting Column properties usingJBuilder’s visual design tools . . . . . . .Setting properties in code . . . . . . . . .Persistent columns . . . . . . . . . . . . . .Combining live metadata with persistentcolumns . . . . . . . . . . . . . . . . . . .Removing persistent columns . . . . . . . . .Using persistent columns to add emptycolumns to a DataSet. . . . . . . . . . .Controlling column order in a DataSet . . . . . 69. . 69Chapter 9. . 70. . 71Establishing a master-detailrelationshipDefining a master-detail relationship . . . . . .Creating an application with a master-detailrelationship . . . . . . . . . . . . . . . . .Fetching details . . . . . . . . . . . . . . . . .Fetching all details at once . . . . . . . . . .Fetching selected detail records on demand.Editing data in master-detail data sets . . . . .Steps to creating a master-detail relationship . .Saving changes in a master-detail relationship .Resolving master-detail data sets to aJDBC data source . . . . . . . . . . . . . . 71. . 72. . 72. . 73. . 73. . 73. . 73. . 73Saving changes from a QueryDataSet . . . . .Adding a button to save changes from aQueryDataSet . . . . . . . . . . . . . . .Saving changes back to your data source witha stored procedure . . . . . . . . . . . . . .Saving changes using a QueryResolver . . .83. .83. .84. .84. .85. .85.85.85.85.86.87.87.87.88.89.90.90.9193. . .94.94.97.97.97.98.99100. . 100Chapter 10Using data modules to simplifydata access. . 74. . 74Creating a data module using the design tools .Create the data module with the wizard . . .Add data components to the data module . .Adding business logic to the data module . .Using a data module . . . . . . . . . . . . .Adding a required library to a project . . .Referencing a data module in yourapplication . . . . . . . . . . . . . . . .Understanding the Use DataModulewizard . . . . . . . . . . . . . . . . . .Creating data modules using the Data Modeler.Creating queries with the Data Modeler . . .Opening a URL . . . . . . . . . . . . . .Beginning a query. . . . . . . . . . . . . . 75. . 75Chapter 8Saving changes back to your datasource. .81. .8177. . . 78. . . 79. . . 80. . . 80ii103.104104104106106106. . 107.108109109110110
Adding a Group By clause . . . . . . . .Selecting rows with unique column valuesAdding a Where clause . . . . . . . . . .Adding an Order By clause . . . . . . . .Editing the query directly . . . . . . . . .Testing your query . . . . . . . . . . . .Building multiple queries . . . . . . . . .Specifying a master-detail relationship . .Saving your queries . . . . . . . . . . .Generating database applications . . . . . .Using a generated data module inyour code . . . . . . . . . . . . . . . .112113113114114114115115116117Data type dependent patterns . . . .Patterns for numeric data . . . . .Patterns for date and time data . .Patterns for string data . . . . . .Patterns for boolean data . . . . .Presenting an alternate view of the dataEnsuring data persistence . . . . . . . .Making columns persistent . . . . . .Using variant data types . . . . . . . . .Storing Java objects . . . . . . . . .Using other controls and eventsFiltering, sorting, and locating dataSynchronizing visual components . . . . . . .Accessing data and model information from aUI component . . . . . . . . . . . . . . . .Displaying status information . . . . . . . . .Building an application with aJdbStatusLabel component . . . . . . . .Running the JdbStatusLabel application . .Handling errors and exceptions . . . . . . . .Overriding default DataSetExceptionhandling on controls . . . . . . . . . . 4.136136136137138.140142143144Adding functionality todatabase applications. 150. 150. 150. 151. 152. 152. 153. 154. 155. 155157. . . 157. . . 158. . . 158. . . 158. . . 159. . . 159. . . 160Chapter 14Creating a distributed databaseapplication using DataSetDataUnderstanding the sample distributed databaseapplication (using Java RMI and DataSetData)Setting up the sample application . . . . . .What is going on? . . . . . . . . . . . . .Passing metadata by DataSetData . . . .Deploying the application on multiple tiersChapter 12Using pick lists and lookups. . . . . . . . . . .Data entry with a pick list . . . . . . . . . .Adding a pick list field . . . . . . . . . . . .Removing a pick list field . . . . . . . . . .Create a lookup using a calculated column .Create a lookup using the PickListDescriptorparameters . . . . . . . . . . . . . . . . .Using calculated columns . . . . . . . . . . . .Create a calculated column in the designer .Aggregating data with calculated fields . . .Example: Aggregating data withcalculated fields . . . . . . . . . . . . .Setting properties in the AggDescriptor . . .Creating a custom aggregation eventhandler . . . . . . . . . . . . . . . . . . .Adding an Edit or Display Pattern for dataformatting . . . . . . . . . . . . . . . . . . .Display masks . . . . . . . . . . . . . . . .Edit masks . . . . . . . . . . . . . . . . . .Using masks for importing and exportingdata . . . . . . . . . . . . . . . . . . . .Chapter 13. . 118Chapter 11Retrieving data for the examples . . . . . . . .Filtering data . . . . . . . . . . . . . . . . . .Adding and removing filters . . . . . . . . .Sorting data . . . . . . . . . . . . . . . . . . .Sorting data in a JdbTable . . . . . . . . . .Sorting data using the JBuilder visualdesign tools . . . . . . . . . . . . . . . .Understanding sorting and indexing . . .Sorting data in code . . . . . . . . . . . . .Locating data . . . . . . . . . . . . . . . . . .Locating data with a JdbNavField . . . . . .Locating data programmatically . . . . . . .Locating data using a DataRow . . . . . . .Working with locate options . . . . . . . . .Locates that handle any data type . . . . . .Column order in the DataRow and DataSet .135161. 161. 162. 163. 163. 164Chapter 15Database administration tasksExploring database tables and metadatausing the Database Pilot . . . . . . . . . . . .Browsing database schema objects . . . . .Setting up drivers to access remote andlocal databases . . . . . . . . . . . . . . .Executing SQL statements . . . . . . . . . .Using the Explorer to view and edit table dataUsing the Database Pilot for databaseadministration tasks . . . . . . . . . . . . . .Creating the SQL data source . . . . . . . .Populating a SQL table with data usingJBuilder . . . . . . . . . . . . . . . . . . .Deleting tables in JBuilder . . . . . . . . . .Monitoring database connections . . . . . . . .About the JDBC Monitor . . . . . . . . . . .Using the JDBC Monitor in a runningapplication . . . . . . . . . . . . . . . . .Adding the MonitorButton to the Palette. .Using the MonitorButton Class from code.Understanding MonitorButton properties . . 144. . 147. . 147. . 148. . 149. . 149. . 149iii165. . 165. . 166. . 166. . 167. . 168. . 170. . 170. 172. 172. 172. 172. 174. 174. 174. 174
Chapter 16Tutorial: Importing and exportingdata from a text fileStep 1: Creating the project . . . . . . . . . .Step 2: Creating the text file . . . . . . . . . .Step 3: Generating an application . . . . . . .Step 4: Adding DataExpress components toyour application . . . . . . . . . . . . . . . .Step 5: Adding dbSwing components tocreate a user interface . . . . . . . . . . . .Step 6: Adding a JButton Swing component . .Step 7: Compiling and running your applicationStep 8: Using patterns for exporting numeric,date/time, and text fields . . . . . . . . . . .Step 4: Designing the columns for theapplication . . . . . . . . . . . . . . . . . . .Adding columns and editing columnproperties . . . . . . . . . . . . . . . . . .Specifying calculations for the calculatedcolumns. . . . . . . . . . . . . . . . . . .Step 5: Adding dbSwing components tocreate a user interface . . . . . . . . . . . . .Step 6: Aggregating data with calculated fields .175. . 176. . 176. . 177. . 177Index. . 178. . 180. . 181. . 182Chapter 17Tutorial: Creating a basicdatabase application185Step 1: Creating the project . . . . . . . . . . . . 187Step 2: Generating an application . . . . . . . . . 187Step 3: Adding DataExpress components toyour application . . . . . . . . . . . . . . . . . . 188iv. . 190. . 190. . 191. . 192. . 194197
Figures2.12.24.1Diagram of a typical database applicationJDataStore Explorer . . . . . . . . . . .Database component displayed instructure pane. . . . . . . . . . . . . . .4.2Connection Descriptor dialog box. . . . .5.1Query property editor . . . . . . . . . . .5.2Parameters page . . . . . . . . . . . . .5.3Resource Bundle dialog . . . . . . . . .8.1UI for saving changes from aQueryDataSet. . . . . . . . . . . . . . .10.1 Data Modeler . . . . . . . . . . . . . . .10.4 Group By page . . . . . . . . . . . . . .10.5 Where page . . . . . . . . . . . . . . . .10.6 Order By page . . . . . . . . . . . . . .10.7 Link Queries dialog box . . . . . . . . . .10.8 Arrow showing relationship betweenqueries . . . . . . . . . . . . . . . . . .10.9 Editor showing code generated byData Modeler . . . . . . . . . . . . . . .10.10 Data Module Application wizard . . . . .11.1 Running database application . . . . . .11.2 Application running filters . . . . . . . . .11.3 Click on column header to sort at runtime. . 12. . 20. 29. 30. 47. 49. 49. 5.315.416.116.216.317.117.217.317.4. 116.11611712312612717.517.617.7vSort property editor . . . . . . . . . . .Sorted application at runtime . . . . . .Sample application with JdbNavField . .Lookup application. . . . . . . . . . . .Calculated columns . . . . . . . . . . .Column designer. . . . . . . . . . . . .Database Pilot . . . . . . . . . . . . . .Enter SQL page of the Database Pilot. .JDBC Monitor . . . . . . . . . . . . . .JDBC Monitor with output . . . . . . . .Import/export database application . . .Import/Export application at runtime. . .Exporting data to text file applicationat runtime . . . . . . . . . . . . . . . .Basic database application . . . . . . .Query dialog box. . . . . . . . . . . . .queryDataSet1 node expanded . . . . .queryDataSet1 columns in the columndesigner . . . . . . . . . . . . . . . . .JdbTable component in the UI designer .Basic database application withnavigation bar and status label . . . . .Agg dialog box . . . . . . . . . . . . . . 128. 129. 131. 140. 144. 154. 165. 168. 173. 173. 175. 180. 181. 186. 189. 189. . 191. . 193. . 194. . 195
TutorialsImporting and exporting data from a text file . . . . 175Creating a basic database application . . . . . . . 185vi
Chapter1IntroductionChapter 1Developing Database Applications provides information on using JBuilder’sDataExpress database functionality to develop database applications. It also describeshow to use dbSwing components to create a user interface (UI) for your application.Basic features that are commonly included in a database application are explained byexample so you can learn by doing. Conceptual information is provided, followed withexamples as applicable, with cross-references to more detailed information whereverpossible.Be sure to check for documentation additions and updates at http://www.borland.com/techpubs/jbuilder. Also, check the JBuilder online help. The information in the onlinehelp is more up-to-date than the printed material.If you have questions about creating database applications using JBuilder, visit thedatabase newsgroup at der.database. This newsgroup is dedicated to issues about writingdatabase applications in JBuilder and is actively monitored by our support engineers aswell as the JBuilder Development team. For discussions about dbSwing components,borland.public.jbuilder.dbswing newsgroup is a good source for getting help creat
Borland Software Corporation 100 Enterprise Way Scotts Valley, California 95066-3249 www.borland.com Refer to the file deploy.html located in the redist directory of your JBuilder product for a complete list
Database Applications and SQL 12 The DBMS 15 The Database 16 Personal Versus Enterprise-Class Database Systems 18 What Is Microsoft Access? 18 What Is an Enterprise-Class Database System? 19 Database Design 21 Database Design from Existing Data 21 Database Design for New Systems Development 23 Database Redesign 23
Getting Started with Database Classic Cloud Service. About Oracle Database Classic Cloud Service1-1. About Database Classic Cloud Service Database Deployments1-2. Oracle Database Software Release1-3. Oracle Database Software Edition1-3. Oracle Database Type1-4. Computing Power1-5. Database Storage1-5. Automatic Backup Configuration1-6
Database Management Systems UNIT-I Introduction RGMCET (CSE Dept.) Page 1 UNIT-I INTRODUCTION TO DBMS Database System Applications, database System VS file System - View of Data - Data Abstraction -Instances and Schemas - data Models - the ER Model - Relational Model - Database Languages - DDL - DML - Database Access for applications Programs - Database Users and .
The term database is correctly applied to the data and their supporting data structures, and not to the database management system. The database along with DBMS is collectively called Database System. A Cloud Database is a database that typically runs on a Cloud Computing platform, such as Windows Azure, Amazon EC2, GoGrid and Rackspace.
Creating a new database To create a new database, choose File New Database from the menu bar, or click the arrow next to the New icon on the Standard toolbar and select Database from the drop-down menu. Both methods open the Database Wizard. On the first page of the Database Wizard, select Create a new database and then click Next. The second page has two questions.
real world about which data is stored in a database. Database Management System (DBMS): A collection of programs to facilitate the creation and maintenance of a database. Database System DBMS Database A database system contains information about a particular enterprise. A database system provides an environment that is both
10 Deploying Oracle 12c RAC Database on Dell EMC XC Series All-Flash 3110-BP-SDS 2.1.2 Oracle RAC database configuration Oracle RAC allows running multiple database instances on multiple servers in the cluster against a single database. The database spans multiple servers but appears as a single unified database to end-user applications.
How Web Applications Interact with Database Connecting to MySQL Database PHP program connects to the database server before conducting query on database using. The code shown below uses new mysqli along with its 4 arguments to create the database connection. How Web Applications Interact with Database
