Linux Ubuntu and SAP at Virtualizing Mode … Why not …

Dear All,

Get back again on post the blogs … Now, How to run SAP GUI at Linux in Virtualizing Mode ?

Short ways :

1. Download the latest (at this blogs wroted) PlatinGUI at : ftp://ftp.sap.com/pub/sapgui/java/710r10 …
What is PlatinGUI ? SAP GUI that run on Platform Independent … so … this application is Java Version.
2. Download the latest (at this blogs wroted) JRE at : http://www.java.com/en/download/index.jsp
3. Extract / Install this latest of JRE ( if not installed yet ), how to check that Java has been install or not ?
simply, type at the shell of linux : java -version

on this screen-shots that the Java has been installed.

Don’t forget to set the JAVA_HOME at /etc/bash.bashrc files, (for ubuntu example)
rachman@manz-ubuntu:~$ sudo gedit /etc/bash.bashrc

type :
JAVA_HOME=”Path_Java_Installation”
export JAVA_HOME

save and exit.

4. Install PlatinGUI :
rachman@manz-ubuntu:~$ java -jar PlatinGUI-Linux-710r10.jar
then, click Install and follow the instructions

5. After finish the installation, set the configuration setting with running the SAPGUI logon at :
rachman@manz-ubuntu:~$ /opt/SAPClients/SAPGUI/bin/guilogon

In that screen-shot, has a one entry of SAP Production server … how to be set like that ?

Click New Button, and will be show the new dialog box :

First, type your description of your server, and leave the connection type with R/3, and then goto Advanced Tab, don’t forget to click the Expert Mode … wow … some Expert play in here ..

Type :

conn=/H/<<your SAP Application Server Address>>/S/<<type your SID / System Identifier>>

/H means : host server to connect, you can bind your IP or FQDN

for example: 192.168.1.1 ( IP Based ), manz-ubuntu.virtualize.com ( FQDN / Fully Qualified Domain Name )

/S means : SID ( System Identifier ), for this example we use 00 for SID, but why become 3200 ?

based on information that i’m get, the SAP use some port to access their application server, here we are the ports :

sapdp##  32##/tcp # SAP Dispatcher.       3200 + System-Number
sapgw##  33##/tcp # SAP Gateway.          3300 + System-Number
sapsp##  34##/tcp #                       3400 + System-Number
sapms##  36##/tcp # SAP Message Server.   3600 + System-Number
sapdp##s 47##/tcp # SAP Secure Dispatcher 4700 + System-number
sapgw##s 48##/tcp # SAP Secure Gateway    4800 + System-Number

sapgw97  3397/tcp # SAP Oss
sapgw98  3398/tcp # SAPcomm
sapgw99  3399/tcp # SAP EPS
sapdp99  3299/tcp # SAProuter

this ports will be mapped into VirtualBox extradata settings.

After that, click save to saving your connection.

Now, before you can run…

Goto hosts OS first and then set the extradata on hosts OS like this :

VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapdp/HostPort” 3200
VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapdp/GuestPort” 3200
VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapdp/Protocol” TCP

 

VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapgw/HostPort” 3300
VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapgw/GuestPort” 3300
VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapgw/Protocol” TCP

VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapsp/HostPort” 3400
VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapsp/GuestPort” 3400
VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapsp/Protocol” TCP

VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapms/HostPort” 3600
VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapms/GuestPort” 3600
VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapms/Protocol” TCP

VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapdp#s/HostPort” 4700
VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapdp#s/GuestPort” 4700
VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapdp#s/Protocol” TCP

VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapgw#s/HostPort” 4800
VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapgw#s/GuestPort” 4800
VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapgw#s/Protocol” TCP

VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapgw97/HostPort” 3397
VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapgw97/GuestPort” 3397
VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapgw97/Protocol” TCP

VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapgw98/HostPort” 3398
VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapgw98/GuestPort” 3398
VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapgw98/Protocol” TCP

VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapgw99/HostPort” 3399
VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapgw99/GuestPort” 3399
VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapgw99/Protocol” TCP

VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapdp99/HostPort” 3299
VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapdp99/GuestPort” 3299
VBoxManage setextradata Ubuntu “VBoxInternal/Devices/pcnet/0/LUN#0/Config/sapdp99/Protocol” TCP

And then, run your Ubuntu guests OS…

Devices:

pcnet, this name used for PCNET-PCI or PCNET-fast
e1000, this name used for Intel PRO/1000

Ubuntu name is your virtualbox guests identifications

After all successful steps, we can see the SAP run on Virtualizations …

Ok that’s all folks … just do it

SAP run on Virtualization Ubuntu OS’es with PlatinGUI … so … no matter you will working on Linux …

Cheers,

Man’z

Delphi … Frame … whats going on ???

Dear All …

You may create an application for GUI without MDI Form related due to has an animation when creating or resizing like maximize or minimize on it … but we have another tricky to reduce the animation on MDI Form … another posts …

Now, we talk about Frame …

Frame is inheritance from TForm class, you may use as usual, but this TFrame doesn’t have event onShow and onHide, so we can tricked it when it created.

Frame is different with Form, when from call using myForm.show event, but how about Frame ??? you never find it when type Frame. (dot) ..

Here … create the frame is :

var
  F: TFrame;
begin
  F := TFrame.Create(Self);
  F.Parent := Panel1;
end;

Now, for example, how about you have one label to make it move into right of Frame when your frame is resized ???

When you trigger the frame when it created, you may never get you want, the label will move into right position when it frame is created, but when you showing it, the label never moving into correct position…

But, don’t be panic if you cannot get what you want… you may get trick at your frame using messages.

Put at private sections :

procedure CMShowingChanged(var M: TMessage); message CM_SHOWINGCHANGED;

then , complete the procedure like this :

procedure TFrame1.CMShowingChanged(var M: TMessage);
begin
  inherited;
  if Showing then
  begin
    // ... put your onShow code here
  end
  else
  begin
    // ... put your onHide code here
  end;
end;

That’s all falks … next posts will how to avoid the animation on MDIChild form

Have a nice coding,

Man’z

Devex: Unbound and Bounded Image Data Aware ver. 2

Dear All,

After we trying the previous technique, we have face the problem … But, Fortunately we found the way to change it and running smoothly …

Change the step and you will find the (maybe) good solutions … but still have one problem when handling the big data aware …

First alias ka hiji kieu :

put one procedure at Public Level:

public
 { Public declarations }
 procedure UpdateGambar(AView: TcxGridTableView; ARecordIndex: integer);

kemudian, create the code like shown below, in previous post we put this code at GetProperties event :

procedure TForm8.UpdateGambar(AView: TcxGridTableView; ARecordIndex: integer);
var
 AValue: string;
 APicture: TPicture;
 AFileName : string;
begin
 AView.DataController.PostEditingData;
 AFileName := 'C:\Manz\Projects\Web\hrms\public_html\images\photo\' +
 VarToStr(AView.DataController.Values[ARecordIndex, cxGrid1DBTableView1nip.Index]) + '.jpg';
 if FileExists(aFileName) then
 begin
   APicture := TPicture.Create;
   try
     APicture.LoadFromFile(AFileName);
     SavePicture(APicture, AValue);
   finally
     FreeAndNil(APicture);
   end;
 end
 else AValue := '';

 AView.DataController.Values[ARecordIndex, cxGrid1DBTableView1Column1.Index] := AValue;
end;

next, at event onPropertiesEditValueChanged at column Image on File, put this code like shown below :

procedure TForm8.cxGrid1DBTableView1Column1PropertiesEditValueChanged(
 Sender: TObject);
var
 AIndex: Integer;
begin
 AIndex := cxGrid1DBTableView1.DataController.FocusedRecordIndex;
 UpdateGambar(cxGrid1DBTableView1, AIndex);
end;

next, at event Form Create put this code like shown below :

procedure TForm8.FormCreate(Sender: TObject);
var
 i: integer;
begin
 for i := 0 to cxGrid1DBTableView1.DataController.RecordCount - 1 do
 UpdateGambar(cxGrid1DBTableView1, i);
end;

After that, you may running your project  …. and … tadaaaa…. , smooth scrolling in the grid without knowing the error will be appear

But, this the problem when handling the big data aware, cause will be loaded slowly …, you may filtering your data table or query with the limit.

That’s all folks, … gitu azah dach … sekian dan terima kasih .. jangan lupa donat nya ya weekekekekekek

Salam Coderz,

Man’z

Devex: Bounded and Unbounded Image Data Aware

Dear All,

Met ketemu lagi dalam acara Delphi Coderz …

Kemarin, ada dari temen bro @ImanD mengenai bagaimana memunculkan gambar bukan dari database saja, akan tetapi dari file juga, soalnya kita pengguna Developer Express Quantum Grid.

Setelah membaca dan mengurai dari keterangan Knowledge Basenya Developer Express. Akhirnya bisa ditampilkan apa yang diinginkan … sebelumnya mah udah jalan tapi gak bisa munclu gambarnya

Nah, sekarang daripada ngomong gak karuan langsung saja, kita buat project baru dan simpan deh komponen yang diperlukan seperti zConnection, zTable (karena pake ZeosLib), DataSource dan cxGridnya.

Untuk di gridnya ditambahkan 1 column unbound, jadi dikenal dengan column cxGrid1DBTableView1Column1, dengan set properties :

cxGrid1DBTableView1Column1.Properties := Images
.Properties.Images.GraphicClassName := TJPEGImage
.Properties.Images.ImmediatePost := true

sekarang untuk Databindingnya di set ke:

cxGrid1DBTableView1Column1.DataBinding.ValueType := string

untuk DataBinding.FieldName nya di kosyonk an saja, karena ini tidak dipakai, jadi column ini disebut unbound data-aware.

Sekarang … Codingz , tadi nya saya pikir bisa disimpan pada event onCustomDrawCell, karena untuk masalah beda warna tiap baris bisa digunakan event ini, tapi ternyata terjadi infinite-loop pada event Paint nya DevexGrid.

Jadi, saya tempatkan pada event : onGetProperties dari cxGrid1DBTableView1Column1 dengan isi :

procedure TForm8.cxGrid1DBTableView1Column1GetProperties(
  Sender: TcxCustomGridTableItem; ARecord: TcxCustomGridRecord;
  var AProperties: TcxCustomEditProperties);
var
  AFileName, AValue: String;
  APicture: TPicture;
begin
  AFileName := 'C:\Manz\Projects\Web\hrms\public_html\images\photo\' +
               VarToStr(ARecord.Values[cxGrid1DBTableView1nip.Index]) + '.jpg';
  if (fileexists(AFileName)) then
  begin
    APicture := TPicture.Create;
    try
      APicture.LoadFromFile(AFileName);
      SavePicture(APicture, AValue);
    finally
      freeAndNil(APicture);
    end;
  end
  else AValue := '';

  cxGrid1DBTableView1.DataController.Values[ARecord.RecordIndex, Sender.Index] := AValue;
end;

Dengan hasil snap-shot diatas .. maka hasil yang di-inginkan keluar seperti ini

ada 2 field tertera diatas, yaitu from DB dan from File, nah yang from File inilah yang disebut dengan unbounded data-aware.

Dikau temanz .. bisa mencoba nya … any error, any mistake (salah), access denied or error yang lain … don’t ask … alias jangan tanya … silahkan trace sendiri sajah yah …

Seperti biasa, kalau code ini berguna untuk kalian teman-temanz, teman-temanz bisa donate kepadaku ya ya ya for keep this blog live and given the some tricky …

Salam Coderz,

Man’z

procedure TForm8.cxGrid1DBTableView1Column1GetProperties(
Sender: TcxCustomGridTableItem; ARecord: TcxCustomGridRecord;
var AProperties: TcxCustomEditProperties);
var
AFileName, AValue: String;
APicture: TPicture;
begin
AFileName := ‘C:\Manz\Projects\Web\hrms\public_html\images\photo\’ +
VarToStr(ARecord.Values[cxGrid1DBTableView1nip.Index]) + ‘.jpg’;
if (fileexists(AFileName)) then
begin
APicture := TPicture.Create;
try
APicture.LoadFromFile(AFileName);
SavePicture(APicture, AValue);
finally
freeAndNil(APicture);
end;
end
else AValue := ”;

cxGrid1DBTableView1.DataController.Values[ARecord.RecordIndex, Sender.Index] := AValue;
end;

Crosstab or Whatever that said at MySQL …

Dear All, again MySQL again…hehehehe … simple trick from me that will save your time of work when you using looping in code … yeah code … in Delphi, VB, what ever you create the aplication.

We will trying to create crosstab or whatever you will said … in MySQL …

First, saya punya data sebagai berikut :

Tabel karyawan:
 NIP   | Nama
 ------------------------
 001   | Udin tea
 002   | Budi Kahandap
 003   | Andi Kararangge
 004   | Cecep Gorbacep
 005   | Ono Ontohod 

 Tabel master_absen:
 tipe_absen | deskripsi
 ----------------------
 AL         | ALPA
 SK         | SAKIT
 CT         | CUTI 

 Tabel absen:
 NIP  |     TGL    | tipe_absen
 ------------------------------
 001  | 01/07/2009 | AL
 001  | 02/07/2009 | AL
 002  | 05/07/2009 | CT
 004  | 05/07/2009 | CT
 002  | 06/07/2009 | SK
 003  | 07/07/2009 | SK
 003  | 08/07/2009 | SK
 003  | 09/07/2009 | SK
 003  | 10/07/2009 | SK
 005  | 11/07/2009 | AL
 005  | 12/07/2009 | AL
 005  | 13/07/2009 | AL
 005  | 14/07/2009 | AL

Actually we want to create the result of query like this :

 NIP | NAMA            | AL | CT | SK
 ------------------------------------
 001 | Udin tea        | 2  | 0  | 0
 002 | Budi Kahandap   | 0  | 1  | 0
 003 | Andi Kararangge | 0  | 0  | 4
 004 | Cecep Gorbacep  | 0  | 1  | 0
 005 | Ono Ontohod     | 4  | 0  | 0

Tapi … ada tapinya juga neh …. kalau di master_absen dan di data absen ditambah datanya menjadi :

 tipe_absen | deskripsi
 --------------------------
 AL         | ALPA
 SK         | SAKIT
 CT         | CUTI
 IZ         | IZIN PULANG   <---- tambahan data

kemudian data absen jadi :

 NIP  |     TGL    | tipe_absen
 ------------------------------
 001  | 01/07/2009 | AL
 001  | 02/07/2009 | AL
 002  | 05/07/2009 | CT
 004  | 05/07/2009 | CT
 002  | 06/07/2009 | SK
 003  | 07/07/2009 | SK
 003  | 08/07/2009 | SK
 003  | 09/07/2009 | SK
 003  | 10/07/2009 | SK
 005  | 11/07/2009 | AL
 005  | 12/07/2009 | AL
 005  | 13/07/2009 | AL
 005  | 14/07/2009 | AL
 003  | 20/07/2009 | IZ    <--- tambahan data
 004  | 21/07/2009 | IZ    <--- tambahan data

hasil yang diinginkan jadi :

 NIP | NAMA            | AL | CT | IZ | SK
 -----------------------------------------
 001 | Udin tea        | 2  | 0  | 0  | 0
 002 | Budi Kahandap   | 0  | 1  | 0  | 0
 003 | Andi Kararangge | 0  | 0  | 1  | 4
 004 | Cecep Gorbacep  | 0  | 1  | 1  | 0
 005 | Ono Ontohod     | 4  | 0  | 0  | 0

Nah ada tambahan kolom IZ tuh ….

Sekarang, How to make the simple single query statements ? yeah … query statements …

This below is step … step by step we will describe it …

Kalau kita menggunakan sql query dengan link left join seperti biasa :

select k.nip, k.nama, a.tipe_absen, a.tgl
from karyawan k
left join absen a on k.nip = a.nip
left join master_absen m on a.tipe_absen = m.tipe_absen
group by k.nip, a.tgl
order by k.nip

we got result like this :

Nah itu baru memunculkan semua data yang ada.

Sekarang kita coba di otak atik sql nya dengan menggunakan power of group_concat dan select concat statement dan menjadi sql kita, untuk mendapatkan hasil berapa banyak si-dia absen dan tidak menghiraukan tanggal berapa saja, dan saya tidak akan menjelaskan untuk group_concat and select concat functions disini, bukan gag mau … tapi cape ngetiknya , seperti dibawah ini :

select concat(
'select k.nip, k.nama',
group_concat(
concat(', sum(if(a.tipe_absen="', m.tipe_absen, '", 1, 0)) As ', m.tipe_absen, '\n')
order by m.tipe_absen separator ''),
'from karyawan k
left join absen a on k.nip = a.nip
left join master_absen m on a.tipe_absen = m.tipe_absen
group by k.nip, m.tipe_absen
order by k.nip, m.tipe_absen') into @Absence_SQL
from master_absen m;
prepare Absences from @Absence_SQL;
execute Absences;

Nah, disini kita sudah menggunakan fitur statement dari MySQL, dan juga return statement ke variable @Absence_SQL dan kita akan lakukan execute Absences untuk mendapatkan resultset seperti dibawah ini :

Tapi … eit … just a moment … we got false data …, jika dilihat … record no 2 dan 3, kan ada 002 untuk Budi Kahandap itu punya status CT dan SK …. gak mungkin untuk memunculkan data seperti ini dan diserahkan kepada yang berwajib … hehehehe

So, we change again the sql structure to meet our requirements … like shown as below :

select concat(
'select T.nip, T.nama',
group_concat(concat(', sum(T.', m.tipe_absen,') As ', m.tipe_absen, '\n')
order by m.tipe_absen separator ''), '\n',
'from (select k.nip, k.nama',
group_concat(
concat(', sum(if(a.tipe_absen="', m.tipe_absen, '", 1, 0)) As ', m.tipe_absen, '\n')
order by m.tipe_absen separator ''),
'from karyawan k
left join absen a on k.nip = a.nip
left join master_absen m on a.tipe_absen = m.tipe_absen
group by k.nip, m.tipe_absen
order by k.nip, m.tipe_absen) As T
group by T.nip') into @Absence_SQL
from master_absen m;
prepare Absences from @Absence_SQL;
execute Absences;

Nah, so … after we concatenate the sql statements again .. what we got ???

You may see what we want … as shown below :

Ow, … we got right what we want …. records no 2 has become one line …

ck… ck… ck… just a moment … but how we added the data has been described in beginning of this post ?

Just insert the data at master_absen and data absen … and then … just running again the last sql statements, and you will get the results like this :

Look at the columns … somethin’ differents between this result of query than before  ??? You decide

Dikau friendz bisa mengubah – ubah query diatas sesuai dengan keinginan ….

Sampai jumpa di lain Query Tricky. Oh ya… don’t forget to donate to make this blog live and give another tricky …

Salam DBA,

Man’z

When your MySQL Server make you headache …

Dear all, met me again …

Now, we will posting how to resolve when your MySQL Server make you headache … alias bikin sakit kepala dan mau pecah … karena MySQL menolak semua access dari all host and IP.

Actually we don’t know what this happen persist, teuing kunaon tah … langsung begitu gag mau terima koneksi dari host manapun …. dan muncul salah satu pesan seperti ini :

ERROR 1130 (HY000): Host ‘localhost’ is not allowed to connect to this MySQL server.

atawa:

ERROR 1130 (HY000): Host ‘manz’ is not allowed to connect to this MySQL server.

atawa:

ERROR 1130 (HY000): Host ‘192.168.1.200′ is not allowed to connect to this MySQL server.

But … don’t worry … be happy i already found the technique to resolve this problem …

We don’t know if other site already mention …

Teknik ini saya pakai karena server saya menggunakan windows …

Langkah – langkah nya :

1. Matikan service dari MySQL :

net stop MySQL

2. Jalankan service dengan background dan skipping checking user and privilege table:

start /b mysqld –skip-grant-tables –user=root

3. Reset user table di mysql dengan memanggil mysql command:

mysql -e “insert into mysql.user(host, user, grant_priv, super_priv, ssl_cipher, x509_issuer, x509_subject) values(‘root’,'%’,'Y’,'Y’,”,”,”);”

4. Kemudian matikan MySQL yang jalan di background process tersebut:

mysqladmin -uroot shutdown

5. Jalankan service MySQL secara normal:

net start MySQL

After that , ta..da…. dikau bisa masuk ke MySQL lagi, kalo anda tidak reset password, anda bisa pake password yang lama.

Silahkan mencicipi …

Salam DBA,

Man’z

Ngitung balance lagiii ….

Dear All readers,

Back again with “Ngitung Balance” …

Pada postingan dulu, saya melakukan perhitungan balance dengan menggunakan fitur yang ada FastReport dan silahkan baca disini untuk balance FastReport.

Nah, sekarang bagaimana ngitung balance dengan menggunakan MySQL atau Single Simple Query ?

Setelah sekian waktu mencoba, mengutak – mengatik … dan akhirnya ketemu dengan simple.

Silahkan coba dengan membuat table seperti ini :

CREATE TABLE accountdeposit
(
  id INTEGER(11) NOT NULL AUTO_INCREMENT,
  memCode CHAR(9) COLLATE latin1_swedish_ci NOT NULL,
  transCode CHAR(100) COLLATE latin1_swedish_ci NOT NULL,
  stat ENUM('D','C') NOT NULL,
  balance FLOAT(9,3) NOT NULL,
  transDate TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP
            DEFAULT CURRENT_TIMESTAMP,
  author INTEGER(11) NOT NULL, PRIMARY KEY (id),
  KEY memCode (memCode)
);
INSERT INTO accountdeposit (id, memCode, transCode, stat, balance,
                            transDate, author) VALUES
(5, '0321001', 'Buka Account', 'C', 100000, '2009-03-21 00:05:44', 1),
(6, '0321001', 'open list 3 Form', 'D', 15000, '2009-03-23 10:51:16', 1),
(7, '0321001', 'open list 4 Form', 'C', 20000, '2009-03-23 13:05:00', 1);
COMMIT;

Nah, itu untuk struktur dan data nya … itu diatas mah sample doank ya …

Sekarang bagaimana untuk retrieve agar mendapatkan seperti dibawah ini ?

+------------------+------------+------------+-------------+
| transCode        | Debit      | Credit     | LastBalance |
+------------------+------------+------------+-------------+
| Buka Account     |      0.000 | 100000.000 |  100000.000 |
| open list 3 Form | -15000.000 |      0.000 |   85000.000 |
| open list 4 form |      0.000 |  20000.000 |  105000.000 |
+------------------+------------+------------+-------------+

Nih dibawah sql scriptnya :

select transCode,
case when stat='D' then (-balance) else 0 end as Debit,
case when stat='C' then balance else 0 end as Credit,
(@LB := @LB + if (stat='D', -balance, balance)) as LastBalance
from (select @LB := 0) as LastBlnc, accountdeposit
where memCode = '0321001'
group by memcode, transdate
order by transdate;

Hasilnya ? ya seperti diatas …

Thanks to bro @yayaretina that has been open my mind to create a Simple Single Queries ..

Cheers,

Man’z

MySQL Trick for auto-key on trigger

Dear All,

Once again, MySQL make me headache to handle un-linked key that consist at temporary tables and this two table is manipulate by record-form model.

This is a scenario :

We’ve 2 table — simple table that want to inserting the key for linked between that tables, that the key from master table is generated from trigger.

for example :

create table tbl_a
(
  uid varchar(40) default '-',
  name varchar(50),
  constraint pk_tbl_a primary key (uid)
);

create table tbl_b
(
  uid varchar(40),
  qty double precision default 0,
  description varchar(100),
  constraint fk_tbl_b foreign key (uid)
    references tbl_a (uid)
    on update cascade on delete cascade
);

now, we create the trigger for this table a.

create trigger trig_tbl_a before insert on tbl_a for each row
set new.uid = uuid();

but, how to insert the tbl_b.uid ? if we use simple insert statement … ?

After we long discussed with our best friends … Now, we have one good idea.

This the resolving the problem :

drop and re-create the trigger at table a :

drop trigger trig_tbl_a;
create trigger trig_tbl_a before insert on tbl_a for each row
begin
  set new.uid = uuid();
  create temporary table if not exists t_uid(uid varchar(40));
  delete from t_uid;
  insert into t_uid values (new.uid);
end;

then, create trigger for table b:

create trigger trig_tbl_b before insert on tbl_b for each row
set new.uid = (select uid from t_uid);

after that, we can relate the data on table b using the uid key.

try this :

mysql> insert into tbl_a (name) values ('Manz');
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> insert into tbl_b (qty, description) values (10, 'Movie ordered with DVD original version');
Query OK, 1 row affected (0.03 sec)

mysql> select * from tbl_a;
+--------------------------------------+------+
| uid                                  | name |
+--------------------------------------+------+
| 4b5a8c27-0321-11de-88cd-184e3e4ece1f | Manz |
+--------------------------------------+------+
1 row in set (0.02 sec)

mysql> select * from tbl_b;
+--------------------------------------+------+-----------------------------------------+
| uid                                  | qty  | description                             |
+--------------------------------------+------+-----------------------------------------+
| 4b5a8c27-0321-11de-88cd-184e3e4ece1f |   10 | Movie ordered with DVD original version |
+--------------------------------------+------+-----------------------------------------+
1 row in set (0.00 sec)

Now, we can use the code to update my uid. And result is my headache is gone….

Cheers,

Man’z

MySQL Grouping Tips

Hai hai … friend … ada kasus dari temen nih, sempet keringetan karena gak ketemu jalan keluarnya, sehingga posting di forum.

Jadi sebagai bahan arsip ajah, saya arsipkan dan siapa tahu ada yang membutuhkannya.

Ini pure di MySQL :

DROP TABLE IF EXISTS `tnama`;
CREATE TABLE `tnama` (
`id` int(11) NOT NULL auto_increment,
`NIK` varchar(10) default NULL,
`Nama` varchar(10) default NULL,
`NoTelp` varchar(10) default NULL,
`Grup` varchar(10) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

– —————————-
– Records
– —————————-
INSERT INTO `tnama` VALUES (‘1′, ‘A’, ‘A’, ‘A’, ‘A’);
INSERT INTO `tnama` VALUES (‘2′, ‘A’, ‘A’, ‘A’, ‘B’);
INSERT INTO `tnama` VALUES (‘3′, ‘A’, ‘A’, ‘A’, ‘C’);
INSERT INTO `tnama` VALUES (‘4′, ‘B’, ‘B’, ‘B’, ‘A’);
INSERT INTO `tnama` VALUES (‘5′, ‘B’, ‘B’, ‘B’, ‘B’);
INSERT INTO `tnama` VALUES (‘6′, ‘C’, ‘C’, ‘C’, ‘A’);
INSERT INTO `tnama` VALUES (‘7′, ‘C’, ‘C’, ‘C’, ‘B’);

hasil dari select biasa adalah :

NIK Nama  NoTelp  Grup
----------------------
A     A     A       A
A     A     A       B
A     A     A       C
B     B     B       A
B     B     B       B
C     C     C       A
C     C     C       B

Nah sekarang pengen hasilnya seperti ini :

NIK Nama  NoTelp  Grup
-------------------------
A    A      A     A,B,C
B    B      B     A,B
C    C      C     A,B

Gimana tuh … sempet saya juga pusyink memikirkan nya , dan ternyata ketemu dengan menggunakan reserved word yang ada di MySQL

Statement nya :

select nik, nama, telp, group_concat(grup)
from tnama
group by nik

Hasilnya …. sama dengan yang di-ingin-kan ….

Salam DBA,

Man’z

SAP Part 3 – Create and Monitoring User

Dear All,

Now, as per my promise, we’ll show to you, how to create user and monitoring they do.

Just it simply.

1. Create User

Login as SAP* or user has been assigned SAP_ALL profile like shown below :

And, after logon to system, find out to create user using t-code SU01 or select from menu tree at : Tools -> Administration -> User Maintenance -> Users, shown like below :

And then type the name at the User fields, and push F8 to create the users, show like below :

After pushing F8 or creating new user, the dialog will shown like this :

Input the Last name (required) and other information at this screens.

Go to Logon data tab, and type the initial password twice. This initial password should be change at first login. Because we trying for create user with full access, type SUPER at User Group for Authorization Object, shown below :

And the go to profile tab, and fill the profile with SAP_ALL and push enter, shown like below :

After all required data has been filled, push save and the screen you may see like this :

2. Users Monitoring

As the super user or user with full priviledges, you may see who’s users login and what they do in our system.

You may use SM04 – Users Overview or using tree menu at : Tools -> Administration -> Monitor -> System Monitoring -> User Overview. Using that t-code will shown like below :

In this sample we shown 2 users online at our system with they transaction code do, and then what they do at our system, just double clicked it at the user online, will shown like this :

That’s all falks for user create and monitoring… Have fun ..

Cheers,

Man’z