Thursday, November 26, 2015

How do you access MariaDB in XAMPP?

We have seen that MariaDB is an off spring of MySQL and when we launch (as administrator) XAMPP from its shortcut shown here:

What we see displayed is the following (it is assumed any service running is stopped and the bottom pane is cleared):

Click Start for MySQL and it is already running as shown here:


MySQL is running on Port 3306.
Click on Shell.

The XAMPP prompt is displayed with the following message.
Setting environment for using XAMPP for Windows.
Jayaram@HODENTEK8 c:\j

When you enter mysql as shown you get the MariaDB prompt as shown

MariaDB syntax is very similar to MySQL syntax as for as its usage is concerned.
Alternately you can do the same from the install directory as shown here:
Microsoft Windows [Version 10.0.10240]
(c) 2015 Microsoft Corporation. All rights reserved.


C:\>cd C:\J\mysql\bin

C:\J\mysql\bin>dir mysql*
 Volume in drive C is TI10672700E
 Volume Serial Number is 16FB-D230

 Directory of C:\J\mysql\bin

10/14/2015  10:44 PM         3,571,528 mysql.exe
10/14/2015  10:45 PM         3,517,768 mysqladmin.exe
10/14/2015  10:45 PM         3,595,080 mysqlbinlog.exe
10/14/2015  10:44 PM         3,515,208 mysqlcheck.exe
10/14/2015  10:45 PM        11,527,496 mysqld.exe
10/14/2015  10:44 PM         3,568,456 mysqldump.exe
10/14/2015  10:16 PM             8,385
10/14/2015  10:16 PM            25,817
10/14/2015  10:16 PM            36,102
10/14/2015  10:45 PM         3,506,504 mysqlimport.exe
10/14/2015  10:45 PM         3,507,528 mysqlshow.exe
10/14/2015  10:45 PM         3,525,448 mysqlslap.exe
10/14/2015  10:44 PM         3,916,616 mysqltest.exe
10/14/2015  10:48 PM         9,949,000 mysqltest_embedded.exe
10/14/2015  10:38 PM         3,899,392 mysql_client_test.exe
10/14/2015  10:48 PM        10,124,104 mysql_client_test_embedded.exe
10/14/2015  10:16 PM             8,799
10/14/2015  10:16 PM             4,417
10/14/2015  10:48 PM         9,804,104 mysql_embedded.exe
10/14/2015  10:46 PM         3,735,368 mysql_install_db.exe
10/14/2015  10:45 PM         3,119,944 mysql_plugin.exe
10/14/2015  10:16 PM             9,565
10/14/2015  10:46 PM         3,124,040 mysql_tzinfo_to_sql.exe
10/14/2015  10:45 PM         3,178,824 mysql_upgrade.exe
10/14/2015  10:46 PM         3,109,192 mysql_upgrade_service.exe
10/14/2015  10:57 PM         1,750,344 mysql_upgrade_wizard.exe
              26 File(s)     95,639,029 bytes
               0 Dir(s)  797,801,631,744 bytes free

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.8-MariaDB binary distribution

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Typ
e '\c' to clear the current input statement.

You can also get to the administrator using the mysqladmin.exe as shown here (truncated portion):

For MariaDB server find contents as shown here:


MariaDB has good documentation. Here is the help on Utility | Use:
MariaDB [(none)]> help Utility
You asked for help about help category: "Utility"
For more information, type 'help ', where is one of the following

MariaDB [(none)]> help USE
Name: 'USE'
USE db_name

The USE db_name statement tells MySQL to use the db_name database as
the default (current) database for subsequent statements. The database
remains the default until the end of the session or another USE
statement is issued:

USE db1;
SELECT COUNT(*) FROM mytable;   # selects from db1.mytable
USE db2;
SELECT COUNT(*) FROM mytable;   # selects from db2.mytable

Here are some queries run on this version:
 MariaDB Version
MariaDB [(none)]> Select Version(), CURRENT_DATE;
| Version()      | CURRENT_DATE |
| 10.1.8-MariaDB | 2015-11-26   |
1 row in set (0.04 sec)

MariaDB [(none)]>

 Find User interacctively
Find the USER() (this is interactively run)
MariaDB [(none)]> SELECT
    -> USER()
    -> ,
| USER()         | CURRENT_DATE |
| ODBC@localhost | 2015-11-26   |
1 row in set (0.00 sec)

MariaDB [(none)]>

There are only two bases in the installation.
Show databases
MariaDB [(none)]> show databases
    -> ;
| Database           |
| information_schema |
| test               |
2 rows in set (0.08 sec)

MariaDB [(none)]>


How do you run a simple query in HeidiSQL?

It is really no different from running a query in SQL Server Management Studio. You need to have an instance of SQL Server running on your computer. Launch HeidiSQL and get connected to the instance.

You will be seeing this (different in your case) displayed in the HeidiSQL user interface. Either you can get connected to a session you created or a new one using the New button at the bottom of the UI.

You can connect to a single database by selecting it from the list as shown. Here Northwind was selected.

The Nov21 sessions gets changed and the changed sessions window opens as shown:


Click on Query to open a window for inserting query related statements as shown:

In the Query Window insert your SQL statement. For example a query such as the one shown:

SELECT TOP 50 [CategoryName]
  FROM [Northwind].[dbo].[Product Sales for 1997]


Click Query and the response will appear below the query window as shown.


The tabbed window in the center has all the necessary tabs like the SAP SQLAnywhere Studio Central.

Wednesday, November 25, 2015

What is an uniqueidentifier?

 Unique Identifiers are used in many places, SQL Server's data type; as GUID in ActiveX Controls, and as a 128 bit IP address in IPV6 although it is not called GUID. Using 128 bits for the IP address makes it possible to assign a whole lot more addresses than IPV4.

Uniqueidentifiers are also called GUIDs(Globally Unique Identifier) which are unique and different from any other. It was introduced in SQL Server 7.0. The GUID is obtained by the function call NewID()or as a hexadecimal formatted string. The unique identifier data type is stored in the computer as a 16-byte (128 bits) binary value. The formatted GUID using Hexadecimal is usually a string represented by groups separated by hyphens:
Here is how the uniqueidentifier obtained by calling the NewID() function.
Declare @myid uniqueidentifier
set @myid=NEWID()

A uniqueidentifier is obtained every time this function is called as shown here:

While it is great for security, it takes up too much space (think of millions of rows with a uniqueidentifier data type column).

More about uniqueidentifier in SQL Servers here.

Saturday, November 21, 2015

How do you connect to SQL Server 2012 using HeidiSQL?

 HeidiSQL is a client application and you can use it to work with databases. If you want to know more about it, or download and install HeidiSQL go here.

Launch HeidiSQL from your desktop or click the HeidiSQL app from the All Apps menu shown.

The Heidi Session Manager is launched as shown.


Click New and choose Session in root folder from the drop-down list as shown.


You can change the session name by over-writing as shown. Herein it is Nov21.


By default the connection is to MySQL using TCP/IP and localhost(

Let us change the Network type to Microsoft SQL Server using TCP/IP by using the drop-down menu shown.


Choose Windows authentication as shown.


Click Open. The connection is refused.


OK. Let us look if the SQL Server has started. Open the Control Panel|...|Services and verify that the server has started. Indeed it is running (option chosen at SQL Server Installation).


Now let us change the Hostname /IP to Hodentek8\RegencyPark (over write
Click Open after changing the hostname.
 You may get a save modifications message.


Click Yes. The program processes this information and the client is now connected to the named instance of SQL Server 2012 as shown.


Now you are cooking!

What is HeidiSQL?

It is a client tool used by web developers for working with MySQL Server, Microsoft SQL databases and PostgreSQL. It is free, Open Source since 9 years of active development and very useful.

What all HeidiSQL can do?

HeidiSQL which is a client application can carry out a lot of data related tasks:
•Connect to multiple servers in one window
•Connect to servers via command line
•Connect via SSH tunnel, or pass SSL settings
•Create and edit tables, views, stored routines, triggers and scheduled events.
•Generate nice SQL-exports, compress these afterwards, or put them on the clipboard.
•Export from one server/database directly to another server/database
•Manage user-privileges
•Import text-files
•Export table rows as CSV, HTML, XML, SQL, LaTeX, Wiki Markup and PHP Array
•Browse and edit table-data using a comfortable grid
•Bulk edit tables (move to db, change engine, collation etc.)
•Batch-insert ascii or binary files into tables
•Write queries with customizable syntax-highlighting and code-completion
•Pretty reformat disordered SQL
•Monitor and kill client-processes
•Find specific text in all tables of all databases of one server
•Optimize and repair tables in a batch manner
•Launch a parallel mysql.exe command line window using your current connection settings

Where do you install HeidiSQL?

Download the HeidiSQL 9.3 Installer here. There are other options as well. Double clicking the executable begins the installation.
Here are some screen shots.

 Agree to license terms and click Next.

 Accept default folder.

Chose not to associate for now.

 Launches HeidiSQL Session if the checkbox is checked.

 Leaves a new app on All Apps in Windows 10

It also enters the Programs and Features list in Control Panel.

Friday, November 20, 2015

What is the difference between MySQL and MariaDB?

MySQL predates MariaDB. In fact MariaDB is a fork of MySQL. MySQL was handed down from Sun Microsystems to Oracle which made it somewhat non-open source ( and a need for an open source version became desirable. MariaDB has found acceptance from the likes of Google( and others. Customers like RedHat,SUSE and WikiPedia have moved away from MySQL.

The reasons are obvious:

  • MariaDB has more storage engines then MySQL.
  • Better speed
  • Has new extensions and features
  • Better test and test suites
  • Fewer warnings and bug
  • Truly Open Source (GPL, LGPL or BSD)
  • Strong Community(

Sunday, November 15, 2015

What is Bitnami?

If you want to work in Open Source, this is where you should look into.

This is a very useful site to keep in touch. It is a great resource if you want to work with Open Source stuff. Millions of developers use this site and you should too. This is great if you are trying to teach to yourself or doing some computer stuff for the non-profits. The pre-packaged apps and development environments are free to use and share.

These are some of the things you can do:
  • Running apps in production
  • Complete software development environment for your team
  • Develop locally, deploy in the cloud
  • Quickly test new apps
  • Running your business
  • Maintain parallel environments
  • Research and learn new technologies
  • Launch apps without extensive expertise
It does cover all OSs, Virtualization Platforms; and Cloud Platforms. It covers the whole universe of computer software.

The number of applications that you can get on to is really impressive. Here is a screen shot of the more popular applications.
Microsoft Web Platform Installer (WPI) is another similar program geared towards all the different Microsoft Technologies although WPI also offers many Open Source programs .

Here and here you have two detailed posts to get on to Microsoft Web Platform Installer.