Home > Firebird, Visual Basic > Rutin patch database

Rutin patch database

August 16, 2010

Kesibukan “coding demi sesuap nasi” he he he😀, sempat membuat blog ini enggak keurus.

Untungnya ada beberapa pengunjung yang berbaik hati untuk meninggalkan komennya sehingga saya pun jadi rutin untuk mengecek blog ini.

Melakukan perubahan struktur database merupakan pekerjaan sampingan yang mau tidak mau harus kita terima dengan ikhlas jika membuat aplikasi terutama aplikasi masal, karena biasanya aplikasi tipe ini sering sekali terjadi perubahan untuk memenuhi kebutuhan klien.

Untuk contoh disini saya menggunakan database Firebird.

Firebird sendiri sudah menyediakan command line tool (isql) untuk mengeksekusi pernyataan DML (SELECT, INSERT, UPDATE dan DELETE), DDL (CREATE, DROP, ALTER) dan DCL (GRANT, REVOKE de el el).

Isql ini mirip dengan tool mysql, referensi lengkap tentang isql bisa dilihat disini.

Untuk menjalankan isql kita membutuhkan library fbclient.dll dan secara default kedua file ini biasanya ngumpul di folder “C:\Program Files\Firebird\Firebird_x_x\bin” jika Anda sudah menemukannya, copykan kedua file tersebut ke folder aplikasi Anda. Hal ini kita lakukan untuk menyederhanakan dalam penulisan kode program.

Kita ambil contoh kasus, misal pada saat rilis awal program kita mempunyai struktur tabel siswa seperti berikut :

kemudian terjadi perubahan struktur table siswa dengan adanya penambahan field NAMA_AYAH dan NAMA_IBU

perintah DDL yang kita gunakan untuk menambahkan kedua field tersebut adalah sebagai berikut :

ALTER TABLE SISWA ADD NAMA_AYAH VARCHAR(30);
ALTER TABLE SISWA ADD NAMA_IBU VARCHAR(30);

kemudian skrip diatas kita simpan dengan nama patchdb.sql

Sampai disini muncul masalah yaitu isql tidak bisa langsung mengeksekusi skrip diatas, agar skrip diatas bisa dieksekusi maka file patchdb.sql harus kita edit dengan menambahkan beberapa kode berikut :

SET SQL DIALECT 3;
CONNECT 'C:\Program Files\SmartLibrary\DBSLS.FDB' USER 'SYSDBA' PASSWORD 'masterkey'; //masterkey adalah password default Firebird

sehingga isi akhir dari file patchdb.sql adalah sebagai berikut :

SET SQL DIALECT 3;
CONNECT 'C:\Program Files\SmartLibrary\DBSLS.FDB' USER 'SYSDBA' PASSWORD 'masterkey'; //masterkey adalah password default Firebird

ALTER TABLE SISWA ADD NAMA_AYAH VARCHAR(30);
ALTER TABLE SISWA ADD NAMA_IBU VARCHAR(30);

Skrip diatas dengan asumsi lokasi database (DBSLS.FDB) satu folder dengan aplikasi utama. Oke sekarang saatnya kita mengeksekusi file patchdb.sql dengan memanfaatkan utility isql.

Untuk mencobanya tambahkan sebuah modul kemudian copy paste kode berikut selain itu tambahkan juga referensi Microsoft Scripting Runtime di project Anda :

Option Explicit

Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long

Private Const SYNCHRONIZE   As Long = &H100000
Private Const INFINITE      As Long = &HFFFF
Private Const FILE_PATCH    As String = "patchdb.sql"

Dim fso As Scripting.FileSystemObject

Private Function fileExists(ByVal namaFile As String) As Boolean
    On Error GoTo errHandle

    If Not (Len(namaFile) > 0) Then fileExists = False: Exit Function

    Set fso = New Scripting.FileSystemObject
    fileExists = fso.fileExists(namaFile)
    Set fso = Nothing

    Exit Function
errHandle:
    fileExists = False
End Function

Private Sub patchDB()
    Dim ShellX  As Long
    Dim lPid    As Long
    Dim lHnd    As Long
    Dim lRet    As Long

    Dim cmd As String

    On Error GoTo errHandle

    cmd = Chr(34) & App.Path & "\isql.exe " & Chr(34) & " -q -i " & Chr(34) & App.Path & "\" & FILE_PATCH & Chr(34)

    'pastikan file isql.exe dan fbclient.dll ada di folder aplikasi
    If fileExists(App.Path & "\isql.exe") And fileExists(App.Path & "\fbclient.dll") Then

        'pastikan juga file patchdb.sql ada
        If fileExists(App.Path & "\" & FILE_PATCH) Then
            cmd = Chr(34) & App.Path & "\isql.exe " & Chr(34) & " -q -i " & Chr(34) & App.Path & "\" & FILE_PATCH & Chr(34)
            ShellX = Shell(cmd, vbHide)

            lPid = ShellX
            If lPid <> 0 Then
                lHnd = OpenProcess(SYNCHRONIZE, 0, lPid)
                If lHnd <> 0 Then
                    lRet = WaitForSingleObject(lHnd, INFINITE)
                    CloseHandle (lHnd)
                End If
            End If
        End If
    End If

    Exit Sub
errHandle:
    MsgBox Err.Description, vbExclamation, "Warning"
End Sub

Public Sub Main()
    Call patchDB
End Sub

Pada contoh diatas penulisan kode :

CONNECT 'C:\Program Files\SmartLibrary\DBSLS.FDB' USER 'SYSDBA' PASSWORD 'masterkey';

masih hard-coding tentunya hal ini akan menyebabkan error jika aplikasi diinstall selain difolder “C:\Program Files\SmartLibrary”, jadi silahkan kembangkan sendiri untuk penyelesaiannya🙂

Kesimpulannya dengan menggunakan metode sederhana seperti ini, setiap ada perubahan struktur database kita cukup mengirimkan file patchdb.sql yang berisi perubahan struktur database ke klien, gimana simple kan ?.

Selamat mencoba🙂

  1. August 25, 2010 at 3:56 am

    nice work..
    high quality articles, keep sharing…

    • August 25, 2010 at 6:02 am

      Oke om siipp, ditunggu donasinya😆

  2. Dimas Aritona
    December 31, 2010 at 6:17 am

    bro, kalau kita buat analisa dan generate database firebird lewat coding gimana? saya masih kesulitan dengan database firebird, untuk query mencari nama tabel dalam database firebird, bila ada maka di update dan bila tidak ada tabel atau kolom nya maka baru di create, mohon bantuannya, thx

    • December 31, 2010 at 8:42 am

      Gini om, Firebird menyediakan yang namanya table system mirip dengan Information Schema klo di MySQL.

      Salah satu table system yang ada di Firebird yaitu RDB$RELATIONS berisi informasi table :

      SELECT RDB$RELATION_NAME
      FROM RDB$RELATIONS
      WHERE RDB$SYSTEM_FLAG=0
      

      atau langsung menyebutkan nama tableny, ex :

      SELECT RDB$RELATION_NAME
      FROM RDB$RELATIONS
      WHERE RDB$SYSTEM_FLAG=0 AND RDB$RELATION_NAME = 'SISWA';
      

      Selanjutnya untuk create tabel atau nambah kolom tinggal gunakan aja DDL

      Selamat mencoba🙂

  3. Dimas Aritona
    January 4, 2011 at 8:49 am

    hanya satu kata bro, Mantap!!
    matursuwun untuk pencarian tabel dalam databasenya..

    boleh nambah pertanyaan lagi ga bro,
    misalkan ada 3 tabel: siswa, bulan, dan spp.
    query di firebird untuk bulan ditampilkan horisontal dan siswa ditampilkan vertikal, sedangkan spp ditampilkan menurut siswa bayar spp pada bulan tertentu, mohon bantuannya, thx

    • January 4, 2011 at 11:54 am

      Enggak jelas om, skrg lagi susah ngebayang-bayangin😀 mendingan pake gambar aja.

  4. Dimas Aritona
    January 4, 2011 at 12:45 pm

    hehehe… saya juga bingung mo ngejelasinnya, cara masukin gambarnya gimana ya(newbie). saya gambarin pake ini aja ya,
    ——————————————————————–
    siswa | Januari | Februari | …. | November | Desember |
    ——————————————————————–
    Abi | 10,000 | 10,000 | … | 10,000 | 10,000 |
    Budi | 10,000 | 10,000 | … | 10,000 | 10,000 |
    …. | …. | …. | … | …. | …. |
    ——————————————————————–

    nah, query dalam firebird untuk menampilkan hasil seperti gambar diatas gimana bro, mohon bantuannya, thx.

    • January 4, 2011 at 12:52 pm

      Yahhh, sekalian donk om struktur tabel siswa, bulan, dan spp nya
      Kan querynya berdasarkan tabelnya si om, masa’ sy harus membayangin lagi strukturnya.

  5. Dimas Aritona
    January 4, 2011 at 4:11 pm

    srukturnya, dalam
    tabel siswa ada kolom nis,nama_siswa
    tabel bulan ada kolom kode_bulan, nama_bulan
    tabel spp ada kolom nis,kode_bulan,jumlah_bayar
    trus gimana cara query dalam firebird bro, mohon bantuannya,thx

    • January 5, 2011 at 2:46 am

      Coba gini om :

      SELECT nama,
          MAX(CASE kode_bulan WHEN 1 THEN jumlah_bayar END) AS Januari,
          MAX(CASE kode_bulan WHEN 2 THEN jumlah_bayar END) AS Februari,
          MAX(CASE kode_bulan WHEN 3 THEN jumlah_bayar END) AS Maret
      FROM siswa INNER JOIN spp ON siswa.nis = spp.nis
      GROUP BY nama
      

      Bulan yg lain tinggal dilanjutkan ya😀

  6. Dimas Aritona
    January 5, 2011 at 4:09 am

    WOW… Double Mantap Bro!!
    matursuwun… matursuwun…
    bisa-bisa saya jadi kursus kilat neh sama njenengan, hehehe…

    firebird untuk vb.net ga bisa buat connect crystall report ya bro,
    saya dah download firebird DDEX Provider for Visual Studio dan coba masukkin ke vs 2005
    saat pembuatan dataset lewat data source firebird ga bisa ya bro, tahu ga bro cara2nya
    supaya bisa connect data source firebird, mohon bantuannya,thx

    • January 5, 2011 at 4:42 am

      Sory om sy bukan pengguna Crystall Report, coba baca2x ebook yang ada disini, lihat bagian komentar.

  7. Dimas Aritona
    January 5, 2011 at 5:59 am

    oiya, hehehe… pengguna active report ya, mahal ga ya harganya bro.

    kalau

    SELECT RDB$RELATION_NAME
    FROM RDB$RELATIONS
    WHERE RDB$SYSTEM_FLAG=0 AND RDB$RELATION_NAME = ‘SISWA’;

    itu untuk mengetahui tabel dalam database, sedangkan untuk mengetahui jumlah varchar dalam kolom nama_siswa di tabel siswa gimana querynya dalam firebird bro, mohon bantuannya, thx

    • January 5, 2011 at 6:20 am

      Coba ini om :

      SELECT r.RDB$FIELD_NAME AS field_name, f.RDB$FIELD_LENGTH AS field_length, f.RDB$FIELD_PRECISION AS field_precision, f.RDB$FIELD_SCALE AS field_scale,
      CASE f.RDB$FIELD_TYPE
          WHEN 261 THEN 'BLOB'
          WHEN 14 THEN 'CHAR'
          WHEN 40 THEN 'CSTRING'
          WHEN 11 THEN 'D_FLOAT'
          WHEN 27 THEN 'DOUBLE'
          WHEN 10 THEN 'FLOAT'
          WHEN 16 THEN 'INT64'
          WHEN 8 THEN 'INTEGER'
          WHEN 9 THEN 'QUAD'
          WHEN 7 THEN 'SMALLINT'
          WHEN 12 THEN 'DATE'
          WHEN 13 THEN 'TIME'
          WHEN 35 THEN 'TIMESTAMP'
          WHEN 37 THEN 'VARCHAR'
          ELSE 'UNKNOWN'
      END AS field_type
         FROM RDB$RELATION_FIELDS r
         LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME
         LEFT JOIN RDB$COLLATIONS coll ON f.RDB$COLLATION_ID = coll.RDB$COLLATION_ID
         LEFT JOIN RDB$CHARACTER_SETS cset ON f.RDB$CHARACTER_SET_ID = cset.RDB$CHARACTER_SET_ID
      WHERE r.RDB$RELATION_NAME='SISWA'  -- table name
      ORDER BY r.RDB$FIELD_POSITION;
      

      Referensi :
      Extracting META information from Interbase/Firebird SQL (INFORMATION_SCHEMA)

      Selamat mencoba🙂

  8. Dimas Aritona
    January 5, 2011 at 10:21 pm

    Beneran Triple Mantap Bro!!
    matursuwun… matursuwun… matursuwun…

    Ilmu yang njenengan kasih semoga jadi amal yang barokah, amiin

    • January 6, 2011 at 4:41 am

      Amin🙂

  9. Dimas Aritona
    January 12, 2011 at 2:26 pm

    alhamdulillah, akhirnya jadi juga bikin script untuk analisa dan generate database dengan bantuan skema firebird yang bro kasih, thanks bro..

    • January 13, 2011 at 2:41 am

      Oke om sama2x

  10. kvee
    October 28, 2011 at 4:28 am

    tutorialnya mantap semua pak…..saya byk terinspirasi nih maklum saya cuma mahasiswa yg baru belajar VB…walopun saya masih copas dalam membuat program semoga kedepannya saya bisa semahir bapak^^….jarang2 ada orang mau berbagi ilmu selengkap dan sesimple ini bahkan sampe di kasih contoh filenya…

    makasih byk pak..sangat membantu saya!

    • October 29, 2011 at 2:01 am

      Tq juga om udah mampir, sama sy juga masih mahasiswa beberapa tahun yg lalu😀

  11. kvee
    November 30, 2011 at 8:53 am

    mas saya mau tanya dung bgmn cara membuat database portable y?jadi maksudnya gini
    database bisa di pindah2 gak harus 1 folder sama aplikasinya dan misalnya dengan membuat tombol browse untuk mencari database….

    bisa minta coding di form browse databasenya+coding di form aplikasi yg terhubung dgn DBnya gak mas???udah utak2 mentok mas….>.<

    • December 1, 2011 at 7:50 am

      Klo untuk databasenya mungkin bs menggukan database ms access atau sqlite, trus untuk menampilkan dialog open file ada 2 cara :
      1. Menggunakan komponen CommonDialog
      2. Menggunakan fungsi API GetOpenFileName

  12. anas
    January 19, 2012 at 5:56 pm

    bro mau tanya misal dalam suatu database sudah terdapat data yang tersimpan kemudian user mau menambahkan suatu kolom pada tabel tanpa harus menggangu data sebelumnya gimana cara…scrpt lewat visual basic??? tankz

    • January 24, 2012 at 11:04 am

      Ya pake perintah alter om, seperti yg sy bahas diatas.
      Cara menjalankannya dari VB sama seperti om menjalankan perintah INSERT, UPDATE, dan DELETE

  13. sarkosyi
    January 31, 2012 at 9:27 pm

    bawel ke om google nemu sedikit pencerahan rebuild firebird dengan m* visual studio untuk mengganti password “MASTERKEY” dengan password kita. rumor nanti di versi 3 fitur ganti password ini bakal ada, namun saat ini dikejar setoran masih pake v2.5. Mudah2n ada pencerahan, gimana stepnya tuh mas, mohon pencerahannya y.. msh gelap nih😀

    • February 1, 2012 at 6:56 am

      Untuk mengganti password masterkey bisa dilakukan pada saat insalasi program, firebird sudah menyediakan tool gsec untuk menghandle masalah ini.

      Klo om buat paket instalasinya menggunakan inno setup, tool gsec bisa dipanggil pada section Run

      [Run]
      Filename: "{pf}\Firebird\Firebird_2_0\bin\gsec.exe"; Parameters: "-user SYSDBA -password masterkey -modify SYSDBA -pw ""PASSWORD_YANG_BARU"""; Flags: runhidden; StatusMsg: "Sedang mengganti password database..."
      
      • sarkosyi
        February 2, 2012 at 8:27 am

        O iya, lupa bahwa ini untuk versi embedded.
        ..
        setuju mas admin. cara itu juga diuraikan di petunjuk firebird. sayangnya masih ada kelemahan saat dibuka dengan instalasi baru firebird (file baru security dan *.msg-nya), atau dengan melihat histori role dan user di tabel system dengan IBExpert.

        dibanding dengan big five versi embedded database lain (MySQL, Oracle Berkeley DB‎, SQL Lite, PostgreSQL, Apache Derby), Firebird menawarkan fleksibilitas, multi server, multi platform, dan support yang konsisten, juga dengan memperhatikan kapabilitas database dan kemudahan instalasi untuk user (konsumen). InnoSetup masih yang terbaik untuk mendukung hal itu…. Lho…., berbau promosi y, wkwkwk… Share riset dengan versi database laen nih, semoga membantu..😀

        reff: http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems

        proteksi dilakukan dengan mengambil identitas hardware, dan proteksi di report (pake watermark, selama masa trial). data/file proteksi (lisensi) bisa diencrypt (dan disimpan tersembunyi), opsi: registri, file tersendiri, dan/atau di dalam databasenya sendiri. Insya Allah, konsumen nghargain jerih payah kita, dapet duit tuh dari coding…😀

        sekadar penyemangat untuk para developer: market di kita sangat besar, coba benchmark jumlah penduduk, jenis bisnis dan pelaku bisnis dengan negara lain, atau porsikan secara dunia. PR-nya adalah design sistem yang dibutuhkan mereka bisa kita terjemahkan ke dalam aplikasi kita. Jadi AYO BE SMART!, SEMANGAT!!!😀

        balik lagi ke proteksi firebird tadi, adakah inovasi atau solusinya? ma’af kalo jadi PR bagi mas admin, pecahin ya? maksa nih.. wkwkwk😀

  14. February 2, 2012 at 1:19 pm

    Sekarang yg menjadi pertanyaannya om, tujuan mengganti password ini untuk melindungi dari user yg mana dulu ? Klo cuma end user seperti yg biasa menjadi klien2x kita sy rasa enggak perlu sampai segitunya, mereka juga enggak akan penasaran kok dg struktur database yg kita buat, makanya sy sendiri malah lebih senang menggunakan password default FBnya.

    Klo usernya seperti sy atau om yg lagi cari2x referensi struktur database, ada pengaruhnya enggak dg mengganti password FBnya, buktinya database zahir dan accurate bisa dengan mudah kita jebol😀.

    Kayaxnya enggak perlu jadi PR nih🙂.

  15. sarkosyi
    February 2, 2012 at 6:34 pm

    hatur nuhun.. quick respon yeuh..😀
    baru sj selesai ngtrace raw source firebird, ketemu di security.sql & security_database.sql. masih hipotesa: dari sana user SYSDBA bisa diremove/diganti, dicoba ba’da isya sj lah, disini dah magrib..😀
    betul.., dengan tabel login sendiri (username & password) untuk masuk ke aplikasi, itu sudah memadai untuk end-user..
    tujuan repot2 repot spt ini karena ingin mendukung tim developer agar comfort dengan konsep user friendly (opsi GUI developer, database, instalasi, maintenance, dan update-nya).. PR sekarang tinggal di masalah proteksi level superuser database (firebird)..
    matur nuwun sanget sudah sy repotkan nih,..😀

    • February 3, 2012 at 8:49 am

      Wah2x kren ditunggu nih perkembangan berikutnya🙂

  16. agung
    January 2, 2013 at 5:52 pm

    om klo mau create prosedure melalui patch gmn? cos dah dcoba2 kok ndak bisa..suwun

    • January 3, 2013 at 8:31 am

      Sama aja om, klo mengacu pada postingan sy diatas berarti file patchdb.sql berisi skrip store procedure yg ingin ditambahkan.

  17. agung
    January 4, 2013 at 7:30 am

    gtu ya om, klo sy trace coding yg dr om: if lhnd 0, nilai dr lhnd 0 , kira2 knp om?

    • January 4, 2013 at 8:48 am

      var lHnd menyimpan ID proses ketika menjalankan perintah jadi klo nilai lHnd = 0 berarti perintah yg mengeksekusi file patchdb.sql mungkin gagal, coba om paste kesini skrip store procedurenya yg tersimpan di file patchdb.sql

  18. agung
    January 5, 2013 at 8:12 am

    create procedure hapushutang(kodehutang char(11)) as begin delete from hutang where kodehutang=:kodehutang; end –mohon pencerahanya om..

    • January 5, 2013 at 8:56 am

      Coba dilengkapi seperti ini om :

      SET TERM ^ ;
      
      create procedure hapushutang(kodehutang char(11)) 
      as 
      begin 
      	delete from hutang where kodehutang=:kodehutang; 
      end
      ^
      
      SET TERM ; ^
      
  19. agung
    January 5, 2013 at 11:14 am

    mantap om.. suwun.,hanya bisa membalas dg doa om..he..

    • January 5, 2013 at 11:56 am

      Amin🙂

  1. No trackbacks yet.
Comments are closed.
%d bloggers like this: