Menampilkan Data Mysql ke GridView pada Gambas 3

Berikut ini adalah catatan saya yang pertama mengenai Gambas. Disini kita akan menampilkan data Mysql ke GridView pada Gambas 3 di Ubuntu kesayangan kita. Langsung saja, berikut langkah-langkahnya.

1. Siapkan sebuah tabel pada mysql dengan nama tbl_satuan. buat dua buah field yakni : id_satuan dengan type varchar(3) di set sebagai primary key dan field satuan dengan type varchar(20)


2. Isi table tersebut dengan beberapa jenis satuan

3. Jalankan Gambas 3



4. Buat sebuah project baru dengan type graphicalaplication dan database access pada option dalam posisi enable.


5. Tentukan Project Parent Directory. Disini kita akan menggunakan home sebagai directory dimana project yang akan kita buat tersimpan.


6. Tentukan nama project yang akan kita buat. Project ini akan kita buat dengan nama GridViewMysql


7. Akan muncul pesan sukses apabila kita telah berhasil membuat project dengan benar.


8. Buat sebuah module pada project kita dengan cara klik kanan pada Source, kemudian pilih New, lalu klik Module


9. Tentukan nama module-nya adalah koneksi


10. Masukkan kode berikut

' Gambas module file

Public con As New Connection

'melakukan koneksi ke database mysql
Public Procedure Connect()
con.Host = "localhost"
con.type = "mysql" 
con.login = "root" 
con.Password = "123123" 
con.name = "mywaroeng" 
con.Open 
End

'menutup koneksi dari database mysql
Public Procedure Disconnect()
con.Close()
End

11. Buka Form FMain


12. Tambahkan sebuah GridView dan sebuah tombol


13. Pada form FMain klik tombol Code untuk menampilkan kode dari FMain.

14. Masukkan kode berikut diantara "Public Sub Form_Open()" dan "End" menjadi

Public Sub Form_Open()
  Dim iCount As Integer
  Dim hTable As Table
  Dim sql As String
  Dim rsSatuan As Result
  Dim intRow As Integer = 0
  Dim strType As String = ""

    
  'define the gridview layout

  GridView1.header = GridView.Horizontal
  GridView1.grid = True
  GridView1.Rows.count = 2
  GridView1.Columns.count = 2
  GridView1.Columns[0].text = "id_satuan"
  GridView1.Columns[1].text = "satuan"
  GridView1.Columns[0].width = 100
  GridView1.Columns[1].width = 100

'buka koneksi ke database
koneksi.Connect()
'menjalankan query
 rsSatuan = koneksi.con.Exec("SELECT * FROM tbl_satuan")
 
'cek hasil
If rsSatuan.Available Then
rsSatuan.MoveNext
GridView1.Rows.Count = rsSatuan.Count

'menyusuh hasil
For Each rsSatuan
GridView1[intRow, 0].Text = rsSatuan!id_satuan
GridView1[intRow, 1].Text = rsSatuan!satuan

'Inc intRow kode yang sangat penting untuk me-generate data
Inc intRow

Next
koneksi.con.Quote(rsSatuan.Count & " Charge records returned")
 
Endif

'tutup koneksi ke database
koneksi.Disconnect()
End

15. Pada form FMain klik dua kali tepat pada button1


16. Masukkan code

Me.Close()

17. Jalankan program yg telah kita buat dengan cara klik tombol run pada menu


18. Berikut hasilnya.


Demikian catatan saya kali ini, semoga dapat bermanfaat bagi kita semua.

Cara Instal Apache2 di Ubuntu

sudo apt-get install apache2

buka browser kesayangan anda lalu arahkan ke IP Address ubuntu anda contoh: http://192.168.0.100, jika anda melihat It works! pada browser anda berarti anda telah berhasil mengistall Apache2
pada ubuntu folder root default web anda /var/www
konfigurasi apache ada di /etc/apache2/apache2.conf

Cara Pengaturan DNS di Ubuntu Server 12.04

sudo vi /etc/resolv.conf

    nameserver 8.8.8.8
    nameserver 8.8.4.4

simpan lalu restart networking

sudo /etc/init.d/networking restart

Cara Pengaturan IP Address di Ubuntu Server 12.04

sudo vi /etc/network/interfaces

    iface eth0 inet static
    address 192.168.31.100
    netmask 255.255.255.0
    network 192.168.31.1
    broadcast 192.168.31.255
    gateway 192.168.31.1

simpan lalu restart networking

sudo /etc/init.d/networking restart

Sort dalam Mysql

mysql> SELECT id, title FROM tbl_images order by id desc;
+----+-------+
| id | title |
+----+-------+
|  2 |       |
|  1 |       |
+----+-------+
2 rows in set (0.00 sec)

mysql> SELECT id, title FROM tbl_images order by id asc;
+----+-------+
| id | title |
+----+-------+
|  1 |       |
|  2 |       |
+----+-------+
2 rows in set (0.00 sec)

Contoh-Contoh TRIGGER dalam Mysql

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }


DELIMITER //
CREATE TRIGGER tbl_beli_subt_upd AFTER UPDATE ON tbl_beli
FOR EACH ROW
BEGIN

IF new.harga_beli <=> old.harga_beli and new.jumlah <=> old.jumlah THEN
UPDATE tbl_beli SET sub_total = (new.harga_beli * new.jumlah) WHERE id_beli = old.id_beli;
END IF;

IF new.harga_beli = old.harga_beli and new.jumlah <=> old.jumlah THEN
UPDATE tbl_beli SET sub_total = (old.harga_beli * new.jumlah) WHERE id_beli = old.id_beli;
END IF;

IF new.harga_beli <=> old.harga_beli and new.jumlah = old.jumlah THEN
UPDATE tbl_beli SET sub_total = (new.harga_beli * old.jumlah) WHERE id_beli = old.id_beli;
END IF;

END;
// 
DELIMITER;


DELIMITER //
CREATE TRIGGER upd_sbt AFTER INSERT ON tbl_beli
FOR EACH ROW
BEGIN
set @id_beli = new.id_beli;
set @harga_beli = new.harga_beli;
set @jumlah = new.jumlah;
set @sub_total = @harga_beli * @jumlah;
UPDATE tbl_beli SET sub_total = @sub_total WHERE id_beli = @id_beli
END;
// 
DELIMITER;



drop trigger trig_transaksi;
DELIMITER //
CREATE TRIGGER trig_transaksi after INSERT ON tbl_transaksi FOR EACH ROW
BEGIN
IF new.ktg_transaksi = 'Penjualan' THEN 
UPDATE tbl_barang SET stok = stok - new.jmlh WHERE kode = new.kode_brg;
END IF;
IF new.ktg_transaksi = 'Pembelian' THEN 
UPDATE tbl_barang SET stok = stok + new.jmlh, hrgbli = new.hrgbli WHERE kode = new.kode_brg;
END IF;
END 
// 
DELIMITER ;


drop trigger trig_del_transaksi;
DELIMITER //
CREATE TRIGGER trig_del_transaksi after DELETE ON tbl_transaksi FOR EACH ROW
BEGIN
IF old.ktg_transaksi = 'Penjualan' THEN 
UPDATE tbl_barang SET stok = stok + old.jmlh WHERE kode = old.kode_brg;
END IF;
IF old.ktg_transaksi = 'Pembelian' THEN 
UPDATE tbl_barang SET stok = stok - old.jmlh WHERE kode = old.kode_brg;
END IF;
END 
// 
DELIMITER ;

drop trigger trig_upd_transaksi;
DELIMITER //
CREATE TRIGGER trig_upd_transaksi after UPDATE ON tbl_transaksi FOR EACH ROW
BEGIN
IF new.ktg_transaksi = 'Penjualan' THEN 
UPDATE tbl_barang SET stok = stok + old.jmlh - new.jmlh WHERE kode = new.kode_brg;
END IF;
IF new.ktg_transaksi = 'Pembelian' THEN 
UPDATE tbl_barang SET stok = stok - old.jmlh + new.jmlh, hrgbli = new.hrgbli WHERE kode = new.kode_brg;
END IF;
END 
// 
DELIMITER ;


drop trigger trig_kwi;
DELIMITER //
CREATE TRIGGER trig_kwi after INSERT ON tbl_kwi FOR EACH ROW
BEGIN
UPDATE tbl_kwi_det SET tgl_byr = new.tgl_byr WHERE kwitansi = new.kwitansi;
END 
// 
DELIMITER ;

drop trigger trig_kwi_lun;
DELIMITER //
CREATE TRIGGER trig_kwi_lun after UPDATE ON tbl_kwi_det FOR EACH ROW
BEGIN
UPDATE tbl_nota SET status_pembayaran = 'Lunas',  tgl_byr = new.tgl_byr WHERE nota = old.nota;
END 
// 
DELIMITER ;

DELIMITER //
CREATE TRIGGER trig_transaksi after INSERT ON tbl_beli FOR EACH ROW
BEGIN
INSERT INTO tbl_gudang (id_barang) value (NEW.id_barang);
END 
// 
DELIMITER ;


DELIMITER //
CREATE TRIGGER trig_transaksi after INSERT ON tbl_beli FOR EACH ROW
BEGIN

declare pencacah int;
set pencacah = 0;
select pencacah;
ulang : LOOP
set pencacah = pencacah + 1;
if pencacah > 10 then leave ulang; end if;
END LOOP ulang;
select pencacah;

END 
// 
DELIMITER ;




DELIMITER //
CREATE TRIGGER trig_transaksi BEFORE UPDATE ON tbl_beli FOR EACH ROW
BEGIN
declare x int;
declare n int;
set x = 0;
SET n = new.jumlah;
ulang : LOOP
set x = x + 1;
if x > n then leave ulang; end if;
INSERT INTO tbl_gudang (id_barang) value (NEW.id_barang);
END LOOP ulang;
END 
// 
DELIMITER ;








DELIMITER //
CREATE TRIGGER post_beli BEFORE UPDATE ON tbl_beli FOR EACH ROW
BEGIN
declare x int;
declare n int;
set x = 0;
SET n = new.jumlah;
if new.status = 1 then
ulang : LOOP
set x = x + 1;
if x > n then leave ulang; end if;
INSERT INTO tbl_gudang (id_barang,tanggal_kedaluarsa,id_cabang,id_beli,harga_beli,laba,ppn,harga_jual) value (new.id_barang,new.tanggal_kedaluarsa,'GDG',new.id_beli,new.harga_beli,new.laba,new.ppn,new.harga_jual);
END LOOP ulang;

end if;
END 
// 
DELIMITER ;


====================================================================
drop trigger post_beli;
DELIMITER //
CREATE TRIGGER post_beli BEFORE UPDATE ON tbl_beli FOR EACH ROW
BEGIN
declare x int;
declare n int;
set x = 0;
SET n = new.jumlah;
if new.status = 1 then
ulang : LOOP
set x = x + 1;
if x > n then leave ulang; end if;
INSERT INTO tbl_stok (id_barang,id_beli,harga_beli,laba,ppn,harga_jual,id_cabang) value (new.id_barang,new.id_beli,new.harga_beli,new.laba,new.ppn,new.harga_jual,new.id_cabang);
END LOOP ulang;

end if;
END 
// 
DELIMITER ;
====================================================================
drop trigger post_beli_nota;
DELIMITER //
CREATE TRIGGER post_beli_nota AFTER INSERT ON tbl_beli FOR EACH ROW
BEGIN
INSERT INTO UPDATE (status) value (new.id_barang,new.id_beli,new.harga_beli,new.laba,new.ppn,new.harga_jual,new.id_cabang);
END 
// 
DELIMITER ;
====================================================================
drop trigger post_jual;
DELIMITER //
CREATE TRIGGER post_jual AFTER INSERT ON tbl_jual FOR EACH ROW
BEGIN
declare n int;
SET n = new.jumlah;
UPDATE tbl_stok SET id_jual=new.id_jual
     WHERE id_stok IN (
         SELECT id_stok FROM (
             SELECT id_stok FROM tbl_stok 
             WHERE
             id_jual is NULL AND id_barang = new.id_barang
             ORDER BY id_stok ASC  
             LIMIT 0, n
         ) tmp
     );
END 
// 
DELIMITER ;
====================================================================
drop trigger post_jual_del;
DELIMITER //
CREATE TRIGGER post_jual_del AFTER DELETE ON tbl_jual FOR EACH ROW
BEGIN
UPDATE tbl_stok SET id_jual=NULL WHERE id_jual = old.id_jual;
END 
// 
DELIMITER ;
====================================================================
drop trigger post_jual_upd;
DELIMITER //
CREATE TRIGGER post_jual_upd AFTER UPDATE ON tbl_jual FOR EACH ROW
BEGIN
declare jumlah_lama int;
declare jumlah_baru int;
declare tambah int;
declare kurang int;
SET jumlah_lama = old.jumlah;
SET jumlah_baru = new.jumlah;
SET tambah = jumlah_baru - jumlah_lama;
SET kurang = jumlah_lama - jumlah_baru;
if jumlah_lama > jumlah_baru then
UPDATE tbl_stok SET id_jual=NULL
     WHERE id_stok IN (
         SELECT id_stok FROM (
             SELECT id_stok FROM tbl_stok 
             WHERE
             id_jual = old.id_jual AND id_barang = old.id_barang
             ORDER BY id_stok DESC  
             LIMIT 0, kurang
         ) tmp
     );
end if;
if jumlah_lama < jumlah_baru then
UPDATE tbl_stok SET id_jual=new.id_jual
     WHERE id_stok IN (
         SELECT id_stok FROM (
             SELECT id_stok FROM tbl_stok 
             WHERE
             id_jual is NULL AND id_barang = old.id_barang
             ORDER BY id_stok ASC  
             LIMIT 0, tambah
         ) tmp
     );

end if;
END 
// 
DELIMITER ;
====================================================================
drop trigger post_piutang_nota;
DELIMITER //
CREATE TRIGGER post_piutang_nota AFTER INSERT ON tbl_piutang_nota FOR EACH ROW
BEGIN
UPDATE tbl_piutang_bayar SET status = 1 WHERE id_piutang_nota = new.id_piutang_nota;
End
// 
DELIMITER ;
====================================================================
drop trigger post_hutang_nota;
DELIMITER //
CREATE TRIGGER post_hutang_nota AFTER INSERT ON tbl_hutang_nota FOR EACH ROW
BEGIN
UPDATE tbl_hutang_bayar SET status = 1 WHERE id_hutang_nota = new.id_hutang_nota;
End
// 
DELIMITER ;

Undo dalam Mysql

KOde mulai

BEGIN;
INSERT INTO tbl_piutang_bayar (id_jual_nota, id_piutang_nota) VALUES ('P001-00000001', 'J001-00000001'), ('P001-00000001', 'J001-00000003'), ('P001-00000001', 'J001-00000004');

untuk Undo

ROLLBACK;

untuk tidak undo

COMMIT; 

Variasi Perintah INSERT dalam Mysql

Bentuk Dasar Perintah INSERT

INSERT INTO tbl_barang VALUES('BRG001', 'AYAM BAKAR', 'MAKANAN');

#1. INSERT INTO tabel (field1, field2) VALUES ...

INSERT INTO tbl_barang (kode_barang, nama_barang) VALUES('BRG001', 'AYAM BAKAR');

#2. INSERT ... SELECT ...

    INSERT INTO tbl_jual(nama_barang, kode_barang) SELECT kode_barang, nama_barang FROM tbl_barang;

#3. INSERT IGNORE

    INSERT IGNORE INTO tbl_barang VALUES('BRG001', 'AYAM BAKAR', 'MAKANAN');

#4. INSERT DELAYED

    INSERT DELAYED INTO tbl_barang (nama_barang, kode_barang) VALUES ('BRG001', 'AYAM BAKAR');

#5. INSERT LOW PRIORITY | HIGH PRIORITY

    INSERT INTO tbl_barang VALUES('BRG001', 'AYAM BAKAR');

#6. INSERT ... ON DUPLICATE KEY UPDATE

    INSERT INTO tbl_jual (nama_barang, kode_barang, kategori) VALUES('BRG001', 'AYAM BAKAR', 'MAKANAN') ON DUPLICATE KEY UPDATE kategori='MAKANAN';

#7. REPLACE INTO ...

    REPLACE INTO tbl_barang VALUES('BRG001', 'AYAM BAKAR', 'MAKANAN');

Select dalam Select Mysql

select sum(keranjang.subtotal) as sub_total from (select id_beli_nota, harga_beli, jumlah, (harga_beli * jumlah) as subtotal from tbl_beli where id_beli_nota = 'BLI-000000001') keranjang group by keranjang.id_beli_nota

Update dengan limit Mysql

UPDATE table_name SET name='test'
     WHERE id IN (
         SELECT id FROM (
             SELECT id FROM table_name 
             ORDER BY id ASC  
             LIMIT 0, 10
         ) tmp
     );

Menghilangkan Data Duplikat

SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]]  FROM table

Mengambil id terakhir pada field AUTO INCREMENT Mysql

SELECT LAST_INSERT_ID();

Membuat User Baru Pada Mysql Server

mysql> use mysql;
mysql> insert into user (host,user,password,select_priv) values('localhost','kopkar', password('123123'),'Y');
mysql> flush privileges;

Mengubah NULL menjadi 0 pada Mysql

IFNULL(field,0)

Format Tanggal pada Mysql

DATE_FORMAT(date,format)


Format   | Description
%a  | Abbreviated weekday name
%b  | Abbreviated month name
%c  | Month, numeric
%D  | Day of month with English suffix
%d  | Day of month, numeric (00-31)
%e  | Day of month, numeric (0-31)
%f  | Microseconds
%H  | Hour (00-23)
%h  | Hour (01-12)
%I  | Hour (01-12)
%i  | Minutes, numeric (00-59)
%j  | Day of year (001-366)
%k  | Hour (0-23)
%l  | Hour (1-12)
%M  | Month name
%m  | Month, numeric (00-12)
%p  | AM or PM
%r  | Time, 12-hour (hh:mm:ss AM or PM)
%S  | Seconds (00-59)
%s  | Seconds (00-59)
%T  | Time, 24-hour (hh:mm:ss)
%U  | Week (00-53) where Sunday is the first day of week
%u  | Week (00-53) where Monday is the first day of week
%V  | Week (01-53) where Sunday is the first day of week, used with %X
%v  | Week (01-53) where Monday is the first day of week, used with %x
%W  | Weekday name
%w  | Day of the week (0=Sunday, 6=Saturday)
%X  | Year of the week where Sunday is the first day of week, four digits, used with %V
%x  | Year of the week where Monday is the first day of week, four digits, used with %v
%Y  | Year, four digits
%y  | Year, two digits

Date and Time Functions

mysql> SELECT something FROM tbl_name

    -> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;

=============================================================

mysql> SELECT DAYOFMONTH('2001-11-00'), MONTH('2005-00-00');

        -> 0, 0

=============================================================

mysql> SELECT DATE_ADD('2006-05-00',INTERVAL 1 DAY);

        -> NULL

mysql> SELECT DAYNAME('2006-05-00');

        -> NULL

=============================================================

mysql> SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);

        -> '2008-02-02'

mysql> SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);

        -> '2008-02-02'

=============================================================

mysql> SELECT ADDDATE('2008-01-02', 31);

        -> '2008-02-02'

=============================================================

mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');

        -> '2008-01-02 01:01:01.000001'

mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');

        -> '03:00:01.999997'

=============================================================

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');

        -> '2004-01-01 13:00:00'

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');

        -> '2004-01-01 22:00:00'

=============================================================

mysql> SELECT CURDATE();

        -> '2008-06-13'

mysql> SELECT CURDATE() + 0;

        -> 20080613

=============================================================

encrypt & decrypt sederhana

Berikut ini adalah catatan mengenai encrypt & decrypt, Banyak tehik lain yang lebih baik anda dapat melakukan searching di google. Berikut cara simple encrypt & decrypt data anda. Langkah-langkah.
1. Buka VB 2008, buat sebuah project baru lalu berinama Encrypt-Decrypt-MD5Hash
2. Pada Form1 masukkan 3 buah textbox dan 2 buah tombol seperti pada gambar
3. masukkan kode