[DATABASE] PostgreSQL Features

Functions

Functions allow blocks of code to be executed by the server. Although these blocks can be written in SQL, the lack of basic programming operations which existed prior to version 8.4, such as branching and looping, has driven the adoption of other languages inside of functions. Some of the languages can even execute inside of triggers. Functions in PostgreSQL can be written in the following languages:

* A built-in language called PL/pgSQL resembles Oracle's procedural language PL/SQL.
* Scripting languages are supported through PL/Lua, PL/LOLCODE, PL/Perl, plPHP, PL/Python, PL/Ruby, PL/sh, PL/Tcl and PL/Scheme.
* Compiled languages C, C++, or Java (via PL/Java).
* The statistical language R through PL/R.

PostgreSQL supports row-returning functions, where the output of the function is a set of values which can be treated much like a table within queries.

Functions can be defined to execute with the privileges of either the caller or the user who defined the function. Functions are sometimes referred to as stored procedures, although there is a slight technical distinction between the two.

Indexes

PostgreSQL includes built-in support for B+-tree, hash, GiST and GiN indexes. In addition, user-defined index methods can be created, although this is quite an involved process. Indexes in PostgreSQL also support the following features:

* PostgreSQL is capable of scanning indexes backwards when needed; a separate index is never needed to support ORDER BY field DESC.
* Expression indexes can be created with an index of the result of an expression or function, instead of simply the value of a column.
* Partial indexes, which only index part of a table, can be created by adding a WHERE clause to the end of the CREATE INDEX statement. This allows a smaller index to be created.
* The planner is capable of using multiple indexes together to satisfy complex queries, using temporary in-memory bitmap index operations.

Triggers

Triggers are events triggered by the action of SQL DML statements. For example, an INSERT statement might activate a trigger that checked if the values of the statement were valid. Most triggers are only activated by either INSERT or UPDATE statements.

Triggers are fully supported and can be attached to tables but not to views. Views can have rules, though. Multiple triggers are fired in alphabetical order. In addition to calling functions written in the native PL/PgSQL, triggers can also invoke functions written in other languages like PL/Perl.

MVCC

PostgreSQL manages concurrency through a system known as Multi-Version Concurrency Control (MVCC), which gives each user a "snapshot" of the database, allowing changes to be made without being visible to other users until a transaction is committed. This largely eliminates the need for read locks, and ensures the database maintains the ACID principles in an efficient manner.

Rules

Rules allow the "query tree" of an incoming query to be rewritten. One common usage is to implement updatable views.

Data types

A wide variety of native data types are supported, including:

* Variable length arrays (including text and composite types) up to 1GB in total storage size.
* Arbitrary precision numerics
* Geometric primitives
* IPv4 and IPv6 addresses
* CIDR blocks and MAC addresses
* XML supporting Xpath queries (as of 8.3)
* UUID (as of 8.3)

In addition, users can create their own data types which can usually be made fully indexable via PostgreSQL's GiST infrastructure. Examples of these are the geographic information system (GIS) data types from the PostGIS project for PostgreSQL.

User-defined objects

New types of almost all objects inside the database can be created, including:

* Casts
* Conversions
* Data types
* Domains
* Functions, including aggregate functions
* Indexes
* Operators (existing ones can be overloaded)
* Procedural languages

Inheritance

Tables can be set to inherit their characteristics from a "parent" table. Data in child tables will appear to exist in the parent tables, unless data is selected from the parent table using the ONLY keyword, i.e. select * from ONLY PARENT_TABLE. Adding a column in the parent table will cause that column to appear in the child table.

Inheritance can be used to implement table partitioning, using either triggers or rules to direct inserts to the parent table into the proper child tables.

This feature is not fully supported yet—in particular, table constraints are not currently inheritable. As of the 8.4 release, all check constraints and not-null constraints on a parent table are automatically inherited by its children. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited.

Inheritance provides a way to map the features of generalization hierarchies depicted in Entity Relationship Diagrams (ERD) directly into the PostgreSQL database.

Other features

* Referential integrity constraints including foreign key constraints, column constraints, and row checks
* Views. Although native support for updateable views has not been implemented, the same functionality can be achieved using the rules system.
* Full, inner, and outer (left and right) joins
* Sub-selects
o Correlated sub-queries [2]
* Transactions
* Supports most of the major features of SQL:2008 standard [3] unsupported supported <-- lead to documentation for the next release of PostgreSQL, follow this link to find manuals for already released versions of PostgreSQL * Encrypted connections via SSL * Binary and textual large-object storage * Online backup * Domains * Tablespaces * Savepoints * Point-in-time recovery * Two-phase commit * TOAST (The Oversized-Attribute Storage Technique) is used to transparently store large table attributes (such as big MIME attachments or XML messages) in a separate area, with automatic compression. * Regular expressions [4] Add-ons

* Geographic objects via PostGIS. GPL.
* Shortest-Path-Algorithms with pgRouting using PostGIS. GPL.
* Full text search via Tsearch2 and OpenFTS. (As of version 8.3, Tsearch2 is included in core PostgreSQL)
* Some synchronous multi-master derivatives or extensions exist, including
o pgcluster (BSD license)
o Postgres-R (in early stages of development)
* Several asynchronous master/slave replication packages, including
o Londiste (BSD license)
o Slony-I (BSD license)
o Mammoth Replicator. (BSD license[9], formerly proprietary)
o Bucardo
* There are proxy (middleware) tools that enable replication, failover or load management and balancing for PostgreSQL:
o PGPool-II.
o Sequoia available for a number of different server besides PostgreSQL.

Source : Wikipedia

[DATABASE] SQLite - Embedded Database

SQLite is a in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is currently found in more applications than we can count, including several high-profile projects.

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format. Think of SQLite not as a replacement for Oracle but as a replacement for fopen()

SQLite is a compact library. With all features enabled, the library size can be less than 300KiB, depending on compiler optimization settings. (Some compiler optimizations such as aggressive function inlining and loop unrolling can cause the object code to be much larger.) If optional features are omitted, the size of the SQLite library can be reduced below 180KiB. SQLite can also be made to run in minimal stack space (16KiB) and very little heap (100KiB), making SQLite a popular database engine choice on memory constrained gadgets such as cellphones, PDAs, and MP3 players. There is a tradeoff between memory usage and speed. SQLite generally runs faster the more memory you give it. Nevertheless, performance is usually quite good even in low-memory environments.

SQLite is very carefully tested prior to every release and has a reputation for being very reliable. Most of the SQLite source code is devoted purely to testing and verification. An automated test suite runs millions of tests involving hundreds of millions of individual SQL statements and achieves over 99% statement coverage. SQLite responds gracefully to memory allocation failures and disk I/O errors. Transactions are ACID even if interrupted by system crashes or power failures. All of this is verified by the automated tests using special test harnesses which simulate system failures. Of course, even with all this testing, there are still bugs. But unlike some similar projects (especially commercial competitors) SQLite is open and honest about all bugs and provides bugs lists including lists of critical bugs and minute-by-minute chronologies of bug reports and code changes.

The SQLite code base is supported by an international team of developers who work on SQLite full-time. The developers continue to expand the capabilities of SQLite and enhance its reliability and performance while maintaining backwards compatibility with the published interface spec, SQL syntax, and database file format. The source code is absolutely free to anybody who wants it, but professional support services are also available.

We the developers hope that you find SQLite useful and we charge you to use it well: to make good and beautiful products that are fast, reliable, and simple to use. Seek forgiveness for yourself as you forgive others. And just as you have received SQLite for free, so also freely give, paying the debt forward.

Source : SQLite Official Website

[DATABASE] PostgreSQL - Advanced Open Source Database

PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL92 and SQL99 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional documentation.

An enterprise class database, PostgreSQL boasts sophisticated features such as Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a sophisticated query planner/optimizer, and write ahead logging for fault tolerance. It supports international character sets, multibyte character encodings, Unicode, and it is locale-aware for sorting, case-sensitivity, and formatting. It is highly scalable both in the sheer quantity of data it can manage and in the number of concurrent users it can accommodate. There are active PostgreSQL systems in production environments that manage in excess of 4 terabytes of data. Some general PostgreSQL limits are included in the table below.

Limit	Value
Maximum Database Size	Unlimited
Maximum Table Size	32 TB
Maximum Row Size	1.6 TB
Maximum Field Size	1 GB
Maximum Rows per Table	Unlimited
Maximum Columns per Table	250 - 1600 depending on column types
Maximum Indexes per Table	Unlimited

PostgreSQL has won praise from its users and industry recognition, including the Linux New Media Award for Best Database System and five time winner of the The Linux Journal Editors' Choice Award for best DBMS.

Featureful and Standards Compliant

PostgreSQL prides itself in standards compliance. Its SQL implementation strongly conforms to the ANSI-SQL 92/99 standards. It has full support for subqueries (including subselects in the FROM clause), read-committed and serializable transaction isolation levels. And while PostgreSQL has a fully relational system catalog which itself supports multiple schemas per database, its catalog is also accessible through the Information Schema as defined in the SQL standard.

Data integrity features include (compound) primary keys, foreign keys with restricting and cascading updates/deletes, check constraints, unique constraints, and not null constraints.

It also has a host of extensions and advanced features. Among the conveniences are auto-increment columns through sequences, and LIMIT/OFFSET allowing the return of partial result sets. PostgreSQL supports compound, unique, partial, and functional indexes which can use any of its B-tree, R-tree, hash, or GiST storage methods.

GiST (Generalized Search Tree) indexing is an advanced system which brings together a wide array of different sorting and searching algorithms including B-tree, B+-tree, R-tree, partial sum trees, ranked B+-trees and many others. It also provides an interface which allows both the creation of custom data types as well as extensible query methods with which to search them. Thus, GiST offers the flexibility to specify what you store, how you store it, and the ability to define new ways to search through it --- ways that far exceed those offered by standard B-tree, R-tree and other generalized search algorithms.

GiST serves as a foundation for many public projects that use PostgreSQL such as OpenFTS and PostGIS. OpenFTS (Open Source Full Text Search engine) provides online indexing of data and relevance ranking for database searching. PostGIS is a project which adds support for geographic objects in PostgreSQL, allowing it to be used as a spatial database for geographic information systems (GIS), much like ESRI's SDE or Oracle's Spatial extension.

Other advanced features include table inheritance, a rules systems, and database events. Table inheritance puts an object oriented slant on table creation, allowing database designers to derive new tables from other tables, treating them as base classes. Even better, PostgreSQL supports both single and multiple inheritance in this manner.

The rules system, also called the query rewrite system, allows the database designer to create rules which identify specific operations for a given table or view, and dynamically transform them into alternate operations when they are processed.

The events system is an interprocess communication system in which messages and events can be transmitted between clients using the LISTEN and NOTIFY commands, allowing both simple peer to peer communication and advanced coordination on database events. Since notifications can be issued from triggers and stored procedures, PostgreSQL clients can monitor database events such as table updates, inserts, or deletes as they happen.

Highly Customizable

PostgreSQL runs stored procedures in more than a dozen programming languages, including Java, Perl, Python, Ruby, Tcl, C/C++, and its own PL/pgSQL, which is similar to Oracle's PL/SQL. Included with its standard function library are hundreds of built-in functions that range from basic math and string operations to cryptography and Oracle compatibility. Triggers and stored procedures can be written in C and loaded into the database as a library, allowing great flexibility in extending its capabilities. Similarly, PostgreSQL includes a framework that allows developers to define and create their own custom data types along with supporting functions and operators that define their behavior. As a result, a host of advanced data types have been created that range from geometric and spatial primitives to network addresses to even ISBN/ISSN (International Standard Book Number/International Standard Serial Number) data types, all of which can be optionally added to the system.

Just as there are many procedure languages supported by PostgreSQL, there are also many library interfaces as well, allowing various languages both compiled and interpreted to interface with PostgreSQL. There are interfaces for Java (JDBC), ODBC, Perl, Python, Ruby, C, C++, PHP, Lisp, Scheme, and Qt just to name a few.

Best of all, PostgreSQL's source code is available under the most liberal open source license: the BSD license. This license gives you the freedom to use, modify and distribute PostgreSQL in any form you like, open or closed source. Any modifications, enhancements, or changes you make are yours to do with as you please. As such, PostgreSQL is not only a powerful database system capable of running the enterprise, it is a development platform upon which to develop in-house, web, or commercial software products that require a capable RDBMS.

[J2ME] RecordStore - Database in J2ME

public class RecordStore

extends Object

A class representing a record store. A record store consists of a collection of records which will remain persistent across multiple invocations of the MIDlet. The platform is responsible for making its best effort to maintain the integrity of the MIDlet's record stores throughout the normal use of the platform, including reboots, battery changes, etc.

Record stores are created in platform-dependent locations, which are not exposed to the MIDlets. The naming space for record stores is controlled at the MIDlet suite granularity. MIDlets within a MIDlet suite are allowed to create multiple record stores, as long as they are each given different names. When a MIDlet suite is removed from a platform all the record stores associated with its MIDlets will also be removed. MIDlets within a MIDlet suite can access each other's record stores directly. New APIs in MIDP 2.0 allow for the explicit sharing of record stores if the MIDlet creating the RecordStore chooses to give such permission.

Sharing is accomplished through the ability to name a RecordStore created by another MIDlet suite.

RecordStores are uniquely named using the unique name of the MIDlet suite plus the name of the RecordStore. MIDlet suites are identified by the MIDlet-Vendor and MIDlet-Name attributes from the application descriptor.

Access controls are defined when RecordStores to be shared are created. Access controls are enforced when RecordStores are opened. The access modes allow private use or shareable with any other MIDlet suite.

Record store names are case sensitive and may consist of any combination of between one and 32 Unicode characters inclusive. Record store names must be unique within the scope of a given MIDlet suite. In other words, MIDlets within a MIDlet suite are not allowed to create more than one record store with the same name, however a MIDlet in one MIDlet suite is allowed to have a record store with the same name as a MIDlet in another MIDlet suite. In that case, the record stores are still distinct and separate.

No locking operations are provided in this API. Record store implementations ensure that all individual record store operations are atomic, synchronous, and serialized, so no corruption will occur with multiple accesses. However, if a MIDlet uses multiple threads to access a record store, it is the MIDlet's responsibility to coordinate this access or unintended consequences may result. Similarly, if a platform performs transparent synchronization of a record store, it is the platform's responsibility to enforce exclusive access to the record store between the MIDlet and synchronization engine.

Records are uniquely identified within a given record store by their recordId, which is an integer value. This recordId is used as the primary key for the records. The first record created in a record store will have recordId equal to one (1). Each subsequent record added to a RecordStore will be assigned a recordId one greater than the record added before it. That is, if two records are added to a record store, and the first has a recordId of 'n', the next will have a recordId of 'n + 1'. MIDlets can create other sequences of the records in the RecordStore by using the RecordEnumeration class.

This record store uses long integers for time/date stamps, in the format used by System.currentTimeMillis(). The record store is time stamped with the last time it was modified. The record store also maintains a version number, which is an integer that is incremented for each operation that modifies the contents of the RecordStore. These are useful for synchronization engines as well as other things.

Since:

MIDP 1.0

[DATABASE] Firebird - Portable DBMS Software

Firebird is a relational database offering many ANSI SQL standard features that runs on Linux, Windows, and a variety of Unix platforms. Firebird offers excellent concurrency, high performance, and powerful language support for stored procedures and triggers. It has been used in production systems, under a variety of names, since 1981.

The Firebird Project is a commercially independent project of C and C++ programmers, technical advisors and supporters developing and enhancing a multi-platform relational database management system based on the source code released by Inprise Corp (now known as Borland Software Corp) on 25 July, 2000.

Benefit :

FREE LIKE A BIRD. Anyone can build a custom version of Firebird, as long as the modifications are made available, under the same IDPL licensing, for others to use and build on.

FREE LIKE FREE BEER. No fees for download, registration, licensing or deployment, even you distribute Firebird as part of your commercial software package.

Firebird's development depends on voluntary funding by people who benefit from using it. Funding options range from donations, through Firebird Foundation memberships to sponsorship commitments.

Choosing Firebird and saving or making money by your choice? Show your appreciation and encouragement by contributing money in proportion to these benefits.

Source : Firebird Website

[DATABASE] Relational Database feat. Oracle

What is a relational database?

As mentioned before, a relational database is based on the separation and independence of the he logical and physical representations of the data. This provides enormous flexibility and means you can store the data physically in any way without affecting how the data is presented to the nd user. The separation of physical and logical layers means that you can change either layer ithout affecting the other.

A relational database can be regarded as a set of 2-dimensional tables which are known as relations" in relational database theory. Each table has rows ("tuples") and columns "domains"). The relationships between the tables is defined by one table having a column with the same meaning (but not necessarily value) as a column in another table.

For example consider a database with just 2 tables :

emp(id number
,name varchar2(30)
,job_title varchar2(20)
,dept_id number)

holding employee information and

dept(id number
,name varchar2(30))

holding department information.

There is an implied relationship between these tables because emp has a column called dept_id which is the same as the id column in dept. In Oracle this is usually implemented by what's called a foreign-key relationship which prevents values being stored that are not present in the referenced table.

Relational databases obtain their flexibility from being based on set theory (also known as relational calculus) which enables sets or relations to be combined in various ways, including:

• join/intersection
• union (i.e. the sum of 2 sets);
• exclusive "OR" (i.e. the difference between 2 sets)
• and outer-join which is a combination of intersecting and exclusive or ing

The intersection or join between 2 sets (in this case, tables) produces only those elements that exist in both sets.

Therefore, if we join Emp and Dept on department id, we will be left with only those employees who work for a department that is in the dept table and only those departments which have employees who are in the emp table.

The union produces the sum of the tables - meaning all records in Emp and all records in Dept. and this may be with or without duplicates.

Let's use the following data to provide specific examples:

Emp

Id	Name	Dept Id
1	Bill Smith	3
2	Mike Lewis	2
3	Ray Charles	3
4	Andy Mallory	4
5	Mandy Randall	6
6	Allison White	1

Dept

Id	Name
1	HR
2	IT
3	Marketing
4	Sales
5	Finance

The join of Emp and Dept. on the department id would produce the following result:

Emp.Id	Emp.Name	Dept.Id	Dept.Name
1	Bill Smith	3	Marketing
2	Mike Lewis	2	IT
3	Ray Charles	3	Marketing
4	Andy Mallory	4	Sales
6	Allison White	1	HR

The union of Emp and Dept. would produce the following results

Id	Name
1	Bill Smith
2	Mike Lewis
3	Ray Charles
4	Andy Mallory
5	Mandy Randall
1	HR
2	IT
3	Marketing
4	Sales
5	Finance

The union operator is only allowed when the number and data types of the columns in the 2 sets are the same. It is not normally be used to combine sub sections from one or more tables rather than entire tables.

There are other operators and variations but there isn't the space or the time to provide full details in this short Oracle tutorial.

The later versions of Oracle (Oracle 8 onwards) support both relational and object-oriented features. The relational features are more prominent at the moment, but this is beginning to change. In this context an object has both attributes and methods (programs stored with the object that performs a certain action or task) and in a true object-oriented database would belong to a class and would allow multilevel inheritance.

Source : A Short Oracle Tutorial for Beginners

[DATABASE] History of Databases

History of Databases - From Trees To Objects

The storage and management of data is probably the biggest headache for all businesses.

It has been so for a long while and is likely to continue for a long while too. As companies aim to store more and more details about their customers and their buying habits, they need to store and manage more and more data. The only way this can be done efficiently and at a reasonable cost is by the use of computers.

In the late 1960s/early 1970s, specialised data management software appeared the first database management systems (DBMS). These early DBMS were either hierarchical (tree) or network (CODASYL) databases and were, thererfore, very complex and inflexible which made life difficult when it came to adding new applications or reorganising the data. The solution to this was relational databases which are based on the concept of normalisation - the separation of the logical and physical representation of data.

In 1970 the relational data model was defined by E.F. Codd (see "A Relational Model of Data for Large Shared Data Banks" Comm. ACM. 13 (June 6, 1970), 377-387).

In 1974 IBM started a project called System/R to prove the theory of relational databases. This led to the development of a query language called SEQUEL (Structured English Query Language) later renamed to Structured Query Language (SQL) for legal reasons and now the query language of all databases.

In 1978 a prototype System/R implementation was evaluated at a number of IBM customer sites.

By 1979 the project finished with the conclusion that relational databases were a feasible commercial product.

Meanwhile, IBM's research into relational databases had come to the attention of a group of engineers in California. They were so convinced of the potential that they formed a company called Relational Software, Inc. in 1977 to build such a database. Their product was called Oracle and the first version for VAX/VMS was released in 1979, thereby becoming the first commercial rdbms, beating IBM to market by 2 years.

In the 1980s the company was renamed to Oracle Corporation. Throughout the 1980s, new features were added and performance improved as the price of hardware came down and Oracle became the largest independent rdbms vendor. By 1985 they boasted of having more than 1000 installations.

As relational databases became accepted, companies wanted to expand their use to store images, spreadsheets, etc. which can't be described in 2-dimensional terms. This led to the Oracle database becoming an object-relational hybrid in version 8.0, i.e. a relational database with object extensions, enabling you to have the best of both worlds.

Source : A Short Oracle Tutorials for Beginners

[Oracle] Adding PL/SQL Functionality into an Application

What Is PL/SQL?
PL/SQL stands for Procedural Language extension to SQL. The language offers a robust programming environment that enables you to program procedurally and/or with object-oriented programming techniques such as encapsulation, information hiding, and function overloading. With PL/SQL, you can use state-of-the-art programming for the Oracle Database server and toolset.

Why Use PL/SQL?
PL/SQL provides procedural constructs such as variables, constants, and types. The language provides selective and iterative constructs to SQL. The following are the major benefits of using PL/SQL :

• Integration of procedural constructs with SQL

  Pl/SQL integrates control statements and conditional statements with SQL. This gives you a better control of SQL statemetns and their execution.

• Reduction in network congestion

  PL/SQL allows you to logically combine SQL statements as one unit. The application can send the entire block to the database instead of sending the SQL statements one at a time. This reduces the network traffic.

• Modularized program development

  PL/SQL allows you to group logically related statements within blocks. You can nest blocks inside larger blocks to build powerful programs. Also, you can break your application into smaller modules.

• Integration with tools

  The PL/SQL engine is integrated in Oracle tools such as HTML DB, Oracle Database XE, Oracle Forms, Oracle Reports and so on. When you use these tools, the locally available PL/SQL engine processes the procedural statements and only the SQL statements are passed to the database.

• Portability

  PL/SQL programs can run anywhere Oracle server runs irrespective of the operating system and the platform. You do not need to tailor them to each new environment.

• Exception handling

  An exception is an error in PL/SQL that is raised during the execution of a block. PL/SQL allows you to handle exceptions efficiently. You can define separate blocks for dealing with exceptions.

You may find it necessary to use database applications that include programming logic, sequential statements, and SQL statements. Using PL/SQL, you can build applications that are resilient to change over time and that scale to large user populations.

Source : Oracle Curriculum

[Database] Access Database (mdb) and Java Connectivity

Mungkin banyak yang masih bingung (termasuk saya), cara untuk mengkoneksikan database access (.mdb) dengan Java. Nah di sini, saya akan mencoba untuk mengulas langkah-langkahnya.

1. Buat database Access dengan format .mdb, misalnya : hospital.mdb
   Misalnya, dalam hospital.mdb terdapat tabel Dokter terdapat field-field sebagai berikut :
   ID Nama Alamat Telp Golongan_ID Spesialisasi_ID ShiftKerja_ID


2. Membuat ODBC
   ODBC merupakan aturan yang digunakan untuk mengakses sebuah database. Caranya :
   ~ Start - Control Panel - Administrative Tools - Data Sources(ODBC)
   
   
   
   ~ Pilih tab User DSN - Add
   ~ Pilih Select
   
   
   
   ~ Pilih OK
   ~ Isi textbox Database Source Name, misalnya hospital
   
   
   
   ~ Klik Advanced, kemudian isi textbox Login Name dan Password misalnya: Login Name --> admin dan Password --> admin
   
   
   
   ~ Klik OK
   ~ Kemudian klik OK lagi, dan pada User Data Sources akan muncul 'hospital'


3. Membuat codingan javanya
   Contohnya seperti ini:
   

   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.Statement;
   
   /**
   *
   * @author Jeffrey
   */
   public class Main {
     private class tes{
   
     }
     /**
      * @param args the command line arguments
      */
     public static void main(String[] args) {
         // TODO code application logic here
         try {
             Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
             String cs = "jdbc:odbc:hospital;uid='admin';pw='admin";
             Connection cn = DriverManager.getConnection(cs);
             String qry = "SELECT * FROM Dokter";
             Statement stm = cn.createStatement();
             ResultSet rs = stm.executeQuery(qry);
             while(rs.next()) {
                 System.out.println("ID : "+rs.getString("ID"));
                 System.out.println("Nama : "+rs.getString("Nama"));
                 System.out.println("Alamat : "+rs.getString("Alamat"));
             }
             cn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
     }
   
   }

   
   

Ya, program diatas akan menampilkan query : "SELECT * FROM Dokter", yaitu: mengambil semua record pada tabel Dokter. Kemudian,

           while(rs.next()) {
              System.out.println("ID : "+rs.getString("ID"));
              System.out.println("Nama : "+rs.getString("Nama"));
              System.out.println("Alamat : "+rs.getString("Alamat"));
          }

hanya akan menampilkan field ID, Nama dan Alamat dari record-record pada tabel Dokter.

[Database] Oracle Database (Oracle XE) and C# Connectivity

Download PDF Version : Oracle Database (Oracle XE) dan C# Connectivity

Langkah-langkah yang harus dilakukan sebelum mengkoneksikan Oracle Database dengan aplikasi desktop C# adalah :

1. Menginstall Oracle XE kemudian menggunakan user yang otomatis di-generate “system” atau membuat user baru,


2. Jika service database masih belum berjalan maka lakukan Start Database,


3. Menginstall IDE yang mendukung C#, sebagai contoh kali ini menggunakan Visual C#,


4. File – New – Project kemudian pilih Windows Forms Application beri nama Solution yang diinginkan,


5. Pada Solution Explorer, klik kanan pada References kemudian Add References. Pilih Oracle.DataAccess.dll dan klik OK,


6. Pada Solution Explorer, klik kanan pada nama project anda misal “Kepegawaian” kemudian


7. Add – New Item pilih C# Class dan beri nama semisal Koneksi.cs,


8. Bagian yang paling penting dari Koneksi.cs adalah
   

   private string OracleServer = "Data Source=(DESCRIPTION="
   + ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))"
   + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));"
   + "User Id=jeffrey;Password=jeffrey;";

   
   HOST merupakan alamat IP dari server database Oracle, jika menggunakanOracle XE maka database
   Oracle terdapat pada localhost.
   SERVICE NAME adalah XE jika menggunakan Oracle XE.
   User Id dan Password diisi sesuai dengan User ID dan Password pada database server.
   
   Koneksi.cs :
   

   using System;
   using System.Collections.Generic;
   using System.Text;
   using System.Data;
   using System.Windows.Forms;
   using Oracle.DataAccess.Client;
   
   public class Koneksi
   {
      private OracleConnection conn;
   
      private string OracleServer = "Data Source=(DESCRIPTION="
           + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))"
           + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));"
           + "User Id=jeffrey;Password=jeffrey;";
   
      public bool Open()
      {
          try
          {
              conn = new OracleConnection(OracleServer);
              conn.Open();
              return true;
          }
          catch (Exception ex)
          {
              MessageBox.Show(ex.Message);
          }
          return false;
      }
   
      public void Close()
      {
          conn.Close();
          conn.Dispose();
      }
   
      public DataSet ExecuteDataSet(string sql)
      {
          try
          {
              DataSet ds = new DataSet();
              OracleDataAdapter da = new OracleDataAdapter(sql, conn);
              da.Fill(ds, "result");
              return ds;
          }
          catch (Exception ex)
          {
              MessageBox.Show(ex.Message);
          }
          return null;
      }
   
      public OracleDataReader ExecuteReader(string sql)
      {
          try
          {
              OracleDataReader reader;
              OracleCommand cmd = new OracleCommand(sql, conn);
              reader = cmd.ExecuteReader();
              return reader;
          }
          catch (Exception ex)
          {
              MessageBox.Show(ex.Message);
          }
          return null;
      }
   
      public int ExecuteNonQuery(string sql)
      {
          try
          {
              int affected;
              OracleTransaction mytransaction = conn.BeginTransaction();
              OracleCommand cmd = conn.CreateCommand();
              cmd.CommandText = sql;
              affected = cmd.ExecuteNonQuery();
              mytransaction.Commit();
              return affected;
          }
          catch (Exception ex)
          {
              MessageBox.Show(ex.Message);
          }
          return -1;
      }
   }
   
   

9. Mengakses database dengan memanfaatkan method-method pada class Koneksi.cs, semisal menggunakan Form1.cs
   Form1.cs :
   

   using System;
   using System.Collections.Generic;
   using System.ComponentModel;
   using System.Data;
   using System.Drawing;
   using System.Linq;
   using System.Text;
   using System.Windows.Forms;
   using Oracle.DataAccess.Client;
   
   namespace WindowsFormsApplication2
   {
      public partial class Form1 : Form
      {
          public Koneksi con;
   
          public Form1()
          {
              InitializeComponent();
              this.con = new koneksi();
          }
   
          private void button1_Click(object sender, EventArgs e)
          {
              this.con.Open();
              OracleDataReader odr;
              odr = this.con.ExecuteReader(textBox2.Text);
              while (odr.Read()) {
                  Console.Write(odr["KARYAWAN_ID"]+"\t");
                  Console.Write(odr["KARYAWAN_NAMA"] + "\t");
                  Console.WriteLine(odr["KARYAWAN_TGLMASUK"] + "\t");
              }
              this.con.Close();
          }
   
          private void button2_Click(object sender, EventArgs e)
          {
              this.con.Open();
              this.con.ExecuteNonQuery(textBox1.Text);
              this.con.Close();
          }
      }
   }

   
   
10. Selain menggunakan method ExecuteReader(string sql) dan ExecuteNonQuery(sql), akses database
    dapat juga menggunakan method ExecuteDataSet(string sql) dengan return value DataSet yang ditampilkan langsung dengan menggunakan DataGridView.