D2 TEKNIK KOMPUTER (BSI PROGRAMMER)
PostgreSQL adalah sistem database yang kuat untuk urusan relasi, open source. Memiliki lebih dari 15 tahun pengembangan aktif dan sudah terbukti segala rancangan arsitekturnya telah mendapat reputasi tentang “kuat”, “handal”, “integritas data”, dan “akurasi data”.
LOGO
LINUX
Setelah berhasil terkoneksi ke database sistem (template1) mulailah untuk membuat database. Anda memang dapat menggunakan template1 sebagai ajang latihan, tapi hal tersebut tidak dianjurkan, karena template1 merupakan template bagi database baru. Setiap objek (tabel, view, function, dan sebagainya) yang terdapat pada template1 akan di-copy ke database baru.
template1=> CREATE DATABASE rab; psql menyiapkan perintah \c untuk berpindah ke database lain.
template1=> \c rab Atau jika dimulai dari console Linux:
$ psql -u rab
Setelah itu Anda akan mendapat salam sambutan dan sebuah prompt.
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: rab
rab=>
Selanjutnya penulis tidak menuliskan lagi prompt rab=> ini. Jika prompt diawali # atau $ berarti merupakan bash
prompt.
Windows
Berikut ini daftar perintah yang berhubungan dengan penggunaan PSQL (Prosedural SQL) Pada PostgreSQL 8.2. Perintah-perintah di bawah ini bertujuan untuk memudahkan database administrator dalam mengelola basis data dengan PostgreSQL.
postgres=# \?
General
\c [connect] [DBNAME|- USER|- HOST|- PORT|-]
connect to new database (currently "postgres")
\cd [DIR] change the current working directory
\copyright show PostgreSQL usage and distribution terms
\encoding [ENCODING]
show or set client encoding
\h [NAME] help on syntax of SQL commands, * for all commands
\q quit psql
\set [NAME [VALUE]] set internal variable, or list all if no parameters
\timing toggle timing of commands (currently off)
\unset NAME unset (delete) internal variable
\! [COMMAND] execute command in shell or start interactive shell
Query Buffer
\e [FILE] edit the query buffer (or file) with external editor
\g [FILE] send query buffer to server (and results to file or |pipe)
\p show the contents of the query buffer
\r reset (clear) the query buffer
\w FILE write query buffer to file
Input/Output
\echo [STRING] write string to standard output
\i FILE execute commands from file
\o [FILE] send all query results to file or |pipe
\qecho [STRING] write string to query output stream (see \o)
Informational
\d [NAME] describe table, index, sequence, or view
\d{t|i|s|v|S} [PATTERN] (add "+" for more detail)
list tables/indexes/sequences/views/system tables
\da [PATTERN] list aggregate functions
\db [PATTERN] list tablespaces (add "+" for more detail)
\dc [PATTERN] list conversions
\dC list casts
\dd [PATTERN] show comment for object
\dD [PATTERN] list domains
Dalam Data Definition Language (DDL) perintah yang biasa digunakan seperti CREATE, DROP dan ALTER. Adapun penjelasan singkatnya sebagai berikut :
1. CREATE, merupakan perintah yang digunakan untuk membuat struktur objek
pada database, yang dapat berupa database, table, view, procedure, trigger dan
sebagainya.
2. DROP, merupakan perintah yang digunakan untuk menghapus struktur objek
pada database.
3. ALTER, merupakan perintah yang digunakan untuk mengubah struktur objek
yang telah ada pada database.
----------------------------------------------------------------------------------------------
Delphi Programming (PostgreSQL)
----------------------------------------------------------------------------------------------
Buat sebuah project baru dan letakkan code ini :
unit Database_PostGreSQL_Delphi2;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, Grids, DBGrids, StdCtrls, ZConnection, DB, ZAbstractRODataset,
ZAbstractDataset, ZDataset, ExtCtrls, DBCtrls, ZAbstractTable;
type
TForm2 = class(TForm)
DBGrid1: TDBGrid;
Edit1: TEdit;
Edit2: TEdit;
ComboBox1: TComboBox;
Button1: TButton;
Button2: TButton;
Button3: TButton;
Button4: TButton;
Label1: TLabel;
Label2: TLabel;
Label3: TLabel;
ZConnection1: TZConnection;
ZQuery1: TZQuery;
DataSource1: TDataSource;
DBNavigator1: TDBNavigator;
Edit3: TEdit;
Button5: TButton;
Button6: TButton;
Edit4: TEdit;
Button7: TButton;
Button8: TButton;
procedure FormActivate(Sender: TObject);
procedure Button1Click(Sender: TObject);
procedure Button5Click(Sender: TObject);
procedure Button4Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure Edit3Change(Sender: TObject);
procedure DBGrid1DblClick(Sender: TObject);
procedure Button3Click(Sender: TObject);
procedure Button6Click(Sender: TObject);
procedure Button7Click(Sender: TObject);
procedure FormCreate(Sender: TObject);
procedure Button8Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form2: TForm2;
implementation
{$R *.dfm}
function GridSelectAll(Grid: TDBGrid): Longint;
begin
Result := 0;
Grid.SelectedRows.Clear;
with Grid.DataSource.DataSet do
begin
First;
DisableControls;
try
while not EOF do
begin
Grid.SelectedRows.CurrentRowSelected := True;
Inc(Result);
Next;
end;
finally
EnableControls;
end;
end;
end;
//Pakai Database x : POSTGRESQL
//Delphi 2010
//PostgreSQL : User : postgres ; passwords : naikolan
procedure TForm2.Button1Click(Sender: TObject);
begin
ZQuery1.Filtered:=True;
if Length(Edit1.Text) <= 0 then
Begin
MessageBox(Handle,'Maaf data Ada Yang Kosong','Warning',MB_IconInformation);
If ZQuery1.IsEmpty Then
Begin
ZQuery1.SQL.Clear;
ZQuery1.SQL.Add('select * from "MasterNim"');
ZQuery1.ExecSQL;
Edit1.SetFocus;
End;
Exit;
End;
With ZQuery1 Do
Begin
SQL.Clear;
SQL.Text:='INSERT INTO "MasterNim"("Nama", "Kelas", "Status") VALUES('
+QuotedStr(Edit1.Text)+',' +Quotedstr(Edit2.Text)+',' +Quotedstr(Combobox1.Text)+')';
ExecSQL;
End;
With ZQuery1 Do
Begin
Active:=False;
SQL.Clear;
SQL.Add('select * from "MasterNim"');
ExecSQL;
Active:=True;
End;
end;
procedure TForm2.Button2Click(Sender: TObject);
begin
ZQuery1.Filtered:=True;
With ZQuery1 Do
begin
Active:=False;
SQL.Clear;
SQL.Text:='UPDATE "MasterNim" SET "Kelas"=' +Quotedstr(Edit2.Text)+',' +
'"Status"=' +QuotedStr(Combobox1.Text)+
' WHERE "Nama"=' +Quotedstr(Edit1.Text);
ExecSQL;
Active:=True;
End;
With ZQuery1 Do
Begin
Active:=False;
SQL.Clear;
SQL.Text:='SELECT * FROM "MasterNim"';
ExecSQL;
Active:=True;
End;
end;
procedure TForm2.Button3Click(Sender: TObject);
begin
if (ZQuery1.RecordCount=0) then
begin
MessageBox(Handle,'Data Belum Terpilih','Warning',MB_IconError);
Exit;
end
else if MessageDlg('Yakin Data mau dihapus..??? ',mtConfirmation,[mbYes,mbNo],0)=mrYes then
begin
With ZQuery1 Do
Begin
SQL.Clear;
SQL.Text:='DELETE FROM "MasterNim" WHERE "Nama"=' +Quotedstr(Edit1.Text);
ExecSQL;
End;
With ZQuery1 Do
begin
Active:=False;
SQL.Clear;
SQL.Text:='SELECT * FROM "MasterNim"';
Open;
Active:=True;
end;
end;
end;
procedure TForm2.Button4Click(Sender: TObject);
begin
Edit1.Clear;
Edit2.Clear;
Combobox1.Text:='';
Button1.Enabled:=True;
Button2.Enabled:=False;
Button3.Enabled:=False;
//Select Database
With ZQuery1 Do
Begin
Active:=False;
SQL.Clear;
SQL.Text:='Select * from "MasterNim"';
ZQuery1.ExecSQL;
Active:=True;
End;
end;
procedure TForm2.Button5Click(Sender: TObject);
begin
if ZQuery1.Locate('Nama',Edit3.Text,[loCaseInsensitive,LoPartialKey]) then
Begin
Edit1.Text:=ZQuery1['Nama'];
Edit2.Text:=ZQuery1['Kelas'];
Combobox1.Text:=ZQuery1['Status'];
Button1.Enabled:=False;
Button2.Enabled:=True;
Button3.Enabled:=True;
End;
end;
procedure TForm2.Button6Click(Sender: TObject);
begin
With Zquery1 Do
Begin
Active:=False;
SQL.Clear;
SQL.Text:='SELECT * FROM "MasterNim" WHERE "Nama"=' +Quotedstr(Edit4.Text);
Open;
Active:=True;
End;
end;
procedure TForm2.Button7Click(Sender: TObject);
begin
GridSelectAll(DBGrid1);
end;
procedure TForm2.Button8Click(Sender: TObject);
begin
ZQuery1.First;
ZQuery1.MoveBy(Random(ZQuery1.RecordCount));
end;
procedure TForm2.DBGrid1DblClick(Sender: TObject);
begin
Edit1.Text:=DBGrid1.Fields[0].AsString;
Edit2.Text:=DBGrid1.Fields[1].AsString;
Combobox1.Text:=DBGrid1.Fields[2].AsString;
Button1.Enabled:=False;
Button2.Enabled:=True;
Button3.Enabled:=True;
end;
procedure TForm2.Edit3Change(Sender: TObject);
begin
if ZQuery1.Locate('Nama',Edit3.Text,[loCaseInsensitive,LoPartialKey]) then
Begin
Edit1.Text:=ZQuery1['Nama'];
Edit2.Text:=ZQuery1['Kelas'];
Combobox1.Text:=ZQuery1['Status'];
Button1.Enabled:=False;
Button2.Enabled:=True;
Button3.Enabled:=True;
End;
end;
procedure TForm2.FormActivate(Sender: TObject);
begin
Button2.Enabled:=False;
Button3.Enabled:=False;
end;
procedure TForm2.FormCreate(Sender: TObject);
begin
Randomize;
end;
end.
Keterangan Konfigurasi :
1. username : postgres dan passwords : naikolan
2. hostname : localhost
3. port : default
4. Table : MasterNim , Database : PostLourens
5. Menggunakan Delphi 2010 , Component Zeoslib
Silahkan download contoh PostgreSQL With Delphi Programming pada link di bawah ini
