Web & Business

Interfacing with third party data feeds and loading data to your own database happens all over the business world. Whether you are loading data from an XML, CSV, or TXT file, every now and then you will run in to that annoying mystery character error. You’ve written your code to read your XML file and insert rows in to your database, but when you go check the data that was inserted with SQL Developer (or your program of choice), you notice that some characters have been turned in to mysterious black boxes with white question marks. The problem is with encoding multi-byte characters.

You need to make sure that your database and tables are created with UTF-8 encoding. The second step is to make sure that your database connection from Symfony/Propel is made using UTF-8 encoding as well. In your database.yml file, you need to specify the encoding.

The yml for your connection should look something like:

prod:
  propel:
    class:  sfPropelDatabase
    param:
      phptype: oracle
      hostspec: (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)))(CONNECT_DATA = (SID=YOUR_DB_SID)))
      username: YOUR_DB_USERNAME
      password: YOUR_DB_PASSWORD
      encoding: utf8

Run your script again and import data with the right encoding. Your mystery characters should go away from your database. Enjoy.

Those of you who stumbled upon this post with the same problem, but are not using Symfony and/or Propel, the theory is the same. If you are just using PHP’s PDO to connect to MySQL or another database abstraction layer of your choice, the above steps still apply. First, make sure your database uses the right encoding and that your tables are created with it as well. Second, make sure your database connection from PHP is established with the UTF-8 encoding. It makes all the difference.

Having problems connecting to your Oracle database with Symfony?
Check out my previous post about the matter: Connecting to an Oracle 11g database with Symfony 1.0 & Propel

I recently worked on a data integrations project for a big mobile communications company. The job required some data feeds to be imported to an Oracle database. While the e-commerce framework was using Symfony 1.0, the database was top of the line Oracle 11g, so the challenge was to get Propel to connect to the new Oracle database.

After researching the issue for hours and looking through every blog post, article, and tutorial that mentioned Symfony, Propel, and Oracle; I was able to piece together a solution. The solution to this problem was not outlined anywhere else on the entire internet, so here I am writing a post about it.

THE SOLUTION

The way to connect to an Oracle database from an old version of Symfony and Propel is actually quite simple. The host, port, and SID have to all be configured in the database.yml file in one TSN connection string.

The TSN connection string in your config/databases.yml file should look something like this.

prod:
  propel:
    class:  sfPropelDatabase
    param:
      phptype: oracle
      hostspec: (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)))(CONNECT_DATA = (SID=YOUR_DB_SID)))
      username: YOUR_DB_USERNAME
      password: YOUR_DB_PASSWORD

Connecting to Oracle this way will probably work with newer versions of Symfony and Propel as well. Note that you must have PHP compiled with the oci8 Oracle extension for Propel to be able to connect to an Oracle database.