Posts Tagged Database

Solve an issue encountered when SpotFire read Microsoft Access database

Situation:

SpotFire read data from an Access database for generating graphs. An error message always pops up and says:

Error message: The required OLE DB provider (Microsoft.ACE.OLEDB.12.0) for Microsoft Access is not installed 

Solution:

Download Microsoft Access Database Engine 2010 Redistributable package at the following link:

http://www.microsoft.com/en-us/download/details.aspx?id=13255

If your OS is 64 bit version of Windows 7, please download and install the x64  version (accesdatabaseengine_x64.exe).

There is a trick for installing the 64 bit version on the machine with MS Office 2010 32bit version installed. Install the 64-bit runtime files using the “/passive” switch like this:

“AccessDatabaseEngine_X64.exe /passive”

Of course, in the Windows command line window. It works like a charm.

Reference

http://spotfirecommunity.tibco.com/community/Themes/leanandgreen/Forums/viewIphoneThread.aspx?PostID=4863

 

Share

Tags: , , , ,

A skeleton of a MS Access VBA function operating data tables

Use Microsoft Access VBA can do all sorts of database operations. The following is the framework of a VBA subroutine. It includes all VBA database operation mechanisms. You can just borrow it and adapt it to your situation.

Sub FillAField(tablename)
    Dim db As Database
    Dim db_record As DAO.Recordset
    Dim sql As String
    Dim id As String
    Dim field2 As Long

    'Open connection to current Access database
    Set db = CurrentDb()

    ' Create SQL statement to retrieve value from GST table
    ' replace to your sql statement
    sql = "select field1, field2 from " & tablename

    Set db_record = db.OpenRecordset(sql)
    ' move the record pointer
    db_record.MoveFirst
    
    'Retrieve value if data is found
    Do While Not db_record.EOF
        id = db_record("id")
        field2 = db_record("field2")
        
        ' process the field2 and generate some data for another field
        Fields = Split(field2, "_")
        theyear = Fields(UBound(Fields))
        sql = "update " & tablename & " set year = " & theyear & " where id=" & id & ";"
        ' update data in the same table
        db.Execute sql
        ' move the record pointer
        db_record.MoveNext
    Loop

    map_record.Close
    Set map_record = Nothing
End Sub
Share

Tags: , , ,

iPhone/iPod App to control MySQL database

In last few weeks, I reviewed several iPhone/iPod apps that can connect to MySQL server and manipulate data. My main focus are the free apps. There are a couple of free apps the emerges from the weeds.

Liya

Liya is iPhone/iPod Touch app, which can connect to MySQL5 and PostgreSQL databases. It allows iPhone/iPod users to access and edit the database contents. Its interface is very simple and easy to use. It like a driven down tool, from databases -> tables in the selected database -> data record. Once in the data record screen, you can search data and edit data values. If you are looking for a tool the allows you view, edit, and add data record, this one definitely can serve the purpose.

DB Tracklayer

DB Tracklayer is a free iPhone/iPod app, which can connect to MySQL server. Compare to Liya, the one is little bit slow, but it is more powerful because it not only can view and edit data, it can modify table structure, administrate MySQL server. Once you connect to a MySQL database server, and you can execute various commands on database just as a client terminal. Its main features include the following:

  • Table data alteration (insert, update and drop)
  • Browse, create, insert, drop various kinds of object with GUI
  • Generate and execute SQL statement
  • Easy to browse server administration items

Share

Tags: , , , , , , , , ,

Database migration: MS Access to MySQL server

The Problem
Recently I solved a database migration problem: migrate MS Access database encoded in GB2312 to MySQL server encoded in UTF-8. I’d like to share what I did and hope it is useful to help you solve your problem.

I had a lot of useful data in an MS Access database. They were collected through ASP Gallery 1.0, which was run on Windows IIS web server. I did not have an IIS server to run ASP Gallery for a long time and do not have a plan to launch an IIS server in the coming future.

I wrote simple PERL CGI’s and used it from 2007 to 2008. Now I have a powerful Apache2 server and I developed an powerful PHP web application. In order to use the useful information in the MS Access database, I have to migrate all data to MySQL server.

The Solution
There is a big problem in front of me. All data in the MS Access database was encoded in GB2312. My MySQL server and everything else related to the new web server use UTF-8 encoding. I have to convert the GB2312 encoding to UTF-8 encoding. That was solved easily. See the corresponding function in migrate.php.

First I used the MySQL migrate tool (you can download it from mysql.com website) to transfer data (migrate_class) to MySQL server. Then I wrote a nice PHP application called migrate.php, that help me to merge the data from the table to MySQL server (category) table in donghome database.

The PHP code

<?php
// database-specific information
DEFINE(DB_USER,"your username");
DEFINE(DB_PASSWORD,"your password");
DEFINE(DB_HOST,"your mysql server host");
DEFINE(DB_NAME,"gmain");

// connect to mysql
$db_connection = mysql_connect(DB_HOST,DB_USER,DB_PASSWORD);

// select the database
mysql_select_db(DB_NAME);

// process gmain database that includes old gallery data tables

//update_class_table();

//migrate_class();


function migrate_class()
{
	$sql = "SELECT classid, pclassid, title FROM class";
	$query_result = mysql_query($sql);
	$counter = 0;
	while ($row = mysql_fetch_array($query_result, MYSQL_NUM))
	{
		$classid=$row[0];
		$pClassID = $row[1];
		$name = $row[2];
		$counter ++;
		echo $counter. " : ";
		update_category($classid, $pClassID, $name);
	}

}
function update_category($classid, $pClassID, $name)
{
	$sql1 = "INSERT INTO donghome.category (id_category,parent_id,name) ".
			"VALUES ($classid,$pClassID,'".mysql_escape_string($name)."');";
	echo $sql1."<br>";
	mysql_query($sql1);
}

function update_class_table()
{
	$sql = "SELECT * FROM migrate_class";
	$query_result = mysql_query($sql);
	$str = "all classes in the table <br>";
	$str .= "ClassID,directory,pClassID,title,hyperlink,hasdir,user,path<br>";
	while ($row = mysql_fetch_array($query_result, MYSQL_NUM))
	{
		$path = "";
		$classid=$row[0];
		$directory = $row[1];
		$pClassID = $row[2];
		$title = $row[3];
		$hyperlink = $row[4];
		$hasdir = $row[5];
		if ($row[5])
		{
			$path = buildpath($directory,$pClassID);
		}
		$str .= "*** $row[0],$row[1],$row[2],".convertGB_UTF8($title).",$row[4],$row[5],$row[6],$path<br>";
		updateClass($classid,$directory,$pClassID,$title, $hyperlink, $hasdir, $path);
	}
	echo $str;
}

function updateClass($classid,$directory,$pClassID,$title, $hyperlink, $hasdir, $path)
{
	$sql1 = "INSERT INTO class (classid,directory,pclassid,title,hyperlink,hasdir,path) ".
			"VALUES ($classid,'$directory',$pClassID,'".mysql_escape_string(convertGB_UTF8($title))."', '$hyperlink', $hasdir, '$path');";
	echo $sql1."<br>";
	mysql_query($sql1);
}

function buildpath($path,$parentdir)
{
	$apath = $path;
	$sql = "SELECT * FROM migrate_class WHERE ClassId = $parentdir;";
	$query_result = mysql_query($sql);
	$row = mysql_fetch_array($query_result, MYSQL_NUM);
    if ($row[5])
	{
		$apath = $row[1]."/".$apath;
	}
    $aparentdir = $row[2];
	if ($aparentdir != 0)
	{
		$apath = buildpath($apath,$aparentdir);
	}
	return $apath;
}


function convertGB_UTF8($str)
{
	$str = mb_convert_encoding($str, "UTF-8", "GB2312");
	return $str;
}

?>
Share

Tags: , , , , , ,

SQLite database engine and sample code in C#

The definition in SQLite official site is the following: “SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world.” So, how can we use SQLite engine to develop standalone desktop database application in Windows environments. At SQLite official site, you can download all C source of the SQLite library. Nowadays people develop .NET applications by using C#, VB.NET, C++ etc. I usually use C# to develop Windows applications. How can we utilize SQLite library in .NET environments? In another words, does a SQLite .net interface exist? Yes, it does. System.Data.SQLite, an open source ADO.NET provider for the SQLite database engine, provides all the functionality you need to operate SQLite databases. What you need to do is to download latest version of System.Data.SQLite at http://sourceforge.net/project/showfiles.php?group_id=132486&package_id=145568. Run the setup file and install SQLite library and the .NET interface library into your PC. Then you can use this library to develop standalone high standard database application. To operate the database you can use commands of SQL language. Pretty a amazing. I wrote a PERL script running in Linux system to operate MySQL databases on MySQL server. Later on, I ported this PERL script to C#. I did not need to change any SQL statements used in the PERL script. Some of them are really complicated.

In order to give your some guideline to start using System.Data.SQLite library, I am going to share a simplified framework, a C# class, with you. You can modify it to use it in your project freely.

/*
 * Author: Zhanshan Dong
 * Created: 4/3/2009
 * Modified: 9/18/2009
 */

using System;
using System.Windows.Forms;
using System.Data.SQLite;
using System.IO;
using System.Collections.Generic;

namespace TwitterGIFT
{
	/// 
	/// Description of Class1.
	/// 
	public class GiftSQLite
	{
		private SQLiteConnection Conn;
		private SQLiteCommand Cmd;
		private string dbFilename = "";
        
		public GiftSQLite(string userName)
		{
			username = userName;
			dbFilename = userName + ".db";
			string FullPath = Application.StartupPath + "\\" + dbFilename;
			if (Application.StartupPath.Substring(Application.StartupPath.Length - 1,1) == "\\")
			    FullPath = Application.StartupPath + dbFilename;
			bool hasDBFile = File.Exists(FullPath);
			ConnectDB();
			Cmd = CreateCMD();
			if (! hasDBFile ) 
			{ 
				CreateTables(); 
			} 
		}

		~GiftSQLite()
		{
			Cmd.Dispose();
			Conn.Close();
		}
		
		private void ConnectDB()
		{
			Conn = new SQLiteConnection();
			Conn.ConnectionString = "Data Source=" + username + ".db;New=True;Compress=True;Synchronous=Off";
			Conn.Open();
		}
		
		private SQLiteCommand CreateCMD()
		{
			SQLiteCommand cmd = new SQLiteCommand();
			return Conn.CreateCommand();
		}

		private void disposeCMD(SQLiteCommand cmd)
		{
			cmd.Dispose();
		}
		
		private void ExecuteNonQuerySQL(string SQL) {
			Cmd.CommandText = SQL;
			Cmd.ExecuteNonQuery();
		}

		private SQLiteDataReader ExecuteQuerySQL(string SQL, SQLiteCommand cmd) {
			cmd.CommandText = SQL;
			return cmd.ExecuteReader();
		}
		
		private void CreateTables()
		{
			// create a table
			ExecuteNonQuerySQL("CREATE TABLE tablename (field1 integer primary key, ...)");
			...
		}

		private int getRowCount(string tablename)
		{
			SQLiteCommand cmd = CreateCMD();
			SQLiteDataReader dbReader = ExecuteQuerySQL("SELECT count(id_"+ tablename + ") FROM "+ tablename,cmd); 
			int rowCount = 0;
			if (dbReader.HasRows) { dbReader.Read(); rowCount = dbReader.GetInt32(0); }
			dbReader.Close();
			cmd.Dispose();
			return rowCount;
		}

		private void processAllRows()
		{
			SQLiteCommand cmd = CreateCMD();
			SQLiteDataReader dbReader = ExecuteQuerySQL("SELECT * FROM tablename",cmd);
			if (dbReader.HasRows) 
			{
				while (dbReader.Read())
				{
					int id = dbReader.GetInt32(0);
					...
					...
					...
				}
			}
			dbReader.Close();
			disposeCMD(cmd);
		}
		
	}
}

Besides the functionality in your application to do database operation, you do need SQLite database client tools to browse and manage your database. There are plenty of tools available. I tested a very simple one from http://sqlitetool.googlepages.com/. RazorSQL provides an array of tools for editing, browsing, querying and managing SQLite database. TwitterGIFT is a Windows application we developed that utilized the SQLite engine to handle database.

Share

Tags: , , , , ,