Doctrine TimeStamp Error: Import SQL Server 2008 tables
How to import a SQL Server table with a field Timestamp with Doctrine? When the tutorials here and there are not enough, there is often only one way to achieve your ends... Get your hands dirty! Today I propose a solution, which I hope will be quickly integrated by the Community in charge of the development of Doctrine, which allows to create Symfony2 entities from a SQL Server database which contain a field of type Timestamp.
What is the Timestamp type?
It seems necessary to me to specify some small information on the Timestamp type. I'm not a MySQL expert, but from information I've come across, the MySQL Timestamp looks like a date, which makes it easier to convert. However, the same type for SQL Server is a little more subtle in its understanding. The documentation is available on the Microsoft Technet.
A data type that exposes automatically generated single-character binary numbers in a database.
Interesting… And how do I tell Doctrine not to convert me? I propose a solution to divert the problem...
Find a “custom” solution
I can already see the messages coming telling me that there is already a ready-made method that works. YES ! Moreover, it is this Symfony2 task that can be called in this way:
php app/console doctrine:mapping:import
This task works fine, but as I read here, Doctrine works for about 70-80% of possible mappings on a database. In my case, I wanted to export the mapping of a SQL Server (2008) table, which contained a famous TimeStamp field. And in the remaining 20% of the mappings, there are precisely these types of fields.
I will run the command and attempt to import the mappaqe.
In my case, I had to use two additional options, namely:
- –em=”…” to specify theEntity Manager in charge of my SQL Server database;
- --filter=”table”. I wanted to use this option which worked with MariaDB but seemed to be ignored when used with MSSQL. This allows you to specify a table to export. In my case, I had copied my BDD, I had deleted the tables which did not interest me and created a new profile ofEntity Manager.
And here is the exception raised:
From now on, we're going to have to rig the dice.
The Timestamp type is not supported - and I admit I don't really know what type of PHP fields to associate it with - we will temporarily tell Doctrine that this type must be converted datetime.
- Go to the folder indicated by the tree structure, vendordoctrinedbalibDoctrineDBALPlatforms.
- In this folder are the classes to support the majority of existing DBMS.
- In my case I was working on SQL Server 2008 R2, so I edit the file SQLServer2008Platform.php, but feel free to edit another file if it matches your configuration.
- We will edit the method initializeDoctrineTypeMappings. In variable doctrineTypeMapping is present the information to convert the types. Add the line:
$this->doctrineTypeMapping['timestamp'] = 'datetime';
- Restart mapping import. The mapping of all fields have been imported.
- You must remember to remove the line you added to the file. If you are unsure, remove all vendors and re-install them.
- Now go successively to the files that have been generated. The path of each is indicated, and in turn, follow the indications below.
- You now have the equivalent of your tables in XML.
- Delete the lines that correspond to a TimeStamp type field. This way, Doctrine will ignore these fields.
- A small precaution with SQL Server is to add square brackets around each value contained by "column" having one or more spaces.
For this line,
The SQL Server column is "My Infos". Doctrine does not know how to handle spaces with MSSQL. So you need to add hooks like this:
- When your files have been corrected, we launch entity generation.
1php app/console doctrine:mapping:convert annotation ./src
You may need to specify the Entity Manager to use, like this: –em=”…”.
- You can now delete the mapping, i.e. the .xml files present in the Resources.
That's it, you now have valid entities generated by Doctrine. This solution a bit … “brian fuck” is valid as long as the team responsible for the development of Doctrine has not integrated a viable solution that would allow the TimeStamp type to be supported natively within PHP.
Good luck and see you soon !