Software Salariman

A port of my Python DBI program to Perl. Note that you have to install package DBD-ODBC for the ODBC driver.

use warnings;
use strict;
use DBI;

use constant MDB_PATH => '<path>';

my $dbh = DBI->connect('DBI:ODBC:DRIVER=Microsoft Access Driver (*.mdb);Dbq=' . MDB_PATH);

my $sth = $dbh->prepare(
  "SELECT name FROM MSYSOBJECTS WHERE name NOT LIKE 'MSYS%' AND type = 1");
$sth->execute();
my $ref = $sth->fetchall_arrayref();

for my $row ( @{$ref} )  {
  my $table_name = @$row[0];
  my $sth = $dbh->prepare("SELECT COUNT(*) FROM [$table_name]");
  $sth->execute();
  my @data = $sth->fetchrow_array();
  if ($data[0] == 0) {
    print "$table_name is empty\n";
  }
}

$dbh->disconnect();

See Also

• List Empty Access Tables using Python.

PS

2008-08-08: Replaced sprintf("SELECT COUNT(*) FROM [%s]", $table_name) with "SELECT COUNT(*) FROM [$table_name]" since Perl can evaluate variables in a string.

Added empty parentheses when calling functions to be consistent.

Labels: Database, Perl

We wanted to find empty tables in a Microsoft Access database. Below is a Python script that uses the PythonWin odbc module to find empty tables in an MS-Access database. Edit the required path to your database file by modifying the MDB_PATH variable.

Follow the note at the start of the script to configure your MS-Access database security if you get the following message: dbi.program-error: [Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'MSYSOBJECTS'. in EXEC.

# List empty tables in an Access database by Kam-Hung Soh 2008.
# Before using this script, you have to allow User and Group Permissions in Access.
# 1. Open database.
# 2. Select menu item Tools / Security / User and Workgroup Permissions.
# 3. In 'User and Group Permissions' dialog:
# 3.1. Select User/Group Name = Admin.
# 3.2. Select Object Name = MSysObjects.
# 3.3. Check 'Read Data' check box.
# 3.4. Press OK button to close dialog box.

import odbc

MDB_PATH = r'<path>'

conn = odbc.odbc(r"DRIVER={Microsoft Access Driver (*.mdb)}; Dbq=%s;" % MDB_PATH)
cur = conn.cursor()
cur.execute(r"SELECT name from MSYSOBJECTS WHERE name NOT LIKE 'MSYS%' AND type = 1")
for x in cur.fetchall():
    table_name = x[0]
    cur.execute(r'SELECT COUNT(*) FROM [%s]' % table_name)
    row = cur.fetchone()
    if row[0] == 0:
        print table_name + ' is empty'
cur.close()
conn.close()

Script Notes

MS-Access stores object metadata in a system table called MSysObjects. In this table, a user table object has a type value of '1' and its name doesn't start with 'MSys'. This script first gets a list of all user tables from MSysObjects, then counts the number of rows in those tables. If a table has no rows, the script prints the table name and a message.

The fetchall() function always returns a list of tuples even if only one column is selected, so you have to extract the required column data using an array operator (e.g. x[0]).

The table name in the second cur.execute() SQL statement is delimited by square brackets in case the table name has whitespaces. Without these delimiters, you may see the following message: dbi.program-error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in WITH OWNERACCESS OPTION declaration. in EXEC.

See Also

• List Empty Access Tables using Perl.

Labels: Database, Python

Introduction

A pivot table or cross tabulation is a reporting feature that BAs love to use to summarise transaction data, such as server logs and sales figures. Spreadsheet programs such as Microsoft Excel or OpenOffice.org Calc have nifty wizards to help you create a pivot table. You can also create pivot tables in databases. For example, Microsoft Access has a TRANSFORM … PIVOT SQL statement for generating a crosstab or pivot table.

What if you're using a database program that doesn't directly support pivot tables? For example, Sqlite 3 and MySQL don't seem to have any SQL statements for pivot tables.

All is not lost; another way to express a pivot table is to use aggregate functions, condition clauses and GROUP BY clause in this template:

SELECT col1, col2, … <aggregate>(<condition>) … FROM table1 GROUP BY col1, col2, ….

For Sqlite 3, the aggregate functions and GROUP BY is similar to SQL in other datbase programs. The condition clause we can use has this syntax: case when <expression> then <expression> end.

In the next section, we'll demonstration how to create pivot tables in Sqlite 3 using this template. All examples will be shown using Sqlite 3's command line interface, sqlite3.exe.

Sqlite 3 Pivot Table Demonstration

First, you have to download some sample transaction data. I used the NumberGo Pivot Table Tutorial AcmeShirtsCompany.xls spreadsheet as the raw data for this demonstration.

We start sqlite3.exe and use the -column -header arguments make the output of queries more readable.

sqlite3 -column -header test.db
SQLite version 3.5.9
Enter ".help" for instructions

Now we create a shirt table based on the headings in that spreadsheet:

create table shirt (Region varchar(8), Category varchar(8), Shirt_Style varchar(8), ShipDate date, Units integer, Price double, Cost double);

Next we load some transaction data into the shirt table:

insert into shirt values ('East','Boys','Tee',date('2005-01-01'),11,5.25,4.66);
insert into shirt values ('East','Boys','Golf',date('2005-01-01'),12,5.26,4.57);
insert into shirt values ('East','Boys','Polo',date('2005-01-01'),13,5.27,5.01);
insert into shirt values ('East','Girls','Tee',date('2005-01-01'),14,5.28,5.01);
insert into shirt values ('East','Girls','Golf',date('2005-01-01'),15,5.29,5.10);
insert into shirt values ('East','Girls','Polo',date('2005-01-01'),16,5.30,4.67);
insert into shirt values ('West','Boys','Tee',date('2005-01-01'),33,6.25,5.36);
insert into shirt values ('West','Boys','Golf',date('2005-01-01'),35,6.26,6.24);
insert into shirt values ('West','Boys','Polo',date('2005-01-01'),36,6.27,6.03);
…

Let's begin our analysis with a simple question: How many shirts were sold in each region?

select Region, sum(Units) from shirt group by Region;
Region      sum(Units)
----------  ----------
East        21841
North       27275
South       29994
West        23984

Next: in each region, how many Boys' and Girls' shirts were sold? Here's where a pivot table is useful:

select
  Region
  , sum(case when Category = 'Boys' then Units end) as Boys
  , sum(case when Category = 'Girls' then Units end) as Girls
  , sum(Units) as SubTotal
from shirt
group by Region;
Region      Boys        Girls       SubTotal
----------  ----------  ----------  ----------
East        10586       11255       21841
North       14049       13226       27275
South       14312       15682       29994
West        10763       13221       23984

We can drill further into the data: How many of each shirt style were sold?

select
  Region
  , Category
  , sum(case when Shirt_Style = 'Tee' then Units end) as Tee
  , sum(case when Shirt_Style = 'Golf' then Units end) as Golf
  , sum(case when Shirt_Style = 'Polo' then Units end) as Polo
  , sum(Units) as SubTotal
from shirt
group by Region, Category;
Region      Category    Tee         Golf        Polo        SubTotal
----------  ----------  ----------  ----------  ----------  ----------
East        Boys        3458        3096        4032        10586
East        Girls       3688        3481        4086        11255
North       Boys        4597        4702        4750        14049
North       Girls       4196        4598        4432        13226
South       Boys        5192        4670        4450        14312
South       Girls       5113        5377        5192        15682
West        Boys        3722        3791        3250        10763
West        Girls       4472        4235        4514        13221

The pattern becomes obvious, if rather tedious, when you want to use a specific values as a new virtual column.

Discussion

In this article, I've presented a SQL template for generating pivot tables for database programs, such as Sqlite 3, that do not have explicit support for this feature. While this template is extensible, it relies on the developer knowing beforehand the possible values (e.g. Category has 'Boys' and 'Girls', or Shirt Styles has 'Tee', 'Golf' and 'Polo') to use in the condition clause of the template. If there are many possible values, then it becomes very tedious to enumerate each of them in the SQL case when … then … end clause.

2008-06-01: I had a play with MySQL and found that I can use the same SQL statements to create the pivot tables.

I had saved the data in AcmeShirtsCompany.sql, so to set up my MySQL database, I created the shirt table using mysql.exe, exit the interpreter, then loaded the data into the database using this cmd.exe command: mysql -u root -p -D test < AcmeShirtsCompany.sql.

2008-06-06: See also SQL Cookbook by Anthony Molinaro, O'Reilly Media.

Labels: Database, Programming

Introduction

I wanted to export all tables from an Access database into a folder of CSV formatted text files. For most data types, such as Text or Integer, all data was exported without any loss. However, floating point numbers (Float or Double) were exported with only two decimal places.

Approach

You can export a Microsoft Access table to a text file using either the menu item File / Export or this Access SQL statement from KB208408 …

SELECT * INTO [Text;FMT=Delimited;HDR=Yes;Database=<Path>].[<Table>.csv] FROM <Table>

Solutions

Here's three solutions with varying advantages:

• KB153364 article suggests formatting the number column to define the required decimal places. This solution requires writing a SQL statement for each table you're exporting.
• You can add a schema.ini into the destination export folder and specify the number of decimal places for all floating point numbers in a table using the NumberDigits keyword. This solution requires creating or copying a schema.ini file into the export directory before exporting the tables.
• You can change the No. of digits after decimal in your Regional and Language Options of your computer. This solution means you or your user has to remember to the change that setting on their computer before exporting the tables.

About Schema.ini File

A slight digression: schema.ini is a file used by the Text Driver to determine the characteristics of tables and columns of a database. You can find the syntax and properties of the schema.ini file in Schema.ini File (Text File Driver).

When exporting tables, Access creates a schema.ini file in the export folder if that file does not exist. If there is an existing schema.ini file and the section for the output CSV file does not exist, Access will add a new section for the output CSV file. Otherwise, Access does not change the schema.ini file.

Conclusion

None of the solutions are very satisfactory for writing a general program to export tables from an Access database into a folder of text files. The simplest solution is to define the required number of decimal places in the database connection string (the part between the square brackets in the SQL statement). However, I couldn't find any formal specification of this string on the Web.

Labels: Database, Software

The table below relates users to groups. Which groups do two specified users have in common?

        ID    USER_ID   GROUP_ID
---------- ---------- ----------
         1          1          1
         2          2          1
         3          1          2
         4          3          2
         5          2          2

Here's a query that works in MS-Access and Oracle Express:

select group_id
from
  user_group where group_id in (select group_id from user_group where user_id = 1) 
  and user_id = 2;

  GROUP_ID
----------
         1
         2

Note: I've used where … in because a user can belong to more than one group. If the sub-query returns more than one row, Oracle Express reports this error:

ORA-01427: single-row subquery returns more than one row

You can look at the problem as one where you're looking for the intersection of two sets. If your DBMS supports it, you can use the intersect set operator. The following query works in Oracle Express:

select group_id from user_group where user_id = 1
intersect
select group_id from user_group where user_id = 2;

  GROUP_ID
----------
         1
         2

Labels: Database, Oracle

Connecting JDBC-based tools such as SQL Developer or DbVisualizer to SQL Server Express required the following steps:

• Obtain JDBC Driver
• TCP/IP for SQL Server Express
• Authentication Method

Obtain JDBC Driver

Using SQL Developer, when you get the following exception …

Unable to find driver: net.sourceforge.jtds.jdbc.Driver

… download the jTDS JDBC driver and install it in your JRE's ext folder. The latest version of the driver is 1.2. Of course, there are other JDBC drivers for SQL Server you can use.

TCP/IP for SQL Server Express

By default, TCP/IP for SQL Server Express is disabled, so JDBC cannot connect to it and you may get the following exception …

Network error IOException: Connection refused: connect

Enable TCP/IP

To enable TCP/IP, start SQL Server Configuration Manager.

1. Expand SQL Server 2005 Network Configuration node.
2. In the right pane, select Protocols for SQLEXPRESS. The right pane should now show Protocols and Status columns.
3. Select Enable from the TCP/IP context menu.

Find or Configure TCP/IP Port

After enabling TCP/IP, you have to find out which port number to use. SQL Server Express allocates a port dynamically each time it is started, so to find or configure the port number, continue using SQL Server Configuration Manager …

1. Select Properties from the TCP/IP context menu. The TCP/IP Properties dialog should open.
2. Select the IP Addresses tab.
3. In the IPAll node …
   1. The TCP Dynamic Ports field shows the currently used port number. If you set that field to blank, then SQL Server Express should not automatically choose another port when it restarts.
   2. Set the desired port number in the TCP Port field.
   3. Press OK to apply your settings and close the dialog.

Test TCP/IP

If you change the TCP/IP port, you have to restart SQL Server Express before it can use the new port number. To test that your port number is used, start a cmd window and type: netstat -an. For instance, if you used port 1433, you should see this line in the list of ports used:

TCP    0.0.0.0:1433           0.0.0.0:0              LISTENING

Authentication Method

By default, SQL Server Express uses Windows Authentication Mode to authenticate connections. If you get this exception …

Login failed for user '<User name>'. The user is not associated with a trusted SQL Server connection.

… then you may have to enable SQL Server Authentication Mode and create or enable a user.

1. Start Microsoft SQL Server Management Studio Express (SSMSE) and connect to your database server.
2. In Object Explorer pane, select Properties from your database's context menu. The Server Properties dialog should open.
3. Select Security page.
4. Select SQL Server and Windows Authentication Mode check box.
5. Press OK button to close the dialog.
6. In Object Explorer pane, expand Security / Logins node.
7. Select existing user sa. Note that there is a red downward arrow next to that user's image.
8. View sa's properties. The Login Properties dialog should open.
9. Select Status page.
10. Ensure that the Login: Enabled radio button is selected.
11. Select General page.
12. Enter a password for this user.
13. Press OK button to close the dialog.
14. If you refresh the Object Explorer pane, note that user sa no longer has a red downward arrow.

Finally …

After all these steps, you should be able to connect to your SQL Server Express database using JDBC.

Labels: Database, Java

A quickie: I had to dump all the data from an Oracle database and e-mail them to a colleague. Not being familiar with backup and restore for Oracle, I decided to just run exp for each of the tables. To get a list of tables in a schema, I executed this query in DbVisualizer, SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = '<schema>', and saved the output to a file, table.txt. Then in Windows cmd, I wrote this script into a file and ran it:

for /f %%i in (table.txt) do exp <connection string> TABLES=%%i FILE=%%i

Labels: Database, Oracle, Windows Cmd

OpenOffice.org has released version 2.2 of their office suite. Seems to be just a bug-fix release. Having installed the latest version, I thought I'd connect the Base product to my test MySQL database. It should have been painless but I kept getting a JDBC driver could not be loaded error. After some head-scratching, it turns out that the MySQL JDBC driver had to be copied into the Java Runtime (JRE) ext folder. Here's how I set up a connection between OpenOffice and MySQL.

1. Download and extract mysql-connector-java-5.0.4-bin.jar.
2. Copy mysql-connector-java-5.0.4-bin.jar to C:\Program Files\Java\jre1.5.0_11\lib\ext\ folder.
3. Start OpenOffice and select Tools / Options menu item, then select Java from the tree pane.
4. In Java Options pane, select the appropriate JRE and select the OK button.
5. In OpenOffice, select File / New / Database menu item.
6. In Select Database step, select Connect to an existing database radio button, select MySQL from the drop down list, then press the Next button.
7. In Set up MySQL connection step, select Connect using JDBC (Java Database Connectivity) radio button, then press the Next button.
8. In Set up JDBC connection step, press Test class button. If your connector is copied in the JRE's ext, then the JDBC Driver Test dialog should report The JDBC driver was loaded successfully. If the test fails, it will report The JDBC driver could not be loaded. Close the test dialog and continue configuring your database connection.
9. Enter the Name of the database (e.g. test), Server URL (e.g. localhost) then press the Next button.
10. In Set up user authentication step, enter User name (e.g. root) then press Test Connection button. If the connection was unsuccessful, the Connection Test dialog should report Access denied for user 'X' to database 'Y'. Close the test dialog and press the Next button.
11. In Save and proceed step, use the default options then press the Finish button. OpenOffice will prompt you to save the database connection as an .odb file.

Notes: OpenOffice doesn't find the MySQL driver class after setting the CLASSPATH in the Options dialog. Another bug is that I can't remove a wrong JAR entry from the Options dialog.

Labels: Database, Software

We wanted a quick hack to initialize a table with a set of rows. The computer we were using had a free version DBVisualizer which only supported running one SQL statement at a time and we didn't want to install SQLPlus or SQL Loader yet. This Wikipedia Insert (SQL) entry shows how to do multi-row insert in various flavours of SQL. The Oracle solution uses a system table called DUAL which seems to act as a placeholder in the SQL statement and return the result of any query.

Labels: Database, Oracle

I was trying to shutdown an Oracle server on my Windows test computer. Whenever I try to enter my credentials in the Oracle Enterprise Manager console, I get this message: RemoteOperationException: ERROR: Wrong password for user. Oracle's online help was utterly useless because it just describes exactly what I could see with no explanation or tips. I checked on Google and I found this thread. Message 173817 explains what to do, which is to allow my credentials to be used for running a batch job on Windows.

Labels: Database, Oracle