Skip to content


Subversion checkout URL

You can clone with
Download ZIP


Extending Propel to emulate database functions #564

suporteon opened this Issue · 7 comments

4 participants


I need to translate an application from MySQL to SQL Server.

There are many hard-coded queries involving MySQL database functions like NOW(), LCASE(), MATCH() AGAINST(), that has equivalent in SQL Server, but with a different syntax and structure.

I choose Propel because it seems to be easy-to-use.

I'm looking for a way to emulate database functions like NOW() {MySQL} and GETDATE() {SQL Server} by extending Propel classes.

I think something like this:

$result = Function::NOW(); // MySQL
$result = Function::GETDATE(); // SQL Server
When the function accepts parameters, like LCASE() {MySQL} or LOWER() {SQL Server}, it could be like this:

$table = new MyTable();
$result = Function::LCASE($table->getName()); // MySQL
$result = Function::LOWER($table->getName()); // SQL Server

$table = new MyTable();
$result = Function::MD5($table->getPassword()); // MySQL
$result = Function::HASHBYTES('MD5', $table->getPassword()); // SQL Server
The perfect solution would be if I can create equivalence between two functions with the same purpose from different databases (MySQL and SQL Server).

So, instead of using Function::HASHBYTES() in my PHP code, I could always use Function::MD5(), because it is more simple, and when my application is connected to a SQL Server Database, internally, HASHBYTES() is invoked.


@suporteon, how do you want to use it? I really don't fully understand. Are you searching for a approach that 'translates' My-SQL to MS-SQL or do you want just something like the following?

$query = new myTableQuery();

Your examples confuses me a bit. Can you provide a real example (not pseudocode), please?
Actually, in Propel you select columns through:


If you use here something like


Then you need probably some kind of SQL translator. Integrating this into propel should be handled as the applyLimit in the DBAdapter classes.
Take a look here runtime/lib/util/BasePeer.php +845 and runtime/lib/adapter/DBMSSQL.php +144.


Dear marcj,

Thank you for your response.

I will provide you a real example:

Imagine I am working on an application that connects to a MySQL Database. So, I write the line below:


Lets suppose I need to migrate my application to SQL Server.

There is no MD5() function in SQL Server Databases.

I will need to do a hard work in source code of my application, by translating/replacing ALL specific MySQL functions to SQL Server equivalents.

That line will become:

$query->select("HASHBYTES('MD5', myColumn)");

Do you have a suggestion to avoid this approach? I was thinking about to emulate inside Propel some database functions for both, MySQL and SQL Server, no more than 8 functions.


I am not creating an application from scratch, I need to translate an existing application, it has many database functions inside its queries.

There is a lot of situations in which I need to implement in Propel both MySQL and SQL Server functions.

$query->select("DATEPART('hh', myColumn)");

Below, is a situation where I can't use PHP, the data manipulation needs to be done inside the query.

$query->select("CONCAT(firstName, ' ', lastName)";
$query->select("firstName + ' ' + lastName");

In the end, I would need to rewrite the code (hard work, because it is a large application containing more than 200 queries).


But your app is already Propel based, isn't it?


No, it isn't.

The application does not belong to me or my company, it is GNU.

I created a PHP script with complex regular expressions to convert all queries from the application source-code to work with Propel.

What I need now is to release the application in both MySQL, SQL Server, and maybe, other databases.

Now you can understand my point-of-view: database functions are my problem.

This is a work I'll always need to do, every time a new version of this application is available.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.