1

It keep saying

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

Here is my Schema:

function artx_partner_schema() {
  $schema = array();

  $schema['artx_partner'] = array(
    'description' => 'The base table for artx_partner.',
    'fields' => array(
      'partner_id' => array(
        'description' => 'The primary identifier for a partnership.',
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'uid' => array(
        'description' => 'User identifier.',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'nid' => array(
        'description' => 'Node identifier.',
        'type' => 'int',
        'not null' => FALSE,
      ),
      'product_id' => array(
        'description' => 'Identifier for a product.',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'reqtime' => array(
        'description' => 'time stamp of the request',
        'type' => 'datetime:normal',
        'mysql_type' => 'datetime',
        'unsigned' => TRUE,
        'not null' => FALSE,
      ),
      'restime' => array(
        'description' => 'time stamp of the response to request',
        'type' => 'datetime:normal',
        'mysql_type' => 'datetime',
        'unsigned' => TRUE,
        'not null' => FALSE,
      ),
      'status' => array(
        'description' => 'the status of the request, pending(0)/AC(1)/Rej(2)',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
      ),
    ),
    'primary key' => array('partner_id'),
    'unique keys' => array('unique' => array('uid', 'nid', 'product_id')),
    'foreign keys' => array(
      'owner' => array(
        'table' => 'users',
        'columns' => array('uid' => 'uid'),
      ),
      'product_display' => array(
        'table' => 'node',
        'columns' => array('nid' => 'nid'),
      ),
      'product' => array(
        'table' => 'commerce_product',
        'columns' => array('product_id' => 'product_id'),
      ),
    ),
  );

  return $schema;
}
3

Unsigned attribute is not supported with datetime. Following code will create table for you.

function artx_partner_schema() {
  $schema = array();
  $schema['artx_partner'] = array(
    'description' => 'The base table for artx_partner.',
    'fields' => array(
      'partner_id' => array(
        'description' => 'The primary identifier for a partnership.',
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'uid' => array(
        'description' => 'User identifier.',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'nid' => array(
        'description' => 'Node identifier.',
        'type' => 'int',
        'not null' => FALSE,
      ),
      'product_id' => array(
        'description' => 'Identifier for a product.',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'reqtime' => array(
        'description' => 'time stamp of the request',
        'type' => 'datetime:normal',
        'mysql_type' => 'datetime',
        'not null' => FALSE,
      ),
      'restime' => array(
        'description' => 'time stamp of the response to request',
        'type' => 'datetime:normal',
        'mysql_type' => 'datetime',
        'not null' => FALSE,
      ),
      'status' => array(
        'description' => 'the status of the request, pending(0)/AC(1)/Rej(2)',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
      ),
    ),
    'primary key' => array('partner_id'),
    'unique keys' => array('unique' => array('uid', 'nid', 'product_id')),
    'foreign keys' => array(
      'owner' => array(
        'table' => 'users',
        'columns' => array('uid' => 'uid'),
      ),
      'product_display' => array(
        'table' => 'node',
        'columns' => array('nid' => 'nid'),
      ),
      'product' => array(
        'table' => 'commerce_product',
        'columns' => array('product_id' => 'product_id'),
      ),
    ),
  );
  return $schema;
}
2

Contrary to what others have said, your use of 'mysql_type' => 'datetime' is OK (and will work as you intend, provided the database is MySQL).

What triggers the PDOException is the use of the unsigned attribute. This was first pointed out in the answer Pushpinder Rana - +1 from me for that. However, I would not use his suggested schema verbatim: The line 'type' => 'datetime:normal' is not portable and will trigger a PDOexception if the code is ported to a site that uses a database engine that does not support datetime. (The fact that datetime is not supported by all is the reason for the change in Drupal 7.)

Use of the fallback type datetime:normal for the case when the DB is not MySQL will not work as you intend for all database engines. For portable code, you should use a fallback. I recommend that in this case, you provide a fallback type varchar for the case that the database is not MySQL.

(While this page suggests one uses text as a generic fallback, I prefer varchar for datetime. Both are variable length generic fields. On PostgreSQL they're equivalent, but on some other database engines, varchar is slightly faster for short strings such as datetime.)

I.e.: replace the relevant part of your schema with the following:

  'reqtime' => array(
    'description' => 'time stamp of the request',
    'type' => 'varchar',
    'mysql_type' => 'datetime',
    'not null' => FALSE,
  ),
  'restime' => array(
    'description' => 'time stamp of the response to request',
    'type' => 'varchar',
    'mysql_type' => 'datetime',
    'not null' => FALSE,
  ),
5
  • api.drupal.org/api/drupal/includes%21database%21schema.inc/… suggests text as the fallback. Or just omitting 'type' if it's always going to be used on MySQL anyway. However, given that the database is MySQL, why is the fallback causing an error? – Paul Gregory May 20 '14 at 7:05
  • This answer is omitting the 'unsigned' => TRUE, (which Pushpinder Rana first pointed out is not supported) without actually pointing out that this has changed. So whilst the revised schema will probably work, it will not be for the reason stated in this answer. – Paul Gregory May 20 '14 at 7:12
  • Entire section: 'mysql_type', 'pgsql_type', 'sqlite_type', etc.: If you need to use a record type not included in the officially supported list of types above, you can specify a type for each database backend. In this case, you can leave out the type parameter, but be advised that your schema will fail to load on backends that do not have a type specified. A possible solution can be to use the "text" type as a fallback. – Paul Gregory May 20 '14 at 7:19
  • I agree that a fallback is better for portable code, but if you're using a type that isn't supported in all engines then the portability of the code is already compromised. Reading the spec again I realise that text is a generic fallback suggestion, and your suggestion of varchar is probably better for a datetime fallback, depending on the database engine. – Paul Gregory May 20 '14 at 7:27
  • 1
    @PaulGregory, I agree with you regarding 'unsigned' => TRUE. Kudos to @PushpinderRana for spotting that. I've updated my answer. I've also added a note about varchar vs text. – Free Radical May 20 '14 at 7:41
0

Below field in your schema definition is causing problem...

  'reqtime' => array(
    'description' => 'time stamp of the request',
    'type' => 'datetime:normal',
    'mysql_type' => 'datetime',
    'unsigned' => TRUE,
    'not null' => FALSE,
  ),
  'restime' => array(
    'description' => 'time stamp of the response to request',
    'type' => 'datetime:normal',
    'mysql_type' => 'datetime',
    'unsigned' => TRUE,
    'not null' => FALSE,
  ),

Try removing type attribute.. It should support mysql_type attribute..

Drupal doesn't support field of type datetime. It's removed from Drupal 7.

Find more information below..

https://drupal.org/node/159605

1
  • Drupal 7 doesn't support type of datetime, but does support mysql_type of datetime. – Paul Gregory May 20 '14 at 7:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.