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!
Subscribe to RSS Feed Follow me on Twitter!