Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to use SQL Server connection in Laravel?

Tags:

I got a working project made in Laravel 3 that I have to switch to MsSQL Server (not my call though, sniff...) and I don't understand the Laravel configuration on this database type...

I changed the default inside database.php to this 'default' => 'sqlsrv' then I configured the host, database, username, password in the sqlsrv array but then I get this error message:

This extension requires the Microsoft SQL Server 2012 Native Client ODBC Driver to communicate with SQL Server`

After some research I found that we need PDO of SQLSRV, which I already have as version 5.4 and in my phpinfo I get this returned pdo_sqlsrv support : enabled so it seems all correct but at the same time it seems to ask for ODBC SQLSRV connections, does I really have to use that?

I would rather connect directly from the Laravel database connection... but even if I need it and I created some ODBC connection to the server, how do I put them inside my configuration? Here is what I have in my configuration (of course the my... is replace by my real details) :

'sqlsrv' => array(     'driver'   => 'sqlsrv',     'host'     => 'myServerIP',      'database' => 'myDatabase',     'username' => 'myUsername',     'password' => 'myPassword',     'prefix'   => '',  ), 

Any help would be greatly appreciated, the only thing I found close to my problem was this link from the Laravel forum: Laravel query on SQL Server but then it just stop without giving any configurations.

like image 487
ghiscoding Avatar asked Aug 13 '13 16:08

ghiscoding


People also ask

Can Laravel connect to SQL Server?

Laravel makes connecting with SQL Server database and running queries extremely simple out of the box. The SQL Server, MSSQL database configuration for your application is located at Laravel Project Root Folder, config/database.

Which PHP version is required for Laravel?

The Laravel framework has a few system requirements: PHP >= 5.4, PHP < 7. Mcrypt PHP Extension.

What is transaction in Laravel?

A transaction gives you the ability to safely perform a set of data-modifying SQL queries (such as insertions, deletions or updates). This is made safe because you can choose to rollback all queries made within the transaction at any time.

What is the SQL port?

The default port for the SQL Server database engine is the 1433. You can check the protocols by going to the SQL Server Configuration Manager. Then go to SQL Server Network Configuration > Protocols for MSSQLServer right-click TCP/IP and select Properties. Go to the IP Addresses page and look for the TCP Port.


1 Answers

That's what I did to connect my Laravel app to MS SQL Server:

  1. sudo apt-get update

  2. sudo apt-get install php5-sybase

if you try to install php5-mssql it will install php5-sybase anyway, it is “Sybase / MS SQL Server module for php5”.

this will make Laravel use the dblib driver instead of sqlsrv.

check out this file for reference Illuminate\Database\Connectors\SqlServerConnector.php.

  1. open this file: /etc/freetds/freetds.conf

under [global] section and under tds version = 4.2

add tds version = 8.0            (without ; semi column at the beginning of the line)

and add client charset = UTF-8 just under the above line            (without ; semi column at the beginning of the line)

This lets the driver encode all the data in utf-8 and avoid the strange characters in data

the file will look like this:

[global]          # TDS protocol version  ;       tds version = 4.2          tds version = 8.0          client charset = UTF-8 
  1. create file locales.conf in this directory /etc/freetds and past the following inside it:     (this allow correct parsing of dates with SQL Server).

[default]      date format = %Y-%m-%%I:%M:%S.%z  [en_US]      date format = %%%%I:%M:%S:%z%p      language = us_english      charset = iso_1 

  1. edit the php.ini file

first find the file with php -i | grep php.ini

then open the file and search for mssql.charset       (it will probably be like disabled like this         ; mssql.charset = "ISO-8859-1"     )

make it mssql.charset = "UTF-8"   <<make sure you remove the ; semi column to enable it>>

save and exit

  1. now restart your server (nginx or apache or php-fpm)

  2. of course you know that you need to set the database config to use sqlsrv by default 'default' => 'sqlsrv', and add your credentials.


OTHER METHODS:

you can use ODBC driver to connect

here’s a nice package https://github.com/ccovey/odbc-driver

like image 199
Mahmoud Zalt Avatar answered Sep 25 '22 16:09

Mahmoud Zalt