MySQL : Update multiple tables with a single query (even without correlation)

Sometimes in programming activities, we encounter a condition in which we need to update some fields in two or more different tables.
Updating it one by one wouldn't be effective both in case of code writing and performances.

Fortunately, in MySQL we can update multiple tables with a single query even though both of tables has no relation each other.

For example : 

On a block of the program, I want to update the field named 'keluar'  and 'saldo akhir'  on table named 'barang_mutasi' with a primary key named 'kd_barang' valued  'A0001'.
And I also have to reduce the amount of field named 'stock' on the 'barang_master' table according to the same specified primary key 'kd_barang'  ('A0001').
As we seen from the description above, there is no correlation between the two tables, the only similarity is both of them have a field named 'kd_barang'  with a same value 'A0001'

So the SQL script would be like this:

UPDATE barang_mutasi a, barang_master b

SET a.keluar = a.keluar + 1, a.saldo_akhir = a.saldo_akhir - 1, a.tgl_update=now(), b.stock = b.stock - 1, b.tgl_update=now()

WHERE a.kd_barang = 'A0001' AND a.yemm=EXTRACT(year_month FROM now())  AND b.kd_barang='A0001';

I give them a different highlight, so it became clear that there was no relation between the two tables

Interesting, isn't it?

How to set a custom sequential Receipt Number

When I made a Point Of Sale system for a business unit, I was faced with the requirement that the system should be able to produce an unique receipt number. At a glance, i've just wonder to take advantage on AUTO INCREMENT feature as a primary key (in this case I use MySQL database).  But the requirements (demanded by the business owner) is different of course. They asked for something like this:

• Receipt Number should have the YYYYNNNN format, where YYYY is the year of the transaction, while NNNN is the number of transactions
• Receipt Number will have to be reset when it comes into the new year of transaction, for example if the last number in 2012 was 20120986, then the system must reset the number at the beginning of the year into 20130001

of course, here,  I can't use the AUTO INCREMENT  feature anymore, also i can't  use the "record count" method  (since i can't guarantee validity of records count)

To overcome this I've created a function to generate receipt number, with a method to find maximum number of recorded receipt number's through SQL query, and then compared the YYYY information on resulted receipt number with the current year. If it found different then the year (YYYY) is converted into current year and the serial number are reset to '0001 '.

If it was the same year, the serial number will be added by 1 then added again with 10000. (so if the sequential is 55, then 55 + 10000 = 10055, but we only have to take four characters (start from 2) as a String according to requirement to produce 0055. Simple right?

Here's the function that I create (in Visual Basic)

    Private Function GetNoBukti() As String
    Dim Skr As String
    Dim RcdAkhir As String
    Dim urut As Integer
    Dim Curryear As Integer
    Dim RS As ADODB.Recordset
    Dim SQL As String
        Set RS = New ADODB.Recordset
     
        SQL = "SELECT IF(MAX(id_bukti) IS NOT NULL, MAX(id_bukti), '0000') AS rcd FROM penjualan_header"
        RS.CursorLocation = adUseClient
        RS.Open SQL, conn, adOpenDynamic, adLockOptimistic
     
        RcdAkhir = RS!rcd
        Curryear = Year(Now)
        urut = Val(Mid(RcdAkhir, 5, 4))
        If (Mid(RcdAkhir, 1, 4) < Curryear) Or (RcdAkhir = "0000") Then
            GetNoBukti = Curryear & "0001"
        Else
            GetNoBukti = Curryear & Mid(10000 + urut + 1, 2, 4)
        End If
    End Function

As on the SQL string described above, if the record was not found in transaction table (result NULL) then I give the dummy sequent '0000 'to handle a condition where the new system was first installed

What do you think? Do you have a more effective way? Feel free to share here

How to put checkboxes in Windows Explorer detail view

Here is given an simple trick to activate the checkbox in the Windows Explorer detail view (Windows 7)

Click Start > type "folder options" (without quotes)  in search column, then press Enter

 In the Folder Options click view tab, then put checks in the item named "use check boxes to select the items",  then press OK

 
Now open Windows Explorer, try to see a  folder, if you hover the mouse over a file, then a checkboxes will appear on it's left side

 This feature become handy and helpful especially when we want to select multiple files for further operations without having to hold down the Ctrl or Shift or by blocking it by mouse.

WinSCP on Linux? Use FileZilla instead!

After I deciding to move my programming environment from Windows XP to Ubuntu 11.10,  I need to find some tools on Linux to support my daily jobs. In addition the tool should be Open Source / Freeware, and another concern is  I really avoid using Wine to run Windows applications because I personally think the is not so stable and some of thems looks bad.

 I tried to find an equivalent tool for WinSCP (a SSH File Transfer tool for easy copy files from / to the server development), I have tried  some applications like Midnight Commander, SecPanel, muCommander but nothing as good as WinSCP.

An unexpected solution, we actually can use FileZilla, the popular applications for the FTP client for SSH File Transfer such as WinSCP does.

FileZilla Site Manager

As shown above, fill in your Host address, Port : 22, and for Protocol, choose SFTP - SSH File Transfer Protocol. Also put the User & Password information on the fields. Hit the Connect button, and you're done.

On thing I like from FileZilla, it'll tell us if a file (between local and remote site) has changed. And it'll offer us whether to synchronize it or not. Nice!

Feel free to discuss if you've got a better tool than this

First posting in year 2012

It's been a long time for me to hadn't update this blog, because of my high workload at my ofice. Beginning in this 2012, I have a resolution to start writing this blog in English. Am I daring? Maybe yes, considering that I have a bad English. But if not now when? Do I have to wait until I'm completely  fluent in English? 

My determination was made, I will write in English even though there are some mistakes. Feel free to give me your corrections and suggestions if any of you find some mistakes / grammars in my postings.

Last, as i always said,  forgive my bad English :-)