Calling iSeries (AS/400) Commmand Using Visual Basic.NET

This blog post demonstrate on how to run iSeries Command using VB.Net. This is needed for example to change user ID password via an application.
Schedule Task also might be used it to perform some jobs that have conditional things or validation.

In order to run AS/400 Command via Visual Basic,you need a pre-requisite. You have to install IBM iSeries Client Access and make reference to IBM.Data.DB2.iSeries.dll in your application.
In my computer the library is located in C:\Program Files\IBM\Client Access folder.

Basically to call AS/400 Command, you will use ADO.Net objects and execute QSYS.QCMDEXEC syntax.
For example, if you have a program called MYPGM that you want to execute, you could send this SQL statement to the AS/400:
Note: The second parameter must contain exactly 15 digits: ten to the left of the decimal and five to the right.
You can write a program to count the length of the command and create the second parameter for you
Continue reading

PHP Adapter Design Pattern Example

This post contains two examples of Adapter Design Pattern using PHP. I am giving these examples in order to understand more comprehend about Adapter Pattern.

First, implementation of Adapter Design Pattern statement
Convert the interface of a class into another interface clients expect.
Adapter lets classes work together that couldn’t otherwise because of incompatible interfaces.
Adapters are helpful if you want to use a class that doesn’t have quite the exact methods you need.

Second, implementation of using legacy class and you can’t change that legacy class but you want to port its method to another needful method.

First Example

An example class ‘clsProducts’ methods can be used inside many Adapter class to returns another result.
clsProducts object instance is passed as Adapter classes constructor argument and clsProducts methods are being used inside those Adapter classes.
Continue reading

Fast Displaying Large CSV File, Filter And Save Filtered Data Using SQLite & Java

SQLite CSV file often used for download or upload data from/to database. If table’s records count is large or very large then downloaded data to CSV file will have large in size.
Opening that large CSV file using spreadsheet application requires a lot of memory and CPU resources. Depending on user computer specifications, some consume much time to open.

It is much faster using Notepad++ to open large CSV file but if we want to filter data and save it to another CSV then using Notepad++ will not be efficient especially for some complex filtering criteria.

==> The workaround of this situation is to import that large CSV to SQLite. After importing to SQLite, we can filter it using SQL Query and save it back to CSV file.

According to documentation, SQLite is an embedded SQL database engine. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file.
Think of SQLite not as a replacement for Oracle but as a replacement for fopen(). Its performance is usually quite good even in low-memory environments.

This blog post gives example on basic connection to SQLite, Sql Query, importing CSV, save query to CSV. Also example on using SQLite in Java so you can build your own application.
Continue reading

Easy Creating Or Convert To PDF File Programmatically Using PHP mPDF

We can create PDF file using PHP easily. I choose mPDF in order to do this. Why mPDF ? That because the method is to use html string for writing PDF content and then produce PDF as an output.
We can format PDF content like an HTML page i.e using bold <b/>, table <table/>, etc which is easy for Web developer off course.

PHP.Net has own recommended PDF creation class which are Haru PDF and PDFLib. They are great but many lines and methods to do simple ‘Hello World’ example. I feel easier using mPDF since it uses HTML string.

This blog post will demonstrate how to install and gives some basic examples so reader can have brief ideas how to create PDF programmatically.

Download & Install MPDF

Download PHP source code of MPDF from
Copy to a folder in your web server directory. I am using MPDF57 for this demonstration.
Continue reading

ASP.Net Ajax Creating Multiple Calendar Extenders on TextBoxes Programmatically

A web app having multiple Textboxes and Date Pickers or Calendar Extenders are very common.
Usually a page for submitting more than one rows which include Date field.

If Textboxes with attached Calender depends on some logic then we need to create them programmatically.

I will show how to do it using Asp.Net Ajax Control Toolkit Calendar Extender as Date picker control. I also used the simple ‘Northwind’ sample Database for demo purpose.

This small application will show Employees Name, Title and Hire Date info. Hire Date can be choose using calendar extender & updated.

  1. Creating multiple TextBoxes and Calendar Extenders programmatically
    <form id="form1" runat="server">
            <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
            <asp:UpdatePanel ID="updpanel" runat="server">
                <asp:Table ID="tblinput" runat="server" >
                    <asp:TableHeaderRow ID="TableHeaderRow1" runat="server" Font-Names="Calibri" Font-Size="10pt">
                        <asp:TableHeaderCell>Hire Date</asp:TableHeaderCell>
                <asp:PlaceHolder ID="PlaceHolder1" runat="server"></asp:PlaceHolder>
                <asp:Button ID="btnSubmit" runat="server" Text="Submit" 
                    onclick="btnSubmit_Click" />  <asp:Label ID="lblmsg" runat="server"></asp:Label>

    I have Table Control as container of Labels & TextBoxes. Also PlaceHolder control that will be contained by AjaxControlToolkit Calendar Extender.

    Labels, TextBoxes and Calendar Extender created programmatically inside Page_Load procedure.

  2. Continue reading

Scheduling Excel Report With SSRS 2008 R2 And ASP.Net

As default SQL Server Reporting Service 2008 R2 has Scheduled Report Generation named Subscription. You can choose what report and when to generate it also delivery method.

However this feature is quite standard. We must supply report’s Parameter manually to Subscription. So that in this blog I will show you how to make more flexible to this kind of schedule using Visual Basic Asp.Net

The generated report will be on Excel format.

Here are step by step:

  1. Prepare the report you want to sent and deploy Report Server
  2. Browse the report via http://Your_serverip/ReportServer and get its exact Url for example http://Your_serverip/ReportServer/Pages/ReportViewer.aspx?%2fYour_Report_Name&rs:Command=Render
  3. Continue reading

Get Top Queries By Average CPU Time SQL Server 2008 R2

Database performance plays significant effect in large user application. We need to examine and identify causes that makes system slow.

One step we can do is to identify query which takes high CPU time. Use transact SQL sys.dm_exec_sql_text in order to get top CPU expensive queries.

Taken from Microsoft site,, the code using sys.dm_exec_sql_text is below:

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;

Above T-SQL gets Top five query by Average CPU Time. Open your SQL Server Management Studio, choose your database and run above query.

After getting those Top queries, you can open SQL Server Database Engine Tuning Advisor to analyze them and apply the Tuning Advisor recommendation to get better performance.
You can take a look at my post about How to use Database Engine Tuning Advisor for your reference.

Agung Gugiaji