Tag: php

How to connect to the MS-SQL Server from the FreeBSD 13.0 Server by the CodeIgniter 3 PHP Framework

  • O.S.: FreeBSD 13.0-RELEASE-p4
  • Database: Microsoft SQL Server 2016 on Windows Server 2016
  • Web Server: Apache 2.4.48
  • PHP Version: 7.4.##
  • Required PHP modules: php74-pdo-7.4.##, php74-pdo_dblib-7.4.##

There are several ways to connect MS-SQL Server by PHP CodeIgniter Framework on FreeBSD, however, they are not easy. It is relatively simple on the Windows OS plateforms, but is not on my FreeBSD Server. Using FreeTDS and unixODBC by combining both (it can be odbc.ini only without freetds.conf but still FreeTDS driver is required) is also working well but needs some elbow greases to accomplish its goal (it will be my next posting subject).

Before the mssql module was removed from the earlier versions of PHP 7.X, sole use of the FreeTDS with the PHP mssql module through CodeIgniter Framework was working well without any trouble. However, with new PHP 7.X branches, it wasn’t, and needed to find the solution to move on newly upgraded server environments.

During struggling with the FreeTDS and the unixODBC‘s manuals, FAQ, and PHP online documents, I found that using PDO_DBLIB was pretty straightforward and also working fine with my FreeBSD server to connect to MS-SQL Server 2016 on the Windows server 2016.

Probably most of the users already installed the PDO module for PHP, so only pdo_dblib might need to be installed on user’s FreeBSD server additionally. The next step is just updating ‘database.php’ in the user’s CodeIgniter framework’s ‘application/config’ directory as below.

User needs to make sure to use ‘dbdriver’ as pdo, and the ‘hostname’ and ‘database’ must be empty. Those two must be set in the ‘dsn’ string.

$db['YOUR CONNECTOR NAME'] = array(
	'dsn'	=> 'dblib:host=SERVER ADDRESS:PORT#;dbname=DATABASENAME',
        'hostname' => '',
	'username' => 'Your DB User Name',
	'password' => 'DB User Password',
	'database' => '',
	'dbdriver' => 'pdo',
	'dbprefix' => '',
	'pconnect' => FALSE,
	'db_debug' => (ENVIRONMENT !== 'production'),
	'cache_on' => FALSE,
	'cachedir' => '',
	'char_set' => 'utf8',
	'dbcollat' => 'cp932_japanese_ci', //=> Your DB Locale
	'swap_pre' => '',
	'encrypt' => FALSE,
	'compress' => FALSE,
	'stricton' => FALSE,
	'failover' => array(),
	'save_queries' => TRUE
);

It is still in my question of its stability and performance concerns, but so far on my small business web application for the small office, it is running without any problem. I will keep reviewing and monitoring the system but in fear of possible broken system again by the future version upgrades of O.S., PHP, PHP Framework, or Database itself!

CI Session Configuration by using database

1. Create Table in Default database($this->db), PostgreSQL

CREATE TABLE ci_sessions
(
    id character varying(128) NOT NULL,
    ip_address character varying(45) NOT NULL,
    "timestamp" bigint NOT NULL DEFAULT 0,
    data text NOT NULL DEFAULT ' '::text,
    CONSTRAINT id_ip_address_pk PRIMARY KEY (id, ip_address)
)

2. Adjust session section in config.php file as below

    $config['sess_driver'] = 'database';
    $config['sess_cookie_name'] = 'ci_session';
    $config['sess_expiration'] = 0;   // Zero for terminating session by closing browser.
    $config['sess_save_path'] = 'ci_sessions'; // table name in default database
    $config['sess_match_ip'] = TRUE;    // by IP address
    $config['sess_time_to_update'] = 300;
    $config['sess_regenerate_destroy'] = FALSE;

3. Adjust cookie section in each config.php file in all main and sub-domain system as below if session needs to be shared with sub-domain system.

    $config['cookie_prefix'] = 'example_';
    $config['cookie_domain'] = '.example.com';
    $config['cookie_path'] = '/';
    $config['cookie_secure'] = FALSE;
    $config['cookie_httponly'] = FALSE;