Showing posts with label database. Show all posts
Showing posts with label database. Show all posts

Friday, September 23, 2011

Database Configuration for MSSQL

If you are connecting to a MSSQL database, first ensure that your php-mssql package is installed. A simple command can show you this:
$ rpm -qa | grep php-mssql
The output should look something like this:
php-mssql-5.3.8-4.el6.remi.x86_64
Next check that freetds is installed.
$ rpm -qa | grep freetds
The output should look something like this:
freetds-0.82-6.el6.x86_64
You should now be able to connect to your MSSQL database directly from the command line. Make sure to replace the server, user and password with valid information.
$ /usr/bin/tsql -S [mssql.servername.or.ip] -U [ValidUser]
locale is "en-US.UTF-8"
locale charset is "UTF-8"
Password: [password]
1>
Enter "quit" to exit your successful connection. If the tsql command doesn't return the 1> prompt, verify that you can get to your MSSQL server with the following command:
ssh [mssql.servername.or.ip] -p 1433
Also verify that your username and password are valid.
At this point you can set your database configuration to connect to the database.
    var $default = array(
        'driver' => 'mssql',
        'persistent' => false,
        'host' => 'mssql.server.or.ip',
        'login' => 'username',
        'password' => 'password',
        'database' => 'database',
        'prefix' => ''
    );
Through much trial and error I've discovered that if you connect directly to the server in this manner some commands, such as mssql_bind, mssql_execute, and mssql_init don't work. Now you could setup and execute stored procedure commands through mssql_query this isn't as secure.
Instead, set up a freetds configuration for the server, connect with that instead, and the commands will now work fine.
Edit /etc/freetds.conf and add the following block:
[ServerIdentifier]
        host = [mssql.servername.or.ip]
        port = 1433
        tds version = 8.0
Then use the ServerIdentifier you chose instead of the server name or ip in the database configuration host variable and you're all set.

Tuesday, September 13, 2011

Database Configuration for Oracle

If you are connecting to an Oracle database, first ensure that your php_oci8 extension is installed. A simple php page can show you this:
<?php
  phpinfo();
?>
View the page and you should see a heading "OCI8" and it should tell you the version and if it is enabled. Next ensure you can talk to the database. Another simple php file can do this for you. This page will list all tables in the database (code from Oracle's website)
<?php
  $conn = oci_connect('username', 'password', 'host/database');
  $stid = oci_parse($conn, 'select table_name from user_tables');
  oci_execute($stid);
  echo "\n";
  while (($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) != false) {
    echo "\n";
    foreach ($row as $item) {
      echo '  \n";
    }
    echo "\n";
  }
  echo '
'.($item !== null ? htmlentities($item, ENT_QUOTES) : ' ')."
'; ?>
Now that you know you can access your Oracle database via php, configure your CakePHP connection. Edit /app/config/database.php
var $default = array(
  'driver' => 'oracle',
  'connect' => 'oci_connect',
  'persistent' => false,
  'host' => 'host',
  'port' => 1521,
  'login' => 'username',
  'password' => 'password',
  'database' => 'host:1521/database',
  'prefix' => '',
);
And that's all there is to it.

Monday, July 25, 2011

Log Database Errors

First read here: Catch database errors before it’s too late

It's as simple as adding an onError function to your app_model.php file, and whenever there is a database error, the error in the database will be logged in your CakePHP log.

Edit /app/app_model.php and add the following function:
    public function onError() {
        $db = ConnectionManager::getDataSource('default');
        $err = $db->lastError();
        $this->log($err);
        $this->log($this->data);
    }

Wednesday, November 3, 2010

Database: Setup Reminders Table

Create reminders table
CREATE TABLE IF NOT EXISTS `reminders` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL,
  `drug_id` int(11) NOT NULL,
  `days_of_week` varchar(7) NOT NULL,
  `time` time NOT NULL,
  `created` datetime NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Database: Setup Carriers Table

Create carriers table
CREATE TABLE IF NOT EXISTS `carriers` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(40) NOT NULL,
  `extension` varchar(30) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Database: Setup Drugs Table

Create drugs table
CREATE TABLE IF NOT EXISTS `drugs` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `brand_name` varchar(50) NULL,
  `indications` varchar(255) NULL,
  `adverse_reactions` varchar(255) NULL,
  `counseling_points` varchar(500) NULL,
  `notes` varchar(255) NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Database: Setup Users Table

Create users table
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL auto_increment,
  `slug` varchar(40) NOT NULL,
  `username` varchar(30) NOT NULL,
  `password` varchar(40) NOT NULL,
  `first_name` varchar(40) NOT NULL,
  `last_name` varchar(40) NOT NULL,
  `email` varchar(100) NOT NULL,
  `email_verified` tinyint(1) NOT NULL default '0',
  `carrier_id` int(11) NOT NULL,
  `cell_number` varchar(10) NOT NULL,
  `modified` datetime NULL,
  `created` datetime NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `slug` (`slug`),  
  UNIQUE KEY `email` (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
The created and modified (or updated, either name works) fields will be filled in by CakePHP automatically, as long as they are set as DATETIME fields and default to NULL. Long field names are separated with underscores '_' so CakePHP can easily understand them.

CakePHP: Configuration

CakePHP uses the /app/tmp directory for a number of different operations. Model descriptions, cached views, and session information are just a few examples.

Make sure the /app/tmp directory and all subdirectories in the Cake installation are writable by the web server user (apache).
chown -R apache /app/tmp
The Security seeds need to be set for uniqueness. Edit /app/config/core.php Comment out the following lines, duplicate them and change their values:
Configure::write('Security.salt', 'DYhG93b0qyJfIxfs2guVoUubWwvniR2G0FgaC9mi');
Configure::write('Security.cipherSeed', '76859309657453542496749683645');
Upload file.

Create site database in MySQL.

Copy /app/config/database.php.default to /app/config/database.php

Edit /app/config/database.php Change values for 'host', 'login', 'password', and 'database' to match your database configuration.

Save and upload the file and test by browsing to the root page.