Sun Report Builder

Sun Report Builder | OpenOffice.org repository for Extensions

Create with the Sun Report Builder stylish, smart-looking database reports. The flexible report editor can define group and page headers as well as group and page footers and even calculation fields are available to accomplish complex database reports.

Along with the flexible database client of StarOffice it is possible to create database reports from HSQL, Oracle, or almost any other database type.

The Sun Report Builder uses the Pentaho Reporting Flow Engine of Pentaho BI.

Operating System: System Independent
Compatible with: OpenOffice.org 3.1 | StarOffice 9 Update 1 or higher.

How to Install Zen Cart on a LAMP server

How to: Install Zen Cart on a LAMP server

(Note: I first installed through Godaddy's Connection but found automatically created MySQL database can't be accessed remotely. Then I am trying to install manually. HL )

If you have ever had a need for an on-line shopping cart, and have ever tried to install a shopping cart, you know that making a choice can be a challenge, and getting the shop up and running can be a challenge.

Not so much with Zen Cart. Zen Cart is an open source e-commerce solution that serves as a stand-alone shopping cart and is installed on top of a LAMP server. If you're not sure how to install a LAMP server check out my article "How to: Install a LAMP server." With that LAMP server up and running you are ready to install Zen Cart.

The installation of Zen Cart will require you to use a few Linux commands: zip, mv, and chmod. But that is it. For the purposes of this article I will assume you are using phpmyadmin for the database creation. If you need help installing phpmyadmin check out my article "Install phpmyadmin for easy MySQL administration."

With all of that in mind, let's begin the installation.

Getting Zen Cart

The first thing you need to do is download Zen Cart. But don't just open your browser to the download page. Instead ssh to the server that will house Zen Cart, cd to the server document root, and use wget to download the necessary file into the correct directory. The command to download Zen Cart is:

sudo wget "http://downloads.sourceforge.net/project/zencart/CURRENT_ Zen Cart 1.3.x Series/Zen Cart v1.3.8 - Full Release/zen-cart-v1.3.8a-full-fileset-12112007.zip"

NOTE: Since there are space in the path name you do have to make use of the "" when using wget to download the file.

With the file downloaded and in place it is time to unzip the file. To unzip the Zen Cart file issue this command:

sudo unzip zen-cart-v1.3.8a-full-fileset-12112007.zip

which will create the directory zen-cart-v1.3.8a-full-fileset-12112007. To make your life easier you will want to change the name of that directory to a much easier name. This directory is what you (and your customers) will use to access your shopping cart, so make it easy. I rename mine zencart or shopping. Rename this to suit your needs. Rename it with the command:

sudo mv zen-cart-v1.3.8a-full-fileset-12112007 zencart

Now it's time to create the configuration file and make some changes to permissions.

Configuration files and permissions

Zen Cart does not unpack with the necessary configuration files in place. What you have to do is rename to files so they will serve as your configuration files. To do this issue the following commands (from within your Zen Cart directory):

sudo mv includes/dist-configure.php includes/configure.php

and

sudo mv admin/includes/dist-configure.php admin/includes/configure.php

The next step is to change the permissions of the configuration files like so:

sudo chmod 777 includes/configure.php

and

sudo chmod 777 admin/includes/configure.php

The configuration files are set. Time to change permissions of some directories.

Below are all of the commands you need to run to set the correct permissions for your installation:

sudo chmod 777 cache
sudo chmod 777 includes/languages/english/html_includes
sudo chmod 777 media
sudo chmod 777 pub
sudo chmod 777 admin/backups
sudo chmod 777 admin/images/graphs

With this complete you are now ready to create your database and log into the web-based installer and install Zen Cart.

Installing via web

After you create your database (using phpmyadmin) log into the Zen Cart install like so:

http://IP_TO_SERVER/ZENCART/zc_install

Where IP_TO_SERVER is the actual IP address of your server and ZENCART is the name you have given your Zen Cart directory.

The web-based installation is very simple. You will only have to insert options like the MySQL user and password, name of site, administrator name/email, etc. Outside of some very easy questions, Zen Cart will most likely correctly guess all of the details of your installation.

After you have stepped through the installation wizard your Zen Cart installation is done. You will be directed to either immediately administer the site or view the site.

Final thoughts

Zen Cart is one of the better shopping cart systems available from the open source community. It is easy to install/administer, robust, feature-rich, and reliable. If you have a need for ecommerce make sure you check out Zen Cart first.

MySQL and UTF-8

MySQL and UTF-8 [Web Application Component Toolkit]

Good support from 4.1

utf-8 is utf8 in MySQL.

A collation defines the sort order for the data, it may be case sensitive or not

To find out your current setup:

SHOW VARIABLES LIKE 'character_set_database';  SHOW VARIABLES LIKE 'character_set_client'; 

To see available character sets and collations on your database:

SHOW CHARACTER SET;  SHOW COLLATION LIKE 'utf8%'; 

Character set and collation can be set per server, database, table, connection;

Server (/etc/my.cnf):

[mysqld]  ...
default-character-set=utf8
default-collation=utf8_general_ci  

Database:

(CREATE | ALTER) DATABASE ... DEFAULT CHARACTER SET utf8 

Table:

(CREATE | ALTER) TABLE ... DEFAULT CHARACTER SET utf8 

Connection:

SET NAMES 'utf8'; 

A PHP mysql connection (not totally confirmed, but see tests below) defaults to a latin1 connection, so, your first query after connection should be:

mysql_query("SET NAMES 'utf8'"); 

In php versions 5.2 and later, use

mysql_set_charset('utf8',$conn);  

The CONVERT() function can convert between charsets, eg:

INSERT INTO utf8table (utf8column)  SELECT CONVERT(latin1field USING utf8) FROM latin1table;  

As mentioned in charsets, field widths may need to be increased to deal with multi-byte characters.

Code to generate a mass change of collations:

<?php   // this script will output the queries need to change all fields/tables to a different collation // it is HIGHLY suggested you take a MySQL dump prior to running any of the generated // this code is provided as is and without any warranty   die("Make a backup of your MySQL database then remove this line");   set_time_limit(0);   // collation you want to change: $convert_from = 'latin1_swedish_ci';   // collation you want to change it to: $convert_to   = 'utf8_general_ci';   // character set of new collation: $character_set= 'utf8';   $show_alter_table = true; $show_alter_field = true;   // DB login information $username = 'user'; $password = 'pass'; $database = 'table'; $host     = 'localhost';   mysql_connect($host, $username, $password); mysql_select_db($database);   $rs_tables = mysql_query(" SHOW TABLES ") or die(mysql_error());   print '<pre>'; while ($row_tables = mysql_fetch_row($rs_tables)) {     $table = mysql_real_escape_string($row_tables[0]);          // Alter table collation     // ALTER TABLE `account` DEFAULT CHARACTER SET utf8     if ($show_alter_table) {         echo("ALTER TABLE `$table` DEFAULT CHARACTER SET $character_set;\r\n");     }       $rs = mysql_query(" SHOW FULL FIELDS FROM `$table` ") or die(mysql_error());     while ($row=mysql_fetch_assoc($rs)) {                  if ($row['Collation']!=$convert_from)             continue;           // Is the field allowed to be null?         if ($row['Null']=='YES') {             $nullable = ' NULL ';         } else {             $nullable = ' NOT NULL';         }           // Does the field default to null, a string, or nothing?         if ($row['Default']==NULL) {             $default = " DEFAULT NULL";         } else if ($row['Default']!='') {             $default = " DEFAULT '".mysql_real_escape_string($row['Default'])."'";         } else {             $default = '';         }           // Alter field collation:         // ALTER TABLE `account` CHANGE `email` `email` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL         if ($show_alter_field) {             $field = mysql_real_escape_string($row['Field']);             echo "ALTER TABLE `$table` CHANGE `$field` `$field` $row[Type] CHARACTER SET $character_set COLLATE $convert_to $nullable $default; \r\n";         }     } }   ?>

MySQL tables, columns and connections with UTF-8

What happens when you INSERT a UTF-8 string into a MySQL database using PHP's MySQL extension? Well that depends on what string you use. If you use something like "Iñtërnâtiônàlizætiøn" then doing just about anything in MySQL will be data-safe (but, of course, the collation will be incorrect). If you have characters that don't encode the same in UTF-8 and latin1 (e.g. text in Chinese, Russian, ...) then the behaviour depends on both the table definition and the encoding of the connection to the database.

	Table or column charset
	latin1 (MySQL default)	utf8
default connection mysql_pconnect()	data is binary-safe but not encoded properly*	data is binary-safe but not encoded properly*
using SET NAMES 'utf8';	data destroyed; string is converted to "????"	works fine!

* It seems that MySQL will look at the data as being a series of bytes all within the latin1 codepage. If you use the same code to read and write the data then it will round-trip fine (but MySQL's collation will obviously be wrong). If you use tools with a knowledge of the data types used by MySQL, like phpMyAdmin or even mysql_dump, then the wrong encoding is obvious.

(this test was performed with PHP versions 4.4.2 and 5.1.2 and MySQL 5.0.20; PHP source for test)

Further Reading

• Fun with Collations
• MySQL documentation
• MySQL and UTF-8 - no more question marks

How to Enable Remote Access To MySQL Database Server

How Do I Enable Remote Access To MySQL Database Server?

By default, MySQL database server remote access disabled for security reasons. However, some time you need to provide the remote access to database server from home or from web server.

MySQL Remote Access

You need type the following commands which will allow remote connections:

Step # 1: Login over ssh if server is outside your IDC

First, login over ssh to remote MySQL database server

Step # 2: Enable networking

Once connected you need edit the mysql configuration file my.cfg using text editor such as vi.

• If you are using Debian Linux file is located at /etc/mysql/my.cnf location
• If you are using Red Hat Linux/Fedora/Centos Linux file is located at /etc/my.cnf location
• If you are using FreeBSD you need to create a file /var/db/mysql/my.cnf

# vi /etc/my.cnf

Step # 3: Once file opened, locate line that read as follows

[mysqld] 

Make sure line skip-networking is commented (or remove line) and add following line

bind-address=YOUR-SERVER-IP

For example, if your MySQL server IP is 65.55.55.2 then entire block should be look like as follows:
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/English
bind-address = 65.55.55.2
# skip-networking
....
..
....Where,

• bind-address : IP address to bind to.
• skip-networking : Don't listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should removed from file or put it in comment state.

Step# 4 Save and Close the file

Restart your mysql service to take change in effect:# /etc/init.d/mysql restart

Step # 5 Grant access to remote IP address

# mysql -u root -p mysqlGrant access to new database
If you want to add new database called foo for user bar and remote IP 202.54.10.20 then you need to type following commands at mysql> prompt:mysql> CREATE DATABASE foo;
mysql> GRANT ALL ON foo.* TO bar@'202.54.10.20' IDENTIFIED BY 'PASSWORD';

How Do I Grant access to existing database?

Let us assume that you are always making connection from remote IP called 202.54.10.20 for database called webdb for user webadmin, To grant access to this IP address type the following command At mysql> prompt for existing database:mysql> update db set Host='202.54.10.20' where Db='webdb';
mysql> update user set Host='202.54.10.20' where user='webadmin';

Step # 5: Logout of MySQL

Type exit command to logout mysql:mysql> exit

Step # 6: Open port 3306

You need to open port 3306 using iptables or BSD pf firewall.

A sample iptables rule to open Linux iptables firewall

/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT

OR only allow remote connection from your web server located at 10.5.1.3:

/sbin/iptables -A INPUT -i eth0 -s 10.5.1.3 -p tcp --destination-port 3306 -j ACCEPT

OR only allow remote connection from your lan subnet 192.168.1.0/24:

/sbin/iptables -A INPUT -i eth0 -s 192.168.1.0/24 -p tcp --destination-port 3306 -j ACCEPT

A sample FreeBSD / OpenBSD pf rule ( /etc/pf.conf)

pass in on $ext_if proto tcp from any to any port 3306

OR allow only access from your web server located at 10.5.1.3:

pass in on $ext_if proto tcp from 10.5.1.3 to any port 3306  flags S/SA synproxy state

Step # 7: Test it

From remote system or your desktop type the command:
$ mysql -u webadmin –h 65.55.55.2 –p
Where,

• -u webadmin: webadmin is MySQL username
• -h IP or hostname: 65.55.55.2 is MySQL server IP address or hostname (FQDN)
• -p : Prompt for password

You can also use telnet to connect to port 3306 for testing purpose:$ telnet 65.55.55.2 3306

MySQL Connector for OpenOffice.org 1.0

MySQL Connector for OpenOffice.org 1.0 - OpenOffice.org Wiki

(Note: I tried UnixODBC first but found utf-8 problem. Then I selected this way, so far so good. HL)

MySQL Connector for OpenOffice.org is a MySQL driver for OpenOffice.org. It can be used to connect from OpenOffice.org 3.1 to a MySQL server 5.1 or newer.

Before MySQL Connector for OpenOffice.org became available you'd have to use MySQL Connector/J (JDBC) or MySQL Connector for ODBC to connect to a MySQL server.

Therefore the driver is also referred to as a native driver in the context of OpenOffice.org. The term native driver clashes with the MySQL definition of a native driver: MySQL Connector for OpenOffice.org does not implement the MySQL Client Server protocol. Technically speaking the MySQL Connector for OpenOffice.org is implemented as a proxy on top of the MySQL Connector/C++.

The driver is delivered as an OpenOffice.org extension.

Advantages

Using MySQL Connector for OpenOffice.org has the following advantages:

• Easy installation through the OpenOffice.org Extension Manager.
• Seamless integration into OpenOffice.org.
• Work on multiple MySQL schemata (databases) simultaneously
• Connect to MySQL servers using named pipes (Windows) or Sockets (Unix)
• No need to go through an additional Connector installation routine (ODBC/JDBC)
• No need to configure or register an additional Connector (ODBC)
• No need to install or configure a driver manager (ODBC)
• No need for a Java Runtime Environment (JDBC)

Status

The MySQL Connector for OpenOffice.org is released in version 1.0, in production quality.