Artikel 15: Fitur LastInsertID (MySQL) Dan Returning (PostgreSQL) Pada Data Master-Detail

Fitur LastInsertID (MySQL) Dan Returning (PostgreSQL) Pada Data Master-Detail

Dalam pemrograman database MySQL dengan Delphi, atau pemrograman aplikasi Delphi dengan database  PostgreSQL, kedua fitur di atas akan menjadi penting dan sangat bermanfaat. Untuk melihat manfaat LastInsertID dan Returning ini, kita anggap bahwa kita punya dua tabel, yaitu tabel “ref_jenis_jurnal” yang menjadi tabel master:

0001 0002 0003 0004 0005 0006 0007 0008 0009 0010

CREATE TABLE `ref_jenis_jurnal` (     `id` INT(8) NOT NULL AUTO_INCREMENT,     `jenis` VARCHAR(200) NOT NULL,     `keterangan` TEXT NULL,     `tabel` VARCHAR(50) NULL DEFAULT NULL,     PRIMARY KEY (`id`) ) COLLATE='latin1_swedish_ci' ENGINE=InnoDB AUTO_INCREMENT=5;

Dan sebuah tabel lagi, “ref_jenis_jurnal_rek” yang menjadi tabel detail:

0001 0002 0003 0004 0005 0006 0007 0008 0009 0010 0011 0012

CREATE TABLE `ref_jenis_jurnal_rek` (     `id` INT(8) NOT NULL AUTO_INCREMENT,     `jenis_jurnal` INT(8) NOT NULL,     `rek` VARCHAR(7) NOT NULL,     `posisi` ENUM('D','K') NOT NULL,     PRIMARY KEY (`id`),     INDEX `FK__ref_jenis_jurnal` (`jenis_jurnal`),     CONSTRAINT `FK__ref_jenis_jurnal` FOREIGN KEY (`jenis_jurnal`) REFERENCES `ref_jenis_jurnal` (`id`) ON UPDATE CASCADE ON DELETE CASCADE ) COLLATE='latin1_swedish_ci' ENGINE=InnoDB AUTO_INCREMENT=14;

Perlu Dicatat

Contoh tabel di atas adalah tabel-tabel dalam database aplikasi akuntansi. Bahwasannya kedua tabel di atas adalah tabel di database MySQL.

Nah, dari aplikasi Delphi, user dibolehkan membuat sebuah record untuk tabel “ref_jenis_jurnal” sekaligus record-record yang menjadi detailnya di tabel “ref_jenis_jurnal_rek”. Saat di-save nanti, yang pertama kita lakukan adalah memasukkan record master ke tabel master, lalu mengambil nilai field ref_jenis_jurnal.id untuk nanti dijadikan value pada field detail ref_jenis_jurnal_rek.jenis_jurnal.
Pertanyaannya, bagaimanakah kita mendapatkan nilai field ref_jenis_jurnal.id ini saat proses menyimpan data?
Di MySQL, setiap proses insert data ke tabel yang memiliki field primary auto_increment akan mengubah variabel global session LAST_INSERT_ID yang dapat diambil dengan fungsi MySQL “LAST_INSERT_ID()”.Di Delphi, fungsi ini tidak perlu kita panggil manual, karena banyak vendor 3rd party VCL Components sudah mendukungnya:

• UniDac (Devart) telah mendukungnya dengan TUniTable.LastInsertID dan TUniQuery.LastInsertID;
• MyDac (Devart) pun mendukungna dengan TMyTable.InsertID dan TMyQuery.InsertID;

Contoh penggunaan property LastInsertID pada UniDac dapat dilihat pada contoh kode berikut. Penggunaan pada MyDac tidak jauh berbeda.

0001 0002 0003 0004 0005 0006 0007 0008 0009 0010 0011 0012

procedure TForm1.Button1Click(Sender: TObject); var   ID: Int64; begin   UniTable1.TableName := 'ref_jenis_jurnal';   UniTable1.Open;   UniTable1.Append;   UniTable1.FieldByName('jenis').AsString := 'Jurnal Pembayaran Listrik & PDAM';   //..............   UniTable1.Post;   ID := UniTable1.LastInsertId; end;

Dari situ, kita bisa melakukan insert untuk record-record detail dengan cara:

0001 0002 0003 0004 0005 0006 0007

//save reord detail:   UniTable2.TableName := 'ref_jenis_jurnal_rek';   UniTable2.Open;   UniTable2.Append;   UniTable2.FieldByName('jenis_jurnal').AsInteger := ID;   //..............   UniTable2.Post;

Pertanyaan lain akan timbul: Apakah tidak akan terjadi tabrakan pada fungsi LAST_INSERT_ID() ini? Di saat bersamaan dengan waktu kita melakukan insert ke tabel master, ternyata ada user lain yang juga melakukan insert ke tabel yang sama. Nilai mana yang akan dihasilkan oleh LAST_INSERT_ID()?
Jangan khawatir. MySQL memiliki mekanisme locking yang bagus sampai ke level record. Dan setiap koneksi yang berbeda akan diidentifikasi oleh MySQL sebagai session yang berbeda pula. Meskipun proses insert dilakukan oleh dua user pada saat yang persis sama, di server MySQL sendiri akan dilihat sebagai dua session yang berlainan, dan MySQL akan memilih salah satu session untuk diproses duluan, sementara yang lain akan disimpan dulu di cache. Saat salah satu session diproses dan yang lain menunggu inilah yang disebut locking (penguncian). Dengan mekanisme locking dan session ini, nilai field ref_jenis_jurnal.id yang dikembalikan oleh fungsi LAST_INSERT_ID() dijamin unik dan berbeda pada setiap session koneksi.
Kemudian, apakah PostgreSQL memiliki konsep yang sama? Sayang sekali, tidak!
Lho, sebuah server database yang disebut-sebut sebagai versi open source dari Oracle kok kalah sama MySQL? Tidak juga. PostgreSQL memiliki cara lain untuk menangani “insertion ID ” ini. Bahkan lebih canggih daripada yang ditawarkan MySQL. Untuk melihat seberapa canggih fitur PostgreSQL yang satu ini, silahkan perhatikan gambar di bawah:
Klik di sini agar mendapatkan sedikit gambaran.
Sudah fresh? Ok, kita lanjut.
Di PostgreSQL, tersedia sebuah keyword returning yang bersifat opsional saat kita melakukan insert ke tabel menggunakan query. Sayang sekali, kita akan sedikit kerepotan bila menggunakan komponen TDataset dan turunannya (TUniTable, TMyQuery, dsb.) untuk menginsert data. Tapi tidak masalah, karena saya pikir bahwa sebagai programmer Delphi berpengalaman, Anda akan memilih query untuk menginsert data, bukan TDataset. Tidak seperti saya yang kerjanya asal jadi
fitur Returning ini dapat kita gunakan seperti ini:

0001 0002 0003 0004 0005 0006 0007 0008 0009 0010 0011

procedure TForm1.Button2Click(Sender: TObject); var   ID: Int64; begin   //.................   UniQuery1.SQL.Text := 'insert into ref_jenis_jurnal(jenis,...,...) values (...,...,...) returning id';   UniQuery1.Open;   ID := UniQuery1.Fields[0].AsInteger;   UniQuery1.Close;   //................. end;

Lihat perintah insert SQL pada kode di atas? Ada “returning id” pada SQL itu. Maksudnya, begitu perintah insert berhasil, nilai field “id” yang baru dikembalikan lagi ke aplikasi kita. Kita dapat mengambil nilai ini dengan kode

0001 0002 0003

ID := UniQuery1.Fields[0].AsInteger; //atau: ID := UniQuery1.FieldByName('id').AsInteger;

Canggihnya, kita bahkan bisa melakukan return terhadapt field-field lain yang bukan auto_increment. Sembarang field lah pokoknya. Lebih hebat lagi, kita dapat me-return beberapa field sekaligus:

0001 0002 0003 0004 0005 0006 0007 0008 0009 0010 0011 0012 0013

procedure TForm1.Button2Click(Sender: TObject); var   ID: Int64;   Jenis: String; begin   //.................   UniQuery1.SQL.Text := 'insert into ref_jenis_jurnal(jenis,...,...) values (...,...,...) returning id, jenis';   UniQuery1.Open;   ID := UniQuery1.Fields[0].AsInteger;   Jenis := UniQuery1.Fields[1].AsString;   UniQuery1.Close;   //................. end;

Tinggal kita sesuaikan saja dengan kebutuhan kita. MySQL atau PostgreSQL? Itu urusan Anda, silahkan sesuaikan kebutuhan dan tingkat kompleksitas database yang Anda perlukan.

Sumber:  http://www.cenadep.org/wp-content/uploads/2012/09/Gambar-Penyegaran-Mata-Sebelum-Membahas-Delphi-dan-Database.jpg