The Wahyudiharto's Blog

In a spesific programming case, database synchronization method will needed to accomodate data transfer between 2 or more servers. There are so much techniques we can adopt to make it work. And that's all depends on needs, business requirement, infrastructure availability & of course skill of you're own self.

Anyway, I don't wanna talk about synchronization techniques but actually, this latest article was begun from my previous one - Simple MySQL Replication Using FTP - when I implement automatically SQL file synchronization - using FTP protocol & by the help of crond daemon - between 2 RedHat (RHEL) server. The goal is that both servers having the same databases & tables (server A sending data to server B, server B sending data to server A).

However, the problem arises when one of the server changed the OS from RHEL to debian based (Ubuntu). While the automatic script I made from RHEL won't work on Ubuntu - especially the lines referring to FTP commands - bash shell script.

So, how to make the synchronization running again? No doubt, rewrite the script ASAP! Take a look at below script:

echo machine your.domain.or.ip > /root/.netrc
echo login your_username >> /root/.netrc
echo password your_password >> /root/.netrc
chmod 600 /root/.netrc

ftp your.domain.or.ip <<_FTP_
binary
put name.of.the.file
bye
_FTP_

Above is the code that only work on RHEL. While the FTP account created on separated file named by .netrc, this is not compatible to Ubuntu.

ftp -n -i <<_FTP_
open your.domain.or.ip
user your_username your_password
binary
put name.of.the.file
quit
_FTP_

Somehow, the FTP account initialization right on above script is integrated in the same script. And it just succedded running in Ubunru server. Moreover, there's no script affected in other servers. So, lesson of learning today is: never change server OS except you're ready for the impact. Deal?

Labels: Linux, MySQL, Network, PHP, Programming, Projects, Ubuntu, Web Programming

Accessing single core database records from several existing online systems is a top application development priority. There are no others choice to retrieve rows on-the-fly accurately without having a pure real live connection. In a corporation which implementing single sign-in on some separated (web based) applications, each login page modules must connected to single dedicated users table in data center in order to verify user’s right. This is a sample about the use of clustering application technique.

Integrating multiple online systems so that it have both data connection; one in local & one in other host can be made with 2 styles; simple & difficult. I said it simple if there’s less or no security policy restriction on both servers. For example, let assume that it’s possible for server B granted request query only from server A. On this condition, it’s easy as provide two common connections in a configuration laid in server A without paying attention on security issues in server B.

<?php
$linkA = mysql_connect("hostA", "mysql_userA", "mysql_passwordA");
mysql_select_db("databaseA", $linkA);
$resultA = mysql_query("SELECT * FROM tableA", $linkA);
$num_rowsA = mysql_num_rows($resultA);

$linkB = mysql_connect("hostB", "mysql_userB", "mysql_passwordB");
mysql_select_db("databaseB", $linkB);
$resultB = mysql_query("SELECT * FROM tableB", $linkB);
$num_rowsB = mysql_num_rows($resultB);

echo "Host A: $num_rowsA Rows\nHost B: $num_rows Rows";
?>

This also can be done if you have designed the network and servers infrastructure by your own self as secure as it should be, by using VPN line or granted all privileges in server B only from server A. But, be sure that you must have root privileges on both servers to configure this out. All of above is a simple one.



But, what if you have no root privileges at all, or there’s a bit complicated situation which server A currently reside on an external web hosting company and server B is behind a firewall on local network (no port allowed except HTTP)? Well … well, this is not a simple way. Although that it’s not a simple, but it has another solutions. Solve the problem - I called it – with bridging method.

Bridging method basically based on SOAP technique. You can clearly read my previous article on this blog about what is SOAP actually including my extra specimen code. This method is simply as using XML to transferring the data. It’s a RMI like on Java (Remote Method Invocation) or known as application server. From picture above, a request query sent to Server B from Server A. Server B recognizing accepted command & continuing to process the query. Then result query will be returned back to Server A. Anyway, it might have a bit degraded performance, but believes me; I don’t want to talk about it right now.

Back to topic, at least, result query retrieval mode on web based application divide into 2 conditions which is single & multiple results. A single result can directly passing to a variable or text box. A more complex is multiple results such as menu/list box. Watch picture below.



Picture above explains a page from server A displaying result query obtained from data bridging from other host (server B). First object (the text box) hold only one row result returned, while combo box can hold many records. Here, I’ll explain what you have done to solve this. To get started, first off all, you need the same PHP SOAP library taken from my previous article. I renamed this library as “bridge.soap.php” & let it stored on both servers. On script in Server A, you must define the initialization:

require_once('bridge.soap.php');
$serverpath ='http://serverB/bridge.php';
$namespace="urn:xmethods-SOAPWebService";

$serverpath is variable hold a target application server on server B. okay, let’s straight more deep, if you need to request a single row result query then this below is the template:

$perintah="SELECT * FROM tableB WHERE fieldB='" . $varB ."'";
$field='fieldB';
$param= array('perintah'=>$perintah,'field'=>$field);
$client=new soapclient($serverpath);
list($field) = $client->call('get_row',$param,$namespace);
unset($client);

$field variable explain what field on database Server B that will be retrieved. And so on, the result query will also be stored on this same variable name (just to make easier). Anyway, it’s quite different if you need to retrieve multiple fields record by bridging & store it to combo box. Check below template:

$perintah="SELECT * FROM tableB order by fieldB1";
$field='fieldB1# fieldB2';
$param= array('perintah'=>$perintah,'field'=>$field);
$client = new soapclient($serverpath);
$record = $client->call('get_loop',$param,$namespace);
$row=explode("#",$record);
for ($x = 0; $x < count($row); $x++) {
$value='';
$col=explode("|",$row[$x]);
for ($y = 0; $y <= count($col); $y++) $value=$value . $col[$y] . '|';
$value=substr($value, 0, -2);
$value_kode=explode("|",$value);
echo("<option value='$value_kode'>$value</option>");
}
unset($client);

Same as first template, $field also hold what field that will be retrieved but it can contains more than single field. For example above, it declared to retrieve fieldB1 and fieldB2 (with # as delimiter). This is similar to query statement:

SELECT fieldB1,fieldB2 FROM tableB order by fieldB1

2nd template actually based on 1st skeleton template. I modified 1st template into 2nd so that it now possible to gain multiple fields. Even both templates look similar, but it’s different; while the 1st calling get_row function, the 2nd calling get_loop function. These both functions are exists on server B. It’s a kind a RPC (Remote Procedure Call) function as a part of application server. For instances, below script are template of SOAP server (I named as bridge.php) only reside on server B (the function definition called from Server A):

<?php
function get_row($perintah,$field)
{
$param = array();
$fieldno=explode("#",$field);

$hasil=mysql_query($perintah) or die($perintah);
while ($row=mysql_fetch_array($hasil))
{
for ($x = 0; $x <= count($fieldno); $x++) array_push($param,$row[$fieldno[$x]]);
}
return $param;
}

function get_loop($perintah,$field)
{
$param = array();
$param_row='';
$fieldno=explode("#",$field);

$hasil=mysql_query($perintah) or die($perintah);
while ($row=mysql_fetch_array($hasil))
{
$param_col='';
for ($x = 0; $x < count($fieldno); $x++) $param_col=$param_col . $row[$fieldno[$x]] . '|';
$param_col=substr($param_col, 0, -1) . '#';
$param_row=$param_row . $param_col;
}
$param_row=substr($param_row, 0, -1);
return $param_row;
}

require_once('bridge.soap.php');
$linkB = mysql_connect("hostB", "mysql_userB", "mysql_passwordB");
mysql_select_db("databaseB", $linkB);

// create the server object
$server = new soap_server;

// register the lookup service
$server->register('get_row');
$server->register('get_loop');

// send the result as a SOAP response over HTTP
$server->service($HTTP_RAW_POST_DATA);
?>

So, now it looks quite clear, huh? The application on server A is connecting to server B through bridge.php. The bridge.php become as database gateway, receive request, querying & send the result back. This is quite stupid, but also more reliable than a technique which I describe earlier (synchronization method) on this blog. Both of techniques were currently used on some of my projects showed on my homepage. You can simulate all appropriate to your existing situation. If you have other ideas or techniques, please let us know. See you on my next experiences…

Labels: MySQL, PHP, Programming, Web Programming

If you have develop an application (Win32 based I mean) & playing around with great number of records then I suggest you not to let users wasting their time to scroll rows in a list of table & select what record they need time after time. Got what I’m talking about? No? Okay, in a specific case, data transactions committed by an assist of others record, let say a combo or grid of records. It would be nothing if it has 10 records, how about 100 or more? I can guarantee that users will be flustered or the mouse soon will be broken because a long scroll or thousands clicks over it.

I’m saying about to keep last record position in memory & store it on next task. Pretty simple but it helps users effectively, at least minimize the use of scrolling. Here my example on Delphi, with TDBGrid contains records retrieved from a table & assigned with TQuery component. Once TQuery refreshed, record pointer will be at first position. To avoid this, save bookmark pointer before TQuery refreshed & store position in after. Check a code sliced below:

unit ULastRecordPointer;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, Grids, DBGrids, ComCtrls, DB, DBTables, ExtCtrls, JPEG;

type
TFLastRecordPointer = class(TForm)
...
private
{ Private declarations }
public
{ Public declarations }
end;

var
FLastRecordPointer: TFLastRecordPointer;
...
ptr_record: TBookmark;

implementation

{$R *.dfm}

procedure TFLastRecordPointer.FormRefresh(Sender: TObject);
begin
try
q3.GotoBookmark(ptr_record);
except
//
end;
end;

procedure TFLastRecordPointer.DBGrid1DblClick(Sender: TObject);
begin
ptr_record:=q3.GetBookmark;
end;
end.

The last record position will be saved if users had double clicking on grid (pick a record) & try to store it after the form refreshed. So that the small black triangle will stay on it’s previous position.



With this help of TBookmark, users job will assisted & no more times will be wasted. Furthermore, your users mouse will be much more durable ;-p

Labels: Delphi, My Tricks, My Works, MySQL

JavaScript is one of parts that can’t be separated with web programming. Sometimes we usually depend on it, although we can solve the problem with another technique but the use of JavaScript is much helpful to support dynamic runtime application. For example manipulating or counting math is possibly without refreshing the page. Therefore, it is much simpler than AJAX. This article purposely dedicated to my self & any newbies which want to learn JavaScript methods.

Just like my own experience improving a page counting difference between two dates, here I found something new & I’d like to archived for me then share to you. The web page project it self are related to MySQL date field type & based on PHP programming. The subject was, how to calculate the year & month difference by both dates after the page loaded & once the date object is changed? As usual, I set the date interface with JavaScript so it would generate an interesting GUI just like common date object on Win32 programming.



Those dates GUI are compatible with date type format in MySQL (yyyy-mm-dd). When the date icon clicked, it will show small interactive calendar panel. The first date object I named with date_start, the second is date_end & sequentially, the difference year & month is n_year & n_month. The core date subtract function need 2 parameter dates & I created as well as it will return more than one value (year & month). On JavaScript, the treatment is put it on array mode. Pick out a small portion below:

function selisihTgl(dateEnd,dateStart)
{
…
return [yearAge, monthAge];
}

Displaying returned both value from function in array are simply called the array variable just like any others programming which is 0 based in bracket.

function fSelisih(sender)
{
…
obj.n_year.value=selisih[0]
obj.n_month.value=selisih[1]
}

That’s it, kinda simple isn’t it? By the way – unfortunately -, when dates changed, the date GUI object doesn’t have trigger event to call the function. So, I take <FORM> tag with onMouseMove event. Pretty dumb, but it works fine. Below is complete script from A to Z, check this out:

// portion of PHP code to retrieve both dates value from MySQL database
...
//
<HTML>
<HEAD>
<TITLE>Your Title Please</TITLE>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
</HEAD>
<script language="JavaScript">
function selisihTgl(dateEnd,dateStart)
{
var baru = new Date(dateEnd.substring(0,4),
dateEnd.substring(5,7)-1,
dateEnd.substring(8,10));

var yearBaru = baru.getYear();
var monthBaru = baru.getMonth();
var dateBaru = baru.getDate();

var lama = new Date(dateStart.substring(0,4),
dateStart.substring(5,7)-1,
dateStart.substring(8,10));

var yearLama = lama.getYear();
var monthLama = lama.getMonth();
var dateLama = lama.getDate();

yearAge = yearBaru - yearLama;

if (monthBaru >= monthLama)
var monthAge = monthBaru - monthLama;
else
{
yearAge--;
var monthAge = 12 + monthBaru -monthLama;
}

if (dateBaru >= dateLama)
var dateAge = dateBaru - dateLama;
else
{
monthAge--;
var dateAge = 31 + dateBaru - dateLama;

if (monthAge < 0)
{
monthAge = 11;
yearAge--;
}
}

return [yearAge, monthAge];
}


function fSelisih(sender)
{
var obj=sender;
var selisih = selisihTgl(obj.date_end.value,obj.date_start.value);
obj.n_year.value=selisih[0]
obj.n_month.value=selisih[1]
}
</script>
<BODY onLoad="fSelisih(this)">
...
<form action="path/to/save/or/ignore/it" method="post" name="form1" onMouseMove="fSelisih(this)">
...
</form>
...
</BODY>
</HTML>

Don’t forget to insert the function within onLoad event on <BODY> tag to automatically count them expressly when the page are fully loaded.



Done! Enough for now lesson & I’ll be back with another unique tips & trick programming.

Labels: JavaScript, My Tricks, My Works, MySQL, PHP, Programming, Projects, Tips Trik, Web Programming

The answer is absolutely yes, it is possible to do that. This problem faced me when I had to solve how to move some group of values from a table into an excel sheet on one of my earlier web based project couple weeks ago.This article would describe you step by step on how to make it done.

First of all, let’s assumed that you have a compact table just like my example below. It is only contains 2 column (kd_bank & nm_bank) and let call the table with tm_bank.



Then, create a PHP project from your favorite editor. Take a look on complete code below:

<?
$link=mysql_connect("your_db_host","your_db_user","your_db_password");
mysql_select_db("your_db_name");
// Functions for exporting to excel.
function xlsBOF()
{
echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
return;
}
function xlsEOF()
{
echo pack("ss", 0x0A, 0x00);
return;
}
function xlsWriteNumber($Row, $Col, $Value)
{
echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
echo pack("d", $Value);
return;
}
function xlsWriteLabel($Row, $Col, $Value )
{
$L = strlen($Value);
echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
echo $Value;
return;
}
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Disposition: attachment;filename=mysql2xls.xls");
header("Content-Transfer-Encoding: binary ");

xlsBOF();
xlsWriteLabel(0,0,"Hasil Export Excel:");
// Make column labels. (at line 3)
xlsWriteLabel(2,0,"Kode Bank");
xlsWriteLabel(2,1,"Nama Bank");
$xlsRow = 3;
$perintah="select * from tm_bank order by nm_bank";
$hasil=mysql_query($perintah);
while($row=mysql_fetch_array($hasil))
{
$kd_bank=$row["kd_bank"];
$nm_bank=$row["nm_bank"];

xlsWriteLabel($xlsRow,0,$kd_bank);
xlsWriteLabel($xlsRow,1,$nm_bank);
$xlsRow++;
}
xlsEOF();
?>
<html>
<head>
<title>MySQL2XLS</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
Hit F5 To Re-Download the XLS
</body>
</html>

Finally, save the code & execute it from browser.



The script will automatically opening a download window consist of a file (mysql2xls.xls). You may select Open option & see what will appear next.



Voila, an excel sheet will shown up where all the value from MySQL are transferred into it. From here, do as you like to modify. Good day.

Labels: Excel, MySQL

What is AJAX? Well you can see what is it stand for behind the word to the left image shown. But - for those who just heard AJAX acronym - what does it like? What does it purposed to? Many of AJAX online documentation you can pointed to and learned, but anyway, this official wiki page is a good start. As personally for me, AJAX give many useful things in a web programming. Generally, it can eliminated some limitations you will face in a pure HTML page. And somehow, it can make website more attractive & increasing interactivity.

Moreover, this blog article would like describe out about a simple AJAX implementation, especially just for beginner programmer like me ;-). AJAX technology is not a latest one in World Wide Web but it’s almost new to me. I’m not an expert but this is my first experience to AJAX and I’d like to share about it to you. Think about a job but you have to force finished it only with one tool. And this was happened to me when I had to create a web chat module written in PHP.

Basically, a common rule acceptable chat application is that it has capability to refresh the display automatically when everyone leave a message, for example: Yahoo Messenger or IRC. In a regular HTML page (web based application), you can use a META tag but it will refresh the whole page in a specified time. Look at an example below:

<meta equiv="refresh" content="5;URL=chat.php">

The lack is, the message you type in a input box will lost when the time achieved (5 second to get the page refreshed automatically – chat.php). Give it 60 second and you will get an unreliable chat application since there are long duration time to rendering a new screen.

The point is, you will never have a good chat application with above technique. But the mighty AJAX can. How? First of all, take a subject to the database side. This will hold the whole messages from users. Create a table contains 2 row in MySQL just like below script. (database name: chat)

# MySQL-Front Dump 2.5
#
# Host: xxx Database: chat
# --------------------------------------------------------
# Server version 4.1.11

USE chat;

#
# Table structure for table 'chat'
#

DROP TABLE IF EXISTS chat;
CREATE TABLE chat (
pesan text,
time time NOT NULL default '00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

This is a simple table, you can rearrange it to your needs later. Anyway, look at the picture below and this what’s looks like with MySQLFront



Finally, I remade & simplify the chat AJAX script until it contain only 2 files; the chat library (chat.lib.php) & the form page (index.php).



Take a look at complete chat.lib.php class below:

<?php
class chat
{
var $host="your_db_host_server";
var $user="your_db_user";
var $db="chat";
var $pass="your_db_password";

function connect_easy($query)
{
$b = array();
if(!$connect = mysql_connect($this->host,$this->user,$this->pass));
if(!$dbr = mysql_select_db($this->db));
if(!($result = mysql_query($query)));
@$num = mysql_num_rows($result);
@$num2 = mysql_num_fields($result);
for($x=0;$x<$num;$x++)
{
$a = mysql_fetch_array($result);
for($i=0;$i<$num2;$i++)
{
$b[$x][$i] = html_entity_decode($a[$i]);
}
}
return $b;
}

function show($a)
{
if(count($a)>0)
{
$a=array_reverse($a);
if(count($a)<9)
$end=count($a);
else
$end=9;
for($i=0;$i<$end;$i++)
{
echo "<font size=2 color=red>".$a[$i][1]."</font>: ".$a[$i][0]."<br />";
}
}
}
}

All you need to do is change the variable of $host, $user and $pass and make an appropriate with your existing database server. Globally, this class has 2 function; the connection query & retrieval argument. Next, copy below script as form page and rename it as index.php.

<?
session_start();
require_once("chat.lib.php");
$action=$_GET["action"];
switch ($action)
{
case 'refresh' : $refresh = new chat();
$query="select * from chat";
$a=$refresh->connect_easy($query);
$refresh->show($a);
exit;
break;

case 'submit': $submit = new chat();
$query="insert into chat values ('".$_GET["chat"]."',NOW())"; $submit->connect_easy($query);
exit;
break;
}
?>

<html>
<head>
<title>AJAX Chat</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript">
function ajaxConstructor()
{
var request_;
var browser = navigator.appName;
if(browser == "Microsoft Internet Explorer")
{
request_ = new ActiveXObject("Microsoft.XMLHTTP");
}
else
{
request_ = new XMLHttpRequest();
}
return request_;
}

var http = new Array();
var http2 = new Array();

function getRefresh()
{
var curDateTime = new Date();
http[curDateTime] = ajaxConstructor();

http[curDateTime].open('get', 'index.php?action=refresh');

http[curDateTime].onreadystatechange = function()
{
if (http[curDateTime].readyState == 4)
{
if (http[curDateTime].status == 200 || http[curDateTime].status == 304)
{
var response = http[curDateTime].responseText;
document.getElementById('ajax_chat').innerHTML = response;
}
}
}

http[curDateTime].send(null);
}

function getSubmit()
{
var curDateTime = new Date();
http2[curDateTime] = ajaxConstructor();
http2[curDateTime].open('get', 'index.php?action=submit&chat=' + document.ajax.chat.value);
http2[curDateTime].send(null);
}

function kirim()
{
getSubmit();
document.ajax.chat.value=" ";
}

function refreshLayar()
{
getRefresh();
window.setTimeout("refreshLayar()", 2000);
}
</script>
</head>

<body onLoad="refreshLayar()">
<div id="ajax_chat" style="overflow=auto; width: 375px; height: 200px; border: 1px;" align="left"></div><br>
<form action="JavaScript: kirim()" method="get" name="ajax">
<font size="2" face="Trebuchet MS, Verdana, MS Sans Serif">Tulis Pesan: </font>
<input name="chat" type="text">
<input type="button" value="Kirim" onClick="kirim()">
</form>
</body>
</html>

All the both files must stay in the same path of a web server and make sure that you have a valid parameter database connection described earlier in chat.lib.php. Take a test and call the index page from any browser available from client.



That’s it & you’re done. The explanation of index page is very much simple. It contains some of core chat functions in JavaScript; the AJAX constructor, refreshing display, submit message & auto render screen in 2 second!. Got it? Well, class dismissed & hope this lesson benefit to us.

Labels: AJAX, MySQL, PHP

As many programmers know, there are several ways connection link can be set up between MySQL database server & client application. One simplest way can be done through MyODBC, the ODBC driver for MySQL. Except for those who using Delphi native component such as dbExpress or from 3rd party component like Zeus or application built with Lazarus compiler, the application deployment within MyODBC driver would required 2 setup wizards. One for the application & one for the MyODBC setup. Somehow, - for me – it will decrease prestige & users manner since they will face some annoyed parameters within doing the 2nd setup of MyODBC wizard.

Integrating MyODBC driver with application is another way & become my favorite trick when I did a client server project from Delphi. There are some reason why I still use ODBC & integrating it to the main program. First, I don’t even suspended with 3rd party component for expanding or future application release. All I need is only BDE & Data Access component. Then, I feel free to compile the codes wherever the native component & compiler exist. My application can also deployed smoothly even from Linux (with emulation engine) because nothing complicated API’s driver involved. Other advantages is, my client only need to installing a single setup software (including BDE’s & MySQL ODBC driver).

Based on my researches before, CMIIW - MyODBC driver just work with a single library file (myodbc3.dll) & some parameters planted in Windows registry. So, anyone can build their custom ODBC wizard & replace the functionality of standard MyODBC setup. There are 2 fixed registry track path of MyODBC driver & below is each of it:






The trick to integrating it is so simple as you have to create similar registry value according the above parameters. And don’t forget to take the myodbc3.dll library file taken from original MyODBC setup. Below is a sample in Delphi on how to create & integrating the driver into the application:

AppIni := TIniFile.Create('odbc.ini');
Reg:=TRegistry.Create;
AppIni.WriteString('ODBC 32 bit Data Sources',eddbname.Text,'MySQL ODBC 3.51 Driver (32 bit)');
AppIni.WriteString(eddbname.Text,'Driver32',extractfilepath(application.ExeName)+'myodbc3.dll');

Reg.RootKey:=HKEY_LOCAL_MACHINE;
Reg.OpenKey('SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources',true)
Reg.WriteString(eddbname.Text,'MySQL ODBC 3.51 Driver');
Reg.CloseKey;

Reg.OpenKey('SOFTWARE\ODBC\ODBC.INI\'+eddbname.Text,true)
Reg.WriteString('Database',eddbname.Text);
Reg.WriteString('Description',eddescription.Text);
Reg.WriteString('Driver',extractfilepath(application.ExeName)+'myodbc3.dll');
Reg.WriteString('Option','3');
Reg.WriteString('Password',edpasswd.Text);
Reg.WriteString('Port',edport.Text);
Reg.WriteString('Server',edhost.Text);
Reg.WriteString('Stmt','');
Reg.WriteString('User',eduser.Text);
Reg.CloseKey;

Reg.OpenKey('SOFTWARE\ODBC\ODBCINST.INI\MySQL ODBC 3.51 Driver',true)
Reg.WriteString('APILevel','2');
Reg.WriteString('ConnectFunctions','YYN');
Reg.WriteString('CPTimeout','60');
Reg.WriteString('Driver',extractfilepath(application.ExeName)+'myodbc3.dll');
Reg.WriteString('DriverODBCVer','03.51');
Reg.WriteString('FileExtns','*.txt');
Reg.WriteString('FileUsage','0');
Reg.WriteString('Setup',extractfilepath(application.ExeName)+'myodbc3.dll');
Reg.WriteString('SQLLevel','1');
Reg.WriteInteger('UsageCount',2);
Reg.CloseKey;

Reg.OpenKey('SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers',true)
Reg.WriteString('MySQL ODBC 3.51 Driver','Installed');
Reg.CloseKey;

Reg.Free;
AppIni.Free;

To make it more portable & dynamic, I suggest to save each of variable parameter into an encrypted text file. Here below is the sample of my own ODBC driver custom wizard:




That’s all, dude. Once you can build your custom MySQL ODBC driver, you will never need a standard MyODBC setup wizard. So that your users will feel more comfortable to the using of the application.

Labels: MySQL

After try & error with several parameters in my.cnf configuration file, finally below is my version of optimized file.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
max_allowed_packet = 10M

# tambahan optimisasi
skip-locking
skip-innodb
query_cache_type=1
query_cache_limit=1M
query_cache_size=32M
interactive_timeout=100
wait_timeout=15
connect_timeout=10
table_cache=512
thread_cache=32
key_buffer = 128M
log_slow_queries=/var/log/mysqld.slow.log
long_query_time=2
skip-host-cache
skip-name-resolve

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# tambahan optimisasi
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout

[client]
socket=/var/lib/mysql/mysql.sock

Note that the configuration above has tried on MySQL 4.xx. Comment regarding to this from the reader is needed.

Labels: MySQL