SQL math

What the result of these SQL Statements:

SELECT 1/2, 2 - (2 * (1/2));

SELECT 1 * 1.52, 1.0 * 1.52, 1.0 * 1.520, 1.00 * 1.520;

Please try with any kind of database software!

Using PHP Radius Extension

The FreeRADIUS server software package includes several tools to assist in testing and using the server, some pf them is Radclient and Radtest. To work with applications such as Web inventory Systems, the system has to connect with Radius server using Radius Client. Today I use PHP Radius Extension as Radius client.

PHP Radius client library
This package is PECL extension for Radius (Remote Authentication Dial In User Service). This extension is based on the libradius of FreeBSD. It allows clients to perform authentication and accounting by means of network requests to remote servers.


Auth_RADIUS
This package is a php wrapper around the php radius extension. There are different Classes for the different authentication methods available through RADIUS. If we are using CHAP-MD5 or MS-CHAP authentication, we will also need the Crypt_CHAP package. In addition, if using MS-CHAP authentication, we will need to enable the mhash and mcrypt extension in php.ini. Documentation of the radius extension is available through the PHP manual at http://www.php.net/radius

In previous install link, my sistem has these modules installed
# php -m
[PHP Modules]
Core
date
ereg
gd
libxml
mysql
mysqlnd
pcre
Reflection
session
SPL
standard

I started the installation with the following sequence:

1. Install Auth_Radius a PEAR wrapper classes for the RADIUS PECL.
#/usr/ports/net/pear-Auth_RADIUS
#make
===> Registering installation for pear-1.9.1
===> Returning to build of pear-Auth_RADIUS-1.0.6
===> Configuring for pear-Auth_RADIUS-1.0.6
===> Registering installation for pecl-radius-1.2.5
#make install
----------------------------------------------------------------------
Libraries have been installed in:
/usr/ports/net/pecl-radius/work/radius-1.2.5/modules
****************************************************************************
The following line has been added to your /usr/local/etc/php/extensions.ini
configuration file to automatically load the installed extension:
extension=radius.so
****************************************************************************
===> Returning to build of pear-Auth_RADIUS-1.0.6
===> Generating packing list
===> Generating temporary packing list
===> Checking if net/pear-Auth_RADIUS already installed
===> Installing examples in /usr/local/share/examples/pear/Auth_RADIUS.
install ok: channel://pear.php.net/Auth_RADIUS-1.0.6
===> Registering installation for pear-Auth_RADIUS-1.0.6

# make clean
===> Cleaning for pear-1.9.1 ###PEAR framework for PHP
===> Cleaning for php5-5.3.3_2
===> Cleaning for pecl-radius-1.2.5 ###Radius client library for PHP
===> Cleaning for php5-xml-5.3.3_2
===> Cleaning for pear-Auth_RADIUS-1.0.6 ###PEAR wrapper classes for the RADIUS PECL


2. Install additional package
#/usr/ports/security/pear-Crypt_CHAP
extension=mcrypt.so
extension=hash.so

3. List modules installed
# php -m
[PHP Modules]
Core
date
ereg
gd
hash
libxml
mcrypt
mhash
mysql
mysqlnd
pcre
radius
Reflection
session
SPL
standard
xml

# pear list
Installed packages, channel pear.php.net:
=========================================
Package Version State
Archive_Tar 1.3.7 stable
Auth_RADIUS 1.0.6 stable
Console_Getopt 1.2.3 stable
Crypt_CHAP 1.0.2 stable ### here version 1.0.2
PEAR 1.9.1 stable
Structures_Graph 1.0.3 stable


4.I have to upgrade Crypt_CHAP (replace mhash function to hash function, because I'm on php5.3)
# pear upgrade
downloading Crypt_CHAP-1.5.0.tar ...
Starting to download Crypt_CHAP-1.5.0.tar (Unknown size)
........done: 22,528 bytes
upgrade ok: channel://pear.php.net/Crypt_CHAP-1.5.0
Installed packages, channel pear.php.net:
=========================================
Package Version State
Archive_Tar 1.3.7 stable
Auth_RADIUS 1.0.6 stable
Console_Getopt 1.2.3 stable
Crypt_CHAP 1.5.0 stable
PEAR 1.9.1 stable
Structures_Graph 1.0.3 stable

5. Restart Apache service
#apachectl graceful

6. Start Radius in debug mode



7. Test authentication



8. Use sample number 2 from here and modify arguments as needed, and test it. It works.

Installation of Mondes' LAB1

Steps to install Mondes' LAB1. This was done remotely via telnet, from 11.50pm to 02.30am.

1. Install Apache web server with prefork MPM (apache-2.0.63_16)
#cd /usr/ports/www/apache20 (released 2008-01-19)
#make install clean
(current Apache is v2.2.16, released 2010-07-25)


2. Install current version of PHP5 (php5-5.3.3_2, released 2010-07-22)
#cd /usr/ports/lang/php5
#make install clean
***************************************************************
Make sure index.php is part of your DirectoryIndex.
You should add the following to your Apache configuration file:
AddType application/x-httpd-php .php
AddType application/x-httpd-php-source .phps
***************************************************************
This port has installed the following files which may act as network servers and may therefore pose a remote security risk to the system.
/usr/local/libexec/apache2/libphp5.so
/usr/local/bin/php
/usr/local/bin/php-cgi

AddType application/x-httpd-php .php <<.html>>
AddType application/x-httpd-php-source .phps


3. Install mysql shared extension for php (php5-mysql-5.3.3_2)
#cd /usr/ports/databases/php5-mysql
#make install clean
***************************************************************
The following line has been added to your /usr/local/etc/php/extensions.ini configuration file to automatically load the installed extension:
extension=mysql.so
***************************************************************


4. Install session shared extension for php (php5-session-5.3.3_2)
#cd /usr/ports/www/php5-session
#make install clean
***************************************************************
The following line has been added to your /usr/local/etc/php/extensions.ini
configuration file to automatically load the installed extension:
extension=session.so
***************************************************************


5. Install gd shared extension for php (php5-session-5.3.3_2)
#cd /usr/ports/graphics/php5-gd
#make install clean
****************************************************************************
The following line has been added to your /usr/local/etc/php/extensions.ini
configuration file to automatically load the installed extension:
extension=gd.so
****************************************************************************

6. Install text-based web browser
#cd /usr/local/www/lynx
#make install clean
This port has installed the following files which may act as network
servers and may therefore pose a remote security risk to the system.
/usr/local/bin/lynx


7. Make FreeBSD reread the files in the path so it knows what's there
#rehash


8. Adding PHP5 module to Apache
#cd /usr/local/etc/apache2/
===>modify httpd.conf


9. Create test page

# echo ""  >> /usr/local/www/data/tester.php

10.Test the installation
#apachectl start
#lynx localhost/tester.php

ready for web management (RADIUS and Monitoring).

Linq to NHibernate

My previous codes show the use of NHibernate and Fluent NHibernate as OR/M with .NET framework. Next, I would refer to Fluent NHibernate and use Linq.

1. Download NHibernate.Linq-2.1.2-GA-Bin to get NHibernate.Linq.dll.
2. Add reference to NHibernate.Linq.dll in this file that we work with, and in the beginning add using NHibernate.Linq;
3. At line 43, add this:

  var x = (from p in session.Linq<Personal>() where p.Member== "Y" select p).Take(5);
  Console.WriteLine("Output #1:");
  foreach (var a in x)
    Console.WriteLine("id= {0}, ket= {1}", a.Id, a.Sex);

  var r = from p in session.Linq<Personal>()
          group p by p.Sex into g
          select new { sex = g.Key, count = g.Count() };
  Console.WriteLine("Output #2:");
  foreach (var a in r)
    Console.WriteLine("Sex = {0}, Count= {1}", a.sex, a.count);

4. In this sample, I add a new table and make mapping to database.

public class Personal
    {
        public virtual string Id { get; private set; }
        public virtual string Member { get; set; }
        public virtual string Sex { get; set; }
    }

public class PersonalMap : ClassMap<Personal>
{
  public PersonalMap()
  {
    Table("employees");
    Id(x => x.Id).Column("pk");
    Map(x => x.Member, "member");
    Map(x => x.Sex, "sex");
  }
}

Below the output, and it's time for me to sleep....good morning..

LINQ to MySQL with DbLinq

Language-Integrated Query (LINQ), allows .NET programs to connect to databases. It is an Object-Relational mapping tool, with some similarities to Hibernate or LLBLGen. LINQ is type-safe, queries get compiled into MSIL on the fly, and your C# WHERE clauses are translated into SQL and sent to SQL server for execution. In short, it makes design of data access layers safer and faster.

Today I have work with LINQ to MySQL, and arrived at DbLinq.
DbLinq is the LINQ provider that allows to use common databases with an API close to Linq to SQL. DbLinq is a reimplementation of System.Data.Linq.dll for use with SQL servers. It supports MySQL, Oracle, PostgreSQL, SQLite, Ingres, Firebird, and SQL Server.

This code use DbLinq 0.20.1 (released 2010-04-16).
1. Download and extract DbLinq to c:\DbLinq-0.20.1
2. Copy run_myMetal.bat to runme.bat, and modify it to
DbMetal.exe /conn:"server=localhost;user id=myname;password=a; port=3306; database=mydb" /provider=MySql /code:FerDBLinq.cs /language:C#
4. Execute runme.bat so we get FerDBLinq.cs. Copy the file into your project folder then use Add Existing items from Solution Explorer.

5. Add reference to DbLinq.dll, DbLinq.MySql.dll
6. Open FerDBLing.cs file, add these in the beginning:

using DbLinq.MySql;
using DbLinq.Data.Linq;
using System.Data.Linq.Mapping;
using MySql.Data.MySqlClient;

Here code snippets that follows:

public partial class myDBS: MySqlDataContext
{
    #region Extensibility Method Declarations
  partial void OnCreated();
    #endregion
    public myDBS(string conStr) : base(new MySqlConnection(conStr)) { }
    public myDBS(MySqlConnection conn): base(conn){}

    public DbLinq.Data.Linq.Table<BUkUBeSaR> BUkUBeSaR
    {
      get { return this.GetTable<BUkUBeSaR>();}
    }

5. Creata a function for testing purpose.

private void linqing()
{
  using (MySqlConnection conn = new MySqlConnection(GconString))
  {
    try
    {
      conn.Open();
      myDBS db = new myDBS(conn);

      var q = (from p in db.PerSoNaL where p.member=="Y";
      Console.WriteLine("Output #1:");
      foreach (var a in q)
        Console.WriteLine("id= {0}, ket= {1}", a.PK, a.sex);
 
      var r = from p in db.PerSoNaL group p by p.sex into g
              select new { sex = g.Key, Count = g.Count() };
      Console.WriteLine("Output #2:");
      ObjectDumper.Write(r);

      var t = db.PerSoNaL.GroupBy(p => p.sex);
      var u = t.Select(g => new {g.Key, Count = g.Count()});
      Console.WriteLine("Output #3:");
      foreach (var a in u)
          Console.WriteLine("sex={0}, count={1}", a.Key, a.Count);

      int cnt = (from p in db.PerSoNaL group p by p.sex).Count();
      Console.WriteLine("Output #4:\nCount={0}", cnt);

      var s=db.PerSoNaL.GroupBy(c=> c.sex).ToList().AsQueryable();
      Console.WriteLine("Output #5:");
      foreach (var a in s)
         Console.WriteLine("sex "+a.Key+ ", count=" +a.Count());
      }
      catch (Exception ex)
      {
         MessageBox.Show(ex.Message);
       }
    }
}

The output:
Dblinq works for me, but I could not resolve about the count(), for Male(L) and Female(P) always return 1. I've read issue 157 and 158, but still locked. Do you have any idea?

Fluent NHibernate with MySQL

NHibernate is an Object Relational Mapping framework, which (as ORM states) maps between relational data and objects. It defines its mappings in an XML format called HBM, each class has a corresponding HBM XML file that maps it to a particular structure in the database.

Fluent NHibernate offers an alternative to NHibernate's standard XML mapping files. Rather than writing XML documents (.hbm.xml files), Fluent NHibernate lets you write mappings in strongly typed C# code. This allows for easy refactoring, improved readability and more concise code. Fluent, XML-less, compile safe, automated, convention-based mappings for NHibernate.

Fluent NHibernate is external to the NHibernate Core, but is fully compatible with NHibernate version 2.1, and is experimentally compatible with NHibernate trunk.

Why replace HBM.XML? While the separation of code and XML is nice, it can lead to several undesirable situations.

• Due to XML not being evaluated by the compiler, you can rename properties in your classes that aren't updated in your mappings.
• XML is verbose; NHibernate has gradually reduced the mandatory XML elements, but you still can't escape the verbosity of XML.
• Repetitive mappings - NHibernate HBM mappings can become quite verbose if you find yourself specifying the same rules over again. For example if you need to ensure all string properties mustn't be nullable and should have a length of 1000, and all ints must have a default value of -1.

To counter these issues Fluent NHibernate does by moving mappings into actual code, so they're compiled along with the rest of your application; rename refactorings will alter your mappings just like they should, and the compiler will fail on any typos. As for the repetition, Fluent NHibernate has a conventional configuration system, where you can specify patterns for overriding naming conventions and many other things; you set how things should be named once, then Fluent NHibernate does the rest.

My post before, shows an example of using NHibernate. Below we modify the previous code to work with Fluent NHibernate.

1. Download fluentnhibernate-1.1.zip then extract.
2. Create a console application using Visual Studio and named as FluentNHibernateSample.
3. Add reference to FluentNHibernate.dll, NHibernate.ByteCode.Castle.dll, NHibernate.dll. In this sample we work with MySQL, so add MySql.Data.dll and remember to set Copy Local property to true.
4. Here the modified version of my previous code:

using System;
using System.Collections.Generic;
using System.Collections;
using MySql.Data.MySqlClient;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate;
using NHibernate.Cfg;
using System.Linq;
using FluentNHibernate.Mapping;

namespace FluentNHibernateSample
{
  class Program
  {
    static void Main(string[] args)
    {
       FluentConfiguration config = Fluently.Configure()
          .Database(MySQLConfiguration.Standard
          .ConnectionString(c => c
             .Server("localhost")
             .Database("mydb")
             .Username("myname")
             .Password("mypass")));
       ISessionFactory factory = config.Mappings(m =>
          m.FluentMappings.AddFromAssemblyOf<Coba>())
          .BuildSessionFactory();

       var session = factory.OpenSession();
       System.Collections.IList myRows = session.CreateCriteria(typeof(Coba)).List();
       foreach (Coba item in myRows)
         Console.WriteLine("Id:{0} - Ket:{1}", item.Id, item.Ket);

       IQuery query = session.CreateQuery("from Coba order by ket desc");
       IList<Coba> dta = query.List<Coba>();
       foreach (Coba item in dta)
         Console.WriteLine("Id:{0} - Ket:{1}", item.Id, item.Ket);

       query = session.CreateQuery("FROM Coba WHERE kode=:kode");
       query.SetString("kode", "19");
       foreach (Coba item in query.List<Coba>())
         Console.WriteLine("\nResult:\nID:{0} with Ket:{1}", item.Id, item.Ket);
       session.close();
       Console.ReadKey();
     }
   }
   public class Coba
   {
     public virtual string Id{ get; private set; }
     public virtual string Ket {get; set;}
   }

   public class CobaMap : ClassMap<Coba>
   {
     public CobaMap()
     {
        Table("masjob");
        Id(x => x.Id).Column("kode");
        Map(x => x.Ket, "ket");
     }
  }
}

5. Run. We get the same result with my previous code.

The code does not use xml configuration files at all!

Excel Constants Enumeration

When we automate an application such as a Microsoft Office application, the calls to the properties and methods of the Office application's objects must be connected in some way to those objects. The process of connecting property and method calls to the objects that implement those properties and methods is commonly called binding.

Early Binding sets the connections between Excel and the other application early during design time, while Late Binding the connection isn't made until run time.

Early Binding has several advantages, because the Object Model of the other application is known during development, we can make use of Intellisense and the Object Browser to help write code. Late Binding does not allow us to use debugging tools, Intellisense, or Object Browser. The advantage to Late Binding is we do not have to worry about the wrong version of an object library being installed on the user's computer.

While coding for Office automation, I usually record a macro then refer to the commands into mine. We might use some constants, and for Late Binding we have to know the constants value of a constant name.

To get Excel constants enumeration:
1. Open Excel
2. Open Visual Basic Editor, then type the constants name e.g:

  Sub main()
     MsgBox (xlInsideHorizontal)
     MsgBox (xlNone)
  End Sub

Another way:
1. Open Excel
2. Open Visual Basic Editor, then press F2
3. Type the constants in the search box e.g: xlNone
4. Click Search button, we get -4142

Each Office program that supports VBA has a set of built-in constants, with numeric values that represent some state or property. These constants are usually prefixed with the initials of the source program. i.e. 'xl' for Excel, 'ol' for Outlook, 'wd' for Word.