This forum is in READ-ONLY mode.
You can look around, but if you want to ask a new question, please use the new forum.
Home » support » Installation and setup » SQL-Server data-type mismatch error using ODBC driver
SQL-Server data-type mismatch error using ODBC driver [message #77312] Thu, 23 April 2009 16:16 Go to next message
jjdunn  is currently offline jjdunn
Messages: 2
Registered: April 2009
Location: USA
Junior Member
Just getting started with Symfony. I _must_ use SQL Server 2005 to interact with legacy database.

I can connect to SQL Server using ODBC driver as recommended on PDO help site for Windows environment. I was successful in reverse-engineering my existing schema. After tweaking/cleaning it up a little, generated model classes ok. Generated Admin page for one table. It displays data ok, pages ok, sorts ok, etc. There are 37 existing records in the Locations table.

When I press the 'delete' button on one row under Actions in the generated Admin page, I get the following error:

[wrapped: SQLSTATE[42000]: Syntax error or access violation: 402 [Microsoft][SQL Native Client][SQL Server]The data types varchar and text are incompatible in the equal to operator. (SQLExecute[402] at ext\pdo_odbc\odbc_stmt.c:133)]

NOTE: I do *not* have any 'text' columns in my database (or) in my Symfony model. All columns are varchar or nvarchar.

QUESTION: Why is Propel creating a 'text' type parameter??

I'm using:
Windows XP Home (development machine only)
Apache 2.0.54
PHP 5.2.9-1
Symfony 1.2.5
Propel 1.3
SQL Server 2005 SP2 [required!]
ODBC driver
MS SQL Native Client v. 9.00.3042

Database.yml
============
all:
propel:
class: sfPropelDatabase
param:
classname: PropelPDO
phptype: mssql
dsn: "odbc:Driver={SQL Native Client};Server=localhost;Database=MYDBNAME;"
username: MYUSER
password: MYPASSWORD
persistent: true
pooling: true
encoding:
options: none

schema.yml (for one table):
===========================
Locations:
_attributes: { phpName: Locations }
LocationID: { type: VARCHAR, size: '10', primaryKey: true, required: true }
LocationName: { type: VARCHAR, size: '128', required: true }
NextAddStudentID: { type: INTEGER, size: '4', scale: '0', required: false, defaultValue: ((0)) }
DefaultState: { type: VARCHAR, size: '40', required: false }
DefaultCountry: { type: VARCHAR, size: '40', required: false }
DefaultNativeLanguage: { type: VARCHAR, size: '6', required: false, foreignTable: Languages, foreignReference: LanguageCode }
DefaultNewsLanguage: { type: VARCHAR, size: '6', required: false, foreignTable: Languages, foreignReference: LanguageCode }
DefaultAppOrigin: { type: VARCHAR, size: '30', required: false }
LocationSignature: { type: VARCHAR, size: '2000', required: false }
AcceptConfirmWeeks: { type: SMALLINT, size: '2', scale: '0', required: false, defaultValue: ((2)) }
CourseLocID: { type: VARCHAR, size: '10', required: true }
RidesRptName: { type: VARCHAR, size: '60', required: false }
bIgnoreAccents: { type: BOOLEAN, size: '1', required: true, defaultValue: ((0)) }
RegistrarEmailAddress: { type: VARCHAR, size: '50', required: false }
FromName: { type: VARCHAR, size: '50', required: false }
EmailSignature: { type: VARCHAR, size: '2000', required: false }
BounceToEmailAddress: { type: VARCHAR, size: '50', required: false }
EnableConfirmModule: { type: BOOLEAN, size: '1', required: true, defaultValue: ((0)) }
_indexes: { PK_Locations: [LocationID] }

LOG
===
Apr 23 15:31:45 symfony [debug] {sfPropelLogger} prepare: select * from (select top 1 * from (select top 1 Locations.LOCATIONID, Locations.LOCATIONNAME, Locations.NEXTADDSTUDENTID, Locations.DEFAULTSTATE, Locations.DEFAULTCOUNTRY, Locations.DEFAULTNATIVELANGUAGE, Locations.DEFAULTNEWSLANGUAGE, Locations.DEFAULTAPPORIGIN, Locations.LOCATIONSIGNATURE, Locations.ACCEPTCONFIRMWEEKS, Locations.COURSELOCID, Locations.RIDESRPTNAME, Locations.BIGNOREACCENTS, Locations.REGISTRAREMAILADDRESS, Locations.FROMNAME, Locations.EMAILSIGNATURE, Locations.BOUNCETOEMAILADDRESS, Locations.ENABLECONFIRMMODULE FROM Locations WHERE Locations.LOCATIONID=:p1 order by Locations.LOCATIONID asc) deriveda order by LOCATIONID desc) derivedb order by LOCATIONID asc
Apr 23 15:31:45 symfony [debug] {sfPropelLogger} Binding 'TEST' at position :p1 w/ PDO type PDO::PARAM_STR
Apr 23 15:31:45 symfony [err] {sfPropelLogger} SQLSTATE[42000]: Syntax error or access violation: 402 [Microsoft][SQL Native Client][SQL Server]The data types varchar and text are incompatible in the equal to operator. (SQLExecute[402] at ext\pdo_odbc\odbc_stmt.c:133)
Apr 23 15:31:45 symfony [err] {PropelException} [wrapped: SQLSTATE[42000]: Syntax error or access violation: 402 [Microsoft][SQL Native Client][SQL Server]The data types varchar and text are incompatible in the equal to operator. (SQLExecute[402] at ext\pdo_odbc\odbc_stmt.c:133)]


Stack Trace:
==============
at ()
in SF_SYMFONY_LIB_DIR\plugins\sfPropelPlugin\lib\vendor\propel\ util\BasePeer.php line 466 ...

463. if ($stmt) $stmt = null; // close
464. if ($criteria->isUseTransaction()) $con->rollBack();
465. Propel::log($e->getMessage(), Propel::LOG_ERR);
466. throw new PropelException($e);
467. }
468.
469. return $stmt;

* at BasePeer::doSelect(object('Criteria'), object('DebugPDO'))
in SF_ROOT_DIR\lib\model\om\BaseLocationsPeer.php line 362 ...
359. $criteria->setDbName(self::DATABASE_NAME);
360.
361. // BasePeer returns a PDOStatement
362. return BasePeer::doSelect($criteria, $con);
363. }
364. /**
365. * Adds an object to the instance pool.
* at BaseLocationsPeer::doSelectStmt(object('Criteria'), null)
in SF_ROOT_DIR\lib\model\om\BaseLocationsPeer.php line 325 ...
322. */
323. public static function doSelect(Criteria $criteria, PropelPDO $con = null)
324. {
325. return LocationsPeer::populateObjects(LocationsPeer::doSelectStmt($ criteria, $con));
326. }
327. /**
328. * Prepares the Criteria object and uses the parent doSelect() method to execute a PDOStatement.
* at BaseLocationsPeer::doSelect(object('Criteria'), null)
in SF_ROOT_DIR\lib\model\om\BaseLocationsPeer.php line 308 ...
305. {
306. $critcopy = clone $criteria;
307. $critcopy->setLimit(1);
308. $objects = LocationsPeer::doSelect($critcopy, $con);
309. if ($objects) {
310. return $objects[0];
311. }
* at BaseLocationsPeer::doSelectOne(object('Criteria'))
in n/a line n/a ...
* at call_user_func(array('LocationsPeer', 'doSelectOne'), object('Criteria'))
in SF_SYMFONY_LIB_DIR\routing\sfObjectRoute.class.php line 160 ...
157. throw new InvalidArgumentException(sprintf('You must pass a "method" option for a %s object.', get_class($this)));
158. }
159.
160. return call_user_func(array($className, $this->options['method']), $this->filterParameters($parameters));
161. }
162.
163. protected function getObjectsForParameters($parameters)
* at sfObjectRoute->getObjectForParameters(object('Criteria'))
in SF_SYMFONY_LIB_DIR\plugins\sfPropelPlugin\lib\routing\sfProp elRoute.class.php line 84 ...
81. $parameters = $criteria;
82. }
83.
84. return parent::getObjectForParameters($parameters);
85. }
86.
87. protected function getObjectsForParameters($parameters)
* at sfPropelRoute->getObjectForParameters(array('module' => 'locations', 'action' => 'delete', 'sf_format' => 'html', 'LocationID' => 'TEST1'))
in SF_SYMFONY_LIB_DIR\routing\sfObjectRoute.class.php line 109 ...
106. }
107.
108. // check the related object
109. if (!($this->object = $this->getObjectForParameters($this->parameters)) && (!isset($this->options['allow_empty']) || !$this->options['allow_empty']))
110. {
111. throw new sfError404Exception(sprintf('Unable to find the %s object with the following parameters "%s").', $this->options['model'], str_replace("\n", '', var_export($this->filterParameters($this->parameters), true))));
112. }
* at sfObjectRoute->getObject()
in SF_ROOT_DIR\cache\backend\dev\modules\autoLocations\actions\ actions.class.php line 85 ...
82. {
83. $request->checkCSRFProtection();
84.
85. $this->dispatcher->notify(new sfEvent($this, 'admin.delete_object', array('object' => $this->getRoute()->getObject())));
86.
87. $this->getRoute()->getObject()->delete();
88.
* at autoLocationsActions->executeDelete(object('sfWebRequest'))
in SF_SYMFONY_LIB_DIR\action\sfActions.class.php line 53 ...
50. }
51.
52. // run action
53. return $this->$actionToRun($request);
54. }
55. }
56.
* at sfActions->execute(object('sfWebRequest'))
in SF_SYMFONY_LIB_DIR\filter\sfExecutionFilter.class.php line 90 ...
87. {
88. // execute the action
89. $actionInstance->preExecute();
90. $viewName = $actionInstance->execute($this->context->getRequest());
91. $actionInstance->postExecute();
92.
93. return is_null($viewName) ? sfView::SUCCESS : $viewName;
* at sfExecutionFilter->executeAction(object('locationsActions'))
in SF_SYMFONY_LIB_DIR\filter\sfExecutionFilter.class.php line 76 ...
73. return sfView::SUCCESS;
74. }
75.
76. return $this->executeAction($actionInstance);
77. }
78.
79. /**
* at sfExecutionFilter->handleAction(object('sfFilterChain'), object('locationsActions'))
in SF_SYMFONY_LIB_DIR\filter\sfExecutionFilter.class.php line 42 ...
39. {
40. $timer = sfTimerManager::getTimer(sprintf('Action "%s/%s"', $actionInstance->getModuleName(), $actionInstance->getActionName()));
41.
42. $viewName = $this->handleAction($filterChain, $actionInstance);
43.
44. $timer->addTime();
45. $timer = sfTimerManager::getTimer(sprintf('View "%s" for "%s/%s"', $viewName, $actionInstance->getModuleName(), $actionInstance->getActionName()));
* at sfExecutionFilter->execute(object('sfFilterChain'))
in SF_SYMFONY_LIB_DIR\filter\sfFilterChain.class.php line 53 ...
50. }
51.
52. // execute the next filter
53. $this->chain[$this->index]->execute($this);
54. }
55. }
56.
* at sfFilterChain->execute()
in SF_SYMFONY_LIB_DIR\filter\sfCommonFilter.class.php line 29 ...
26. public function execute($filterChain)
27. {
28. // execute next filter
29. $filterChain->execute();
30.
31. // execute this filter only once
32. $response = $this->context->getResponse();
* at sfCommonFilter->execute(object('sfFilterChain'))
in SF_SYMFONY_LIB_DIR\filter\sfFilterChain.class.php line 53 ...
50. }
51.
52. // execute the next filter
53. $this->chain[$this->index]->execute($this);
54. }
55. }
56.
* at sfFilterChain->execute()
in SF_SYMFONY_LIB_DIR\filter\sfRenderingFilter.class.php line 33 ...
30. public function execute($filterChain)
31. {
32. // execute next filter
33. $filterChain->execute();
34.
35. // get response object
36. $response = $this->context->getResponse();
* at sfRenderingFilter->execute(object('sfFilterChain'))
in SF_SYMFONY_LIB_DIR\filter\sfFilterChain.class.php line 53 ...
50. }
51.
52. // execute the next filter
53. $this->chain[$this->index]->execute($this);
54. }
55. }
56.
* at sfFilterChain->execute()
in SF_SYMFONY_LIB_DIR\controller\sfController.class.php line 245 ...
242. }
243.
244. // process the filter chain
245. $filterChain->execute();
246. }
247. else
248. {
* at sfController->forward('locations', 'delete')
in SF_SYMFONY_LIB_DIR\controller\sfFrontWebController.class.php line 48 ...
45. }
46.
47. // make the first request
48. $this->forward($moduleName, $actionName);
49. }
50. catch (sfException $e)
51. {
* at sfFrontWebController->dispatch()
in SF_SYMFONY_LIB_DIR\util\sfContext.class.php line 159 ...
156. */
157. public function dispatch()
158. {
159. $this->getController()->dispatch();
160. }
161.
162. /**
* at sfContext->dispatch()
in SF_ROOT_DIR\web\backend_dev.php line 13 ...
10. require_once(dirname(__FILE__).'/../config/ProjectConfigurat ion.class.php');
11.
12. $configuration = ProjectConfiguration::getApplicationConfiguration('backend', 'dev', true);
13. sfContext::createInstance($configuration)->dispatch();
14.

......

[Updated on: Thu, 23 April 2009 21:42]

Re: SQL-Server data-type mismatch error using ODBC driver [message #77353 is a reply to message #77312 ] Fri, 24 April 2009 15:06 Go to previous messageGo to next message
halfer  is currently offline halfer
Messages: 9535
Registered: January 2006
Location: West Midlands, UK
Faithful Member
This is probably due to the way in which the Propel map for SQL Server works - one of your columns has been set to TEXT rather than VARCHAR. If you think there is a problem with that, report it to the Propel folks, although I wouldn't imagine that change would be made in Propel 1.2 or 1.3 as it sounds like a major change.

In the meantime, just change the type of the field in your database manually, and it should work. Personally, I do not create/recreate my database every time I do a schema change - I just make the changes manually. Would this be OK for your environment?


Remember Palestine
Re: SQL-Server data-type mismatch error using ODBC driver [message #77362 is a reply to message #77353 ] Fri, 24 April 2009 15:58 Go to previous messageGo to next message
jjdunn  is currently offline jjdunn
Messages: 2
Registered: April 2009
Location: USA
Junior Member
halfer wrote on Fri, 24 April 2009 09:06

one of your columns has been set to TEXT rather than VARCHAR.

As I wrote in my original message, there are *no* text columns in my database or in my model. Also, after reading the PDO docs in more detail, I noted that under Windows, VARCHAR columns are limited to 255 chars. Since I had 2 columns of VARCHAR(2000), I changed them to see what would happen. Regenerated the model and the Admin app. Still got the error.

Quote:

If you think there is a problem with that, report it to the Propel folks, although I wouldn't imagine that change would be made in Propel 1.2 or 1.3 as it sounds like a major change.

Yes, it does seem like either a Propel or PDO issue, not a Symfony problem. I cross-posted this to the Propel Issues mailing list but have not received any reply as yet.

Quote:


In the meantime, just change the type of the field in your database manually, and it should work. Personally, I do not create/recreate my database every time I do a schema change - I just make the changes manually. Would this be OK for your environment?

I have no problem manually making database changes. _IF_ I could find the correct change to make!

Thanks for your reply.
- Jeremy
Re: SQL-Server data-type mismatch error using ODBC driver [message #80866 is a reply to message #77362 ] Sun, 28 June 2009 23:42 Go to previous message
dru@4dinc.ca  is currently offline dru@4dinc.ca
Messages: 1
Registered: June 2009
Location: Toronto
Junior Member
jjdunn, did you manage to resolve this issue?? If so, please post.

Thanks

Dru
Previous Topic:[[ ERROR ]] PHP version is at least 5.2.4: FAILED
Next Topic:moving to a new server
Goto Forum:
  

powered by FUDforum - copyright ©2001-2004 FUD Forum Bulletin Board Software