ACCESS FEED: Re: [belajar-access] Membuat Program Multiuser Dengan Front-End Microsoft Access dan Back-End Microsoft SQL Server (Bagian 3)

Bang Sofyan,

File PDF nya aja bang sekalian...

:-)

dibukukan...

Pada 30 Mei 2013 11.50, Sofyan Efendi <sofyanefendi@gmail.com> menulis:

Membuat Program Multiuser Dengan Front-End Microsoft Access dan Back-End Microsoft SQL Server (Bagian 3)

STORED PROCEDURES

SQL Server ibarat lemari baju, kemudian stored procedures ibarat robot canggih yang berdiri disebelahnya. Robot canggih ini dapat diberi perintah secara remote dari jarak yang sangat jauh, yakni perintah apa saja yang berkaitan dengan penyimpanan pakaian kedalam lemari baju. Semua pakaian berantakan yang terdapat didepan lemari pakaian, dapat ia susun secara cepat kemudian disimpan kedalam lemari. Pakaian anak ke rak anak, baju dan sarung ke rak yang satu, dsb. Dia dapat menyusun dengan sangat cepat, karena dia BERDIRI DEKAT DISAMPING LEMARI! Pemilik lemari cukup me-remote dari jarak yang jauh, memberi perintah, sehingga dia bisa bekerja secara otomatis. Tanpa diremote dan diberi perintah, maka robot canggih ini hanya diam berdiri disamping lemari.

Stored Procedures adalah "VBA" nya SQL Server. Ia adalah "ACTION QUERY" nya SQL Server. Dengan demikian, setiap trigger adalah pasti stored procedures, namun tidak semua stored procedures adalah trigger.

Maka jika sahabat hendak membuat program multiuser dengan front-end Microsoft Access dan back-end Microsoft SQL Server, sebaiknya (sangat dianjurkan) mempelajari dengan detail stored procedures ini. Memang dia tidak digunakan pun tidak apa-apa. Program tetap bisa jalan dengan baik. Namun masalahnya, sahabat harus mengirim kurir dari jarak yang jauh, hanya untuk menyimpan baju anak ke rak anak. Karena robot canggih tidak diberi perintah, sehingga sahabat memboroskan waktu sedikit untuk pelaksanaan perintah yang sebenarnya bisa dilakukan oleh robot yang sedang berdiri disebelah lemari baju.

Kemudian, bahasa yang dapat dimengerti oleh "robot" ini hanya bahasa "Transact SQL", sehingga bila sahabat hanya mengerti bahasa Indonesia dan Inggris, belumlah mencukupi. Dia tidak dapat memahami perintah selain Transact SQL. Begitu pula komunikasi dengan "lemari", sahabat harus menggunakan bahasa Transact SQL. Ia adalah bahasa sehari-harinya SQL Server, sehingga seluruh komponen yang berada didalam SQL Server, baik view, trigger, stored procedures, job, dll haruslah menggunakan bahasa Transact SQL.

MEMBUAT STORED PROCEDURES SEDERHANA

Marilah kita belajar dasar-dasar pembuatan stored procedures. Misal, kita hendak output data address pada table Person dalam database AdventureWorks. Maka kode T-SQL nya adalah sbb:

SELECT * FROM AdventureWorks.Person.Address

Maka buatlah procedure baru pada dalam database tsb, secara default tampilan awalnya adalah sbb:

CREATE PROCEDURE [PROCEDURE NAME] AS

Modif kode T-SQL diatas, menjadi:
CREATE PROCEDURE AmbilAlamat

AS
SELECT * FROM AdventureWorks.Person.Address
GO

Kemudian pada Query Analyzer, run dng mengetikan:
EXEC AmbilAlamat
--atau ketikan
AmbilAlamat

MEMBUAT STORED PROCEDURES DENGAN PARAMATER

Procedure diatas mengambil data alamat untuk seluruh kota, padahal saya ingin hanya kota tertentu saja yang di-output. Maka kita harus menambahkan parameter pada stored proceduresnya, menjadi:

CREATE PROCEDURE AmbilAlamat @City nvarchar(30)
AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City = @City
GO

Perhatikan kode pada baris pertama. Mengingatkan kita pada pembuatan suatu prrocedure didalam VBA, misalnya:

Public Function Alamat ( NamaKota As String ) As String

End

Sedangkan GO, mengingatkan kita pada kode "END" didalam VBA.

Ok, kita lupakan VBA, kembali ke Stored Procedures. Run stored procedures diatas, dengan mengetikan:
EXEC AmbilAlamat @City = 'New York'

Procedure diatas menghasilkan data alamat dengan kota hanya New York saja. Bagaimana bila hendak melihat data alamat dengan kota dengan diawali dari kata BAN, misal Bandung, Banten, Banjar, dll. Maka stored procedures diatas harus dimodif lagi, menjadi:

CREATE PROCEDURE AmbilAlamat @City nvarchar(30)
AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City LIKE @City + '%'
GO

Perhatikan pada clausa WHERE. Terdapat kata "LIKE" dan simbol '%'. Persis seperti Access yang menggunakan kriteria LIKE dan tanda bintang (*).

MEMBUAT DEFAULT VALUE PADA PARAMATER STORED PROCEDURES

Jika Sahabat menjalankan procedure diatas tanpa memberikan paramater, maka akan mengakibatkan error, yakni:

Msg 201, Level 16, State 4, Procedure AmbilNama, Line 0

Procedure or function 'AmbilNama' expects parameter '@City', which was not supplied.

Karena itu, sahabat harus memberikan default value NULL diawal deklarasi stored procedure, yakni:
CREATE PROCEDURE AmbilAlamat @City nvarchar(30) = NULL

AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City = ISNULL(@City,City)
GO

MEMBUAT STORED PROCEDURES DENGAN PARAMATER LEBIH DARI SATU (BANYAK/MULTIPLE PARAMETER)

Sebagaimana dalam VBA, kita bisa membuat:

Public Function Alamat ( NamaKota As String, NamaDesa As String ) As String

End

Maka kitapun bisa membuat stored procedure sbb:
CREATE PROCEDURE AmbilAlamat @City nvarchar(30) = NULL, @AddressLine1 nvarchar(60) = NULL

AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City = ISNULL(@City,City)
AND AddressLine1 LIKE '%' + ISNULL(@AddressLine1 ,AddressLine1) + '%'
GO

Untuk menjalankannya, sahabat bisa Run dengan berbagai alternatif, misal:
EXEC AmbilAlamat @City = 'Calgary'

--atau
EXEC AmbilAlamat @City = 'Calgary', @AddressLine1 = 'A'
--atau

EXEC AmbilAlamat @AddressLine1 = 'Acardia'
-- dsb...

MENGAMBIL OUTPUT SUATU STORED PROCEDURES BERPARAMATER

Barangkali sahabat pernah membuat procedure sbb didalam VBA:

Public Function Nilai()

      Nilai = 10

End

Maka kita bisa manfaatkan output nilai diatas kedalam procedure lainnya, misal:

Public Function Nilai2()

      Nilai2 = Nilai + 10

End

Begitu pula dengan stored procedure, kita bisa manfaatkan dengan syntax OUTPUT atau OUT. Misal:
CREATE PROCEDURE JmlhAlamat @City nvarchar(30), @AddressCount int OUTPUT

AS
SELECT @AddressCount = count(*)
FROM AdventureWorks.Person.Address
WHERE City = @City

Kemudian untuk mengambil nilai dari output AddressCount, bisa menggunakan stored procedure sbb:

DECLARE @AddressCount int
EXEC JmlhAlamat @City = 'Calgary', @AddressCount = @AddressCount OUTPUT
SELECT @AddressCount

Atau bisa juga dengan cara tanpa menuliskan nama parameternya, yakni sbb:
DECLARE @AddressCount int

EXEC JmlhAlamat 'Calgary', @AddressCount OUTPUT
SELECT @AddressCount

Sekedar info, DECLARE didalam VBA persis seperti DIM, misal:

Dim X As String

Sedangkan EXEC didalam VBA persis seperti CALL, misal:

Call NamaProcedure (NamaParameter)

ERROR HANDLE DI DALAM STORED PROCEDURES

Terkadang, cukup sulit mengetahui persis penyebab error didalam stored procedures. Alhamdulillah, pada SQL Server 2005, sudah disertakan fasilitas "coba-coba", dan menariknya, ditampilkan juga "pesan error" nya seandainya terdapat error. Hal ini disebut juga "Try Catch". Misalnya:

CREATE PROCEDURE uspTryCatchTest
AS
BEGIN TRY
    SELECT 1/0
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber
     ,ERROR_SEVERITY() AS ErrorSeverity

     ,ERROR_STATE() AS ErrorState
     ,ERROR_PROCEDURE() AS ErrorProcedure
     ,ERROR_LINE() AS ErrorLine
     ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

Persis seperti didalam VBA, terdapat On Error Goto MsgErr, dimana pada MsgErr, bisa kita tambahkan informasi errornya, misal Err.Number, Err.Description, dsb.

MEMBUAT KOMENTAR DI DALAM STORED PROCEDURES

Mungkin saat ini sahabat ingat, procedure ini digunakan untuk apa, kode ini untuk apa, dsb. Namun setelah berlalunya waktu, misal 1 tahun mendatang, bisa jadi sahabat lupa, apa fungsi dari stored procedures ini, kenapa menggunakan kode ini, dsb. Karena itu, buatlah komentar (comments) pada stored procedures yang sahabat buat. Pembuatan komentar ditandai dengan simbol "--" atau bisa juga dengan "/* */". Terdapat 3 macam variasi komentar (comments) yang dapat dibuat, yakni:

1.       Komentar Model Baris, misal:

-- Procedure ini digunakan untuk mengambil data alamat

-- pada suatu kota
CREATE PROCEDURE AmbilAlamat @City nvarchar(30)

AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City = @City
GO

2.       Sekumpulan Komentar, misal:

/*
Procedure ini digunakan
untuk mengambil data alamat

pada suatu kota
*/
CREATE PROCEDURE AmbilAlamat @City nvarchar(30)

AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City = @City
GO

3.       Komentar Model Baris dan Sekumpulan Komentar, misal:

/*
Procedure ini digunakan
untuk mengambil data alamat

pada suatu kota
*/
CREATE PROCEDURE AmbilAlamat @City nvarchar(30)

AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City = @City -- Parameter @City diisi ya nanti saat eksekusinya :)

GO

MINIMALISASI PENGGUNAAN LALU LINTAS NETWORK

Sebagaimana yang dijalaskan Microsoft MSDN pada situsnya di: http://msdn.microsoft.com/en-us/library/aa259204%28v=sql.80%29.aspx , untuk meminimalisasi penggunaan lalu lintas network didalam stored procedure, gunakan syntax "SET NOCOUNT ON", misal:

CREATE PROCEDURE AmbilAlamat @City nvarchar(30)
AS
SET NOCOUNT ON
SELECT *

FROM AdventureWorks.Person.Address
WHERE City = @City
GO

Untuk mengetahui jumlah record yang dihasilkan oleh procedure diatas, gunakan perintah PRINT @@ROWCOUNT. Misal:

CREATE PROCEDURE AmbilAlamat @City nvarchar(30)
AS
SET NOCOUNT ON
SELECT *
FROM AdventureWorks.Person.Address

WHERE City = @City
PRINT @@ROWCOUNT
GO

BERBAGAI VARIASI PENGGUNAAN STORED PROCEDURES

Sebagaimana pada VBA, sebenarnya stored procedure bisa digunakan untuk berbagai macam keperluan, diantaranya insert data, reindex database, backup, dsb. Namun, yang paling sering digunakan adalah:

·      Insert

·      Delete

·      Update

·      Select

·      Get

·      Validate

BEBERAPA PERKIRAAN FAQ

1.       Saya belum banyak paham stored procedure, apakah tidak masalah? Tidak apa-apa. Gunakan saja query ataupun action query di dalam Access, kemudian padukan dengan VBA. Sambil jalan, kedepannya sahabat belajar dan mencoba membuat stored procedure sedikit demi sedikit untuk aplikasi multiuser yang sahabat buat.

2.       Apa yang semestinya dipelajari dalam membuat stored procedure? Seorang anak bayi yang baru lahir, tentulah sangat asing baginya dunia fana yang baru ia lihat ini. Ia awalnya tidak mengetahui, lalu ia mulai memperhatikan sekitarnya, kemudian setelah itu, diajari oleh ayah ibunya kosa kata berbagai benda. Maka Subhanallah, tiba-tiba ia menjadi banyak mengetahui berbagai nama dari benda-benda. Maka sahabat harus belajar "vocabulary" dari stored procedure SQL Server ini, yakni bahasa "Transact-SQL". Semuanya tidak bisa instant, sedikit demi sedikit.

3.       Berapa lama saya bisa menguasai stored procedure ataupun Transact-SQL ini? Berbanding lurus dengan SEMANGAT sahabat dalam mempelajarinya. Bukankah seorang bayi harus mencoba merangkak berbulan-bulan agar bisa jalan?

Insya Allah nanti disambung kembali...

/* --------------------------------------------------------------- */

Penyusun: Sofyan Efendi. Salah seorang pengguna Microsoft Access sebagai front-end dan Microsoft SQL Server sebagai back-end. Sahabat bisa mengunjungi blog penyusun di: http://imopi.wordpress.com/. Terima kasih.

/* --------------------------------------------------------------- */

Wassalamu'alaikum Warahmatullahi Wabarakatuh,
Sofyan Efendi
Access DVD Collections ; Access Course by Request