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!

Installing FreeBSD 11.3 & Xorg on Dell Optiplex 5040

Model: Dell Optiplex 5040
CPU: Intel(R) Core(TM) i5-6500 @ 3.2GHz
Memory: 8 GB
Video: Integrated Video Card (Intel HD530)
FreeBSD Version: FreeBSD 11.3-Release
— Versioon 12.1 caused the trouble of infinite rebooting when “i915kms.ko” module is loading during the boot process as of 03/05/2020 -> Bug Forum Link

  1. Downloaded FreeBSD 11.3 ISO Image from FreeBSD.org
  2. Made the installation USB flesh drive with the image ( Refus 3.9 was used)
  3. Installed FreeBSD on the PC by the USB flesh drive (UEFI mode used).
  4. Performed the common configuration such as Keyboard layout, Timezone, IP4 Network Interface, root password and adding common user accounts.
  5. After completion of FreeBSD Installation, rebooted the PC and signed in to the system with the root account for further process.
  6. Installed necessary ports by pkg (not compiling ports from source codes but downloading the pre-compiled binaries to save the time)
    1. pkg install xorg
    2. pkg install sudo
      1. Enable wheel group as sudo by using visudo
        1. Remove # in front of “# %wheel ALL=(ALL) ALL” line
        2. save and exit from visudo (ZZ as shortcut)
    1. installing windows manager of your choice such as lumina, gnome, kde, xfce, etc.
    2. pkg install firefox
    3. Optional ports: filezilla, libreoffice, gimp, inkscape, scribus (currently scribus-devel-1.5.5_6) , openjdk, netbeans, wordpress, php, python3.7 for the possible productive/development related ports if necessary
Added dbus_enable="YES" in /etc/rc.conf  
(You can use the below command at the shell prompt without editing rc.conf file directly if you want. I love "vi"!!!)
sysrc dbus_enable=YES
Used below command line to add DBUS UUID and started its service 
dbus-uuidgen > /etc/machine-id
service dbus start

Enabling the Integrated Intel HD 530 for Xorg https://wiki.freebsd.org/Graphics#Intel_Integrated_Graphics_.28aka_HD_Graphics.29 )

  1. Installed Intel HD 530 Graphic modules for Xorg with the root account
    • pkg install drm-kmod
  2. added below line in /etc/rc.conf
    • kld_list=”/boot/modules/i915kms.ko”
  3. Added User Group for Accelerated Video ( https://www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/x-config.html )
    • pw group mode video -m USERID || pw groupmod wheel -m USERID
  4. Added (actually created the file) below line to /boot/loader.conf to enable vt(4)
    • kern.vty=vt

Enabling Desktop Environment (Enabled only Lumina at this moment)

  1. Lumina Desktop Environment
    1. Installed Lumina Desktop Environment
      • pkg install lumina
    2. After installation, added below line in .xinitrc for starting its Desktop Environment
      • exec start-lumina-desktop
  2. GNOME Desktop Environment (for your reference. I haven’t done it yet)
    1. Install GNOME Desktop Environment
      • pkg install gnome3
    2. Add below line to /etc/fstab
      • proc /proc procfs rw 0 0
    3. Add below lines in /etc/rc.conf
      • dbus_enable=”YES” –> If you didn’t do this early
      • hald_enable=”YES”
    4. Add below line in .xinitrc
      • exec /usr/local/bin/gnome-session (or use shell command as echo “exec /usr/local/bin/gnome-session” > ~/.xinitrc )

Finally,entering startx at the shell, BOOM!

 

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;