SELECT SCOPE_IDENTITY(), SELECT LAST_INSERT_ID() dan RETURNING PK

Yuph kali ini kita akan membahas fungsi menarik (SELECT SCOPE_IDENTITY(), SELECT LAST_INSERT_ID() dan RETURNING PK) yang dimiliki oleh database2x terkenal seperti SQL Server, MySQL dan Firebird.

Dari nama fungsinya kita bisa menebak kegunaan dari fungsi diatas yaitu untuk mendapatkan nilai ID terakhir (biasanya bertipe angka) yang diinputkan.

Saya dan mungkin Anda biasanya dalam merancang tabel untuk selalu menambahkan field ID dengan attribut IDENTITY/AUTONUMBER dan sekaligus dijadikan sebagai PRIMARY KEY.

Tentunya hal ini kita lakukan untuk mempermudah dalam proses manipulasi data (edit dan delete).

Contoh beberapa tabel yang biasanya menggunakan field ID untuk PRIMARY KEY dan sekaligus diset dengan attribut IDENTITY/AUTONUMBER

Bagi yang males membaca referensi (termasuk sy ) biasa menuliskan kode berikut untuk mengINSERTkan dan mendapatkan nilai ID yang terakhir, kita ambil contoh tabel provinsi.

Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
    Dim lastID As Long = 0

    ' langkah 1 - menambahkan data
    strSql = "INSERT INTO provinsi (keterangan) VALUES (@1)"
    Using cmd As New SqlClient.SqlCommand(strSql, conn)
        With cmd
            .Parameters.Clear()
            .Parameters.AddWithValue("@1", "Riau")

            .ExecuteNonQuery()
        End With
    End Using

    ' langkah 2 - mengambil id terakhir dg memanfaatkan fungsi MAX
    strSql = "SELECT MAX(id) FROM provinsi"
    Using cmd As New SqlClient.SqlCommand(strSql, conn)
        lastID = Convert.ToInt32(cmd.ExecuteScalar())
    End Using

    ' tampilkan ID terakhir
    MessageBox.Show("Last ID : " & lastID.ToString())
End Sub

Sekarang kita lihat bedanya jika memanfaatkan fungsi SELECT SCOPE_IDENTITY().

Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
    Dim lastID As Long = 0

    strSql = "INSERT INTO provinsi (keterangan) VALUES (@1) SELECT SCOPE_IDENTITY()" ' ada tambahan SELECT SCOPE_IDENTITY()
    Using cmd As New SqlClient.SqlCommand(strSql, conn)
        With cmd
            .Parameters.Clear()
            .Parameters.AddWithValue("@1", "Riau")

            lastID = Convert.ToInt32(.ExecuteScalar())
        End With
    End Using

    MessageBox.Show ("Last ID : " & lastID.ToString())
End Sub

Gimana lebih simple kan ? :)

Oke sekarang kita lihat kode untuk MySQL(SELECT LAST_INSERT_ID) dan Firebird (RETURN KOLOM_PK).

' MySQL
Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
    Dim lastID As Long = 0

    strSql = "INSERT INTO provinsi (keterangan) VALUES (@1); SELECT LAST_INSERT_ID()"
    Using cmd As New MySqlClient.MySqlCommand(strSql, conn)
        With cmd
            .Parameters.Clear()
            .Parameters.AddWithValue("@1", "Riau")

            lastID = Convert.ToInt32(.ExecuteScalar())
        End With
    End Using

    MessageBox.Show ("Last ID : " & lastID.ToString())
End Sub

' Firebird
Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
    Dim lastID As Long = 0

    strSql = "INSERT INTO provinsi (keterangan) VALUES (@1) RETURNING ID" ' ID -> kolom primary key
    Using cmd As New FirebirdClient.FbCommand(strSql, conn)
        With cmd
            .Parameters.Clear()
            .Parameters.AddWithValue("@1", "Riau")

            lastID = Convert.ToInt32(.ExecuteScalar())
        End With
    End Using

    MessageBox.Show ("Last ID : " & lastID.ToString())
End Sub

Untuk bisa mengakses database MySQL dan Firebird di Visual Basic .NET kita harus menginstall MySQL Connector/Net dan Firebird ADO.NET Data Provider.

Selamat mencoba