IMDB::Film-0.43 is released

I just released a fresh version of IMDB::Film module to grub movie metadata from IMDB.com. There is not any new functionality in that release. Just bug fixes:

  • fixed issue with plot retrieving;
  • fixed retrieving of movie plot keywords;
  • fixed parsing rating and top 250 or bottom 100 movie property;
  • fixed issue with parsing AKA movie property;
  • fixed bug with getting movie ID from cached paged;
  • updated tests.

Many thanks for all who sent me bug-reports and patches. Grab that version and let me know if you find some problems there.

Perl, C++, mysql and UTF8

Recently I finished with Russian translation of LinuxMCE UI. But as I know now it was the easiest part. After addition Russian descriptions in the database I faced with problem of displaying them. I made a research and found following. To have UTF8 data in the mysql database a few steps should be done:

  • default charset for server should be set to utf8 in the /etc/mysql/my.cnf:
    [mysqld]
    default-character-set=utf8
  • default charset for client should be set to utf8 in the /etc/mysql/my.cnf:
    [client]
    default-character-set=utf8

    After that the charset for mysql shell will be UTF8 but not latin1 as in the stock version.
  • default charset for desired database should be set to utf8:
    alter database pluto_main charset=utf8;
  • default charset for desire table should be set to utf8:
    alter table Text_LS charset=utf8;
  • default charset for all text fields in the table should be also set to utf8:
    alter table Text_LS modify column Description longtext CHARACTER SET utf8 COLLATE utf8_general_ci
  • the client application should pass UTF8 flag to tell mysql about charset for connection. Here is a Perl example:
    my $dbh = DBI->connect("dbi:mysql:pluto_main;host=localhost", "root") or die "Cannot connect to database: $DBI::err!";
    $dbh->{'mysql_enable_utf8'} = 1;
    $dbh->do('SET NAMES utf8');

To display UTF8 text in the Perl script just set UTF8 charset for STDOUT:
binmode STDOUT, ":utf8";

But with LinuxMCE the situation is more complicate. Its UI is developed on C++. So, After a googling I found the way to set UTF8 for connection. There is a function mysql_options() in the Mysql C API. It should be called after mysql_init() but before mysql_connect() or mysql_real_connect() and allows to set desire charset:
mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, 'utf8');
and run some SQL statement when connecting to the MySQL server:
mysql_options(&mysql, MYSQL_INIT_COMMAND, 'SET NAMES utf8');
But it didn’t help. The Russian text is still displayed as question mark. So, have to dig LinuxMCE code to solve that. Otherwise the Russian translation won’t be added.

IMDB::Film v0.42 is out!

I just released a new version of IMDB::Film – 0.42. Following changes were done since previous version:

  • added retrieving of plot keywords for the film;
  • fixed issue with retrieving of movie’s plot contained links;
  • fixed issue with displaying special HTML symbols;
  • added test for the plot keywords;
  • fixed POD documentation.

To implement correct displaying of special HTML symbols two new modules were added into dependencies list – use Text::Unidecode and HTML::Entities.

You can send bugs or ideas to improve the module via CPAN RT.

Oracle date format and Perl

Oracle has its own date format – DD-MM-YY (24-sep-09). So, to insert a value into Oracle date field you should somehow convert date from your format. Sure it can be done using some date/time module or your own function. But there are two simpler ways – set desire date/time format for session or use Oracle date conversion function.

To set specific date/time format for the session just run following query right after connection:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
After that you may insert date such 2009-09-24 without any problems.

Another way is convert date to the Oracle format inside the query:
INSERT into some_table (d_start) VALUES (TO_DATE('2009-09-24 11:12:00', 'YYYY-MM-DD HH24:MI:SS'))

Both approaches work fine. But the first one is more appropriate if you use placeholders in your query.

Connect to Oracle DB from Perl script

After successful installation of DBD::Oracle it’s time to use it. The connection string is the same as for he rest DB:
my $dbi = DBI->connect("dbi:Oracle:$db_name:$db_host:$db_port", $db_user, $db_pass);
As result of running code above I got following error:
Couldn't connect to database db_name: ORA-12154: TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach)
After googling I found that the problem was that. I tried to connect to the remove database but the driver couldn’t do that without special file – tnsnames.ora. It should be placed to the $ORACLE_HOME/network/admin and contain something like that:
db_name =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db_host)(PORT = db_port))
)
(CONNECT_DATA =
(SERVICE_NAME = db_name)
)
)

And the connection string should be changed to use service name from the tnsnames.ora instead of host:
my $dbi = DBI->connect("dbi:Oracle:$service_name", $db_user, $db_pass);
Finally we should export variable ORACLE_SID into our environment. Add this command into .bashrc
export ORACLE_SID="orcl"
or set it using Perl variable $ENV:
$ENV{ORACLE_SID} = 'orcl';

See also

Installing DBD::Oracle under Fedora 11

Recently I got a task which needs to communicate with database Oracle from Perl. Perl has an excellent database abstraction interface DBI. But for specific database it needs a driver – DBD module. To install DBD::Oracle you have to perform following simple steps:

  1. Download and install oracle-instantclient11.2-basic and oracle-instantclient11.2-devel RPMs from Instant Client Downloads for Linux x86 page.
  2. Export ORACLE_HOME:
    export ORACLE_HOME=/usr/lib/oracle/11.2/client/
  3. Install DBD::Oracle using cpan shell or manually.

The most difficult part for me was installing Oracle client. Because navigation on the Oracle web site is not clear. Additionally you can install SQL*Plus package (oracle-instantclient11.2-sqlplus) to use Oracle shell for testing.

The RedHat Enterprise and CentOS users can use Perl-DBD-Oracle RPM to avoid separate Oracle client installation.