Sorting Data Using SQL Like Operator

Thursday, March 27, 2008

Operator Like digunakan dalam sebuah SQL Statement untuk mencari sebuah nilai dalam sebuah table yang mempunyai pola string atau karakter tertentu. operator Like ini ditempatkan setelah klausa where dalam sebuah SQL Statement. perbedaan antara operator Like dengan operator '=' adalah, operator '=' tidak bisa digunakan untuk mencari sebuah nilai yang tidak kita ketahui sebelumnya.



Operator Like ini biasa digunakan bersama dengan karakter '%' (Percent), '*' (Asterisk), '_' (Underscore), '?' (Quatation Marks),'#' (Number Sign),'-' (Hyphen),'[]' (Brackets) dan karakter '!' (Exclamation Point). Disini saya hanya akan membahas penggunaan Operator Like bersama dengan karakter '%', '*', '_' dan karakter '?'. Adapun fungsi dari pengunaan masing-masing karakter tersebut adalah sebagai berikut :



Karakter '*' (di Microsoft Access) atau Karakter '%' artinya adalah semua karakter. Jadi jika anda menuliskan 'SQL Like%' atau 'SQL Like*', itu berarti semua yang mengandung string SQL Like didepannya dan jika anda menuliskan '%SQL Like' berarti semua yang mengandung string 'SQL Like' di belakangnya.



Contoh penggunaan karakter % dalam statement SQL :



Select JudulBuku from Buku

Where JudulBuku like '%SQL Like%'


Contoh penggunaan karakter * dalam statement SQL untuk Microsoft Access :



Select JudulBuku from Buku

Where JudulBuku like '*SQL Like*'


SQL Statement diatas akan menghasilkan semua Data buku yang mengandung string 'SQL Like' didalam judulnya karena anda menambahkan karakter '%' atau '*' sebelum dan sesudah string 'SQL Like' setelah operator Like. Hasil Output SQL Statement Diatas bisa seperti dibawah ini :




JudulBuku

-----------------------------

Using SQL Like Operator

Penggunaan Operator SQL Like



Karakter '?' (di Microsoft Access) atau karakter '_' artinya adalah satu karakter. Sebagai contoh anda ingin menampilkan data buku yang karakter ke dua dalam judulnya adalah huruf 'S' anda bisa menuliskan Statement SQL nya sebagai berikut :


Contoh penggunaan karakter _ dalam statement SQL :



Select JudulBuku from Buku

Where JudulBuku like '_S%'


Contoh penggunaan karakter ? dalam statement SQL untuk Microsoft Access :



Select JudulBuku from Buku

Where JudulBuku like '?S*'



Perlu diperhatikan bahwa beberapa Database Software (RDBMS) seperti Oracle menerapkan aturan Case Sensitive (memperhatikan penggunaan huruf besar dan huruf kecil) terhadap nilai-nilai yang ada didalam table-nya jadi jika anda menyimpan data didalam table dengan nilai 'USING SQL LIKE OPERATOR' maka ketika mencarinya dengan menggunakan pola '%SQL Like%' dalam operator SQL Like maka query tidak akan menghasilkan kembalian judul buku seperti 'USING SQL LIKE OPERATOR
', 'PENGGUNAAN OPERATOR SQL LIKE' dan sebagainya, karena data disimpan dalam huruf kapital sedangkan string pencarian anda adalah huruf besar dan huruf kecil ('SQL Like'). Mudah-mudahan artikel ini bisa menambah wawasan anda tentang Syntax dasar statement SQL.

Spelling Money Amount using Visual Basic Script

Saturday, March 22, 2008


Scroll To English Version

Membilang Nominal Uang Menggunakan Script Visual Basic



Untuk aplikasi yang menangani penjumlahan uang seperti aplikasi accounting, aplikasi penjualan dan sebagainya akan lebih menarik jika ditambahkan 'fitur' untuk mengeja nilai nominal uang misalnya untuk jumlah uang 1525500 aplikasi akan mengeja-nya sebagai berikut :



"Satu Juta Lima Ratus Dua Puluh Lima Ribu Lima Ratus"


Disini aku akan sharing sebuah script visual basic untuk melakukan hal tersebut. biar lebih jelas aku akan buat sebuah aplikasi sederhana dengan Visual Basic, berikut ini langkah-langkahnya :




  • Masuk ke Visual Basic, buat sebuah project standar.EXE

  • tambahkan sebuah komponen CommandButton, sebuah TextBox dan beberapa label, sehingga tampilannya seperti gambar berikut ini :





  • buat sebuah function dengan nama SpellAmount(), sebagai berikut :




Function SpellAmount(Amount As Currency) As String

Dim satuan As Variant

satuan = Array("", "Satu", "Dua", "Tiga", "Empat", "Lima", "Enam", "Tujuh", "Delapan", "Sembilan", "Sepuluh", "Sebelas")



Select Case Amount

Case 0 To 11

SpellAmount = " " & satuan(Fix(Amount))

Case 12 To 19

SpellAmount = SpellAmount(Amount Mod 10) & " Belas"

Case 20 To 99

SpellAmount = SpellAmount(Fix(Amount / 10)) & " Puluh" + SpellAmount(Amount Mod 10)



Case 100 To 199

SpellAmount = " Seratus" & SpellAmount(Amount - 100)

Case 200 To 999

SpellAmount = SpellAmount(Fix(Amount / 100)) & " Ratus" + SpellAmount(Amount Mod 100)



Case 1000 To 1999

SpellAmount = " Seribu" & SpellAmount(Amount - 1000)

Case 2000 To 999999

SpellAmount = SpellAmount(Fix(Amount / 1000)) & " Ribu" + SpellAmount(Amount Mod 1000)



Case 1000000 To 999999999

SpellAmount = SpellAmount(Fix(Amount / 1000000)) & " Juta" & SpellAmount(Amount Mod 1000000)

Case 1000000000 To 2147482999

SpellAmount = SpellAmount(Fix(Amount / 1000000000)) & " Milyar" + SpellAmount(Amount Mod 1000000000)


Case Else

SpellAmount = "Tidak dapat menghitung"

End Select



End Function



  • dobel klik komponen CommandButton, tambahkan kode perintah berikut :




Private Sub Command1_Click()

Dim value As Variant



value = SpellAmount(Text1.Text)

Label1.Caption = "Terbilang : " & Chr(10) & value



End Sub


Tampilan pada saat Runtime :





Function ini akan mengeja/membilang nilai nominal dengan range 1 - 2147482999. Untuk kondisi Amount > 2147482999, aku gunakan string kalimat "Tidak dapat membilang" karena jika tidak aplikasi akan mengalami Error karena terjadi overflow (nilai diluar jangkauan dari tipe data yang digunakan). tetapi walaupun begitu menurutku script ini sudah cukup untuk disertakan dalam sebuah aplikasi penjualan. semoga bermanfaat.













Scroll To Indonesia Version


For application that handle arithmetic operation (for money) such as accounting application, sales application would be more interesting if you add 'Spelling feature' for Amount of money. For example for the amounts of moneys of 1525500 the application will spell it as follows :




"One Million Five Hundreds Twenty Five Thousands Five Hundreds"



Here I will share a visual basic script to do that. to be clearer I will make a simple application with Visual Basic, following is the steps :




  • Make a new Standar.EXE Project in Visual Basic

  • Add a CommandButton, TextBox and some label, so the appearance will be like this :






  • Create a function SpellAmount(), as follows :



Function SpellAmount(Amount As Currency) As String

Dim unit As Variant

unit = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", _

"Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", _

"Sixteen", "Seventeen", "Eighteen", "Nineteen", "Twenty")



Select Case Amount


Case 0 To 20

SpellAmount = " " & unit(Fix(Amount))



Case 21 To 29

SpellAmount = " Twenty" & SpellAmount(Amount Mod 10)



Case 30 To 30

SpellAmount = " Thirty"



Case 31 To 39

SpellAmount = " Thirty" & SpellAmount(Amount Mod 10)



Case 40 To 49

SpellAmount = " Fourty" & SpellAmount(Amount Mod 10)



Case 50 To 50

SpellAmount = " Fifty"



Case 51 To 59

SpellAmount = " Fifty" & SpellAmount(Amount Mod 10)


Case 60 To 79

SpellAmount = SpellAmount(Fix(Amount / 10)) & "ty" & SpellAmount(Amount Mod 10)



Case 80 To 89

SpellAmount = SpellAmount(Fix(Amount / 10)) & "y" & SpellAmount(Amount Mod 10)



Case 90 To 99

SpellAmount = SpellAmount(Fix(Amount / 10)) & "ty" & SpellAmount(Amount Mod 10)




Case 100 To 999

SpellAmount = SpellAmount(Fix(Amount / 100)) & " Hundreds" & SpellAmount(Amount Mod 100)



Case 1000 To 999999

SpellAmount = SpellAmount(Fix(Amount / 1000)) & " Thousand" & SpellAmount(Amount Mod 1000)



Case 1000000 To 999999999

SpellAmount = SpellAmount(Fix(Amount / 1000000)) & " Million" & SpellAmount(Amount Mod 1000000)



Case 1000000000 To 2147482999

SpellAmount = SpellAmount(Fix(Amount / 1000000000)) & " Billion" & SpellAmount(Amount Mod 1000000000)



Case Else

SpellAmount = "Cannot Spelling"

End Select



End Function



  • Double Click on CommandButton component, add the following code :




Private Sub Command1_Click()

Dim value As Variant



value = SpellAmount(Text1.Text)

Label1.Caption = "Amount : " & Chr(10) & value



End Sub


Runtime Screenshot :





This function will spell the value within range 1 - 2147482999. For the condition of Amount more than 2147482999, i use "Cannot Spelling" string
for the return of the function because if i don't use it, the application will experience an
overflow Error (value out of reach). Although it was so,  but i think this
script is powerfull enough to use in a sales application software or accounting
aplication.


SQL: Summed Up Null Values in Oracle and SQL Server 2000

Sunday, March 16, 2008


Jika anda sering menggunakan SQL statement pasti anda pernah melakukan operasi perhitungan matematika terhadap field-field dalam table anda. tapi pernahkah anda bingung mendapatkan perhitungan yang tidak sesuai terhadap field yang bernilai NOT NULL dengan field yang bernilai NULL. misalnya field A bernilai 4 dan field B bernilai NULL, jika anda lakukan penjulahan dengan SUM(fieldA+fieldB) maka hasilnya akan NULL. untuk itu anda perlu menggunakan sebuah fungsi untuk menggantikan nilai NULL tadi dengan sebuah bilangan/angka agar bisa dilakukan operasi matematika terhadap field yang bernilai null tadi.




untuk tutorial ini saya menggunakan table di database Oracle dengan nama EMP yang ada di schema SCOTT dan saya transfer table ini ke database SQL Server jadi saya menggunakan table yang mempunyai data yang sama. meskipun begitu saya juga akan memberikan contoh untuk anda yang menggunakan Microsoft SQL Server 2000 sebagai Database Software anda. jika anda belum tahu struktur dari table EMP, ini dia strukturnya :



EMPNO NUMBER(4) NOT NULL,

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(4),

HIREDATE DATE,

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER(2)



dan ini datanya :





Saya akan menghitung Salary + Commision masing-masing pegawai dengan SQL Statement berikut ini :




SELECT EMPNO, ENAME, JOB,

SAL, COMM, SUM(SAL+COMM) TOTAL_SALARY

FROM EMP

GROUP BY EMPNO, ENAME, JOB,

SAL, COMM




SQL Output :



Oracle without NVL

Maka salary pegawai yang tidak mempunyai komisi (komisi = NULL) tidak akan terhitung. untuk itu anda perlu menggunakan fungsi NVL() di Oracle atau fungsi ISNULL() jika anda menggunakan Database Software Microsoft SQL Server.



Syntax fungsi NVL() di Oracle :

NVL(input_value,replace_value_if_null)

Syntax fungsi ISNULL() di SQL Server 2000 :
ISNULL (input_value , replace_value_if_null)




fungsi NVL() atau fungsi ISNULL() akan menggantikan nilai NULL dengan nilai lainnya yang kita definiskan pada parameter kedua (replace_value_if_null) dengan catatan input_value dan replace_value_if_null mempunyai tipe data yang sama. anda akan mendapatkan pesan error jika anda menggunakan tipe data yang berbeda untuk dua parameter tersebut. sekarang saya akan merubah SQL Statement nya dengan menyertakan fungsi NVL() dan fungsi ISNULL() :




Dengan Fungsi NVL () :


SELECT EMPNO, ENAME, JOB, SAL, COMM,

SUM(nvl(SAL,0)+nvl(COMM,0)) TOTAL_SALARY

FROM EMP

GROUP BY EMPNO, ENAME, JOB, SAL, COMM





SQL Output :



Oracle with NVL

Dengan Fungsi ISNULL () :


SELECT EMPNO, ENAME, JOB, SAL, COMM,

SUM(ISNULL(SAL,0)+ISNULL(COMM,0)) TOTAL_SALARY

FROM EMP

GROUP BY EMPNO, ENAME, JOB, SAL, COMM





SQL Output :


SQL Server With ISNULL


dengan SQL Statement diatas maka pegawai yang tidak mempunyai komisi pun dapat terhitung salary-nya. Semoga bermanfaat.

Oracle SQL: Encrypt Strings using TRANSLATE Function


Scroll To Indonesia Version



If you are using Oracle as your Database Software to manage your datas, did you know that Oracle have a function to do simple string encryption. Although it was simple but the result is prety powerfull to encrypt any input string. in fact it can be used to encrypt any number data types. below i will give you an example how to encrypt a password that have a string data type and stored in a table called LOGIN that contain users login records to login to the application that you made for example. this table have the following structure :



USERID NUMBER,

USERNAME VARCHAR2(30),

PASSWORD VARCHAR2(30),

PROFILE VARCHAR2(15)



Syntax :
TRANSLATE(input_string,pattern_string1,pattern_string2)




Using TRANSLATE Function in SQL Insert Statement


for example you have 'Database Administrator' as your application login password. you can change the SQL statement for validate, inserting, or updating users login data in your application source code or you can add this SQL Statement to your application source code to encrypt the password before you store it on your application login table. this is how to use TRANSLATE function in the SQL Insert Statement :





INSERT INTO login

VALUES(

111,'ADMINISTRATOR',

TRANSLATE(

'Database Administrator',

'ZXCVBNMLKJHGFDSAQWERTYUIOP0987654321 zxcvbnmlkjhgfdsaqwertyuiop',

'~!@#$%^&*()_+|+_)(*&^*%#@!%%*&&)(*&!%#@$#_ )@*$^@%@$#!@#!@#_)(*'),'DBA')



Each character in string 'Database Administrator' will be looked in pattern_string1
then this function will look for character in pattern_string2 which it's position is equal to the character in pattern_string1. in the example of SQL Statement above, character 'D' in string 'Database Administrator' have 14th position at pattern_string1 so the function will look for the character that have the 14th position at pattern_string2 so the result is character '|', and so on.



Using TRANSLATE Function in SQL Select Statement


This is used to retrieving data from LOGIN table. in this example is for checking user login data.





SELECT * FROM login

WHERE username='ADMINISTRATOR'

AND PASSWORD = TRANSLATE(

'Database Administrator',

'ZXCVBNMLKJHGFDSAQWERTYUIOP0987654321 zxcvbnmlkjhgfdsaqwertyuiop',

'~!@#$%^&*()_+|+_)(*&^*%#@!%%*&&)(*&!%#@$#_ )@*$^@%@$#!@#!@#_)(*')



SQL Output :




you must use the same pattern_string as you use in SQL insert statement before.



Using TRANSLATE Function in SQL Update Statement


You may want to update your login password, for that purpose you need to update the record using SQL update statement. this is how to use TRANSLATE function in the SQL Update Statement :




UPDATE login SET password=TRANSLATE(

'oracle dba',

'ZXCVBNMLKJHGFDSAQWERTYUIOP0987654321 zxcvbnmlkjhgfdsaqwertyuiop',

'~!@#$%^&*()_+|+_)(*&^*%#@!%%*&&)(*&!%#@$#_ )@*$^@%@$#!@#!@#_)(*')

WHERE userid=111



Result :





this way anyone who accidentally see your login table will never know what your really password is. there is no harm to be wary. i hope this article will be usefull.













Scroll To English Version


Oracle SQL: Enkripsi Strings menggunakan Fungsi TRANSLATE


Jika anda menggunakan Oracle sebagai Database Software untuk me-manage data-data anda, tahukah anda bahwa oracle mempunyai sebuah function untuk melakukan enkripsi string sederhana. meskipun begitu function ini cukup powerfull dalam meng-encrypt sebuah inputan string bahkan bisa juga digunakan untuk meng-encrypt inputan yang mempunyai tipe data number. dibawah ini adalah contoh sederhana untuk meng-encrypt password bertipe string yang akan disimpan di dalam sebuah table bernama LOGIN yang berisi data login user ke aplikasi. misalnya table ini mempunyai struktur sebagai berikut :




USERID NUMBER,

USERNAME VARCHAR2(30),

PASSWORD VARCHAR2(30),

PROFILE VARCHAR2(15)



Syntax :
TRANSLATE(input_string,pattern_string1,pattern_string2)



Using TRANSLATE Function in SQL Insert Statement


Misalnya password anda adalah 'Database Administrator'. anda bisa merubah atau menambahkan SQL Statement berikut ke source code aplikasi anda untuk melakukan validasi login user, insert user baru, atau update password user lama. berikut ini contoh penggunaan fungsi TRANSLATE di dalam SQL Insert Statement :




INSERT INTO login

VALUES(

111,'ADMINISTRATOR',

TRANSLATE(

'Database Administrator',

'ZXCVBNMLKJHGFDSAQWERTYUIOP0987654321 zxcvbnmlkjhgfdsaqwertyuiop',

'~!@#$%^&*()_+|+_)(*&^*%#@!%%*&&)(*&!%#@$#_ )@*$^@%@$#!@#!@#_)(*'),'DBA')



setiap karakter dalam string 'Database Administrator' akan dicari pada pattern_string1 kemudian fungsi ini akan mencari karakter pada pattern_string2 yang posisi/letak karakternya sama dengan karakter pada pattern_string1. pada contoh SQL Statement diatas karakter 'D' pada string 'Database Administrator' mempunyai urutan/posisi ke-14 pada pattern_string1 kemudian fungsi akan mencari karakter yang mempunyai posisi ke-14 pada pattern_string2 sehingga hasilnya adalah karakter '|', dan seterusnya.



Using TRANSLATE Function in SQL Select Statement


sql statement ini digunakan untuk mendapatkan data dari table LOGIN. dalam contoh ini adalah untuk memeriksa data login user.




SELECT * FROM login

WHERE username='ADMINISTRATOR'

AND PASSWORD = TRANSLATE(

'Database Administrator',

'ZXCVBNMLKJHGFDSAQWERTYUIOP0987654321 zxcvbnmlkjhgfdsaqwertyuiop',

'~!@#$%^&*()_+|+_)(*&^*%#@!%%*&&)(*&!%#@$#_ )@*$^@%@$#!@#!@#_)(*')



SQL Output :





pattern_string harus sama dengan pattern_string yang anda gunakan ketika anda melakukan insert data dengan SQL Insert.



Using TRANSLATE Function in SQL Update Statement


Misalnya anda akan merubah password login anda, untuk itu dibutuhkan SQL Update Statement. berikut ini contoh penggunaan fungsi TRANSLATE di dalam SQL Update Statement :




UPDATE login SET password=TRANSLATE(

'oracle dba',

'ZXCVBNMLKJHGFDSAQWERTYUIOP0987654321 zxcvbnmlkjhgfdsaqwertyuiop',

'~!@#$%^&*()_+|+_)(*&^*%#@!%%*&&)(*&!%#@$#_ )@*$^@%@$#!@#!@#_)(*')

WHERE userid=111


Result :




Dengan cara melakukan enkripsi seperti ini, orang lain yang secara tidak sengaja melihat table login anda, dia tidak akan tahu password anda sebenarnya. tidak ada salahnya lho untuk tetap berhati-hati. semoga artikel saya ini bermanfaat.

Delphi: Data Manipulation Using SQL Statement

Tuesday, March 11, 2008

Scroll to English Version


Manipulasi Data Menggunakan SQL Statement di Delphi


Aku menggunakan sebuah table di Microsoft Access dengan nama poducts pada database tutorial.mdb dan disimpan di C:\DelphiTutorial\sqldml. tutorial ini menjelaskan bagaimana cara melakukan manipulasi data menggunakan sql statement dengan komponen ADOCommand di delphi 7. hal ini mencakup statement sql select,sql insert, sql update dan sql delete. berikut ini struktur dari table products :




productid
text(10),
description text(50),
price number(long integer)




  • Masuk ke Delphi 7.
  • Buat sebuah project baru, simpan di C:\DelphiTutorial\sqldml dengan nama
    sqldml.dpr.
  • tambahkan sebuah komponen ADOConnection (ada di tab ADO) set property
    ConnectionStringnya menjadi :




Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=tutorial.mdb;Persist Security Info=False





  • tambahkan sebuah komponen ADOCommand (ada di tab ADO) set property Connectionnya menjadi ADOConnection1.



SQL Insert statement



Pertama-tama saya akan mengisi table products dengan beberapa record agar perubahan data didalam table karena manipulasi data dapat terlihat.




  • tambahkan tiga buah komponen Edit masing-masing beri nama eproductid, edescription, dan eprice dan sebuah komponen Button biarkan komponen button dengan nama defaultnya.

  • set property Caption Button1 menjadi 'Save'.

  • dobel klik button1, kemudian tambahkan kode perintah berikut pada code editor delphi :






with ADOCommand1 do begin

CommandText := 'insert into products values(:a,:b,:c)';

Parameters[0].Value := eproductid.Text;

Parameters[1].Value := edescription.Text;

Parameters[2].Value := eprice.Text;

Execute;

end;


application.messagebox('Data berhasil disimpan','Informasi',mb_ok);




  • run applikasi dengan menekan F9, kemudian isikan beberapa record ke dalam table.




SQL Select Statement


Sebenarnya fungsi utama ADOCommand ini adalah untuk mengeksekusi statement sql yang tidak mengembalikan hasil (result sets) seperti statement SQL insert,SQL update, SQL delete dan statement DDL (Data Definition language) misalnya statement Create Table, Create Procedure, dll. sedangkan contoh statement sql yang mengembalikan hasil (result sets) adalah statement sql select.



kita akan melakukan pencarian record pada table products berdasarkan field description. Untuk menerima hasil kembalian (result sets) dari statement sql ini kita akan menggunakan komponen ADODataSet.




  • tambahkan sebuah komponen edit, beri nama esearch dan sebuah komponen button dan beri nama bsearch.

  • Ubah property caption dari button bsearch menjadi 'Search'.

  • dobel klik tombol bsearch, kemudian tambahkan kode perintah berikut pada code editor delphi :





with ADOCommand1 do begin

CommandText := 'select * from products where description = :desc';

Parameters[0].Value := ecari.Text;

ADODataset1.Recordset := execute;

end;

MessageDlg('Ditemukan '+ inttostr(ADODataset1.recordcount) + ' Record(s)', mtInformation, [mbOk], 0);



Coba lakukan pencarian data barang yang telah dientri sebelumnya dengan cara menginputkan deskripsi barang tersebut pada kotak edit esearch dan tekan tombol Search.



SQL Update Statement


Idenya begini : saya akan menambahkan fitur pencarian sebelum input data dan menambahkan fitur update pada tombol button1.




  • klik edit eproductid, pada object inspector klik tab Events, cari event OnKeyDown, dobel klik, kemudian tambahkan kode perintah berikut ini :






if key = vk_return then

begin

with ADOCommand1 do begin

CommandText := 'select * from products where productid = :pid';

Parameters[0].Value := eproductid.Text;

ADODataset1.Recordset := execute;

end;



if ADODataset1.RecordCount > 0 then


if messagedlg('Data produk dengan id '''+eproductid.Text

+''' sudah ada'+chr(10)

+'apakah anda akan merubah data
tersebut',mtconfirmation,[mbok,mbcancel],1) = mrok then

begin

edescription.Text :=
ADODataset1.FieldValues['description'];

eprice.Text := ADODataset1.FieldValues['price'];

edescription.SetFocus;


button1.Caption := 'Update';

{ bdelete.Enabled := true; }

end

else

begin

{ button1.Caption := 'Save'; }

eproductid.SelectAll;

exit;

end



else if ADODataset1.RecordCount < 1 then

begin

{ bdelete.Enabled := false; }

button1.Caption := 'Save';

edescription.clear;

eprice.clear;

end;



edescription.SetFocus;


end;



Pada listing kode diatas ada perintah untuk merubah caption dari tombol button1 yaitu kode perintah dengan teks yang saya tebalkan. caption tersebut akan menetukan mode manipulasi data yang akan dilakukan. biar lebih jelas ubah dulu listing kode perintah pada button1 menjadi seperti dibawah ini :





if button1.Caption='Save' then

begin

with ADOCommand1 do begin

CommandText := 'insert into products values(:a,:b,:c)';


Parameters[0].Value := eproductid.Text;

Parameters[1].Value := edescription.Text;

Parameters[2].Value := eprice.Text;

Execute;

end;

application.messagebox('Data berhasil disimpan','informasi',mb_ok);


end

else if button1.Caption='Update' then

with ADOCommand1 do begin

CommandText := 'update products set description = :a, price = :b where productid = :c';

Parameters[0].Value := edescription.Text;

Parameters[1].Value := eprice.Text;

Parameters[2].Value := eproductid.Text;


execute;

application.messagebox('Perubahan berhasil disimpan','informasi',mb_ok);

end;



eproductid.Clear;

edescription.Clear;

eprice.Clear;


eproductid.SetFocus;




run aplikasi, coba masukkan kode produk yang telah disimpan sebelumnya, maka akan ditampilkan messagebox yang berisi konfirmasi untuk update record atau tidak.



SQL Delete Statement



  • tambahkan sebuah komponen button, beri nama bdelete dan ubah Captionnya menjadi 'Delete'.


  • ubah property Enable button bdelete menjadi False

  • dobel klik tombol bdelete, kemudian tambahkan kode perintah berikut pada code editor delphi :






if messagedlg('Hapus data barang '''+eproductid.Text

+'''',mtconfirmation,[mbok,mbcancel],1) = mrok then


begin

with ADOCommand1 do begin

CommandText := 'delete from products '+

'where productid = :pid';

Parameters[0].Value := eproductid.Text;

execute;

end;

eproductid.Clear;

edescription.Clear;


eprice.Clear;

button1.Caption := 'Save';

bdelete.Enabled := false;

eproductid.SetFocus;

end;



setelah anda menginputkan kode ini pada code editor delphi, selanjutnya anda hilangkan semua tanda '{' dan '}' (blok komentar) pada listing kode program Event OnKeyDown edit eproductid agar tombol bdelete bisa aktif pada saat mode update record dan non aktif pada saat mode insert record. Semoga bermanfaat.












Scroll to Indonesian Version

I use a table in Microsoft Access called products in a file called tutorial.mdb and saved in c:\DelphiTutorial\sqldml. this tutorial is about how to manipulate data using sql statement with ADOCommand component in Delphi 7. this is included sql select statement, sql insert statement, sql update statement and sql delete statement. lets get started.



the following is data structure from products table :




productid
text(10),
description text(50),
price number(long integer)




  • Run Delphi.
  • Create new project then save as sqldmp.dpr in c:\DelphiTutorial\sqldml.
  • Add an ADOConnection component, set its ConnectionString Property to :






Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=tutorial.mdb;Persist Security Info=False





  • Add an ADOCommand component, set its Connection property to ADOConnection1.


SQL Insert statement



First of all i will fill the table with some data so we can see the changes that sql statement do to the table's data.




  • Add three Edit component eproductid, edescription, and eprice and a Button component, Button1.


  • Change Button1 Caption property to 'Save'.

  • double klick Button1, then add the following code to the Delphi's code editor :





with ADOCommand1 do begin

CommandText := 'insert into products values(:a,:b,:c)';

Parameters[0].Value := eproductid.Text;

Parameters[1].Value := edescription.Text;

Parameters[2].Value := eprice.Text;


Execute;

end;

application.messagebox('Data saved successfully','Information',mb_ok);



  • Run application, fill the table with some data by pressing Button1.



SQL Select Statement


Actually the main purpose of ADOCommand component is to execute sql statements that don't return a result sets including SQL Insert statement, SQL Update statement, SQL delete statement and DDL statement (Data Definition Language) like Create Table, Create Procedure, etc.



To retrieve data using sql select statement i use an ADODataSet component along with ADOCommand component.




  • Add an Edit Component, esearch and a Button component, bsearch.

  • Change the Caption of bsearch button to 'Search'.

  • Double click bsearch, then add the following code to the Delphi's code editor :




with ADOCommand1 do begin

CommandText := 'select * from products where description = :desc';

Parameters[0].Value := ecari.Text;

ADODataset1.Recordset := execute;

end;

MessageDlg('Found '+ inttostr(ADODataset1.recordcount) + ' Record(s)', mtInformation, [mbOk], 0);



After writing the code above, run application then try searching specific record in the Products table by entering product description in the Edit text box esearch then press 'Search' button.




SQL Update Statement


the idea is : i will add searching record feature before entering the data and add updating feature on button1(Save button) button.




  • klick eproductid, in the object inspector klick Events tab, look for OnKeyDown event, double klick, then add the following code :





if key = vk_return then

begin

with ADOCommand1 do begin

CommandText := 'select * from products where productid = :pid';

Parameters[0].Value := eproductid.Text;

ADODataset1.Recordset := execute;

end;



if ADODataset1.RecordCount > 0 then

if messagedlg('Product with id '''+eproductid.Text

+''' already exist'+chr(10)

+'do you want to change the data
',mtconfirmation,[mbok,mbcancel],1) = mrok then

begin

edescription.Text :=
ADODataset1.FieldValues['description'];


eprice.Text := ADODataset1.FieldValues['price'];

edescription.SetFocus;

button1.Caption := 'Update';

{ bdelete.Enabled := true; }

end

else

begin

{ button1.Caption := 'Save'; }

eproductid.SelectAll;


exit;

end


else if ADODataset1.RecordCount < 1 then

begin

{ bdelete.Enabled := false; }

button1.Caption := 'Save';

edescription.clear;

eprice.clear;


end;



edescription.SetFocus;

end;



in the code listing above there are a command (the bold one) to change the caption of button1. that caption will determine manipulation mode to the data that will be perform. for more clearly, change the code listing in the button1 as follow :





if button1.Caption='Save' then

begin


with ADOCommand1 do begin

CommandText := 'insert into products values(:a,:b,:c)';

Parameters[0].Value := eproductid.Text;

Parameters[1].Value := edescription.Text;

Parameters[2].Value := eprice.Text;

Execute;


end;

application.messagebox('Data saved successfully','informasi',mb_ok);

end

else if button1.Caption='Update' then

with ADOCommand1 do begin

CommandText := 'update products set description = :a, price = :b where productid = :c';

Parameters[0].Value := edescription.Text;


Parameters[1].Value := eprice.Text;

Parameters[2].Value := eproductid.Text;

execute;

application.messagebox('Changes saved successfully','informasi',mb_ok);

end;



eproductid.Clear;


edescription.Clear;

eprice.Clear;

eproductid.SetFocus;




run application, try to input product id that have entered earlier, then a message box will appear displaying confirmation to update the record or no.



SQL Delete Statement




  • Add a button, bdelete and change the Caption to 'Delete'.

  • change Enable to False

  • double klick bdelete button, then add the following code to the Delphi's code editor :






if messagedlg('Delete products '''+eproductid.Text

+''' data',mtconfirmation,[mbok,mbcancel],1) = mrok then

begin

with ADOCommand1 do begin

CommandText := 'delete from products '+

'where productid = :pid';

Parameters[0].Value := eproductid.Text;

execute;


end;

eproductid.Clear;

edescription.Clear;

eprice.Clear;

button1.Caption := 'Save';

bdelete.Enabled := false;

eproductid.SetFocus;

end;



After writing that code above in delphi's code editor, next, remove
the '{' and '}' sign (comment block)
in OnKeyDown event eproductid code listing in order to activate bdelete button on update record mode and deactivate it on insert record mode.


SQL : Using Column Alias to Clarify Field Header

Friday, March 7, 2008

Scroll to English Version


SQL: Menggunakan Column Alias untuk memperjelas Field Header


Jika table anda mempunyai field yang kurang deskripstip anda mungkin akan
mempertimbangkan untuk menggunakan tips yang akan saya berikan ini. sebenarnya tips ini untuk anda yang sering atau terbiasa menggunakan sql editor/ atau DBA (Database Administration) Tools untuk melakukan query data. Misalnya anda mempunyai sebuah table bernama Persons dengan struktur sebagai berikut (saya gunakan struktur di Oracle) :




FNAME VARCHAR2(15),

LNAME VARCHAR2(15),

GENDER CHAR(1),

BIRTHDATE DATE



dan table tersebut mempunyai data sebagai berikut :




FNAME LNAME GENDER BIRTHDATE
--------------- --------------- ---------- ---------
KURT COBAIN M 20-FEB-67
IGOR CAVALERA M 04-SEP-70
AMY LEE F 13-DEC-81
COREY TAYLOR M 18-DEC-73


coba anda gunakan SQL statement dibawah ini :





select fname,lname,gender,birthdate from persons



maka hasilnya adalah :




SQL> select fname,lname,gender,birthdate from persons;

FNAME LNAME GENDER BIRTHDATE
--------------- --------------- ---------- ---------
KURT COBAIN M 20-FEB-67
IGOR CAVALERA M 04-SEP-70
AMY LEE F 13-DEC-81
COREY TAYLOR M 18-DEC-73

disitu terlihat bahwa kolom FNAME dan LNAME kurang deskriptip. agar lebih jelas dalam output query anda bisa menggunakan column alias. berikut ini contoh syntax statement sql di beberapa database SQL.



SQL statement in Oracle, MySQL, Paradox




select fname "First Name", lname "Last Name",gender,birthdate

from persons



output query-nya :





SQL> select fname "First Name",
2 lname "Last Name", gender, birthdate
3 from persons;

First Name Last Name GENDER BIRTHDATE
--------------- --------------- ---------- ---------
KURT COBAIN M 20-FEB-67
IGOR CAVALERA M 04-SEP-70
AMY LEE F 13-DEC-81
COREY TAYLOR M 18-DEC-73




SQL statement in Microsoft Access and Microsoft SQL Server




select fname as [First Name], lname as [Last Name],gender,birthdate

from persons



Maka output query-nya akan sama dengan output sql yang saya gunakan di oracle, MySQL dan Paradox sebelumnya.













Scroll to Indonesian Version

If you have tables that the field are less descriptive, you may want to consider this tips that i will explain below. actually this tips is for you who often use sql editor or DBA (Database Administration) Tools to do queries. for example you have a table called Persons with the following structure (i use a structure in oracle database) :





FNAME VARCHAR2(15),

LNAME VARCHAR2(15),

GENDER CHAR(1),

BIRTHDATE DATE



and this table have the following data :



FNAME LNAME GENDER BIRTHDATE
--------------- --------------- ---------- ---------
KURT COBAIN M 20-FEB-67
IGOR CAVALERA M 04-SEP-70
AMY LEE F 13-DEC-81
COREY TAYLOR M 18-DEC-73


try use this SQL Statement below :




select fname,lname,gender,birthdate from persons



The SQL statement result output are :



SQL> select fname,lname,
2 gender,birthdate from persons;

FNAME LNAME GENDER BIRTHDATE
--------------- --------------- ---------- ---------
KURT COBAIN M 20-FEB-67
IGOR CAVALERA M 04-SEP-70
AMY LEE F 13-DEC-81
COREY TAYLOR M 18-DEC-73


in the sql statement result output above we can see that FNAME and LNAME column are less descriptive. for more clearly column header in the query output you can use a column aliases. the following is an example of sql statement syntax for using column aliases in several sql database engine.



SQL statement in Oracle, MySQL, Paradox




select fname "First Name", lname "Last Name",

gender,birthdate from persons



The SQL statement result output :





SQL> select fname "First Name",

2 lname "Last Name", gender, birthdate
3 from persons;

First Name Last Name GENDER BIRTHDATE
--------------- --------------- ---------- ---------
KURT COBAIN M 20-FEB-67
IGOR CAVALERA M 04-SEP-70
AMY LEE F 13-DEC-81
COREY TAYLOR M 18-DEC-73



SQL statement syntax in Microsoft Access and Microsoft SQL Server




select fname as [First Name], lname as [Last Name],gender,birthdate

from persons



sql statement above will bring the same result output as sql statement that we use in Oracle, MySQL and paradox previously.

Recent Comments

Tags Cloud

Blogumulus by Roy Tanck and Amanda Fazani