Blog Archives

Configure unixODBC for use with PHP and MSSQL on Oracle Linux

This short article describes how to configure unixODBC 2.2.11-10.el5 in conjunction with PHP 5.3.3-26. Many forums out there contain articles that describe the absence of php_mssql drivers in the oracle yum repo. There various reason for that; non in which Oracle has a part. No matter what reason you like best, there is a decent alternative by using unixODBC.

To help all the people out that are just looking for a solution I wrote this article. I wont go into depths, ill just describe the major steps with some hints and tips. Happy reading 🙂

The OS version of my virtual box image:

Linux sandboxpinguin 2.6.18-194.0.0.0.3.el5xen #1 SMP Mon Mar 29 18:27:00 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
  1. Make sure you have the latest version of unixODBC installed. If Yum is configured correctly the following command should do the trick.
     yum update unixODBC
  2. Download the freeDTS driver, this is the driver unixODBC will use to connect to mssql. If wget is available on your production environment (remove it) after running the command:
     wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-stable.tgz

    . Make sure you are in an desired location like your home folder.

  3. Unpack the tar-Gzip ball by running the following command
    tar -xf  ./freetds-stable.tgz
  4. Browse into the unpacked folder and run the configure command
    ./configure --with-tdsver=7.2 --enable-msdblib
  5. If the configure ran without any issues you can link/compile the driver by running the command:
    make

    and then

    make install

    and then

    make clean

    Congratulations, you are now the pride owner of the freeDTS driver 🙂

The next part tend to get a bit fuzzy, feel free to ask questions in the comment and ill try to answer them to the best of my ability.

There are allot of articles available on how to configure the unixODBC DSN correctly. Be adviced: the config is specific for your setup and usually needs to be tweaked. In order to enable you to ill explain the concepts of unixODBC. Ill point out some documentation, commands and stuff. Afterward ill have a short tutorial of the steps i used to configure the odbc connection.

  1. unixODBC is configured properly by using the
    odbcinst

    command.

  2. unixODBC will write into:
    odbc.ini

    files and uses input files to do so;

  3. The connection can be tested with osql commands from bash, but! it will use the hidden .odbc.ini file in your profile instead of the /etc/odbc.ini. PHP and odbcinst use the one in /etc/odbc.ini. If the one in your profile works than make sure it is identical to the one located in /etc/ directory. Below how the osql output will look if configured correctly. (charset isnt relevant in this stage)
    screenshot
  4. Documentation on how to use FreeTDS in conjunction with unixODBC can be found here.
    http://www.freetds.org/userguide/odbcconnattr.htm
  5. Documentation on how to use ODBC can be found here
    http://www.unixodbc.org/odbcinst.html
    (ignore the freetds configuration here and use ad4 to figure the settings out for your setup)

Next ill describe my steps in order to make it work.

Configuring the FreeTDS driver

  1. Create the file /etc/odbcDriver.ini
  2. Insert the following in the file (check the paths)
    [FreeTDS]
    Description     = FreeTDS Driver with protocol v5.0
    Driver          = /usr/local/freetds/lib/libtdsodbc.so
    
  3. Create the file /etc/odbc.ini
  4. Insert the following and tweak this to match your environment
    [ExampleSource]
    Description     = FreeTDS Driver with protocol v5.0
    Driver          = /usr/local/freetds/lib/libtdsodbc.so
    Server          = [SERVERIP]
    Port            = [REMOTE_TSQL_PORT]
    ClientCharset   = UTF-8
    TDS_Version     = 7.1
    Database        = [DATABASENAME]
    Trusted_Connection = Yes      # Required with most MSSQL environments.
    
  5. Register the ODBC driver
     odbcinst -i -d -f /etc/odbcDriver.ini 
  6. Register the data source
     odbcinst -i -s -f /etc/odbc.ini

Finally test your config by using the php odbc functions.

<?php
$sql = "select 1 + 5 as outcome";

$conn = odbc_connect("ExampleSource" , "Username", "Password");
$result = odbc_exec($conn, $sql);
$row = odbc_fetch_array($result);
echo $row['outcome'];

Good luck querying 🙂

Compile PHP5.3.6 on OEL5.5 x64

All you need is apache installed, then download php 5.3.6. src and run the following configuration command.

./configure --build=x86_64-redhat-linux-gnu --prefix=/usr --exec-prefix=/usr --bindir=/usr/bin --sbindir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --includedir=/usr/include --libdir=/usr/lib64 --libexecdir=/usr/libexec --localstatedir=/var --sharedstatedir=/usr/com --mandir=/usr/share/man --infodir=/usr/share/info --cache-file=../config.cache --with-libdir=lib64 --with-config-file-path=/etc --with-config-file-scan-dir=/etc/php.d --disable-debug --with-pic --disable-rpath --without-pear --with-bz2 --with-curl --with-exec-dir=/usr/bin --with-freetype-dir=/usr --with-png-dir=/usr --enable-gd-native-ttf --without-gdbm --with-gettext --with-gmp --with-iconv --with-jpeg-dir=/usr --with-openssl --with-pcre-regex --with-libexpat-dir=/usr --with-zlib --with-layout=GNU --enable-exif --enable-ftp --enable-magic-quotes --enable-sockets --enable-sysvsem --enable-sysvshm --enable-sysvmsg --enable-wddx --with-unixODBC=shared,/usr --enable-shmop --enable-calendar --with-libxml-dir=/usr --with-apxs2=/usr/sbin/apxs --with-mysql --with-gd --enable-soap --enable-mbstring --with-xsl --disable-dba --without-unixODBC --disable-xmlreader --disable-xmlwriter

Next note the errors during configuration and install the required packages needed from the install media then rerun the command up till it finishes succesfully.

When the configuration is done, run the “make all” command after which you are requested to run the “make test” command. Then finaly run the “make install” command.

Installing the correct packages might consume some time…

Good luck! 🙂

–If you encounter an “Cannot find libmysqlclient” error you either didnt install all the mysql packages or you need to add –with-libdir=lib64 behind the –with-mysql=/usr/bin entry.

(The mysql module isnt shipped anymore with the distribution!)

SOAP to JSON using PHP

For anyone that wants to translate output from a SOAP webservice using WLSD to JSON… Here is an example script that shows you the basics 🙂

This is a working example.

You need at least PHP 5.1 for this example to work!

<?php
// Setup and connect the soap client object
try{
	$sc = @new SoapClient('http://webservices.oorsprong.org/websamples.countryinfo/CountryInfoService.wso?WSDL');
} catch (Exception $e) {
	echo $e->getMessage();
}

// Fetch the uri parameter //
if(isset($_GET{'c'}) && !empty($_GET{'c'})){
	$country{'name'} = addslashes(ucwords($_GET{'c'}));
}else{
	$country{'name'} = 'FALSE';
}

// Query the soap webservice
$result = $sc->CountryISOCode(array('sCountryName' => $country{'name'}));
$result = $sc->FullCountryInfo( array(sCountryISOCode => $result->CountryISOCodeResult));

// Output the Json code
echo json_encode($result->FullCountryInfoResult);


print('<h2>Functions described in wsdl</h2><br\>');
foreach($sc->__getFunctions() as $key => $value){
	echo "$key => $value <br/>";
}

print('<h2>Types described in wsdl</h2><br\>');
foreach($sc->__getTypes() as $key => $value){
	echo "$key => $value <br/>";
}

?>

Goodluck , and happy scripting! 🙂

Draw dots in images using PHP, XMLHttp, Mysql.

A family member asked me if it was possible to use html, php, mysql to mark spots in an image of the human skeleton during an medical anamnesis collection. He wanted this to easily mark the complaint spots of his patients. He also needed to be able to remove marks when they where faulty and wanted this all to be stored in a database for easy reference and backup.

Seeing the complexity of it, I accepted the challenge he laid out for me.

The most complex part of this was the ‘how to remove’ spots from an image without deleting the actual fixed image of the skeleton. The solution I came up with was using an Fixed background image in HTML and use a transparent overlay that I will be updating using PHP and XMLHttp request headers.

The result of this approach was the following.

All that needs to be done during reporting is merging both images together, which is fairly easy using php.

But because I though someone else might use this script as well, I thought sharing it here with the world was a nice option. The application is endless, from damage reporting to Location reporting, and in the finished from its fairly easy to understand and adjust to your needs.

I added the sourcecode for this script to this post. You need to have a mysql database, php with GD enabled, browser that supports XHTMLRequest (all modern browsers). Simply dump the files into the a php html enabled location and make sure you create the required SQL table (usign the .sql script inside the archive) and have a go with it (you might need to alter the mysql user/pass inside the PHP script)….

image_draw-rar.pdf

Use the “save as” option to grab the file from this site. Rename the file to something.zip, unpack and access the srcfiles inside the rar archive… use winrar to unpack.

Let me know what you think, or if you found it usefull 🙂

p.s. I wasnt able to verify the author of the used skeleton image. If you are/know him, let me know if this application is allowed, and how to publish the credits.

Simple PHP script to walk and print a directory tree

<?php
function walk_dir($dir){
	$relativedir = '.'.$dir;
	if($dh = opendir($relativedir)){
	while(false !== ($file = readdir($dh))){
		if(($file !== '.') && ($file !== '..')){
			if(!is_dir($relativedir.$file)){
				echo '<a href="'.$dir.$file.'" title="'.$file.'">'.$file.'</a>'."\n";
			}else{
				walk_dir($dir.$file.'/');
			}
		}
	}
	}
}

walk_dir('/');
?>

Fixing the monitoring hosts > hosts view in centreon 2.1.4

When opening the Monitoring > Hosts > Hosts view to view all the available and configured hosts in Centreon 2.1.4 you will get a blank result. This happens because there is a coding error in the following file.

/usr/local/centreon/www/include/monitoring/status/Hosts/xml/hostXML.php on line 249

To correct it you need to add a else statement to the if, elseif validation that happens there. So open the file listed above using your fav. editor (vim in my case)

vim /usr/local/centreon/www/include/monitoring/status/Hosts/xml/hostXML.php

Version 2.1.4

Skip to line 249 typing ” :249 ” followed by an enter stroke.
On that line there should be something like;

if (($ndo["last_hard_state_change"] > 0) && ($ndo["last_hard_state_change"] >= $ndo["last_state_change"]))
                        $hard_duration = Duration::toString(time() - $ndo["last_hard_state_change"]);
                else if ($ndo["last_hard_state_change"] > 0)
                        $hard_duration = " N/A ";

Change it to this by adding the part starting from “else”.

if (($ndo["last_hard_state_change"] > 0) && ($ndo["last_hard_state_change"] >= $ndo["last_state_change"]))
                        $hard_duration = Duration::toString(time() - $ndo["last_hard_state_change"]);
                else if ($ndo["last_hard_state_change"] > 0)
                        $hard_duration = " N/A ";
                else
                        $hard_duration = " unknown ";

Version 2.1.8

Goto rule 272 in the same file and locate this piece of code:

if (($ndo["last_hard_state_change"] > 0) && ($ndo["last_hard_state_change"] >= $ndo["last_state_change"]))
                        $hard_duration = Duration::toString(time() - $ndo["last_hard_state_change"]);
                else if ($ndo["last_hard_state_change"] > 0)
                        $hard_duration = " N/A ";

Add the following to make it work.

if (($ndo["last_hard_state_change"] > 0) && ($ndo["last_hard_state_change"] >= $ndo["last_state_change"]))
                        $hard_duration = Duration::toString(time() - $ndo["last_hard_state_change"]);
                else if ($ndo["last_hard_state_change"] > 0)
                        $hard_duration = " N/A ";
                else
                        $hard_duration = "N/A";

This same rule is responsible for the following errors in your apache error_log.

PHP Notice: Undefined variable: hard_duration in /usr/local/centreon/www/include/monitoring/status/Hosts/xml/hostXML.php on line 271, referer: http://centreon.amis.nl/centreon/main.php?p=20102&amp;o=h

This change should fix it for ya.

Grtz, Chris.

Square or any Root programatically?

Ever wondered how to programatically get the root of any number programatically?

A square root of any number in excel can be found using the function “=SQRT(#);” where # is the number you want to have the root from. When you test this with the number 3 (commonly used to calculate complex “real” power consumption of a three fase system) You should get this as an result.

1,732050808

Because a “square” root is the opposite of a square power from that number you can use the following to find the same root using that same power ^ sign.

Square Root = 3^(1/2) = SQRT(#) = 3^0.5

If you add this example in excel as a check value.

=3^(1/2)

You will notice that the answer is also

1,732050808

Using the formula for complex power calculation in PHP will give you

$Cosphi = ($Cosphi > 1) ? "1" : $Cosphi;
$Phases = ($Phases < 2) ? "2" : $Phases;

$Pw = $U . $I . $Cosphi . ($Phases^(1/2));

Yea! Its that easy!! 😉

Resetting the WP-admin account from the commandprompt.

1. Open a bash shell on the linux box. Using SSH is advised.
2. Generate a password MD5 hash using php.

 php -r "print( md5('YourPassWordHere')); print(\"\r\n\");"

3. Copy the 32 bit string that is the result.
4. Connect to mysql

mysql

5. Connect to the correct database;

show databases;
use databasename;

6. make a copy/paste backup of the admin user data.

select * from wp_users where user_login = 'admin';

7. Update the table

update wp_users set user_pass='the_md5_hash_generated_in_step_2/3' where user_login = 'admin';

8. Log into wordpress using the username “admin” and the password you have used.

— Suggestion after the comment by kadimi —

9. Reset you password using the “wordpress dashboard > Users > Your profile > Change password” option.

Templated mail for nagios / centreon.

This command enables you to dynamicly create a nice templated mail.

Save code below into a file inside the /usr/local/nagios/libexec/mailbytpl.php

On linux you can use the vi command to do so; i.e. $>vi /usr/local/nagios/libexec/mailbytpl.php

#!/usr/bin/php

<?php

// Lets define the defaults //
$showman = false;
$tpldata = false;
$debug = false;
$settings['from'] = 'centreon@amis.nl';
$settings['type'] = 'notification';
$settings['host'] = '{hostname?}';
$settings['reply'] = 'NULL';

// Dont change anything below! //
// Find and sort all the commandline arguments //
// Make a distinct difference between known settings and arguments later to be passed into the template //

if(@is_array($argv)){
// Define that the mail setting isnt passed till we realy found it in the argv
$settings['mail'] = false;
foreach($argv as $value){
$keyval = explode("=", $value);
if(array_key_exists('0', $keyval)){
$key = str_replace('--', '', $keyval[0]);
if(array_key_exists('1', $keyval)){
$val = $keyval[1];
}else{
$val = NULL;
}
}
// Default settings we want to fetch //
switch ($key){
case 'mail':
$settings['mail'] = true;
break;
case 'tplfile':
$settings['tplfile'] = $val;
break;
case 'to':
$settings['to'] = $val;
break;
case 'from':
$settings['from'] = $val;
break;
case 'type':
$settings['type'] = $val;
break;
case 'host':
$settings['host'] = $val;
break;
case 'reply':
$settings['reply'] = $val;
case 'verbose':
$debug=true;
break;
default:
                                // Next to the defaults we fetch additional values...
if(!strstr($key, 'mailbytpl.php')){
$arguments[$key] = $val;
}
}
$keyval = '';
$key = '';
$val = '';
}
$arguments['DATE'] = date('Y-m-d H:i:s');
}else{
// If we got no args (default {basename}.ext is allways passed, still need to fix that.//
// Consider this the spaceholder 😉 //
$showman = true;
}
// Validate if all the needed settings where found //
if(!empty($settings['mail']) && !empty($settings['tplfile']) && !empty($settings['to'])){
// Lets try to open the template file//
if($settings['mail']){
if($settings['tplfile']){
//Does the directory exist?//
if(is_dir(dirname($settings['tplfile']))){
if(opendir(dirname($settings['tplfile']))){
if(is_file($settings['tplfile'])){
$tpldata = file($settings['tplfile']);
}else{
die('ERR::Template file doesnt exist or couldnt be opened');
}
}else{
die('ERR::Template file directory coudnt be opened. please verify the rights!');
}
}else{
die('ERR::Template file directory doesnt exist. please verify its existance!');
}
}else{
$showman = true;
}
}else{
$showman = true;
}
// If we have content from the template file, we need to add the information too it //
if($tpldata){
if(count($tpldata) >= 1){
// If we found more then 1 rule we can continue //
// Lets Generate tablerows too place in the template //
$maildata = '';
$cc = '0';
foreach($arguments as $key => $value){
$maildata .= '
<tr>
<td class="datahead'.$cc.'">'.$key.'</td>
<td class="datacontent'.$cc.'">'.$value.'</td>
</tr>
';
$cc++;
}
$maildata .= '<!--Generated by mailbytemplate.php for nagios by AMIS Services BV.-->'."\r\n";
$mailbody ='';
// If there is an element called $DATAROWS$ then insert the pregenerated block//
foreach($tpldata as $lineno => $line){
$pattern = '$DATAROWS$';
$subject = $line;
if(preg_match($pattern, $subject, $matches, PREG_OFFSET_CAPTURE)){
//if there is a match we match it using the arguments//
//Match is found using $matches[0][0]//
$line = str_replace('$DATAROWS$', $maildata, $line);
}
$mailbody .= $line."\r\n";
}
}else{
// We could have loaded an empty file, lets tell the user! //
die('Template file was found to be empty');
}
}
if($mailbody){
                // You can add any header you like...
                $headers ="MIME-Version: 1.0 \r\n";
                $headers.="From: Centreon <".$settings['from'].">\r\n";
                $headers.="Reply-To: ".$settings['reply']." \r\n";
                $headers.="X-Mailer: PHP/".phpversion()."\r\n";
$headers.="Content-type: text/html; charset='us-ascii' \r\n";
$subject =$settings['type'].":: Notification from ".$settings['host']." ...";
mail($settings['to'], $subject, $mailbody, $headers);
}
}else{
$showman = true;
}

if($showman){
// If the mail command isnt found then show the manual //
echo "Using this module is fairly simple, first edit the mainfile mailbytpl.php and alter the defaults \r\n";
echo "Then run the file like the example using the required settings and adding your own using this \r\n";
echo "Format...\r\n";
echo "\r\n\r\n";
echo "{pathtofile}/mailbytpl.php [args]\r\n";
echo "\r\n\r\n";
echo "Valid arguments\r\n";
echo "\t--mail\t\t\t\t\t'Tell the script to execute the mail functionality'\r\n";
echo "\t--tplfile=[path-to-template]\t\t'The path the the template file that should be used by this script'\r\n";
echo "\t--to=[valid@mail.ext]\t\t\t'A valid mailaddress of the receipient,\$CONTACTMAIL$ within nagios\r\n";
echo "\t--from=[valid@mail.ext]\t\t\t'A valid from addres to exclude it from your junkmail folder'\r\n";
echo "\t--reply=[valid@mail.ext]\t\t'Used as reply-to header to redirect mail to different mailboxes. Defaults to 'NULL'\r\n";
echo "\t--type=[NOTIFICATIONTYPE]\t\t'Used in the subject of the mail i.e. [type]::notification for [host]'\r\n";
echo "\t--host=[HOSTNAME]\t\t\t'Used in the subject of the mail i.e. [type]::notification for [host]'\r\n";
echo "\r\n\r\n";
echo "Add your information to the template by adding additional custom entries. using the following syntax;\r\n";
echo "\t--KEY=VALUE\r\n";
echo "\r\n\r\n";
echo "Example for nagios:\r\n";
echo "\$USER1$/mailbytpl --mail --tplfile=\$USER1$\mail.tpl --to=example@mail.nl --from=example@mail.nl --reply=no-reply@mail.nl\r\n";
echo "--type=\$NOTIFICATIONTYPE$ --host=\$HOSTNAME$ --IP=\$HOSTADDRESS$ --DOWNTIME=\$HOSTDOWNTIME$ --YOUROTHERVAR=\$VALUE$ \r\n";
echo "\r\n\r\n\r\nScript by : Chris Gralike\r\nCompany : AMIS Services BV\r\n GPL:2009©\r\n";
}
?>

Next you can configure the command into centreon as a ‘notification’ command that takes the following commands:

–mail                                                         Should be set, enables the mail functionality
–tplfile=[path to template]                  ‘The is the html template that will be used
–to=[valid@email.com]                       Set the receipient
–from=[valid@email.com]                  Set the sender
–reply=[valid@email.com]                  Set the return-path
–type=[NOTIFICATIONTYPE]          used in the subject of the mail.
–host=[HOSTNAME]                           used in the subject of the mail.
–[KEY]=[VALUE]                                  add additional information to the template, these fields are autofilled if used in the template.

example:

$USER1$/mailbytpl --mail --tplfile=\$USER1$\mail.tpl --to=example@mail.nl --from=example@mail.nl --reply=no-reply@mail.nl --type=\$NOTIFICATIONTYPE$ --host=\$HOSTNAME$ --IP=\$HOSTADDRESS$ --DOWNTIME=\$HOSTDOWNTIME$

Next create a template file to point to. I called mine mail.tpl

<html>
<head>
<title>Network notification</title>
<style>
body {background-color:#999; text-align:left; font-family:verdana;}
table{border:1px solid #020245; width:100%;}
th{background-color:#020245; color:#fff; border-bottom:1px solid #020245; text-align:left;}
td{border-bottom:1px solid #020245; background-color:#ccc; font-size:11px;}
tr{height:20px;}
#1{border-style:none; background-color:#ccc;}
.2{width:150px;  font-size:12px; font-weight:bold;}
.3{width:3px;}
</style>
</head>
<body>
<table cellspacing=0 align='center'>
$DATAROWS$</table>
</body>
</html>

Well hope this was usefull for ya 😉

#!/usr/bin/php
<?php
// Lets define the defaults //
$showman = false;
$tpldata = false;
$debug = false;
$settings[‘from’] = ‘centreon@amis.nl’;
$settings[‘type’] = ‘notification’;
$settings[‘host’] = ‘{hostname?}’;
$settings[‘reply’] = ‘NULL’;
// Dont change anything below! //
// Find and sort all the commandline arguments //
// Make a distinct difference between known settings and arguments later to be passed into the template //
if(@is_array($argv)){
        // Define that the mail setting isnt passed till we realy found it in the argv
        $settings[‘mail’] = false;
        foreach($argv as $value){
                $keyval = explode(“=”, $value);
                if(array_key_exists(‘0’, $keyval)){
                        $key = str_replace(‘–‘, ”, $keyval[0]);
                        if(array_key_exists(‘1’, $keyval)){
                                $val = $keyval[1];
                        }else{
                                $val = NULL;
                        }
                }
                // Default settings we want to fetch //
                switch ($key){
                        case ‘mail’:
                                $settings[‘mail’] = true;
                                break;
                        case ‘tplfile’:
                                $settings[‘tplfile’] = $val;
                                break;
                        case ‘to’:
                                $settings[‘to’] = $val;
                                break;
                        case ‘from’:
                                $settings[‘from’] = $val;

Tip : Regex tester / builder.

And even the title is catchy 🙂

Trying to read all the regexp complexity behind a “Email” is a hard thing… Well thank god we have tooling 😀

http://www.ultrapico.com/Expresso.htm

When working with, C#, PHP, VB, OCS (SIP) and others this tool might be a blessing!

Hope it helps you too 😀