hook_schema results in errors

Posted by Kartagis on February 4, 2013 at 7:58am

Hi,

In my .install file, I've got the following schema:

<?php
function tcmb_schema() {
  $schema['tcmb'] = array(
    'description' => 'Tcmb exchange rates',
    'fields' => array(
      'currency' => array(
        'description' => 'Tcmb currency codes',
        'type' => 'varchar',
        'length' => 3,
      ),
      'updated' => array(
        'description' => 'Tcmb exchange rates date',
        'type' => 'date',
      ),
      'buying' => array(
        'description' => 'Tcmb exchange buying rate',
        'type' => 'numeric',
        'precision' => 6,
        'scale' => 4,
      ),
      'selling' => array(
        'description' => 'Tcmb exchange selling rate',
        'type' => 'numeric',
        'precision' => 6,
        'scale' => 4,
      ),
    ),
  );
  return $schema;
}
?>

and in my hook_install implementation, I do

<?php
  if (!db_table_exists('tcmb')) {
    drupal_install_schema('tcmb');
  }
?>

However, I am getting the following errors when I enable it:

WD php: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that  [error]
corresponds to your MySQL server version for the right syntax to use near 'DEFAULT NULL COMMENT 'Tcmb exchange rates date', 
`buying` DECIMAL(6, 4) DEFAULT' at line 3: CREATE TABLE {tcmb} (
`currency` VARCHAR(3) DEFAULT NULL COMMENT 'Tcmb currency codes', 
`updated`  DEFAULT NULL COMMENT 'Tcmb exchange rates date', 
`buying` DECIMAL(6, 4) DEFAULT NULL COMMENT 'Tcmb exchange buying rate', 
`selling` DECIMAL(6, 4) DEFAULT NULL COMMENT 'Tcmb exchange selling rate'
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COMMENT 'Tcmb exchange rates'; Array
(
)
 in db_create_table() (line 2688 of /var/www/drupal-7/includes/database/database.inc).
Cannot modify header information - headers already sent by (output started at /usr/local/bin/drush7/includes/output.inc:37)                [warning]
bootstrap.inc:1212
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 &#039;DEFAULT NULL COMMENT &#039;Tcmb exchange rates date&#039;, 
`buying` DECIMAL(6, 4) DEFAULT&#039; at line 3: CREATE TABLE {tcmb} (
`currency` VARCHAR(3) DEFAULT NULL COMMENT &#039;Tcmb currency codes&#039;, 
`updated`  DEFAULT NULL COMMENT &#039;Tcmb exchange rates date&#039;, 
`buying` DECIMAL(6, 4) DEFAULT NULL COMMENT &#039;Tcmb exchange buying rate&#039;, 
`selling` DECIMAL(6, 4) DEFAULT NULL COMMENT &#039;Tcmb exchange selling rate&#039;
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COMMENT &#039;Tcmb exchange rates&#039;; Array
(
)
 in db_create_table() (line 2688 of /var/www/drupal-7/includes/database/database.inc).
Drush command terminated abnormally due to an unrecoverable error.

What am I doing wrong?

Regards,

PS: I first created my table manually, then copied what Schema module gave me to .install.

Comments

[FIXED]

Posted by Kartagis on February 4, 2013 at 2:48pm

After a bit of googling, turned out I have to use 'mysql_type' instead of 'type' when it comes to 'date'. It is also valid for 'datetime'.

Regards,

EDIT: I still get the following errors even though I had used 'mysql_type'

    Field tcmb.updated: no Schema type for mysql type date.
    tcmb.updated: no type for Schema type :normal.
    Field tcmb.updated: no Schema type for type .

Care to advice?

Thanks,

PS: My new schema is as follows:

<?php
function tcmb_schema() {
  $schema['tcmb'] = array(
    'fields' => array(
      'currency_name' => array(
        'type' => 'varchar',
        'length' => 50,
      ),
      'currency_symbol' => array(
        'type' => 'varchar',
        'length' => 3,
      ),
      'updated' => array(
        'mysql_type' => 'DATE',
      ),
      'buying' => array(
        'type' => 'numeric',
        'precision' => 6,
        'scale' => 4,
      ),
      'selling' => array(
        'type' => 'numeric',
        'precision' => 6,
        'scale' => 4,
      ),
    ),
  );
  return $schema;
}
?>