I know this is a question that most people will frown upon, but boss insists so…
I always use Symfony under Linux, but now I need to use Symfony under Windows environment and using Ms Sql Server. I'm wondering if it is possible, have anybody successfully done so.
using only sqlserver as keyword, I find three posts
In the guide, you will find that Propel support SQLServer, so I think you shouldn't have too much problems. http://www.symfony-project.org/book/trunk/08-Inside-the-Model-Layer#Database%20Connections
The main problem I see is that I don't think you will have a large base of developpers to help you if you have a SQLServer-centric problem.
documented on: 04 July 2007, nautilebleu
I've no problem with MS Server, but be aware that Apache on Windows is not great (I should know, for the moment I am stuck with it). At the very least reformat your server and do a clean install, ideally Win 2003, and use fcgid or fastcgi. The default MPM will give you threading problems.
Related article:
documented on: 04 July 2007, halfer
I have now used symfony on windows (2000, XP, and 2003) using iis and sql server (2000) on about 4 different production class projects.
I have used the php isapi extension with great success.
Everything works well. There is a small bug in the propel stuff that makes generating the insert sql a pain (does [fieldname]_PK instead of [fieldname_PK] but thats an easy fix).
I have had no issues with anything.
On one of the projects I even store all the i18n translations in the database (using the creole message source) as well as all the php sessions.
Most of the clients I do things for are reluctant enough to use php and smyfony, but trying to get them to move away from win, iis, and sql is even harder.
If you have any questions, just let me know.
chris.
documented on: 05 July 2007, pyjamasam
Wondering if anyone out there has experience using Symfony with SQL Server (2000 in my case). I've just gone through (most of) the tutorial using MySQL, then switched the configuration over to MSSQL and have experienced a few problems. I got around some trouble in creating tables by manually modifying the generated-schema.sql but now I'm having a problem with update statements where MSSQL is complaining about an attempt to update an ID field:
[Native Error: Cannot update identity column 'ID'.] [User Info: UPDATE weblog_comment SET ID = 3,POST_ID = 1,EMAIL = '' WHERE weblog_comment.ID=3]]
Note I'm not trying to update the id field, but one of the other fields. This is probably a creole or propel question but I wonder if there's a way to configure around this?
Any pointers would be much appreciated. I'm trying to make a quick determination of whether Symfony with MSSQL is feasible.
documented on: 11 August 2006, xyzzy
Can you send me your database.yml config file?
I tried to test symfony with MSSQL too, but I can't even connect to the MSSQL server. I always receive the error:
"[wrapped: connect failed [Native Error: ] [User Info: Array]]"
This is my database.yml file:
all:
propel:
class: sfPropelDatabase
param:
dsn: [url]mssql://sa@servername/Northwind[/url]
The strange thing is that I can connect with another php code snippet that uses adodb:
<?php
$db = new COM("ADODB.Connection");
$dsn = "DRIVER={SQL Server}; SERVER={servername};UID={sa};PWD={}; DATABASE={Northwind}";
$db->Open($dsn);
$rs = $db->Execute("SELECT * FROM categories");
while (!$rs->EOF)
{
echo $rs->Fields['categoryName']->Value."<BR>";
$rs->MoveNext();
}
?>
impiastro
Welcome both, to the forum. (By the way, if you could wrap your yml in [code] tags, and your php in [php] tags, it makes things much more readable - more here).
I've not used MS SQL with symfony, but there is a Creole driver for it, so I should think it would work. Also, there is an ODBC driver, if you would rather access it that way.
impiastro, your standalone snippet is going through ODBC, not the native MS SQL calls. Try the calls here to get a fair comparison.
xyzzy, it sounds like you have connected successfully, so you are part way there. I'd suggest that you check that your primary key is marked as such in your schema. Also, ensure that you haven't inadvertently reset the value of your key in the Propel object it belongs to.
halfer
The configuration I was using:
The databases.yml has this:
all:
propel:
class: sfPropelDatabase
param:
dsn: mssql://blah:blah@localhost/blah
and propel.ini has this (I had to change database name/type after finding that whatever was specified in doc for SQL Server was wrong):
propel.database = mssql propel.database.createUrl = mssql://blah:blah@localhost/ propel.database.url = mssql://blah:blah@localhost/blah
I never got past problem with update mentioned in original post and had to give up on using symfony due to time constraints but hopefully I'll get back to it one of these days and will try suggestions given in response to your post (thanks for those to whoever made them). One problem in my case is that I can only access the SQL Server database via stored procedures so most of the benefits of Symfony O/R mapping wouldn't be available to me.
xyzzy
I still think it's an issue with your primary key. Could you post your yaml or xml schema, for just this table, in [code] tags?
Meanwhile, symfony's ORM is okay, but it's not the major benefit, to my mind - the best thing is the way it separates an app out into logical layers. It is possible to refactor code files to have 100-200 lines, depending on ones preference, and this keeps things very neat and maintainable indeed.
You can incidentally extend your data layer to access stored procedures (using raw sql) and return the results from the most appropriate part of your model.
halfer
Report message to a moderator
Here's the definition from schema.yml:
weblog_comment:
_attributes: { phpName: Comment }
id:
post_id:
author: varchar(255)
email: varchar(255)
body: longvarchar
created_at:
So you're right that there's no indication that the id field is the primary key, but I checked the configuration that seemed to work with MySQL and it was the same there, so maybe this is a SQL Server glitch? Or maybe this isn't the snippet of interest?
xyzzy
So you're right that there's no indication that the id field is the primary key, but I checked the configuration that seemed to work with MySQL and it was the same there, so maybe this is a SQL Server glitch?
Not necessarily. It may be that MySQL allows its PKs to be overwritten, and MSSQL does not. Check the documentation on yaml schemas, ensure that your PK is labelled as a PK, and then try again. I suspect that will ensure it works with both db platforms.
documented on: 29 October 2006, halfer
> PropelException:[wrapped: mssql extension not loaded[User Info:Array]] > any ideas?
you need to load mssql in php with extension=php_mssql.dll in your php.ini or however if not on windows.
documented on: 20 February 2006, RoVeRT
Have anyone use Symfony connect to Ms SQL .I have a problem with it,It alway have SQLException .With the database.yml
all: propel: class: sfPropelDatabase param: phptype: sqlserver hostspec: E-Field0204 database: Northwind username: sa password: db
and It's easy to connect through mssql.dll
$connect = mssql_connect('E-Field0204','sa','db');
It's Ok and I have no problem when connect to mysql
The log file Error :
Mar 29 18:02:30 symfony [info] {sfCreole} connect(): DSN: array ( 'database' => 'Northwind', 'encoding' => NULL, 'hostspec' => 'E-Field0204', 'password' => 'db', 'persistent' => NULL, 'phptype' => 'mssql', 'port' => NULL, 'protocol' => NULL, 'username' => 'sa', ), FLAGS: 0
could the problem be with null port number? try to specify it (default is 1433)
also you may have a look at this post: http://permalink.gmane.org/gmane.comp.php.cowiki.user/195
pezetgee
Thanks pezetgee!
It's the problem with port Number!
The error also appear when we use "port: 1433" in database.yml .It's just OK when we use defaut port. I.e., Follow your link ,I fix it by comment in file creole/drivers/mssql/MSSQLConnection.php:
/*
if(!empty($dsninfo['port'])) {
$dbhost .= $portDelimiter.$dsninfo['port'];
} else {
$dbhost .= $portDelimiter.'1433';
}
*/
I am not sure how much of this is trouble with symfony and how much is propel, but below is the list of steps I had to take to port a symfony schema to MS SQLServer. I thought I would post it in case there are bugs that can be fixed and also to help anyone else who may need to do the same task. I would also be most happy to learn if the source of my problems was in front of the screen and if so what I did wrong.
I started with a schema.xml file that works wonderfully in MySQL. Instead of editing the databases.yml I had to edit the propel.ini file to use propel.database = mssql. I discovered this from some related tickets although supposedly this bug was already fixed (I am using 0.7.1218). Once I made this change, I was able to get the symfony propel-build-sql command to generate SQLServer syntax instead of MySQL syntax.
Unfortunately, I also had to make a couple of other changes. I now have 2 separate versions of my schema.xml file. The MySQL version uses
onDelete="RESTRICT" onUpdate="RESTRICT"
in the foreign key attributes and the SQLServer one replaces these with
onDelete="NO ACTION" onUpdate="NO ACTION"
In the resulting schema.sql file I then had to replace all occurrences of
CONSTRAINT [<table_name>]_PK PRIMARY KEY ([id])
with
CONSTRAINT [<table_name>_PK] PRIMARY KEY ([id])
I assume this is a bug in propel that puts the ] in the wrong place.
I also had to move all of the foreign key statements to the bottom of the file since SQLServer doesn't appear to have an equivalent to the MySQL set foreign_key_checks = 0 statement.
Lastly, I had to edit any foreign keys referencing the user table to use [user] instead. Propel does a pretty good job of putting the [] around all table and column names but it appears they missed this spot.
The good news is that once I made all of these changes the script runs just fine.
I am not looking forward to repeating these steps every time my model changes.
documented on: 26 April 2006, jrogoff
I am using Microsoft SQL Server 2000, and can get propel to connect fine.
However, when I run propel-build-sql, the sql is generated so that the ALTER TABLE commands are located in amongst the file before all the tables have been created - this generates 'table not found' errors as there are references to tables not yet created.
E.g. instead of (code simplified for illustration):
CREATE TABLE table_1 CREATE TABLE table_2 ALTER TABLE table 1 ADD CONSTRAINT table_1_FK_1 FOREIGN KEY table_2_id REFERENCES table_2
I get:
CREATE TABLE table_1 ALTER TABLE table 1 ADD CONSTRAINT table_1_FK_1 FOREIGN KEY table_2_id REFERENCES table_2 CREATE TABLE table_2
At the moment I can solve this by manually moving all the ALTER TABLE statements to the end of the sql file, but this is slightly laborious, and obviously prevents propel-insert-sql from working at all.
Does anyone know a way to get the sql to generate correctly?