Sunday, October 15, 2017

MS SQL SMS export large data to Excel without breaking format

If you often work on MS SQL, surely sometimes you want to export large data (result a query) to Excel file. After querying on MS SQL SMS (Microsoft SQL Server Management Studio), you can use right click on the cell top left then select "Copy with Headers" and copy to Excel file or "Save Result As" and save to a CSV file.


However 2 these functions have their problems. "Copy with Headers" cannot copy large data.  "Save Result As" often breaks CSV format if your data contains some special characters.

Fortunately, there is another function for exporting large data without breaking its format. Right click on your database and choose Tasks >> Export Data...



For Data Source, choose SQL Server Native >> select Server name >> select your Database, see the following for example:


Next, select Destination as Microsoft Excel & specify Excel file path.


Next, select Write a query to specify the data to transfer.


In next step, paste your query into or select a file containing your query. In the step Review Data Type Mapping, let review again columns have been converted data (Source Type vs. Destination Type). If you want to fix, click Back to select again Destination Type for converting.

If they are ok, click Next then Finish (don't worry for warning signs). Waiting a moment and you will have your Excel file with correct format you wanted.

Yeah! This is a small tip for you, hope it is useful. Share it to your friend for helping him or her out ๐Ÿ˜. Any comment is welcome!

Happy Halloween!

Friday, September 29, 2017

Ionic 3: Debugging on VS Code and Chrome

After long time, since from my first post Quick start your mobile app with Ionic,  now I have a project using Ionic. So I want to share this post to my team for how to debugging on latest Ionic 3. Because saving debug time means you will delivery the application faster.

As beginning, I want to brief important things for starting with latest Ionic 3:
npm install -g ionic cordova
  • Start an app with command (cutePuppyPics is your app name/project)
ionic start cutePuppyPics tutorial
OK, you are ready to code! But wait, let setup to debug for your app on Chrome firstly. Open your app on VS Code and do the following steps:

1. Press F5 >> select Chrome:


2. Edit file launch.json generated with content like below:
{
    "version": "0.2.0",
    "configurations": [
        {
            "type": "chrome",
            "request": "launch",
            "name": "Launch Ionic on Chrome",
            "url": "http://localhost:8100",
            "sourceMaps": true,
            "webRoot": "${workspaceRoot}/src"
        },
        {
            "type": "chrome",
            "request": "attach",
            "name": "Attach Ionic on Chrome",
            "url": "http://localhost:8100",
            "port": 9222,
            "sourceMaps": true,
            "webRoot": "${workspaceRoot}/src"
        }
    ]
}
3. If you use Windows, let open Properties of Chrome's short cut, and add --remote-debugging-port=9222 in to Target box:


For Mac, let use same debug flag as Windows. This debug flag is used for attaching your Ionic app into existing Chrome running.

4. Press Ctrl+` to open VS Code terminal, key:
ionic serve -b
It will build the app and start the server (http://localhost:8100/) but don't launch browser (-b option).

5. Close all Chrome opened, click debugger icon >> select Launch Ionic on Chrome option >> open a file, for example cutePuppyPics\src\pages\list\list.ts, set break point and press F5. It will launch the app on Chrome and display a debug player on the top of VS Code. In this example, when you click My First List menu and hit an item, it will jump to the break point for debugging, see the following picture:


You can stop the debug by pressing Shift+F5 or stop buttonon debug player. Stopping the debugger doesn't stop the app, it is still running until you press Ctrl+C in terminal.

6. In case you want to debug dynamically, you can attach the debugger into the app is running on Chrome. To try, let open Chrome and key http://localhost:8100 to running the app. Then go to VS Code >> click debugger icon >> select Attach Ionic on Chrome option >> click run (or press F5). It will open a debug player like below, and when you click an item in My First List page, it will jump to the break point.


7. You can change source code and see this change updated to running app on Chrome. That's great point of Ionic which can help to speed up developing your application. I love it due to this live update feature.

Happy coding! Any comment is welcome.

Saturday, September 16, 2017

WAMP 64 Bits + Free SSL (Let's Encrypt)

WAMP likes XAMPP, they are free tools packaged Apache, MySQL / Maria DB & PHP together. XAMP also has other services / applications such as FileZilla, Mercury & Tomcat and it also can run on Linux & OS-X. While WAMP just runs on Windows. However for Windows' users, I recommend WAMP because it is designed for Windows only, so it has some advanced features for Windows but XAMPP doesn't have, especially it has Windows 64 bits version while XAMPP has only 32 bits version for Windows.

1. Install

To install Wamp 64 bits, you can download it from wampserver.aviatechno.net. You should install it as Administrator. You also must install all Visual C++ redistributable packages (x86 + x64) (32bits  + 64bits) for running Wamp 64.

After installing & running, if its system tray icon is green, it's ok. Let choose versions for Apache, PHP & MySQL which you want for your websites.


2. Configure auto start up

For live server, you need to configure Apache & MySQL auto start up when the server restarts.
Go to Windows Services, find wampapache64 & wampmysqld64 services then set them Automatic startup.


Next, right click on Wamp tray icon, select Wamp Settings menu >> select Wampserver Homepage at startup.


3. Enable SSL (HTTPS)

The latest Wamp 64 has already included Open SSL built-in, so you don't need to install Open SSL. Below are steps to enable it for Apache:

  • Open httpd.conf file, uncomment the following lines:
LoadModule ssl_module modules/mod_ssl.so
Include conf/extra/httpd-ssl.conf
LoadModule socache_shmcb_module modules/mod_socache_shmcb.so

  • Open php.ini file, uncomment the following line:
extension=php_openssl.dll

4. Add virtual host for your website

Open httpd-vhosts.conf file and add a virtual host running on HTTP (port 80) for your website, below is an example:
<VirtualHost *:80>
 ServerName mydomain.com
 ServerAlias www.mydomain.com
 DocumentRoot "c:/mywebsites/www.mydomain.com"
 <Directory  "c:/mywebsites/www.mydomain.com/">
  Options Indexes FollowSymLinks
        AllowOverride all
        Order Deny,Allow
        Allow from all
        Require all granted
 </Directory>
 ErrorLog "logs/mydomain.com-error.log"
 CustomLog "logs/mydomain.com-access.log" common
 Alias /.well-known c:/mywebsites/www.mydomain.com/.well-known
</VirtualHost>
In which, .wellknow folder will be used to store a key to challenge with Let's Encrypt server for creating your free SSL certificate in next step.

To check if you made right syntax, open cmd tool, go to Apache bin folder and run:
httpd –t
If the syntax is correct, let restart Apache service, then check your website on a browser. You can read my article "Creating Multiple Virtual Sites on a WAMP Server" for more info on creating virtual hosts.

5. Create free SSL certificate with Let's Encrypt

You can read my article Free SSL Certificate with Let's Encrypt to know about Let's Encrypt and tools to create free SSL certificate. In this post, I will show you how to do this with letsencrypt-win-simple tool.

Download latest letsencrypt-win-simple tool from its release page. Unpack it into a folder, for example: C:\letsencrypt-win-simple. Open cmd tool as Administrator, cd to this folder and run letsencrypt.exe, its interface will show as below:


Key N then key 4, it will ask you enter host names (domain names) as the following screen:


Let key your domain name, for example: www.mydomain.com, then it will require you enter the root folder containing your website, for example: c:\mywebsites\www.mydomain.com. After that, the tool will do a process to create a key in the folder c:\mywebsites\www.mydomain.com\.well-known\acme-challenge, then challenge with letsencrypt.org to authorize and create certificates. These certificates are in the folder: C:\ProgramData\letsencrypt-win-simple\httpsacme-v01.api.letsencrypt.org. We will use them to create virtual host running on port 443 (HTTPS).

On finishing, the tool will ask you to create a scheduled task running on 9 am every day to renew your certificate automatically when it is used 60 days (30 days before expiry), see the following picture. With this scheduled task, you can sleep well :)


6. Add virtual host HTTPS for your website

Now you are ready to up & run your HTTPS website, let create a virtual host running on port 443 with SSL certificates for it, see the following example:
<VirtualHost *:443>
 ServerName mydomain.com
 ServerAlias www.mydomain.com
 DocumentRoot "c:/mywebsites/www.mydomain.com"
 <Directory  "c:/mywebsites/www.mydomain.com/">
  Options Indexes FollowSymLinks
        AllowOverride all
        Order Deny,Allow
        Allow from all
        Require all granted
 </Directory>
 ErrorLog "logs/mydomain.com-error.log"
 CustomLog "logs/mydomain.com-access.log" common

 SSLEngine on
    SSLCertificateFile "C:/ProgramData/letsencrypt-win-simple/httpsacme-v01.api.letsencrypt.org/www.mydomain.com-crt.pem"
    SSLCertificateKeyFile "C:/ProgramData/letsencrypt-win-simple/httpsacme-v01.api.letsencrypt.org/www.mydomain.com-key.pem"
    SSLCertificateChainFile "C:/ProgramData/letsencrypt-win-simple/httpsacme-v01.api.letsencrypt.org/ca-<hex code>-crt.pem"
</VirtualHost>
Restart Apache service then check https://www.mydomain.com on a browser (for example Chrome). If it has the symbol https with green, it's ok. Congratulation!

7. Make some securities for Wamp 

Remember to restart Wamp's services after configuration ๐Ÿ˜ƒ

7.1 Hide server info to prevent bad guys mining it

Open httpd-default.conf file, find & change parameters the following:
ServerSignature Off
ServerTokens Prod
7.2 Make sure  directories / files outside of the document root (website) are not allowed to access

Open httpd.conf file, check if the content of directory tag is like the following:
<Directory />
     Order Deny,Allow
     Deny from all
     Options None
     AllowOverride None
</Directory>
or
<Directory />
     AllowOverride none
     Require all denied
</Directory>

7.3 Reduce time out to prevent DoS attacks

Open httpd-default.conf file, find & set the following parameter:
TimeOut 60
You can set it less than, for example 30.

7.4 Set password for MySQL
  • Left click Wamp's system tray icon.
  • Select MySQL > MySQL console menu.
  • Press Enter on the console opened.
  • Key SET PASSWORD FOR root@localhost=PASSWORD('your_password'); (change your_password to your private password). They press Enter.
7.5 Change permissions for folders in your website

Normally, I prefer to set all folders in my website to allow Read & Execute permission only except some folders having Write permission. To check what user runs Apache, let open Windows Task Manager and click Details tab, and see in User name column:


It often is SYSTEM user. To remove Write permission, right click on your web root folder >> select Properties menu >> click Security tab >> click Edit button >> select SYSTEM user >> on Write permission row, untick on Allow column, tick Deny column:


If you cannot edit on Allow column, back to your web root folder >> select Properties menu >> click Security tab >> click Advanced button >> click Disable inheritance button on SYSTEM user having Full control access inheriting from parent folder. Then you can edit in above step.

For folders which you need to write (upload), just enable Write permission for SYSTEM user on those folders.

That's all for tonight. Bye and see you next post. Any comment is welcome!
Good night!


Thursday, August 17, 2017

Joomla 3: setup to allow registered user to create a post in frontend

Froom Joomla 3.x, you can setup to allow registered user to create a post in frontend without installing any third party extension. Below are steps:

1. Setup ACL (Access Control List) to allow Registered user to create article. Go to System >> Global Configuration >> Articles >> Permission, set Action of Registered to Allowed:


2. Create a menu in frontend for user posting. Remember to select Menu Item Type as Articles >> Create Article and select Access right as Registered:


Now your registered user can create a post, by logging in and clicking the menu (e.g. Create a Post):


However, you should do more steps to customize TinyMCE editor as also as allow user posting HTML content (by default registered user can only post plain text).

3. Enable registered user posting HTML content. Go to System >> Global Configuration >> Text Filters, set Registered group to No Filtering:


4. Customize buttons of TinyMCE editor. Go to Extensions >> Plugins, search TinyMCE then click to configure it:

Open Set 1, drag & drop buttons which you want to appear in the editor for registered user:


To allow empty tags (e.g. <div class="my control class"></div>) in the input content, you can set a value div[*] for Extended Valid Elements field of the TinyMCE editor plugin.

To disable / enable editor xtd buttons like Article, Image etc., you can search its plugin then configure them. For example, to disable button Article (last picture in step 2) for registered user, go to Extensions >> Plugins, search article then click to configure its Access to Special:

5. To receive a notification email when a user create new article, you can use NotificationAry plugin. It is free, just install and enable it. Here is mine:


Yeah! Now you can setup a blog with many users can create articles from frontend then you will get an email notification when having new article and able to approve (publish) it via the link inside the email.

Have dream website! Any comment is welcome!




Thursday, August 3, 2017

Joomla 3.x: Create home page with different content for guests & registered users

When you develop a web application by Joomla 3.x, you may have a need to customize a page with different content for guests & registered users.

For example, I will create a sample web site with different top menu and home page content for guests & registered users. Below are steps:

1. Install & enable OSD Content restriction plugin. This plugin will help to create content in articles based on user access group. So we will use it to load different content for user groups.

2. Create an article for home page. In this article, we input content for guest and non-guest. The following sample will show "This content is only visible to guest users, and NOT visible to registered/logged in users." for guest, and show "This content is NOT visible to guest users. Only logged-in users will be able to see it."  plus the content of a customize HTML module for non-guest (logged user):

3. Create a menu for guest, e.g. Guest Menu. Set up Home menu as Single Article pointing to the article in step 2.

4. Create a menu for non-guest, e.g. Author Menu, all its menu items are set to Registered or other group required logged. Here is a sample:

5. Create modules for showing Guest Menu & Author Menu, put them in same position. Set Access of Guest Menu to GuestAuthor Menu to Public.


6. Done! Let see the home page for guest:

7. Here is the home page for non-guest (logged user):

Yeah! Based on this, you can do what ever you want. Good luck.

Any comment is welcome!

Sunday, July 23, 2017

Playing with template in Joomla 3.x

Template is the heart of Joomla. Almost of things which you need for your website, just do them in your template. In the template, you can override other extensions (components & modules)  without touching to their source code. So you can easily update new versions of the extensions or Joomla without impacting to the front end of your website. This is very important because Joomla community often releases security fixes which you should update immediately for your website if any.

In this article, I will collect & introduce to you some tips which you can play with Joomla's template to setup your website beautifully, quickly and security.

1. Uninstall unnecessary templates

If you don't use a template, let remove it. Less code that means your website is more lightweight, faster and more security. Below the steps to install a template:
  • Go to menu Extensions >> Manage >> Manage
  • Click Search Tools button >> select type as Template >> click the check box of a template wanted to install >> click Uninstall button.

This will remove permanently all source code of the template from your site.

2. Edit source code of a template, code your website on mobile devices

Joomla 3.x provides an editor for editing source code of any template. It is very powerful. I think it is a strength of Joomla against the other CMS. With this editor, you can code your website when you are travelling on mobile devices like smart phone or tablet.
To open the editor for your template, go to Extensions >> Templates >> Templates >> click on the template name:
It will open a screen with files & folders of source code in left side. Here you can do many actions via buttons on the top. Click Documentation button if you want to learn more about this editor. To edit a file, click on its name:


Above are buttons I often use. The right side is source code of selected file. To change for editing the other file, just click on its name. Soo cool ๐Ÿ‘

3. Compile LESS file

Another weapon of Joomla is it supports to compile LESS file directly on the template editor. You don't need to install any extra tool. To know what is LESS, let read: http://lesscss.org.

LESS files are often in less folder. If you open a LESS file on the editor, it will show Compile LESS button. After compiling, it will translate into a CSS file corresponding with LESS file in css folder. The template file uses CSS file to present its styles.


4. Create overrides for extensions

In manual way, you must copy the original PHP file from the source of extension into a proper place in your template directory. The correct directory structure for your override file is:
templates/TEMPLATE_NAME/html/EXTENSION_NAME/VIEW_NAME/FILE_NAME.php
You can read here for an example.

However in Joomla 3.x, you can also create overrides for extensions by clicking Create Overrides tab then click an extension (module, component, layout) which you want to override in the template manager.


5. Customize Protostar template

As a beginner of Joomla template coding, one of the fastest way is studying & customizing an existing template. What is good template for you starting? I recommend the Protostar template. It is one of the two front-end templates included with a Joomla 3.x source code. Protostar likes a blank responsive template, it is simple but displays well on many devices. So you can build your own template from this template with a basic knowledge about PHP, JavaScript & CSS.

In this section, I give an example on how to create a mobile menu which displays all submenus without touching on parent menu. By default, a submenu will be showed when hovering on parent menu. You can do this action on desktop with mouse, but cannot in a mobile with touching screen. It causes your submenu is never reached on the mobile.

By adding below code into template.less file (in @media query for mobile screen) and re-compile it in the template editor, you will have a proper menu on mobile:

@media (max-width: 480px) {
//start changes
    .navigation .nav-child {
        display: block;
        position: unset;
        float: unset;
        border: unset;
        box-shadow: unset;

        &:before {
            display: none;
        }
    }
    .navigation .nav > li > .nav-child {
        &:before {
            display: none;
        }
    }
//end changes
    .item-info > span {
        display:block;
    }
    .blog-item .pull-right.item-image {
        margin:0 0 18px 0;
    }
    .blog-item .pull-left.item-image {
        margin:0 0 18px 0;
        float:none;
    }
}

Here are screens for desktop and mobile:

It's time to go to bed. Bye everyone! Any comment is welcome.

Friday, July 14, 2017

Learn Selenium (a Test Automation Tool) with C# - Part 2

In this part, we will learn how to a test application to do login Facebook automatically.

For beginning, I want to introduce about XPath. It is a major element in the XSLT standard. XPath can be used to navigate through elements and attributes in an XML document. You can read more about it on here.

In Selenium we will use XPath to find element(s) then make action according to the scenarios for testing.

If you are familiar with FireFox, you can use Firebug and FirePath add-on. They are very popular and strong tools for FireFox web developer. You just need to install them, open a website which you want to test, open Firebug >> click FirePath tab >> click the pointer and select an element on the page for getting its XPath value. See the following picture for example:


If your are familiar with Chrome, you can use XPath-Helper extension. But I recommend you use a built-in function of Chrome DevTools. Just open the website, press F12 then press Ctrl + F to enable DOM searching box. Using pointer to point an element then key a XPath value in to searching box for finding matched elements. If the matching is only 1, it is the XPath for this element.


For searching, you can try the first search with syntax:  .//*[@attribute-name='value']
For example a element having id="email", you can try: .//*[@id='email']

Another way on Chrome is using Console panel of Chrome DevTools with the function $x("XPath value"). For example: $x(".//*[@id='email']")

OK, that's enough for XPath. It's time to code our test application. Yes, let see the code first and I will explain later:

using OpenQA.Selenium;
using OpenQA.Selenium.Chrome;

namespace SeleniumTutorial
{
    class Program
    {
        static void Main(string[] args)
        {
            IWebDriver wd = new ChromeDriver(@"E:\working\abc\SeleniumTutorial");
            wd.Url = "https://www.facebook.com/";
            wd.Manage().Window.Maximize();
            wd.FindElement(By.XPath(@".//*[@id='email']")).SendKeys("your_email@domain.com");
            wd.FindElement(By.XPath(@".//*[@id='pass']")).SendKeys("your_password");
            wd.FindElement(By.XPath(@"//input[@value='ฤฤƒng nhแบญp']")).Click();

        }
    }
}


Comparing with Part 1, the blue codes are new. In which:
wd.Manage().Window.Maximize(); ==> to maximize the browser window

Next 2 lines are for finding email box, password box and input (SendKeys) its values. The last line is for finding login button (it depends on the language used on the browser, mine is Vietnamese ). See below picture for more understanding:


Why I don't use @id to find login button? Because I recognize that Facebook changes this id frequently. So I use its value instead of.

Walking through Part 1 & Part 2, I believe that you have enough basic knowledge to start your automation test application with Selenium. Let's practice.

Happy coding! Any comment is welcome.



Thursday, July 6, 2017

Learn Selenium (a Test Automation Tool) with C# - Part 1

Selenium is a set of different software tools each with a different approach to supporting test automation. You can read more about Selenium via its website: http://www.seleniumhq.org/docs/01_introducing_selenium.jsp#introducing-selenium

Selenium has 2 version 1.0 and 2.0. During this article, I just mention about Selenium 2.0. Basically, it is used for testing web application by automation program written by Java, C#, Python... To do that, the test application (created by a tester) will control the browser which the web application (need to be tested) runs on via Selenium webdriver. The browser can be Chrome, FireFox, Internet Explorer... Each browser has a corresponding webdriver to control it.

In this post, I want to introduce Selenium with C#. To start, you must install MS Visual Studio - MSSV (2015 community is preferred) and download Selenium client & webdriver. Below are steps to create

1. Open MSVS and create new C# Windows project, see the following picture for example:

2. Add references for Selenium client & webdriver. There are 2 ways.
     2.1 Use Add Reference menu:



     2.2 Use Manage NuGet Packages menu:



I prefer 2.1 way. If you use Chrome Webdriver, you need to download the latest version from: http://chromedriver.storage.googleapis.com/index.html and unpack it into your project folder. Now you can create a simple test application.

3. Code your test application. Below is simple code for opening Chrome with google.com website
using OpenQA.Selenium;
using OpenQA.Selenium.Chrome;

namespace SeleniumTutorial
{
    class Program
    {
        static void Main(string[] args)
        {
            IWebDriver wd = new ChromeDriver(@"E:\working\abc\SeleniumTutorial");
            wd.Url = "http://google.com";
        }
    }
}


In which, @"E:\working\abc\SeleniumTutorial" is the folder containing Chrome Webdriver (chromedriver.exe).

Yeah! We already knew how to start a C# project with Selenium. I think that's enough for Part 1. In Part 2, we will learn more details how to code a test application with some real test scenarios, for example how to login Facebook automatically.

Any comment is welcome. Have a great day!

Subscribe to RSS Feed Follow me on Twitter!